Visual Basic Concepts
As you use more and more objects in your Visual Basic applications, optimizing your use of those objects becomes more and more important. There are several key techniques to making the most efficient use of objects:
Use early binding.
Minimize the dots.
Use Set and With...End With.
Minimize cross-process calls.
In Visual Basic, referencing another application’s object in your code (by getting or setting an object’s property, or executing one of its methods) constitutes a cross-process call. Cross-process calls are expensive and you should try to avoid them if you are concerned about optimizing your application.
Early Binding vs. Late Binding
Visual Basic can use objects more efficiently if it can early bind them. An object can be early bound if you supply a reference to a type library containing the object, and you declare the type of the object:
Dim X As New MyObject
Dim X As MyObject Set X = New MyObject
Early binding enables Visual Basic to do most of the work of resolving the definition of the object at compile time rather than at run time, when it impacts performance. This also allows Visual Basic to check the syntax of properties and methods used with the object and report any errors.
If Visual Basic cannot bind an object early, it must bind it late. Late binding objects is expensive: At compile time you get no error checking, and each reference at run time requires at least 50% more work by Visual Basic.
Generally, you should always early bind objects if possible. The only times you should have to declare a variable As Object is if you do not have a type library for the object in question, or you need to be able to pass any kind of object as an argument to a procedure.
*For More Information* To learn more about early binding, see "Speeding Object References" in "Programming with Components."
Minimize the Dots
When referencing other applications' objects from Visual Basic, you use the dot syntax "." to navigate an object’s hierarchy of collections, objects, properties, and methods. It is not uncommon to create very lengthy navigation strings. For example:
' Refers to cell A1 on Sheet1 in the first workbook ' of an Microsoft Excel spreadsheet. Application.Workbooks.Item(1).Worksheets.Item_ ("Sheet1").Cells.Item(1,1)
In addition to being a rather lengthy string to type, this line of code is fairly difficult to read — and it is extremely inefficient.
When calling an object from Visual Basic, each "dot" requires Visual Basic to make multiple calls.
To write the most efficient applications, minimize the use of dots when referencing an object.
You can usually minimize the dots by analyzing the objects and methods available to you. For example, the above line of code can be shortened by removing the Item method (this is the default method for collections anyway, so you’ll rarely use it in code) and by using the more efficient Range method:
' Refers to cell A1 on Sheet1 in the first workbook ' of an Microsoft Excel spreadsheet. Application.Workbooks(1).Worksheets("Sheet1")_ .Range("A1")
You can shorten this even further by rewriting the code so that it refers to the active sheet in the active workbook, instead of a specific sheet in a specific workbook:
' Refers to cell A1 on the active sheet in the ' active workbook. Range("A1")
Of course, the above example assumes it’s OK to refer to cell A1 of any sheet that happens to be active.
Use Set and With...End With
Using the Set statement also allows you to shorten navigation strings and gives you a bit more control over your code. The following example uses the Dim****and Set statements to create variables that refer to frequently used objects:
Dim xlRange As Object Set xlRange = Application.ActiveSheet.Cells(1,1) xlRange.Font.Bold = True xlRange.Width = 40
Visual Basic provides the With...End With construct to set an implied object within code:
With Application.ActiveSheet.Cells(1,1) .Font.Bold = True .Width = 40 End With
Minimize Cross-Process Calls
If you are using a cross-process ActiveX component, you can't completely avoid making cross-process calls. However, there are several ways to minimize the number of cross-process calls you need to make. If possible, do not reference objects inside a For...Next loop. Cache values in variables and use the variables in loops. If you have to call a large number of methods on an object, you can greatly improve the performance of your application by moving the code into the component. For example, if the component is Word or Microsoft Excel, you can put a looping macro in a template in Word or a looping procedure into module in Microsoft Excel. You then call the macro or procedure from Visual Basic, which is a single call that launches a looping operation within the component.
If you are writing components, you can design the objects in the component to be efficient by reducing the cross-process calls required to perform an operation. For example, when you have several interrelated properties, implement a method with several arguments — one for each property. Calling the method requires a single cross-process call regardless of how many arguments it has, whereas setting each property requires a separate cross-process call. Likewise, if you anticipate that the component acting as a client will want to call your component in a loop (for example, to sum or average all the values in a list property), you can improve performance by providing methods that do the looping within your object and return the appropriate value.
*For More Information* Component creation is discussed in depth in the Component Tools Guide included with the Professional and Enterprise editions of Visual Basic.