Drew Fletcher
Microsoft Corporation
November 1995
Optimization is not a single set of tricks or techniques. It's not a simple process you can tack on at the end of your development cycle: "There, it works. Now I'll speed it up and make it smaller." To create a truly optimized application you must be optimizing continuously as you develop. You choose your algorithms carefully, weighing speed against size and other constraints; you form hypotheses about what parts of your application will be fast or slow, large or compact; and you test those hypotheses as you go.
You also keep in mind that optimization is not always completely beneficial. Sometimes the changes you make to speed up or trim down your application can result in code that is hard to maintain or debug. Moreover, some optimization techniques fly in the face of structured-coding practice, which may cause problems when you try to expand your application in the future or incorporate it into other programs—not to mention aggravating fellow programmers if you're working as part of a team.
You can waste a lot of time optimizing the wrong things. This is particularly true in the Microsoft® Visual Basic® version 4.0 programming system. That's because so much goes on "behind the scenes" in Visual Basic 4.0 and because there's no true profiler available that would enable you to immediately find the proverbial 10 percent of your code that takes 90 percent of the time or uses 90 percent of the space.
Nevertheless, you can learn a lot by simply stepping through your code and thinking carefully about what's actually happening. It sounds obvious, but I've discovered things in my own code that "seemed like a good idea" when I coded them but that turned out to be amazing memory or CPU hogs in practice. You often forget, for example, that setting properties causes events to occur, and if there's a lot of code in those event procedures an innocuous line of code can cause a tremendous delay in your program.
It's sometimes useful to think in terms of an "optimization budget." You don't have time to optimize everything, so where do you spend your time to get a maximum return on your investment? Obviously, you want to focus on the areas that seem to be the slowest or fattest. To maximize the results of your efforts, however, you also want to concentrate on code where a little work will make a lot of difference.
The bodies of loops are a good place to start. Whenever you speed up the operations inside a loop, that improvement is multiplied by the number of times the loop is executed. For loops with a large number of iterations, even one fewer string operation in the body can make a big difference.
Sometimes things aren't worth optimizing. For example, writing an elaborate but fast sorting routine is pointless if you're sorting only a dozen items. I've seen programs that sort things by adding them to a sorted list box and then reading them back out in order. In absolute terms this is horribly inefficient, but if there aren't a lot of items it's just as quick as any other method, and the code is admirably simple (if a bit obscure).
In other cases optimization is wasted effort. If your application is ultimately bound by the speed of your disk or network, there is little you can do in your code to speed up things. Instead, you should consider ways to make the delays less problematic for your users. Some examples are including progress bars to inform users that code isn't simply hung, caching data so users see delays less often, and yielding so they can use other programs while they wait.
You can optimize your program for a variety of characteristics:
Rarely can you optimize for multiple characteristics. Typically, an approach that optimizes for size compromises on speed; likewise an application that is optimized for speed is often larger than its slower cousin. For this reason, recommendations in one area may conflict with those in another.
Unless you're performing such operations as generating fractals, your applications are unlikely to be limited by the actual processing speed of your code. Typically other factors, such as video speed, network delays, or disk activities, are the limiting factor in your applications. For example, when a form is slow to load, the cause is often the many controls or graphics on the form rather than slow code in the Load event for the form. However, you may find points in your program at which the speed of your code is the gating factor. When that's the case, the following techniques can help you increase the real speed of your code:
The default data type in Visual Basic is Variant. This is handy for beginning programmers and for applications in which processing speed is not an issue. However, if you are trying to optimize the real speed of your application you should avoid Variant. While most operations involving the Variant data type in Visual Basic version 4.0 are faster than were their counterparts in Visual Basic version 3.0, operations involving other simple data types are still faster than Variant equivalents. A good way to avoid Variants is to use Option Explicit (turn on the Require Variable Declaration option in the development environment), an approach that forces you to declare all your variables.
For arithmetic operations avoid Currency, Single, and Double variables. Instead, use Byte, Integer, and Long integer variables whenever you can, particularly in loops. These are the CPU's native data types, so operations on them are very fast. It's surprising how much of your code you can write using only Integer variables. What's more, you can often tweak things so you can use integers when a floating-point value otherwise would be required.
For example, if you always set the ScaleMode property of your Form objects and PictureBox controls to either Twip or Pixel, you can use integers for all the size and position values for controls and graphics methods. In a similar vein, it's often possible to modify calculations so they can be performed entirely with integers. And don't forget the integer-division operator: \. As an example, Microsoft QuickBasic® includes a sample program that generates the Mandelbrot set entirely using integer math. If you need to do math with decimal values, use Double rather than Currency. Double is faster, particularly on machines with math coprocessors, that is, most machines running the Windows NT™ or Windows 95 operating system.
Variables are generally 10 to 20 times faster than properties of the same type. Never get the value of any given property more than once in a procedure unless you know the value has changed. Instead, assign the value of the property to a variable and use the variable in all subsequent code. For example, code like this is very slow:
For i = 0 To 10
picIcon(i).Left = picPallete.Left
Next i
Rewritten, this code is much faster:
picLeft = picPallete.Left
For i = 0 To 10
picIcon(i).Left = picLeft
Next i
In the same way, code like the following,
Do Until EOF(F)
Line Input #F, nextLine
Text1.Text = Text1.Text + nextLine
Loop
is much slower than code like this:
Do Until EOF(F)
Line Input #F, nextLine
bufferVar = bufferVar + nextLine
Loop
Text1.Text = bufferVar
However, the next line of code does the equivalent job and is even faster:
Text1.Text = Input(F, LOF(F))
That's yet another example of a better algorithm being the best optimization.
Collections of objects is a Visual Basic for Applications feature that is new in Visual Basic 4.0. Collections are very useful, but for the best performance you need to use them correctly:
Collections enable you to iterate through them using an integer For...Next loop. However, the new For Each syntax is more readable and in many cases executes faster. The For Each iteration is implemented by the creator of the collection, so the actual speed varies from one collection object to the next. However, For Each generally is never slower than For...Next because the simplest implementation is a linear For...Next style iteration. In some cases the implementor may use a more sophisticated implementation than linear iteration, so For Each can be much faster.
It is quicker to Add objects to a collection if you don't use the before and after parameters. These parameters require that Visual Basic find another object in the collection before it can add the new object.
When you have a group of objects of the same type, you can choose to manage them in a collection or an array; if the objects are not all of the same type, a collection is your only choice. From a speed standpoint, your choice depends on how you plan to access the objects. If you can associate a unique key with each object, then a collection is the better choice. Using a key to retrieve an object from a collection is always faster than traversing an array sequentially. However, if you do not have keys and so will always have to traverse the objects, an array is the better choice. Arrays are faster to traverse sequentially than collections.
Because of the graphical nature of Microsoft Windows, the speed of graphics and other display operations contributes greatly to the perceived speed of your application. In many cases, you can make your application seem faster simply by making your forms repaint faster—even if the actual speed of your application hasn't changed at all. To increase the perceived speed of your application, you can:
Unless you are using graphics methods (Line, PSet, Circle, and Print) you should set ClipControls to False for the form and for all Frame and PictureBox controls. When ClipControls is False, Visual Basic does not do the extra work required to avoid overpainting controls with the background before repainting the controls themselves. On forms containing a lot of controls, the resulting speed improvements are significant.
The optimized setting for the AutoRedraw property depends on what is being displayed. If you can quickly redraw the contents of the form or picture control using graphics methods, you should set AutoRedraw to False and perform the graphics in the Paint event. If you have a complicated display that changes only once in a while, you should set AutoRedraw to True and allow Visual Basic to do the redrawing for you. Note, however, that when AutoRedraw is True, Visual Basic maintains a bitmap it uses to redraw the picture, and this bitmap can take up a considerable amount of memory.
Image controls always paint faster than PictureBox controls. Unless you need some of the capabilities unique to PictureBox controls (such as dynamic data exchange [DDE] and graphics methods) you should use Image controls exclusively.
As for graphics methods themselves, a little experimentation will demonstrate that the Line method is much faster than a series of PSet methods. Avoid using the PSet method, and batch up the points into a single Line method.
Every repaint is expensive. The fewer repaints Visual Basic must perform, the faster your application will appear. One way to reduce the number of repaints is to make controls invisible while you are manipulating them. For example, suppose you want to resize several list boxes in the Resize event for the following form:
Sub Form_Resize ()
Dim i As Integer, sHeight As Integer
sHeight = ScaleHeight / 4
For i = 0 To 3
lstDisplay(i).Move 0, i * sHeight, ScaleWidth, sHeight
Next
End Sub
This approach creates four separate repaints, one for each list box. You can reduce the number of repaints by placing all the list boxes within a PictureBox control and by hiding the PictureBox before you move and size the list boxes. Then, when you make the PictureBox visible again, all the list boxes are painted in a single pass:
Sub Form_Resize ()
Dim i As Integer, sHeight As Integer
picContainer.Visible = False
picContainer.Move 0, 0, ScaleWidth, ScaleHeight
sHeight = ScaleHeight / 4
For i = 0 To 3
lstDisplay(i).Move 0, i * sHeight, ScaleWidth, sHeight
Next
picContainer.Visible = True
End Sub
Often the perceived, or apparent, speed of your application has little to do with how quickly it actually gets through the meat of its task. To the user, an application that starts up rapidly, repaints quickly, and provides continuous feedback feels "snappier" than an application that just "hangs up" while it churns through its work. You can use a variety of techniques to give your application that "snap":
Hiding forms instead of unloading them is a trick that has been around since the early days of Visual Basic version 1.0, but it is still effective. The obvious downside to this technique is the amount of memory the loaded forms consume, but the technique can't be beat if you can afford the memory cost and if making forms appear quickly is of the highest importance.
You also can improve the apparent speed of your application by preloading data. For example, if you need to go to disk to load the first of several files, why not load as many of them as you can? Unless the files are extremely small, the user is going to experience a delay anyway. The incremental time spent loading the additional files probably will go unnoticed, and you won't have to delay the user again.
In some applications you can do a considerable amount of work while you are waiting for the user. The best way to accomplish this is through a Timer control. Use static (or module-level) variables to track your progress, and do a very small piece of work each time the Timer goes off. If you keep the amount of work done in each Timer event very small, users won't see any effect on the responsiveness of the application and you can preload data or do other things that further speed your application.
When you can't avoid a long delay in your program, you need to give the user some indication that your application hasn't simply hung. Windows 95 uses a standard progress bar to provide such information. You can add this progress bar to your applications by using the ProgressBar control, which comes with the Microsoft Windows Common Controls included with the 32-bit version of Visual Basic 4.0. Use the DoEvents function at strategic points, particularly each time you update the value of the ProgressBar, to enable your application to repaint while the user is doing other things.
Apparent speed is most important when your application starts up. Users' first impression of the speed of an application is measured by how quickly they see something after starting the application. With the various run-time dynamic-link libraries (DLLs) that need to be loaded for Visual Basic for Applications, OLE controls, and OLE, some delay is unavoidable with any application. However, you can do the following to provide a response to the user as quickly as possible:
When a form is first loaded, all the code in the Load event executes before the form is displayed. You can modify this behavior by using the Show method in the Load event code, giving the user something to look at while the rest of the code in the event executes. Follow the Show method with DoEvents to ensure that the form gets painted:
Sub Form_Load()
Show ' Display startup form.
DoEvents ' Ensure startup form is painted.
Load MainForm ' Load main application form.
Unload Me ' Unload startup form.
MainForm.Show ' Display main form.
End Sub
The more complicated a form, the longer it takes to load. From this observation comes a rule: Keep your startup form simple. Most applications for Microsoft Windows display a simple copyright screen at startup, and your application can do the same. The fewer controls on the startup form and the less code it contains, the quicker it will load and appear. Even if the startup form immediately loads another, more complicated form, it gives the user immediate feedback that the application has started.
For large applications, you might want to preload the most commonly used forms at startup so that they can be shown instantly when needed. A satisfying way to do this is to display a progress bar in your startup form and update it as you load each of the other forms. Call DoEvents after loading each form so that your startup form will repaint. After all the important forms have been loaded, the startup form can show the first one and unload itself. Of course, each form you load in this manner will run the code in its Load event, so you need to be sure that this doesn't cause problems or excessive delays.
Visual Basic loads code modules on demand rather than all at once at startup. This means that if you never call a procedure in a module, that module is never loaded. Conversely, if your startup form calls procedures in several modules, then all of those modules are loaded as your application starts up, which slows things down. For this reason, you should avoid calling procedures in other modules from your startup form.
A large part of the time required to start a Visual Basic 4.0 application is spent loading the various run-time DLLs for Visual Basic, OLE, and OLE controls. Of course, if these items already are loaded, none of that time need be spent. Thus, users will see your application start up faster if there is another application already running that uses some or all of these DLLs. To make this happen, provide another small, useful application that the user always runs first.
For example, it is easy to write a small calendar application that uses a calendar OLE control and place it in the startup group for Windows. It starts up minimized and is always available, and while it is useful in itself it also ensures that the various Visual Basic run-time DLLs are loaded. Microsoft Office for Windows 95 includes a FastStart application that performs a similar operation for the OLE DLLs. Also keep in mind that the 16-bit run-time DLLs differ from their 32-bit counterparts, so an application that loads the 16-bit set of DLLs will not help improve the startup time of a 32-bit application and vice versa.
Finally, you can divide your application into a main skeleton application and several OLE server executables or DLLs. A smaller main application loads faster, and it can then load the other parts as needed. I discuss this technique in detail later in this article.
You can reduce the size of your application in memory by implementing one or more of the following techniques. You can:
The space used by (nonstatic) local string and array variables is reclaimed automatically when a procedure ends. However, global and module-level string and array variables remain in existence for as long as your program is running. If you are trying to keep your application as small as possible, you should reclaim the space used by these variables as soon as you can. You can reclaim string space by assigning a zero-length string to it:
SomeStringVar = "" ' Reclaim space.
You reclaim the space used by a dynamic array with the Erase statement:
Erase LargeArray
The Erase statement completely eliminates an array. If you want to make an array smaller without losing all of its contents, you can use the ReDim Preserve statement:
ReDim Preserve LargeArray(10, smallernum)
Similarly, you can reclaim some (but not all) of the space used by an object variable by setting it to Nothing. For example:
Global F As New StatusForm
...
F.Show 1 ' Form is loaded and shown modally.
X = F.Text1.Text ' User presses a button that hides form.
Unload F ' Get rid of visual part of form.
Set F = Nothing ' Reclaim space (module data).
Even if you don't use explicit form variables, you should take care to Unload (rather than simply hide) forms you are no longer using.
Another area in which size is a factor is Variants. Each Variant takes 16 bytes, compared to 2 bytes for an Integer or 8 bytes for a Double. Variable-length String variables use a 4-byte pointer plus the length of the string plus 2 bytes for the null terminating character. Passing data by reference (the default) requires an additional 4 bytes. In the same way, each Variant containing a string takes 16 bytes plus the length of the string plus 2 bytes for the null terminating character. Again, add 4 bytes when the Variant is passed by reference. (In 16-bit Visual Basic, each character in a string takes 1 byte; in 32-bit Visual Basic, each character takes 2 bytes.) Because they are so large and quickly consume stack space, Variant variables are particularly troublesome when used as local variables or arguments to procedures.
You'll rarely exhaust stack space in 32-bit Visual Basic, but in the 16-bit version you may still hit the limit. Visual Basic for Applications uses stack space more efficiently—local fixed-length strings larger than 255 bytes are not placed on the stack as they were in Visual Basic 3.0, for example—so even using the 16-bit version stack space is less of an issue.
Finally, if your applications are anything like mine, by the time they're close to being finished parts of them have been rewritten several times. In the process you've probably left behind variables that you're no longer using and sometimes even whole procedures that aren't being called from anywhere. Visual Basic does not detect and remove this "dead code," so you have to look for it and remove it yourself.
In many Visual Basic applications, the space used by graphics dwarfs the memory used by everything else combined. However, there are also opportunities to accomplish significant savings:
To reclaim graphics memory with LoadPicture() and Cls, if you aren't going to use a PictureBox or Image control again, don't just hide it. Instead, remove the bitmap it contains:
Image1.Picture = LoadPicture()
The following technique, new in Visual Basic 4.0, is another way to empty a picture:
Set Image1.Picture = Nothing
Yet another technique enables you to reclaim the memory used by the AutoRedraw bitmap in forms and picture controls. (The AutoRedraw bitmap is the bitmap that Visual Basic uses if you set AutoRedraw property to True or if you reference the Image property of forms or picture controls.) You can reclaim this memory using code like the following:
Mypic.AutoRedraw = True ' Turn on AutoRedraw bitmap.
MyPic.Cls ' Clear it.
MyPic.AutoRedraw = False ' Turn off bitmap.
The PictureBox controls in many Visual Basic applications exist merely to be clicked or to be dragged and dropped. If this is all you're doing with a PictureBox control, you are wasting a lot of Windows resources. For such purposes, Image controls are superior to PictureBox controls. Each PictureBox control is an actual window and uses significant system resources. The Image control, by contrast, is a "lightweight" control rather than a window, and uses far fewer resources. In fact, you can typically use 5 to 10 times as many Image controls as PictureBox controls. Moreover, Image controls repaint faster than PictureBox controls. Use a PictureBox control only when you need a feature that only it provides, such as DDE, graphics methods, or the ability to contain other controls.
Obviously, you use less memory if you load pictures only as you need them at run time rather than storing them in your application at design time. Perhaps less obviously, you can share the same picture among multiple PictureBox controls, Image controls, and forms. Using code like the following, you maintain only one copy of the picture:
Picture = LoadPicture("C:\Windows\Chess.BMP")
Image1.Picture = Picture ' Use the same picture.
Picture1.Picture = Picture ' Use the same picture.
Contrast that with the following code, which causes three copies of the bitmap to be loaded and thus requires more memory and more time:
Picture = LoadPicture("C:\Windows\Chess.BMP")
Image1.Picture = LoadPicture("C:\Windows\Chess.BMP")
Picture1.Picture = LoadPicture("C:\Windows\Chess.BMP")
Similarly, if you load the same picture into several forms or controls at design time, a copy of that picture is saved with each form or control. Instead, you can place the picture in one form and then share it with the other forms and controls as described above. This technique helps you accomplish two optimization goals. First, it makes your application smaller, because the application doesn't contain redundant copies of the picture. Second, it makes your application faster, because the picture doesn't have to be loaded from disk multiple times.
You may want to completely avoid storing pictures in forms or controls at design time. Instead, you can store the pictures as resources in your Visual Basic program and load them as needed at run time with the LoadResPicture function. If you never use all the pictures associated with a form at the same time, this technique saves memory over storing all the pictures in controls on the form. This technique also can speed loading the form because not all the pictures need to be loaded before the form can be shown.
Another technique is particularly useful when you want to tile a bitmap repeatedly across a form. Rather than place bitmaps in controls, you can use the PaintPicture method to display bitmaps anywhere on forms. It helps optimization because you need to load the bitmap only once, yet you can use PaintPicture to draw it multiple times.
Finally, try to use smaller picture data. Several painting and graphics programs enable you to save graphics in a standard compressed bitmap format called run-length encoding (RLE). RLE graphics can be several times smaller than their uncompressed counterparts, particularly for graphics that contain large swatches of solid color; at the same time, .rle files aren't appreciably slower to load or display. The savings you can acquire by using metafiles can be even more significant—tenfold or greater in some cases. Try to use metafiles at their normal size, because they are much slower to paint when they are resized.
The OLE features of Visual Basic 4.0 enable you to think about the architecture of your application in new ways. Instead of having to write a single, monolithic executable file, you can write an application consisting of a core "front-end" executable supported by a swarm of OLE servers. This approach offers several significant optimization benefits:
Additionally, the OLE servers can be debugged independently and reused in other applications. While this may not improve the speed of your current application, it may improve your speed in creating the next one.
To determine how to best optimize your application by segmenting it using OLE, you must evaluate the kinds of OLE servers you can create and how they fit into your application. You can create three kinds of OLE servers with Visual Basic:
Use of one kind of OLE server does not preclude the use of another; that is, you can use all three of them in a single application. From the standpoint of optimizing your application, however, they have very distinctive characteristics.
An out-of-process OLE server is an executable program that offers OLE services to other programs. As with all executables, it starts up and runs with its own stack in its own process space. When a client application uses one of the server's OLE objects, the operation crosses from the client's process space to the server's. Out-of-process OLE servers offer a number of valuable features not available to the other kinds of OLE servers:
Of these, the first and the last features are of particular interest in terms of optimization.
Because an out-of-process OLE server is a separate program, it can operate synchronously with the client. For multitasking with the client program it has a separate "thread"—which is conceptually equivalent to an actual thread, although it is in fact a separate process. The two programs can communicate through OLE and shared objects, but they run independently. This is particularly useful when your application has to perform some operation that takes a long time. The client can call the server to perform the operation and then continue responding to the user.
Even when running on a 32-bit system, your application may not be able to be made 32-bit immediately if it relies on 16-bit DLLs or Visual Basic controls. However, if you segment your application using out-of-process OLE servers, you can leave some parts as 16-bit programs and recompile the rest as 32-bit. You can then designate one part of the application as the core and the rest as OLE servers called from that core. This technique enables you to incrementally take advantage of 32-bit features and performance while preserving your investment in 16-bit components.
For all their strengths, however, out-of-process OLE servers carry a significant performance disadvantage, which manifests itself in two ways:
An out-of-process OLE server is an executable created with Visual Basic, so the same startup issues discussed earlier also apply. The good news is that if you are calling an out-of-process OLE server written in Visual Basic from another program written in Visual Basic, almost all the support DLLs will already be loaded. This greatly reduces the time required to start the server. Furthermore, many OLE servers are smaller than your average Visual Basic application, with few or no forms to load, which again improves load time. Nevertheless, an out-of-process OLE server is always slower to start than an in-process OLE server.
After it begins running, an out-of-process OLE server suffers from its very nature. That is, every interaction with the server is an out-of-process call. Crossing process boundaries requires a lot of CPU cycles. Consequently, every reference to an object from the out-of-process server is much more expensive than an equivalent reference to an object in the client application itself or in an in-process server. However, clever coding (to be discussed later in this paper) can reduce the number of necessary out-of-process calls and, in turn, reduce the impact of the out-of-process call overhead.
An in-process OLE server is a DLL that offers OLE services to other programs. As with all DLLs, it starts up and uses its client's stack and process space. When a client application uses one of the server's OLE objects the operation remains in the client's process space. Compared to out-of-process OLE servers, in-process OLE servers offer two advantages:
Because an in-process server runs as a DLL, no new process needs to be created and none of the run-time DLLs need to be loaded. This can make an in-process OLE server considerably quicker to load than an equivalent out-of-process server. Moreover, because the server is in-process, there is no out-of-process overhead when referring to the methods or properties on an object supplied by the server. Objects from the server operate with the same efficiency as objects within the client application itself.
Of course, there are some limitations to in-process OLE servers. Perhaps most significantly, they must be 32-bit and they can only use modal forms.
The Enterprise Edition of Visual Basic 4.0 enables you to create remote OLE servers that execute on a machine elsewhere on the network. While network overhead will inevitably exact a toll on application performance, you can make up for it by using the resources of additional CPUs. This is particularly true when you work with a remote OLE server that is operating on data local to the machine containing the server. Since this data would have to be fetched across the network anyway, an OLE server operating on it locally and returning only the results across the network may actually be more efficient.
For example, you might create an object in an OLE server that can search for files matching specified criteria on the local hard disk. By making this a remote OLE server and placing a copy on each machine on the network, you can write a distributed file-finder program that searches all the network servers in parallel, using the resources of all those CPUs.
As you use more and more OLE objects in your Visual Basic applications, optimizing your use of those objects becomes more and more important. You can implement several key techniques to make the most efficient use of OLE objects:
In Visual Basic 3.0, referencing an OLE Automation object in your code, such as get/set an object's property or execute one of its methods, constitutes an out-of-process call. Because out-of-process calls are expensive, you should avoid them if you are concerned about optimizing your application.
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
Or, equivalently:
Dim X As MyObject
Set X = New MyObject
Early binding enables Visual Basic to do most of the work of resolving the object definition at compile time rather than at run time when this task can impair performance. Early binding also enables Visual Basic to check the syntax of properties and methods used with the object and to report any errors.
If Visual Basic cannot early bind an object, it must late bind it. Late binding objects is expensive: At compile time you get no error checking, and each reference at run time requires at least 50 percent more work by Visual Basic.
Generally, you should early bind objects whenever possible. The only times you should have to declare a variable,
As Object
are (1) when you do not have a type library for the object in question; and (2) when you need to be able to pass any kind of object as an argument to a procedure.
When referencing OLE 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 a
' Microsoft Excel worksheet.
Application.Workbooks.Item(1).Worksheets.Item("Sheet1").Cells.Item(1,1)
In addition to being a lengthy string to manually type, this line of code is fairly difficult to read and is extremely inefficient.
When your code calls an OLE Server object from Visual Basic, each "dot" requires Visual Basic to make several calls to OLE (GetIdsOfNames, IDispatch).Therefore, to write the most efficient OLE Automation applications, minimize your use of dots when referencing an object.
You can usually make immediate inroads minimizing the dots by analyzing the objects and methods available to you. For example, the preceding 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 a
' Microsoft Excel worksheet.
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 preceding example assumes that it is okay to refer to cell A1 of any sheet that happens to be active.
Using the Set statement also enables 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 for Applications 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
If you are using an out-of-process OLE server, you can't completely avoid making out-of-process calls. However, you can minimize the number of such calls you need to make. If possible, avoid referencing OLE objects inside a For…Next loop. Cache values in variables and use the variables in loops. If you need 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 OLE server. For example, if the OLE server is Microsoft Word or Microsoft Excel you can put a looping macro in a template in Word or a looping procedure into a module in Microsoft Excel. You then call the macro or procedure from Visual Basic—a single call that launches a looping operation within the server.
If you are writing an OLE server, you can design the objects in it to be efficient by reducing the out-of-process calls required to perform an operation. For example, when you have several interrelated properties, you can implement a method with several arguments—one for each property. Calling the method requires a single cross-process call regardless of the number of its arguments, whereas setting each property requires an out-of-process call. Accordingly, if you anticipate uses of your server in which the client will need to call your server 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.