| Object variables occupy an important place in VBA for Excel, and later in this book when database structures are discussed, they assume even greater importance. It's possible to use VBA effectively without knowing how to use object variables, but it isn't easy. Understanding Object Variables Consider the notion of age as a variable. It's something you measure when you're dealing with information about people. If you're issuing them driver's licenses, you measure their age. If you hire them, you put their age at least, their date of birth in a file. If you're meeting them socially, you make a mental note with a rough estimate of their age. Age is a variable. It varies from person to person. Each person has an age: 75 years, 52 years, 16 months, 25 days, and so on. A single age is a value that the variable Age can assume. In VBA, if you're dealing with information about people, you might well declare a variable named Age: Dim Age As Single
Declaring Age in this way, as a single-precision number, means first that it is a number and not, say, a text string such as "Tom"; second it means that Age can have fractional values, such as 75.083 and 1.3. Having declared it, you can use it in statements such as this one: Age = 52.5
VBA knows nothing about ordinary variables such as Age, except that as declared here it's a number and it can have fractional values. It doesn't know that, in people, age values usually run from 0 to the 80s, or that it has to be at least 18 for its owner to vote in state and federal elections, or that society attaches certain attitudes to certain ranges of age. In contrast, an object variable in VBA is a special kind, and VBA knows quite a lot about it. An object variable can represent an Excel worksheet range, for example. I have to declare it as such; for example, Dim TheRange As Range
but after I've done so, VBA knows that TheRange can take on values such as A1:B17, or C4:C65536, or even D4 (yes, a single cell is a range as far as the object model is concerned). TheRange can take on any set of cells as its value. TheRange has some number of columns, some number of rows, possibly a font, the capability to be sorted, and all the other properties and methods that belong to ranges, no matter which cells they comprise.
In the prior section, you saw this code used: Dim SortRange As Range Set SortRange = ActiveSheet.Range(Cells(1, 1), Cells(17, 2))
It uses SortRange as an object variable, first declaring to VBA that it will represent range values (which are sets of cells) and then assigning a particular set of cells, A1:B17, to the object variable SortRange. By assigning the cell addresses to the object variable, and then using the Sort method on the range that the variable represents, the programmer keeps specific cell addresses out of the sort statement. In turn, that enables the programmer to use the sort statement over and over, on different sets of cells, just by assigning different ranges to the SortRange object variable. Setting Object Variables Notice how the range of cells the value is assigned to SortRange the variable. When you assign some value to a simple variable such as Age, all you need to do is mention the variable's name, enter an equal sign, and then enter the value you want the variable to have. In contrast, when you're working with an object variable, you have to use the special keyword Set. Then continue as with a simple variable: mention its name, provide an equal sign, and then enter the value you want the object variable to have. Here are a few more examples: Dim WS As Worksheet Set WS = ThisWorkbook.Worksheets("Sheet1") WS.Move Before:=Sheets(1) Dim TheName As Name Set TheName = ActiveWorkbook.Names("Revenues") TheName.RefersTo = "=Purchases!$A$1:$B$19" Dim TheChart As Chart Set TheChart = Workbooks("Quarter2.xls").Charts("Costs") TheChart.ChartType = xlLine
Declaring Object Variables Object variables can represent many Excel objects beside ranges. A person who writes VBA code for Excel often uses object variables to represent columns, rows, toolbars, charts, chart components such as axes and data series, worksheets, and so on. It's even possible to assign a module with VBA code to an object variable. In general, you can use VBA to declare an object variable representing anything in an Excel workbook that is in the object model. By doing so, you declare that it is an object variable. After you've declared an object variable, you cannot assign to it anything that's a different type. For example, if you have declared TheAxis to represent a chart axis Dim TheAxis As Axis
you cannot assign a value such as 2 to it. That is, this statement Set TheAxis = 2
would result in the compile error Type Mismatch. Objects in For Each Loops This chapter has already discussed For-Next loops and Do While loops in the section "Using Loops." Another sort of loop, the For Each loop, is handy when you're working with object variables. Like a For-Next loop, a For Each loop executes a specific number of times, once for each instance of a variable. In a For-Next loop, the variable is a simple one, often an Integer or Long Integer, that runs from a starting value to an ending value. In a For Each loop, that variable is an object variable that takes on each available instance of its object type. Suppose that you wanted to assign a name representing a year (2003, 2004, 2005, and so on) to each worksheet in a workbook named Annual Results. The following code shows one way to do that: Dim WS As Worksheet Dim WhichYear As Integer WhichYear = 2003 For Each WS In Workbooks("Annual Results").Worksheets WS.Name = WhichYear WhichYear = WhichYear + 1 Next WS
Two variables are declared: one object variable, WS, to represent worksheets and one simple variable, WhichYear, to represent integer numbers. WhichYear is initialized to 2003 and a For Each loop starts. The For Each statement, coupled with the Next statement, causes the object variable WS to take on, in turn, each worksheet in the collection of worksheets that belongs to the workbook named Annual Results. Each time through the loop, the object variable WS represents a different worksheet, and the name of that worksheet is set to the current value of WhichYear. Then WhichYear is incremented, the Next WS assigns the next worksheet to WS, and the loop repeats. When the final worksheet in the collection has been processed, the loop terminates. These elements are necessary for a For Each loop to work properly: The name of an object variable must follow the For Each keywords. The object variable must be followed by a collection, and the collection must be of the same type as the object variable. In the example given earlier, the object variable WS is declared with the Worksheet type. The collection of worksheets in a workbook are also of type Worksheet. It's usually necessary to specify what the collection belongs to. In the example, the collection of worksheets belongs to the workbook named Annual Results.
|
