FIX: Nesting OLE Automation Calls Causes GP Fault

MORE INFORMATION

Multiple IDispatch Calls Use Up Memory

In order to access a property or execute a method on a OLE Automation object, automation controllers (such as Visual Basic) make IDispatch calls to the server application. Each time a IDispatch call is made, Visual Basic creates a temporary variable referencing the object. Visual Basic version 3.0 does not release the memory for these temporary variables until the procedure in which the property access or method invocation resides completes execution.

Therefore, if you have multiple property accesses or method invocations within the same procedure, the amount of free memory steadily decreases until the procedure completes execution. Nesting multiple OLE Automation statements that require IDispatch calls within the same procedure can produce a GP fault in module VBOA300.DLL at 0001:0D03.

It could be useful to estimate the number of IDispatch calls Visual Basic makes. The number of IDispatch calls Visual Basic executes for an individual statement is directly related to the number of properties or methods that are combined by using the dot notation to perform the statement. Thus a good rule of thumb is to count the number of "dots" in the statement. For example, the following statement makes two IDispatch calls:

   oXLSheet.Range("A1").Value = "Price"
				

Depending on how you access a collection, there will also be implicit IDispatch calls. For example, if you access a collection without the Item method, it is implicitly called.

The following statement makes five IDispatch calls - three explicit "dots" and two implicit calls to the Item method:

   oXLApp.Workbooks(1).Sheets(1).Range("A1").Value = "Price"
				

Steps to Reproduce Problem

1. Start Microsoft Excel. Then Start a new project in Visual Basic. Form1 is created by default.
2. Add a command button (Command1) to Form1.
3. Add the following code to the Command1_Click event procedure:

      Sub Command1_Click ()
         Dim ExcelSheet As Object
         Dim StartCell As Object
         Dim EndCell As Object
         Dim I as Integer
         Set ExcelSheet = CreateObject("excel.sheet.5")
         ' The following For Next loop makes 4000 IDispatch calls:
         For i = 1 to 1000
            Set StartCell=ExcelSheet.Cells(i,1)
            Set EndCell=ExcelSheet.Cells(i,5)
            ExcelSheet.Range(StartCell,EndCell).FormulaArray = "=3"
         Next
         ExcelSheet.Application.Quit
         Set ExcelSheet = Nothing
      End Sub
   						

4. Press the F5 key to run program. Then click the command button.

An Excel Worksheet is created and data is inserted into the Range of cells A1:E1000 via OLE automation. The For..Next loop makes 4000 cross-process calls from Visual Basic to Excel to insert the data into the Range of cells. Depending on the amount of Virtual Memory available to Windows on the system, the above code can lead to a low memory state or cause a GP fault in module VBOA300.DLL at 0001:0D03.