Microsoft Office Tutorials and References
In Depth Information
9.5.1 The With Statement
9.5.1 The With Statement
In fact, VBA provides a With statement to handle just the situation in the previous example,
which could be written as follows:
Dim fnt As Font
Set fnt = ActiveSheet.Rows(1).Font
With fnt
.Bold = True
.Italic = True
.Underline = False
.Size = 12
.Name = "Arial"
End With
The general syntax of the With statement is:
With object
' statements go here
End With
where the statements generally refer to the object, but do not require qualification using the
object's name, as in the previous example.
9.5.2 Object Variables Save Execution Time
The main reason that objec t variables are important is not to improve readability, but to save
execution time. In particular, to execute each of the five lines in the first version of the previous
code, VBA needs to resolve the references to the various Excel objects ActiveSheet, Rows(1), and
Font. That is, VBA needs to "climb down" the Excel object model. This takes time.
However, in the code that uses an object variable of type Font, VBA only needs to resolve these
references once. Therefore, the second version runs much more quickly. This difference can be
very noticeable when there are hundreds or thousands of references to resolve.
9.5.3 An Object Variable Is a Pointer
There are some very important differences between object variables and nonobject variables, such
as those of type Integer, Single, or String. As we have mentioned, a nonobject variable can be
thought of as a name for a location in the computer's memory that holds some data. For instance,
in the code:
Dim iVar As Integer
iVar = 123
the variable iVar is a 4-byte memory location that holds the integer value 123. This can be
pictured as in Figure 9-2 . (Actually, the 4-byte memory location holds the value 123 in binary
format, but that is not relevant to our discussion.)
Figure 9-2. Integer variables in memory
 
Search JabSto ::




Custom Search