Excel VBA tutorial for beginners: Object Properties & Methods

this video includes two lectures that are a part of my online excel vba course the first one covers object properties and the second one is on methods I do warn you there's a bit of theory here but it's really important to understand the theory if you want to be able to write your code from scratch or to tweak the code that you find on internet forums properties and methods help you do things with objects the property is basically what an object has and a method is what you do with an object's it's what an object does now let's take a look at an analogy first you have let's say two real-life objects we have a car and we have a shoe cars have different properties two shoes right we have the color we have the size type engine and let's say age of the car then for the shoe we have some things that can be in common like color the science can be in common and then we have things like gender material and season for the shoe right so they have these predefined type of properties but the same thing applies to excel objects they have predefined set of properties now it's important to note that properties come after the object hierarchy so in the last lecture we took a look at that object hierarchy and we saw that they were separated by this dot so the properties come after those dots let's take a look at this example that would be like car dark color but this property might be too broad because you could have a color for the inside a color for the outside right so you might have more details instead you could have card interior that color is black now for your shoe you could say sure that gender is male but should heel that material is I put a question mark because I have no idea what the material for heels are okay but you get the point that you can have property details you get so let's take a look at this in Excel how that would look so let's look at one that doesn't have details that would be something like range a-1 address or range a1 dot value okay there's no detail you get the cell address or you get the cell value now you can have properties with details and this is something that's called when properties return and objects and this phrase that can be confusing to people like what does it mean that properties return objects it means this just think about the shoe we had shoe that heel that material right so the heel is a property of the shoe because shoes can have heels or no heels but a heel is also an object right it's kind of both now it's the same thing with this range the interior dark color or range that Afon to that color okay because that color belongs to the font here and this color here belongs to the interior here and if you look in Microsoft help you see that it's the interior property okay which returns an interior object okay and if this confuses you think about the heel that's what I do next thing to note is that properties can be read-only or they can be both we can read and write to them let's take a look at the first one what do you think this one is read-only or both just read-only right so you can't change the address of a cell you can just read the address of a cell so what about this one rhythm right this one this one read and write so you can get the font color you can get the color of the cell but you can also change the font color and change the color of the cell let's do some examples we can say range a1 dot value is active cell the address so we're using it on the other side of the formula so we're reading the address and we're putting it in range a 1 okay or we have range a1 dot interior color and we're setting it to red because in this case we're actually writing over it is the same here we're changing the color of the font to blue so that's basically it on properties just remember that properties come after the object hierarchy and properties can have details so that was the properties part now let's move on to methods a method is something that an object does let's go back to our shoe and car analogy and let's take a look at some of the methods that they could have so a car can start it can stop it can crash that was the only methods I could come up with a shoe you can put on a shoe you can take off a shoe right so these are things that you can do with these objects now the other thing is that methods can have additional arguments for additional information for example how do you want to start the car do you want to start it quickly or do you want to start it slowly right so quickly and slowly our arguments of the stop method let's say or the start method and the way you would write it in VBA is you have the card out stop right so you have your object method and then you have the argument but you don't have that argument separate it with a dot there are different ways of writing this so that's one way that you put a space and then you separate arguments with a comma just like you do in the formula bar or you can write it in this way where you have a name for each of these arguments and you write them with this colon and an equal sign okay like this one let's say how we start the car that arguments is called stop style and we would type it stop style column and the equal sign another important concept here is that methods can change properties okay how well with our core example if we use the crash method on the car we could change the size property of the car let's switch to higher supplies also Excel we said that methods can have arguments but they don't necessarily have arguments it really depends and a method right so just like it depended on the property if it had details or not it's the same with methods and these are already predefined so the clear method on the range object doesn't have any arguments right that's it you just say it's all clear and that's it now the delete method has arguments and it has one optional argument which is how do you want a shift do you want to shift expose the cells up do you want to shift them to the left the copy method so this is the copy of the sheet get out of their range that one has two optional arguments we can say copy before a sheet or after a sheet now these arguments are optional but they're also exclusive so you can't mention both of them otherwise you get an error let's do some more examples here if we say range a to delete and we want to specify how we want this delete to happen we specify it like this right so we just put a space and then we put the argument in there if we want to copy arranged is another sheet now I'm gonna copy arranged we can say where we want to copy to because the copy method has destination as its argument so it keeps everything on one line we don't have to say copy and then go somewhere else and say dot paste we just say don't copy and we mentioned the destination in its argument but we also have the ability to use the paste special method where we have all the arguments we have when we use in excel you know when we copy yourself go to paste special we can say if we want to paste values if we want paste formatting if we want to paste it as transpose and so on and those arguments is the arguments of the paste special method okay so in this case in this example I'm just saying paste as values and because it's the first one in the argument I just put a space there and mentioned the argument directly ok if you want to avoid that you just want to skip to let's say a last argument for example in this case where I'm copying a sheet I don't want to talk about the before I just want to say it should copy it after I can mention the name of the arguments I say after and I put the sheet name but if I wanted to write this the other way it will look like this this way I'm putting a comma first because I want to skip the before argument and go to the after argument okay so it's up to you which method you want to use when you are writing your VBA code some people prefer this method because they know what you're referring to and some people prefer this one now I use the mix of both methods sometimes because it's faster to go this way and I'm used to the Excel formulas I pretty much I guess most of the time I would go with this method okay but it's really up to you just pick the method that you feel most comfortable with now it's difficult to remember the properties and methods of all of these objects right because there's so many objects there's so many different properties and methods for them you are gonna end up remembering a few because you're gonna use them a lot but a lot of it you're not going to remember okay but there's nothing to worry there no one expects you to remember any of that you just have to know where to look to find them and that's what I'm going to show you in the next lecture I do go in more detail inside the course so if you're interested to find out more check out the link in the description of this video or go to X al plus calm slash courses [Music] [Music] [Music] [Music] [Music] [Music] you

Channel: Leila Gharani

Views: 92,452

Rating: 4.9276137 out of 5

Keywords: excel vba tutorial for beginners, vba methods, vba object color, what are properties in programming, what are methods in programming, vba method arguments, excel vba basics, vba properties, excel visual basic language, excel vba is fun, Advanced Excel Tutorials, excel visual basic, Leila Gharani, Excel 2016, excel vba, Excel 2013, Excel 2010, XelplusVis, Advanced Excel tricks, Excel online course, Excel tips and tricks, Excel for analysts

Id: uYHrzo2gzbo

Length: 13min 9sec (789 seconds)

Published: Thu Apr 05 2018