OFF95: Illegal Operation Using Binder OLE Automation Object (132753)

SYMPTOMS

In Microsoft Excel, if you use a Visual Basic macro to create a Microsoft Office Binder OLE Automation object, you may receive an Illegal Operation error in Microsoft Excel. If you click Details, you may see information similar to the following:

EXCEL caused an invalid page fault in module EXCEL.EXE at
0137:5000d9fd.

CAUSE

This problem occurs if you create an Office Binder OLE Automation object, and you add a Microsoft Excel worksheet section to the Binder without dimensioning the Microsoft Excel section as an object to hold a pointer in memory for the object. For example, when you run the following macro, you receive an Illegal Operation error in Microsoft Excel:

   Sub BinderExample()
      Dim myBinder as Object, y as String
      ' Create Binder object
      Set myBinder = CreateObject("Office.Binder")
      ' Add Microsoft Excel worksheet section
      myBinder.sections.Add Type:="excel.sheet.5"
      y = myBinder.sections(1).Object.Name
      y = myBinder.sections(1).Object.Name
   End Sub

When you run the macro above, the myBinder.sections.Add statement correctly adds a worksheet section to the Binder. However, there is no pointer in memory for this object. The first reference to this section object occurs without an error, but the second reference to this object causes Microsoft Excel to crash.

Note that the above example is not the only example that causes an Illegal Operation error in Microsoft Excel. Other examples may include commands similar to the following:

   myBinder.sections(1).Visible = False
   myBinder.sections(1).Visible = True
   myBinder.Sections(1).Object.Parent.Worksheets.Add
   myBinder.Sections(1).Object.Parent.Worksheets(1).Range("A1").Value = "1"

WORKAROUND

To work around this behavior, when you add a Microsoft Excel worksheet section to a Binder using OLE Automation, dimension the worksheet section as Object type add so that a pointer in memory is reserved for this object. For example, to run the above macro without receiving an Illegal Operation error, modify the macro as follows:

   Sub BinderExample()
      Dim myBinder as Object, XLSec as Object, y as String
      ' Create Binder object
      Set myBinder = CreateObject("Office.Binder")
      ' Add Microsoft Excel worksheet section
      Set XLSec = myBinder.sections.Add(Type:="excel.sheet.5")
      y = myBinder.sections(1).Object.Name
      y = myBinder.sections(1).Visible = True
   End Sub

Microsoft provides examples of Visual Basic procedures for illustration only, without warranty either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. This Visual Basic procedure is provided 'as is' and Microsoft does not guarantee that it can be used in all situations. Microsoft does not support modifications of this procedure to suit customer requirements for a particular purpose.

REFERENCES

For more information about communicating with other applications in Microsoft Excel, click the Index tab in Microsoft Excel Help, type the following text

CreateObject function