PRB: Excel Automation: Method of Object '_Global' Failed

Last reviewed: December 30, 1997
Article ID: Q178510
The information in this article applies to:
  • Microsoft Visual Basic Learning, Professional, and Enterprise Editions for Windows, version 5.0
  • Microsoft Visual Basic Standard, Professional, and Enterprise Editions for Windows, version 4.0
  • Microsoft Excel 97 for Windows
  • Microsoft Excel for Windows 95, versions 7.0, 7.0a
  • Microsoft Visual Basic for Applications version 5.0

SYMPTOMS

While running code that uses Automation to control Microsoft Excel, one of the following errors may occur.

With Microsoft Excel 97, you receive the error:

   Run-time error '1004':
   Method '<name of method>' of object '_Global' failed

   -or-

   Application-defined or object-defined error

With Microsoft Excel 95, you receive the error:

   Run-time error '-2147023174'
   OLE Automation error

CAUSE

Visual Basic has established a reference to Excel due to a line of code that calls an Excel object, method, or property without qualifying it with an Excel object variable. Visual Basic does not release this reference until you end the program. This errant reference interferes with automation code when the code is run more than once.

RESOLUTION

Modify the code so that each call to an Excel object, method, or property is qualified with the appropriate object variable.

STATUS

This behavior is by design.

MORE INFORMATION

To automate Microsoft Excel, you establish an object variable that usually refers to the Excel Application or Workbook object. Other object variables can then be set to refer to a Worksheet, a Range, or other objects in the Microsoft Excel object model. When you write code to use an Excel object, method, or property, you should always precede the call with the appropriate object variable. If you do not, Visual Basic establishes its own reference to Excel. This reference may cause problems when trying to run the automation code multiple times. Note that even if the line of code begins with the object variable, there may be a call to an Excel object, method, or property in the middle of the line of code that is not preceded with an object variable.

The following steps illustrate how to reproduce this problem, and how to correct it.

Steps to Reproduce Behavior

  1. Start a new Standard EXE project in Visual Basic. Form1 is created by default.

  2. Click References from the Project menu and check Microsoft Excel 8.0 Object Library.

  3. Place a CommandButton on Form1.

  4. Copy the following code to the Code Window of Form1:

          Option Explicit
    

          Private Sub Command1_Click()
    
             Dim xlApp As Excel.Application
             Dim xlBook As Excel.Workbook
             Dim xlSheet As Excel.Worksheet
             Set xlApp = CreateObject("Excel.Application")
             Set xlBook = xlApp.Workbooks.Add
             Set xlSheet = xlBook.Worksheets("Sheet1")
             xlSheet.Range(Cells(1, 1), Cells(10, 2)).Value = "Hello"
             xlBook.Saved = True
             Set xlSheet = Nothing
             Set xlBook = Nothing
             xlApp.Quit
             Set xlApp = Nothing
          End Sub
    
    

  5. On the Run menu, click Start or press the F5 key to start the program.

  6. Click the CommandButton. No error occurs. However, a reference to Excel has been created and has not been released.

  7. Click the CommandButton again and note that you receive one of the errors previously described.

    NOTE: The error occurs because the code refers to the Cell's method without preceding the call with the xlSheet object variable.

  8. Stop the project and change the following line:

          xlSheet.Range(Cells(1,1),Cells(10,2)).Value = "Hello"
    

    to:

          xlSheet.Range(xlSheet.Cells(1,1),xlSheet.Cells(10,2)).Value = "Hello"
    

  9. Run the program again. Note that you can run the code multiple times without error.

REFERENCES

For additional information, please see the following article in the Microsoft Knowledge Base:

   ARTICLE-ID: Q167223
   TITLE     : Microsoft Office 97 Automation Help File Available on MSL


Additional query words: activex automation OLE automation
Keywords : kberrmsg vb4win vb5all vb4all VBKBAutomation
Technology : kbvba
Version : WINDOWS:4.0,5.0,7.0,7.0a,97
Platform : WINDOWS
Issue type : kbprb


THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: December 30, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.