XL97: CreateObject and GetObject Work Differently

• Microsoft Excel 97 for Windows

SUMMARY

The CreateObject and GetObject methods of Automation work differently in Microsoft Excel 97 than they do with earlier versions of Microsoft Excel.

This article explains the differences in behavior and offers some suggestions for making Automation code work with Microsoft Excel 97 and earlier versions of Microsoft Excel.

MORE INFORMATION

Behavior in Different Versions of Microsoft Excel

When you use CreateObject or GetObject in a macro to work with a Microsoft Excel sheet object, such as "Excel.Sheet" or "Excel.Sheet.8," the type of object the macro returns is different for different versions of Microsoft Excel.

   Version                    Type of object returned
   --------------------------------------------------

   Microsoft Excel 97         Workbook
   Microsoft Excel 5.0, 7.0   Worksheet

Microsoft provides examples of Visual Basic for Applications 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. The Visual Basic procedures in this article are provided 'as is' and Microsoft does not guarantee that they can be used in all situations. While Microsoft support engineers can help explain the functionality of a particular macro, they will not modify these examples to provide added functionality, nor will they help you construct macros to meet your specific needs. If you have limited programming experience, you may want to consult one of the Microsoft Solution Providers. Solution Providers offer a wide range of fee-based services, including creating custom macros. For more information about Microsoft Solution Providers, call Microsoft Customer Information Service at (800) 426-9400.

You can demonstrate the change in behavior by running the following Visual Basic for Applications macro in Microsoft Excel or Microsoft Visual Basic, version 4.0:

   Sub ShowTypeName()

       Dim xlObj As Object
       Set xlObj = CreateObject("Excel.Sheet")
       MsgBox TypeName(xlObj)
       Set xlObj = Nothing

   End Sub

This change in behavior may cause a problem if your code uses properties and methods that are specific to the type of object to which the macro references.

This following macro works correctly with earlier versions of Microsoft Excel, because the Parent property of xlObj (a Worksheet object) is a Workbook object; and the Close method applies to workbooks:

   Sub DemonstrateProblem()

       Dim xlObj As Object
       Set xlObj = CreateObject("Excel.Sheet")
       MsgBox TypeName(xlObj)
       xlObj.Parent.Close False
       Set xlObj = Nothing

   End Sub

   Run-time error '438':
   Object doesn't support this property or method

If you want to use Automation with Microsoft Excel, but you do not know which version of Microsoft Excel is running, you can modify your code to work correctly with any version of Microsoft Excel.

One way to do this is to check the version of Microsoft Excel from the macro, and then store the version number in a variable. To do this, use the following line of code:

   ExcelVersion = Val(xlObj.Application.Version)

The value of "ExcelVersion" is either 5, 7, or 8 for Microsoft Excel 5.0, 7.0, or 97 respectively.

After you determine the version of Microsoft Excel you are using, modify the macro to work correctly with that version of Microsoft Excel. For example, you can make the macro in this article work correctly by adding a few lines of code. The following example illustrates how to change the macro:

   Sub FixedProblem()

       'Dimension variables.
       Dim xlObj As Object, ExcelVersion As Integer
       Dim xlTemp As Object

       Set xlObj = CreateObject("Excel.Sheet")

       'This line guarantees that xlObj will remain viable when we switch
       'it to the active sheet.
       Set xlTemp = xlObj

       ExcelVersion = Val(xlObj.Application.Version)

       If ExcelVersion >= 8 Then

          Set xlObj = xlObj.ActiveSheet   'Reset the xlObj object to refer
                                          'to the active sheet.
       End If

       'Code that works with the xlObj object goes here.

       Set xlTemp = Nothing   'Both object variables should be set to
       Set xlObj = Nothing    'Nothing.

   End Sub