Type Library Changes

The type library for Microsoft Excel 97 is able to more specifically type objects. This far-reaching change may cause your application to return "Type Mismatch" errors. The following table illustrates some type library changes that will affect your application.

Code Will Be Compiled Earlier

Microsoft Excel 97 and its strongly typed type library allows the compiler to flag "Type Mismatch" errors. These errors are not caught by the compiler in Microsoft Excel 95. Be aware that code that is not used but still exists in the workbook or code that is "protected" by On Error Resume Next will also prevent compilation if a type mismatch occurs. You must fix the underlying error; there is no way to set the compile warning at a lower level.

Note that the Run method of the Application object will no longer allow mismatched argument types. This type mismatch will be caught at compile time. Here's an example of code that is broken:

Sub CallsFunction()
 Dim mv as Variant
 MsgBox Application.Run("MyFunction",mv)
End Sub

Function MyFunction(MyVarl as Long) as String
 MyFunction = "test"
End Function

De-referencing a Typed Object

In Microsoft Excel 95, some objects were typed to return Variant values. For example, you may have used code similar to the following:

Sub AddSheet()
     Worksheets.Add (Worksheets(2))
End Sub

The type library for Microsoft Excel 97 is able to more specifically type objects. Worksheets are now typed to return an Object rather than a Variant. The parentheses in existing code will cause Visual Basic to attempt to de-reference the returned Worksheet object, which will result in an error.

To work around this problem, specify the object directly without using parentheses. This approach won't cause Visual Basic to try to de-reference the object, and the error will be avoided. The following code will work in Microsoft Excel 97:

Sub AddSheet()
      Worksheets.Add  Worksheets(2)
End Sub

Automatic Type Coercion in Arrays

Microsoft Excel can no longer declare an array as variant and then pass an item in the array to other functions and receive it as something other than a variant. This requires changing the declaration of the array argument. The following code works in Microsoft Excel 95:

Sub CallFunction()
    Dim avData as Variant     'note that avData is a variant
    Dim vItem as Variant
    avData =ThisWorkbook.Worksheets(1).Range("A1:A5")
    vItem = TimesTen avData(1,1)
End Sub     

Function TimesTen(iData as Double) as Double    'will no longer work
     TimesTen = iData * 10                      'iData needs variant
End Function

For this code to work in Microsoft Excel 97, the argument iData should be re-dimensioned as a variant data type, as in the following example:

Sub XL97CallFunction()
    Dim avData as Variant     'note that avData is a variant
    Dim vItem as Variant
    avData = ThisWorkbook.Worksheets(1).Range("A1:A5")
    vItem = XL97TimesTen avData(1,1)
End Sub

Function XL97TimesTen(vData as Variant) as Double    
     TimesTen = vData * 10             'vData now variant
End Function

OnRepeat Arguments Now Strongly Typed to String

You may have used the OnRepeat property to control what happens when the user clicks Repeat on the Edit menu. If so, Microsoft Excel 95 allows the following undocumented resetting (unassigning of a custom routine) of the OnRepeat property:

Sub ResetOnRepeat()
    Joe= Null
    Chris= Null
    Application.OnRepeat Joe, Chris     'Line does nothing
End Sub

In Microsoft Excel 97 this will result in the following error: "Run-time error '94': Invalid use of null." The workaround is to use the more common means of resetting an "On…" property, as in the following example:

Sub ResetOnRepeat()
    Application.OnRepeat "", ""
End Sub

Floating Point to Integer Conversion

When converting a Double or Single in a variant into an Integer, Visual Basic rounds rather than truncates the number. Any code that relies on truncation behavior may be broken. For best results, explicitly truncate floating points using the Int method.