Microsoft Office Tutorials and References
In Depth Information
User-Defined Data Types
This routine enters a value into cell A1 of Sheet1 on the active workbook, applies some formatting,
and changes the fonts and size. That’s a lot of typing. To reduce wear and tear on your fingers
(and make your code more efficient), you can condense the routine with an object variable:
Sub ObjVar()
Dim MyCell As Range
Set MyCell = Worksheets(“Sheet1”).Range(“A1”)
MyCell.Value = 124
MyCell.Font.Bold = True
MyCell.Font.Italic = True
MyCell.Font.Size = 14
MyCell.Font.Name = Cambria
End Sub
After the variable MyCell is declared as a Range object, the Set statement assigns an object to
it. Subsequent statements can then use the simpler MyCell reference in place of the lengthy
Worksheets(“Sheet1”).Range(“A1”) reference.
After an object is assigned to a variable, VBA can access it more quickly than it can a
normal, lengthy reference that has to be resolved. So when speed is critical, use object
variables. One way to think about code efficiency is in terms of dot processing. Every
time VBA encounters a dot, as in Sheets(1).Range(“A1”) , it takes time to resolve
the reference. Using an object variable reduces the number of dots to be processed.
The fewer the dots, the faster the processing time. Another way to improve the speed
of your code is by using the With-End With construct, which also reduces the number
of dots to be processed. I discuss this construct later in this chapter.
The true value of object variables becomes apparent when I discuss looping later in this chapter.
User-Defined Data Types
VBA lets you create custom, or user-defined, data types. A user-defined data type can ease your
work with some types of data. For example, if your application deals with customer information,
you may want to create a user-defined data type named CustomerInfo :
Type CustomerInfo
Company As String
Contact As String
RegionCode As Long
Sales As Double
End Type
Search JabSto ::

Custom Search