Microsoft Office Tutorials and References
In Depth Information
9.5 Object Variables
objVar.AMethod( any required parameters )
Note that this syntax is quite similar to the syntax used to call an ordinary VBA subroutine or
function, except that here we require qualification with the name of the variable that points to the
object whose property or method is being called.
For instance, continuing the previous code, we can apply the CheckSpelling method to the row
referred to by rng as follows:
We could include the name of a custom dictionary as a parameter to this method.
9.5 Object Variables
To access a property of an object, or to invoke a method, we can generally take two approaches:
direct or indirect . The indirect approach uses an object variable—that is, a variable that has an
object data type—whereas the direct approach does not.
For instance, to set the Bold property of the Font object for the first row in the active worksheet,
we can take a direct approach, as in:
ActiveSheet.Rows(1).Font.Bold = True
Alternatively, we can assign an object variable. Here are two possibilities:
Dim rng As Range
Set rng = ActiveSheet.Rows(1)
rng.Font.Bold = True
Dim fnt As Font
Set fnt = ActiveSheet.Rows(1).Font
fnt.Bold = True
Object variables are more important than they might seem at first. The most obvious reason for
their use is that they can improve code readability when we need to refer to the same object more
than once. For instance, instead of writing:
ActiveSheet.Rows(1).Font.Bold = True
ActiveSheet.Rows(1).Font.Italic = True
ActiveSheet.Rows(1).Font.Underline = False
ActiveSheet.Rows(1).Font.Size = 12
ActiveSheet.Rows(1).Font.Name = "Arial"
we can use a Font variable to improve readability as follows:
Dim fnt As Font
Set fnt = ActiveSheet.Rows(1).Font
fnt.Bold = True
fnt.Italic = True
fnt.Underline = False
fnt.Size = 12
fnt.Name = "Arial"
Search JabSto ::

Custom Search