HOWTO: Optimize OLE Calls in Visual Basic

• Microsoft Visual Basic Professional and Enterprise Editions, for Windows, version 5.0
• Professional and Enterprise Editions of Microsoft Visual Basic, 16-bit and 32-bit, for Windows, version 4.0

SUMMARY

Because 32-Bit versions of Visual Basic make extensive use of Microsoft OLE Technologies, the correct use of object references can be crucial to the performance of a Visual Basic application.

MORE INFORMATION

First, you should read the Visual Basic documentation on optimizing your application. Visual Basic 4.0 users should refer to Chapter 27, "Optimizing Your Application for Size and Speed," in the Visual Basic "Programmer's Guide". Visual Basic 5.0 users should refer to the "Design Performance and Compatibility" chapter of the "Programmer's Guide". These chapters lay out details of many of the most useful size and speed optimizations.

Use the Most Specific Object Available

In Visual Basic, accessing any object model typically requires the repeated use of the Object.Property syntax. This chain of references can get quite long:

   DBEngine.Workspaces(0).Databases(0).RecordSet.Fields(0).Name

      ' Assume Fields.Count = 10
      For I% = 0 To DBEngine.WorkSpaces(0).Database._
         RecordSet.Fields.Count - 1
            ' Five dots.
            Print DBEngine.WorkSpaces(0).Database.RecordSet.Fields(I%).Name
            ' Five Dots.
      Next
      ' This loop generates OLE calls for each dot (.)
      ' in the Print statement
      ' on each iteration of the loop.

     Dim DB As Database
     Dim X As Fields
     Dim F As Field
       Set DB = DBEngine.Workspaces(0).OpenDatabase("biblio.mdb")
       'Object binding takes place at compile time
       Set X =   DBEngine.Workspaces(0).Databases(0).TableDefs(0).Fields
       'Four dots, only executed once.
       For Each F In X
         Print F.Name
         'One OLE call
       Next

Take Advantage of With Statement Blocks to Reduce Repeat References

To set multiple properties of a single object, you can use With...End With to reduce the number of times the chain of references is invoked to set your properties. In this case, you don't even have to have a temporary object of the object type defined.

This set of assignments:

   DBEngine.WorkSpaces(0).Database.RecordSet.Fields(0).Name = "NYSE"
   DBEngine.WorkSpaces(0).Database.RecordSet.Fields(0).Required = True
   DBEngine.WorkSpaces(0).Database.RecordSet.Fields(0).Size = 10
   DBEngine.WorkSpaces(0).Database.RecordSet.Fields(0).Type = dbText
   DBEngine.WorkSpaces(0).Database.RecordSet.Fields(0).Value = "MSFT"

   With DBEngine.WorkSpaces(0).Database.RecordSet.Fields(0)
      .Name = "NYSE"
      .Required = True
      .Size = 10
      .Type = dbText
      .Value = "MSFT"
   End With

Minimize Cross-Process OLE Calls

InProcess OLE Automation calls will always be faster than Local Process calls (such as automating Excel from Visual Basic for Windows). If you do Local Process OLE automation, an early bound object (Dim X As Excel.Application) rather than a late bound object (Dim X as object) can cut the overhead significantly as only one cross process call is needed. Use the 'With statement' and 'Specific Objects' suggestions above. Use In- Process OLE Servers Instead of Out-of-Process OLE Servers

In-process OLE Servers (OLE servers created using the Make DLL menu option) are significantly faster than out-of-process OLE Servers (OLE servers created using the Make EXE menu option).

Use Server-Side Macros to Reduce Cross-Process Calls

Macro functions created on the Server side (for example, a WordBasic macro created in Word and called from Visual Basic) require only a single cross-process call but can perform multiple operations. This speeds up the total execution time.