Get a month of TabletWise Pro for free! Click here to redeem

Object Variables

8 minutes
Share the link to this page
You need to purchase the class to view this lesson.
One-time Purchase
List Price:  $139.99
You save:  $40
List Price:  د.إ514.18
You save:  د.إ146.92
List Price:  A$201.42
You save:  A$57.55
List Price:  ৳11,885.38
You save:  ৳3,396.06
List Price:  CA$190.22
You save:  CA$54.35
CHF 94.11
List Price:  CHF 131.76
You save:  CHF 37.64
List Price:  kr922.75
You save:  kr263.66
List Price:  €123.88
You save:  €35.39
List Price:  £110.90
You save:  £31.68
List Price:  HK$1,085.28
You save:  HK$310.10
List Price:  ₹10,526.61
You save:  ₹3,007.82
List Price:  RM597.19
You save:  RM170.64
List Price:  ₦54,241
You save:  ₦15,498.53
List Price:  kr1,319.67
You save:  kr377.07
List Price:  NZ$212.70
You save:  NZ$60.77
List Price:  ₱6,925.45
You save:  ₱1,978.84
List Price:  ₨23,257.07
You save:  ₨6,645.35
List Price:  S$194.74
You save:  S$55.64
List Price:  ฿4,386.84
You save:  ฿1,253.47
List Price:  ₺960.89
You save:  ₺274.56
List Price:  R2,348.19
You save:  R670.96
Already have an account? Log In


Hey guys, welcome back. So in this video we're going to discuss a different type of variables. And this type of variables is called object variables. So object variables are a different type of variables. And they differ from normal variables that we've discussed on the last couple videos in the fact that they do not hold values or texts, but they actually hold objects. And by objects, I mean a workbook or worksheet or a chart or a range or some cells.

Okay, so all these are objects. And the two main advantages of using object variables is that number one, they make you type less code. And number two, they can make your code runs slightly faster, although the second advantage does not happen often, but it does happen. Also, when you create object variables, and because they hold objects, then the object variable will possess all the methods and properties of that particular object that it holds. So for example, if an object variable holds a range, then it will have all the methods and properties of a range that we've discussed on previous videos. If it holds the worksheet, then it will have all the methods and properties of a worksheet, and so on and so forth.

So to create an object variable, you just write dim. And then let's say I want to create an object variable of the type of a range so its type is going to be a range object. So I'm going to write dim my range that's the name of the object variable as range. For example. Now, after creating the object variable, and as we did with normal variables as well, you need to assign an object to that object variable, right, because that's what we did with normal valuables when we created them, we assigned values to them. But now instead of assigning values, we're going to assign objects to these object variables.

So let's say I want to assign this table here, because my object is a range, that's the type of it. I'm going to assign the range of this table here that I've got here on the left, from a one to C 16, to that range object. So to do that, I'm going to write set you got to write set my range equals worksheets data dot range, a one to C 16. So I'm actually going to assign the range a one to C 16. In the worksheet called data to The object variable that is called my range. And now I've given it some sort of a nickname.

So now I can actually refer to that range as a Molly range, instead of referring to it as worksheets data dot range A one, two c six, or even range a one two c six if I've got the worksheet data selected, so this actually makes it easier for me to do stuff with that range right? Because I can just write Molly range dot and then Excel has recognized that this is a range object and it has the methods and properties of a range object. So I can write my range dot font, for example. So you can see here starting to have the methods and properties of a range object dot color equals VB red, for example. So if I run this code, the, the ranges font is going to turn to red as you can See here. So you can see here that using object variables makes me type less code because now I've given this object a nickname, I can just refer to it as my range, as opposed to referring to it with the worksheet that is in and then referring to the range.

So now you could see that using object variables makes me type less code, because I can just deal with that range by just typing my range instead of typing this whole code. So that makes it easier for me. There is a type of objects as well, that is school just object. And this object type is actually just a generic object. And it can work with any type of object so it can work if you're trying to set my range equals to a worksheet, or if you're trying to set it equal to a range. So basically, what Whatever variable use with it, you can assign any type of object to it.

However, this is rarely used, because it's normally used when you don't know the type of object is going to be assigned to your object variable, but this rarely happens. So you will often know actually the type of object that you're going to try to assign to your object variable. So you'd normally see people using the my ranges range or dim my workbook as workbook and so on and so forth. Okay, guys. So to illustrate the advantage of using object variables further, I've got here a simple set procedure. And what it's going to do is that it's going to copy this table from the data sheet to the report sheet here.

So if we run this code, you can see here that the table has been copied. However, I can actually reduce the amount of code that I type if I declare some object variables So if I dim data sh T, as worksheets, for example, and then set data sh t to be equal to worksheets data, and also dim RPT sheet as worksheet, and set RPT sheet to be equal to worksheets report, I can now replace every instance of worksheets data with just data as HD. So if I just copy data sh t here, and just I can just replace that, and also every instance of worksheets report with RPT sheet. So I can just do that as well. And if I run the macro is still going to run and do the same thing. So you can see here I've saved myself here, some code typing, and this also can make my code run faster.

And imagine if You've got more lines of code that reference the datasheet and report sheet, it will be much easier to just refer to them as they say HD and RPT HD rather than worksheets, data and worksheets report. So this does save you time. Another thing that I actually faced at work is for example, if I have a worksheet that has a weird name that is hard to memorize, so it would have a bunch of letters and a bunch of numbers. So this name is hard to memorize. So actually, it is easier and I'm just writing that as a common so as not to get an error from Excel VBA. you'd write dim x as worksheet, for example, and then set x to be equal to worksheets, this weird name so it's easier to do that then have to memorize this kind of name when you type it in your code over and over again.

So when you do that, you can just refer to this worksheet with this weird name as just x. So that is much simpler. Okay, so that's it guys for object variables. You're gonna see me use them a lot in our code in the upcoming lessons. Thank you very much watching this video and I'll see on the next one

Sign Up