Earlier in this course, you see most of the data types available to you in Excel VBA. The Object data type was skipped then. An Object variable refers to an Excel object such as a workbook, worksheet, or cell range.
- Working with values such as text strings or intergers can be straightforward in Excel. However, if you're a new programmer, then working with object variables might be unfamiliar to you. In this movie I will show you how to work with object variables, which can represent cell ranges or worksheets or even workbooks. My sample file is the object variables workbook, and you can find it in the chapter two folder of the exercise files collection. This workbook contains three worksheets, although we won't be working with their data in this case.
What I want to do, though, is change the color of the sheet tabs for a couple of the worksheets. To do that I'll use VBA code, and I'll press Alt + F11 to move to the visual basic editor. In module one I have a ChangeWksTabColor subroutine, and the first line, you see, is a variable declaration that declares wks as a worksheet. So that means I can assign one of the worksheets, from all of the worksheets within the workbook to that variable, and that's what I do on the next line.
However, instead of using the Dim keyword, I use Set. Set is what you use for object variables. So here I have wks = ThisWorkbook.Worksheets, and then the worksheet name JanSales in parenthesis and double quotes, because it's a string. What I do next then is I use the Tab.Color properties of the worksheet object and assign it the color red. There are a number of built in colors, and those start with the letters vb, so I'm using visual basic red.
I'll click inside of my subroutine here, and press F5 to run it. Doesn't look like anything happened, but that's because we didn't activate the worksheet. I'll press Alt + F11 to move back to the workbook, and you see that there is a red tint to JanSales at the bottom, and if I click away to another worksheet, you can see that the red color comes out. If we want to, we can reassign the wks object variable to another worksheet.
So I'll press Alt + F11 to move back and I will type below the vbRed line, and here I will use another sub-command to assign the February sales worksheet to it. So I'll type Set wks = ThisWorkbook.Worksheets(*FebSales*) and then below that I'll type wks.Tab.Color = vbBlue.
Everything looks good, press F5 again. Press Alt + F11 to move back to the workbook, and we see FebSales has a blue tint to it, and when I click MarchSales, we'll see that in fact it does have a blue sheet tab. As you do more advanced work in Microsoft Excel, you will find yourself referring to workbooks, worksheets and cell ranges a lot. Declaring object variables allows you to work with them inside of Excel VBA.
- Working in the Visual Basic Editor
- Adding code to a macro
- Creating, exporting, and deleting code modules
- Declaring and using variables
- Managing variable scope
- Defining arrays
- Managing workbooks and worksheets with VBA
- Repeating tasks with loops
- Debugging VBA code
- Cutting, copying, and pasting cell data
- Running and triggering event procedures