From the course: Excel: Learning VBA

Define and use object variables - Microsoft Excel Tutorial

From the course: Excel: Learning VBA

Start my 1-month free trial Buy for my team

Define and use object variables

“

- [Instructor] Earlier in this course, I described most of the data types that are available to you in Excel VBA. I mentioned, but didn't go into any detail over the object data type. So I'll talk about it now. An object variable refers to an Excel object such as a workbook, worksheet, or cell range, and I'll show you how to work with them in the Visual Basic Editor. My sample file is 02_07 object variables, and you can find it in the chapter two folder of the Exercise files collection. In this workbook, I have three worksheets, and we're not going to work with the data. Instead, we're going to focus on a property of the worksheets themselves, and that will be the sheet tab which I'm clicking to move between the different worksheets. I'm going to go to the Visual Basic Editor by pressing Alt F11. And here, I have a single, very short subroutine. And as the name implies, it will change the tab color of a worksheet. The first line is to declare a worksheet object variable called Wks, and that is the shorthand for a worksheet. You can use Wbk or Wkbk for a workbook. The next line assigns a value or object to the worksheet object variable, and that is this workbook and then we look in the worksheets collection and then we want to affect the workbook called January sales. And then below that, we will have that worksheets tab and change its color using a built-in keyword, vbRed. So let's go ahead and run the subroutine. I already have the insertion point flashing in there. So I'll press F5. And if you look at the bottom left, you can see that the sheet tab for January sales is now red with white text. So if I press Alt F11 to move over, you can see it there. And when I click it, we get the name in green and then the red is a little bit more muted in the background. I can also edit the code, so it will refer to a different worksheet. So Alt F11 again. And this time, instead of January sales, I'll do February or Feb, F E B sales. And just to make the change different, I will replace vbRed with vbBlue. I'll press F5 to run. February sales is blue, so I'll click there. And then January sales is red because we changed it away and didn't affect it at all, but we have February sales with a blue tab. And if I click March sales, you can see the full color for both the January and the February worksheet tabs.

Contents