Code won't run on previous Excel version

[werafa]

Hi all,

Have written a complex (for me) piece of code, and want to make it run on earlier versions of excel.

The problem seems to be access to the MS Excel and Office 14 object library.
can anyone say how to do a - if version = this then reference = that - command that I can drop into a workbook open event?

alternatively; I have read about late binding, but don't understand the finer points. Can I drop a late binding sub into a workbook open event and expect everything else to work as per normal? if so, what is the minimum I need to set the object libraries correctly?

Many thanks

[SamT]

Compiler Directive Keyword Summary

compiler constant. #Const
Compile selected blocks of code. #If...Then...#Else

The behavior of the #If...Then...#Else directive is the same as the If...Then...Else statement, except that there is no single-line form of the #If, #Else, #ElseIf, and #End If directives; that is, no other code can appear on the same line as any of the directives. Conditional compilation is typically used to compile the same program for different platforms.

From: http://www.techrepublic.com/blog/msoffice/use-vba-to-return-the-current-version-number-of-any-office-application/1963

Sub GetVersion()
MsgBox "The current version is " & Application.Version, _
vbOKOnly, "Version"
End Sub

You can just as easily return the version number by using a function in the form

Function GetVersion() As Long
GetVersion = Application.Version
End Function

[werafa]

Thanks.

I follow the 'Getversion'. thanks for this one
I don't follow the comments re the compiler. -does this happen automatically withing the vba environment or is it something you specifically structure your code for?

thanks

[SamT]

Compiler #Constants are not compiled. They are only used TO compile

So if you have a #Const VerXp = Whatever Ands a
#If VerXP = Something then
Do
#Else
Do other
#End If
Only the "Do" OR "Do Other" statements are compiled. Of course you can use any variable in the #IF Statement, but if declared outside the #If, they would be compiled, taking up memory during the process.

One way around this is to use VBA's relaxed declaration rules
#IF #Const then
Dim x As ??
Dim Y AS ??
Do
#Else
Dim W AS ?/
Etc
#End If

[GTO]

Hi all,

Have written a complex (for me) piece of code, and want to make it run on earlier versions of excel.

The problem seems to be access to the MS Excel and Office 14 object library.
can anyone say how to do a - if version = this then reference = that - command that I can drop into a workbook open event?

alternatively; I have read about late binding, but don't understand the finer points. Can I drop a late binding sub into a workbook open event and expect everything else to work as per normal? if so, what is the minimum I need to set the object libraries correctly?

Many thanks

What application are you writing the code in?

[Aflatoon]

To add to Mark's comment: if your code is in Excel, then the Excel or Office library references should adjust automatically.

Regarding conditional compilation, you have to set the constants at design time so I don't think they will assist you here if the issue is simply a version one.

[John Wilson]

And for reference Version is NOT a Long it's a String. Crucial if you are comparing versions because "9.0" is greater than "12.0"

[werafa]

Thanks, shall give it a go. It makes sense that you can do this, I have just never seen it done (am self taught, and have followed the path of necessity).

Re the automatic adjustment, the code seems to work ok on newer versions of excel, but fails to roll back to older versions and complains about the object library. An alternative might be to set it to an old object library version and let it roll forward as needed, is there a command to set the available libraries programatically?.

[werafa]

And I am writing in Excel 2010

Tim

[John Wilson]

Normally if you write code for Excel IN Excel it would run on earlier versions UNLESS you used methods not available in the earlier version.

Different if you are working in say PowerPoint and have set a reference to Excel 14.

[werafa]

You have a quick trigger finger John. Thanks.
Maybe it is the methods that are limiting - am using the new chart formatting capabilities in 2010.

And I heard a rather good quote the other day - Remember: it is the second mouse that gets the cheese.....

[John Wilson]

New methods won't work of course in earlier versions. Chart vba is very badly supported before 2010 in PowerPoint, not sure about Excel

[Aflatoon]

In Excel it's fine - although 2007's macro recorder didn't really work where charts are concerned.

[werafa]

Yes, this is true. I have learned however to use the macro recorder to identify the relevant object, then set this as an object in code in order to drill down into the object properties. This has worked great for charts and usually gets the stuff that the recorder misses

I am kinda stuck with PPT as I am not yet fluent with the naming logic - and only program in PPT sporadically