New code for Excel 2007 causes error in Excel 2000

[derekpegg]

Hi I have a workbook that I have designed in Excel 2000 which I am now trying to get working in 2007. I am slowly working through the things that are not working and changing the code accordingly.

One issue I had was that I needed to include the following line of code to make the workbook function correctly in 2007:

ThisWorkbook.CheckCompatibility = False

This works fine in 2007, but obviously Excel 2000 does not understand this as I think this is a new syntax in 2007.

I have a system in place which checks the version of Excel running and only attempts to run this line of code if it is running on 2007, but the macro still stops at this line in 2000.

I have tried a few other things with the final code being:

Sub Runif2007()

If Sheets("Report Creation").Range("C7") = "Excel 2007" Then

     On Error Resume Next
     Application.DisplayAlerts = False

     ThisWorkbook.checkcompatibility = False

     Application.DisplayAlerts = True
     
End If

End Sub

As I said previously, this should not even get to run on Excel 2000, but still brings up the error "Compile error: Method or data member not found".

Is there anyway around this issue as I guess it may come up again with new syntaxes that earlier versions of Excel do not understand?

Thanks for any help in advance.

 

[Richard Schollar]

I suspect that if you placed that particular line of code in its own separate sub routine and call the subroutine rather than the code line you won't get any error:

Sub Runif2007()

If Sheets("Report Creation").Range("C7") = "Excel 2007" Then

     On Error Resume Next
     Application.DisplayAlerts = False

     Call CheckCompatRoutine
     Application.DisplayAlerts = True
     
End If
 
Sub CheckCompatRoutine()
ThisWorkbook.CheckCompatibility = False
End Sub

You might like to look at the Application.Version property to determine what version of Excel the workbook has been opened in.

End Sub

 

[derekpegg]

Big thanks Richard.

That seemed to do it.

Seems odd to a novice like me that that should make any difference, but it did so I won't try to understand the VBA logic of it all.

Thanks again

Derek


PS. On a global board with posts from all 4 corners, one Hampshire man can help another. Cheers!

 

[Richard Schollar]

PS. On a global board with posts from all 4 corners, one Hampshire man can help another. Cheers!

It's a small world