Object Variables

The variables you have seen so far have held data such as numbers and text. You can also create object variables to refer to objects such as worksheets and ranges. The Set statement is used to assign an object reference to an object variable. Object variables should also be declared and assigned a type as with normal variables. If you don't know the type, you can use the generic term Object as the type:

Dim objWorkbook As Object Set objWorkbook = ThisWorkbook MsgBox objWorkbook.Name

It is more efficient to use the specific object type if you can. The following code creates an object variable rng, referring to cell B10 in Sheet1, in the same workbook as the code. It then assigns values to the object and the cell above:

Sub ObjectVariable() Dim rng As Range

Set rng = ThisWorkbook.Worksheets("Sheet1").Range("C10") rng.Value = InputBox("Enter Sales for January") rng.Offset(-1, 0).Value = "January Sales" End Sub

If you are going to refer to the same object more than once, it is more efficient to create an object variable than to keep repeating a lengthy specification of the object. It also makes code easier to read and write.

Object variables can also be very useful for capturing the return values of some methods, particularly when you are creating new instances of an object. For example, with either the Workbooks object or the Worksheets object, the Add method returns a reference to the new object. This reference can be assigned to an object variable so that you can easily refer to the new object in later code:

Sub NewWorkbook()

Dim wkb As Workbook, wks As Worksheet

Set wkb = Workbooks.Add

Set wks = wkb.Worksheets.Add(After:=wkb.Sheets(wkb.Sheets.Count)) wks.Name = "January" wks.Range("A1").Value = "Sales Data" wkb.SaveAs Filename:="JanSales.xlsx" End Sub

This example creates a new empty workbook and assigns a reference to it to the object variable wkb. A new worksheet is added to the workbook, after any existing sheets, and a reference to the new worksheet is assigned to the object variable wks. The name on the tab at the bottom of the worksheet is then changed to January, and the heading Sales Data is placed in cell A1. Finally, the new workbook is saved as JanSales.xlsx.

Note that the parameter after the Worksheets.Add is in parentheses. Because you are assigning the return value of the Add method to the object variable, any parameters must be in parentheses. If the return value of the Add method were ignored, the statement would be without parentheses, as follows:

wkb.Worksheets.Add After:=wkb.Sheets(wkb.Sheets.Count)

With...End With

Object variables provide a useful way to refer to objects in shorthand, and are also more efficiently processed by VBA than fully qualified object strings. Another way to reduce the amount of code you write, and also increase processing efficiency, is to use a With...End With structure. The final example in the previous section could be rewritten as follows:

With wkb

.Worksheets.Add After:=.Sheets(.Sheets.Count) End With

VBA knows that anything starting with a period is a property or a method of the object following the With. You can rewrite the entire NewWorkbook procedure to eliminate the wkb object variable, as follows:

Sub NewWorkbook()

Dim wks As Worksheet With Workbooks.Add

Set wks = .Worksheets.Add(After:=.Sheets(.Sheets.Count)) wks.Name = "January" wks.Range("A1").Value = "Sales Data" .SaveAs Filename:="JanSales.xlsx" End With End Sub

You can take this a step further and eliminate the wks object variable:

Sub NewWorkbook() With Workbooks.Add

With .Worksheets.Add(After:=.Sheets(.Sheets.Count)) .Name = "January" .Range("A1").Value = "Sales Data" End With

.SaveAs Filename:="JanSales.xlsx" End With End Sub

If you find this confusing, you can compromise with a combination of object variables and with...End With:

Sub NewWorkbook()

Dim wkb As Workbook, wks As Worksheet

Set wkb = Workbooks.Add With wkb

Set wks = .Worksheets.Add(After:=.Sheets(.Sheets.Count)) With wks

.Name = "January" .Range("A1").Value = "Sales Data" End With

.SaveAs Filename:="JanSales.xlsx" End With End Sub

With...End With is useful when references to an object are repeated in a small section of code.

0 0

Post a comment