For a macro language, VBA has sophisticated data types and the means to hide or share data in the code. For instance, use the Public statement to declare a variable that is available to all modules in all applications that support VBA. There is also a Private statement to declare variables that are available only to procedures within the same module. The Static statement defines variables inside procedures that retain their value as long as a macro is running. These procedures can remember their state the last time they were called.
As far as performance is concerned, always try to use the smallest, most specific data type to get the best speed of execution. The Variant data type is very flexible because it can have the attributes of both a number and a string, but code that accesses Variant data frequently runs slower than code that uses a simpler data type such as String or Integer.
Visual Basic for Applications also has an Object data type. It can refer to any VBA object, such as a worksheet, cell, chart, or even objects in other applications. One example would be Word's WordBasic object. Object variables can also reduce the work needed to refer to a lengthy identifier in code. For instance, both ways of referring to the Wiz1 dialog sheet are valid in the code below, but which is better?
Const GWizBookName = "PERSONAL.XLS"
Dim GWiz1 As Object
Sub Main()
' Initializations...
' Here's one way of referring to an edit control in the Wiz1 dialog:
Set GWiz1 = ThisWorkbook.DialogSheets("Wiz1")
GWiz1.[RangeEdit].Caption = GDataRange.Address
' Here's another way:
Workbooks(GWizBookName).DialogSheets("Wiz1").[RangeEdit].Caption _
= GDataRange.Address
Example 4. Two kinds of variable reference styles
Actually, there are many more ways than just two!. The first method, using the ThisWorkbook property, is the best way to reference contained sheets in a workbook. ThisWorkbook returns the workbook containing the running code regardless of whether the workbook is an add-in or an ordinary workbook users can edit.
Besides making the code more readable, using ThisWorkbook makes code independent of:
· Whether it is in the active workbook
· Whether the workbook is an add-in
· The name of the workbook (If code references Workbooks("PERSONAL.XLS") as was done above, when the user renames the workbook the code will break).
This will become important if code is installed in PERSONAL.XLS, the personal macro workbook. When this code runs the user's data will be in a different workbook from the one where the dialog sheets and macros are. Using ThisWorkbook is also more maintainable because there is only one hard-coded reference to the Wiz1 dialog. Renaming the dialog affects only one line of code. An explicit object reference (the second method above) should be used only when code needs to refer to something in a different workbook and the names of both workbooks won't change.
Another consideration in working with variables is performance. With VBA objects, shorter references are better. The reason for this is that each property or method in a variable reference counts as an IDispatch transaction. IDispatch is one of the interfaces in Object Linking and Embedding (OLE). In the reference
ActiveSheet.Worksheets(1).Range("A1").Value,
1 2 3 4
there are four IDispatch transactions. The more object accessors or "dot" operators there are in code, the slower it will be. An object variable improves performance whenever a variable reference is used multiple times, as in the example below.
Dim GWiz3 As Object
Sub Main()
'Use an object variable since we will be accessing this a lot.
Set GWiz3 = ThisWorkbook.DialogSheets("Wiz3")
...
End Sub
Sub OptionBinCount_Click()
GWiz3.[BinWidthHelp].Visible = False
GWiz3.[BinCountHelp].Visible = True
GWiz3.Focus = GWiz3.[EditBinValue].Name
End Sub
If the same reference is needed for just a few lines of code rather than in many places, the With statement is a useful way of "factoring" an object reference. Once inside the With...End With block an object reference is re-used for each statement. So, instead of writing
ActiveDialog.Buttons.[ButtonWiz3Cancel].DismissButton = True
ActiveDialog.Buttons.[ButtonWiz3Back].DismissButton = True
ActiveDialog.Buttons.[ButtonWiz3Next].DismissButton = False
ActiveDialog.Buttons.[ButtonWiz3Finish].DismissButton = False
Save six IDispatch transactions (and a lot of typing!) by writing
With ActiveDialog.Buttons
.[ButtonWiz3Cancel].DismissButton = True
.[ButtonWiz3Back].DismissButton = True
.[ButtonWiz3Next].DismissButton = False
.[ButtonWiz3Finish].DismissButton = False
End With
Loops containing object references should always be factored. The fastest way to set properties for a large number of cells is to enclose the loop in a With...End With block:
With ActiveWorkbook.Sheets(2)
For c = 1 to 1000
.Cells(c, 1) = c
Next c
End With