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, 2000 Workbook
Microsoft Excel 5.0, 7.0 Worksheet
Examples
Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements.
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
In Microsoft Excel, when you run the macro, a message box that displays
"Workbook" appears. In versions of Microsoft Excel earlier than Excel 97, the message is "Worksheet".
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
However, this macro fails when you run it in Microsoft Excel 97 or later, because the
Parent property of xlObj (a
Workbook object) is an
Application object, and the
Close method does not apply to the
Application. When you run the macro, you receive the following error message:
Run-time error '438':
Object doesn't support this property or method
Making your Code Work in All Versions of Microsoft Excel
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)
where "xlObj" is the name of the Microsoft Excel object.
The value of "ExcelVersion" is either 5, 7, 8, or 9 for Microsoft Excel 5.0, 7.0, 97, or 2000 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
This macro works correctly with Microsoft Excel 5.0, 7.0, 97, or Microsoft
Excel 2000. The macro also works correctly when you run it from Microsoft
Visual Basic, Microsoft Word, or any other program (including Microsoft Excel) that supports Visual Basic or Visual Basic for Applications.