IFDEF equivalent in VBA

Question

I have code that needs to be run in both Excel 2003 and Excel 2007, and there are several places where changes in versions cause the code to stop. I tried to separate these lines from If-Else statements, but the code will not compile either because it does not recognize the code used for the other. Is there any way to tell one version to ignore a block of code similar to the #ifdef type in C or C ++ in VBA?

Answer 1

This is a good starting point, but it will not work with the version of Excel in which it runs, since it can only be found at runtime, not compile time.

If you need to deploy your code based on information available only at runtime, you can consider late binding as a solution. There are two ways you can hide version issues.

The first method can be used if you need to access a property or method that exists only in certain versions, you can use CallByName. The advantage of calling by name is that it maximizes the preservation of early binding (and intellisense) for your objects.

To give an example, Excel 2007 has a new TintAndShade property. If you want to change the color of the range, and for Excel 2007 also make sure TintAndShade is set to 0, you would run into a problem because your code would not compile in Excel 2003, which does not have TintAndShade as a property of the range object. If you get access to a property that, as you know, is not in all versions using CallByName, your code will be compiled in all versions in order, but only in the versions you specify. See below:

Sub Test() ColorRange Selection, Excel.Application.version, 6 End Sub Sub ColorRange(rng As Excel.Range, version As Double, ParamArray args() As Variant) With rng.Interior .colorIndex = 6 .Pattern = xlSolid If version >= 12# Then 'Because the property name is stored in a string this will still compile. 'And it will only get called if the correct version is in use. CallByName rng.Interior, "TintAndShade", VbLet, 0 End If End With End Sub 

The second way is for classes that must be created through "New" and do not even exist in older versions. You won't run into this problem with Excel, but I will give a quickie demo so you can see what I mean:

Imagine that you wanted to run File IO, and for some strange reason, not all computers had Microsoft Scripting Runtime runtimes on them. But for some equally bizarre reason, you wanted to make sure it was used whenever it was available. If you link to it and use early binding in the code, the code will not compile on systems that do not have a file. Instead, you use the last binding:

 Public Sub test() Dim strMyString As String Dim strMyPath As String strMyPath = "C:\Test\Junk.txt" strMyString = "Foo" If LenB(Dir("C:\Windows\System32\scrrun.dll")) Then WriteString strMyPath, strMyString Else WriteStringNative strMyPath, strMyString End If End Sub Public Sub WriteString(ByVal path As String, ByVal value As String) Dim fso As Object '<-Use generic object 'This is late binding: Set fso = CreateObject("Scripting.FileSystemObject") fso.CreateTextFile(path, True, False).Write value End Sub Public Sub WriteStringNative(ByVal path As String, ByVal value As String) Dim lngFileNum As Long lngFileNum = FreeFile If LenB(Dir(path)) Then Kill path Open path For Binary Access Write Lock Read Write As #lngFileNum Put #lngFileNum, , value Close #lngFileNum End Sub 

Since 2003, there is a complete list of all the additions and changes to the Excel object model:
http://msdn.microsoft.com/en-us/library/bb149069.aspx For changes between 1997 and 2000 here:
http://msdn.microsoft.com/en-us/library/aa140068(office.10).aspx

Answer 2

Yes, you can do conditional compilation in Excel VBA. The following is a short resource and sample code: Conditional Compilation

 #If Win32 Then ' Profile String functions: Private Declare Function WritePrivateProfileString Lib "KERNEL32" Alias "WritePrivateProfileStringA" (ByVal lpApplicationName As String, ByVal lpKeyName As Any, ByVal lpString As Any, ByVal lpFileName As String) As Long Private Declare Function GetPrivateProfileString Lib "KERNEL32" Alias "GetPrivateProfileStringA" (ByVal lpApplicationName As Any, ByVal lpKeyName As Any, ByVal lpDefault As Any, ByVal lpReturnedString As String, ByVal nSize As Long, ByVal lpFileName As String) As Long #Else ' Profile String functions: Private Declare Function WritePrivateProfileString Lib "Kernel" (ByVal lpApplicationName As String, ByVal lpKeyName As Any, ByVal lpString As Any, ByVal lpFileName As String) As Integer Private Declare Function GetPrivateProfileString Lib "Kernel" (ByVal lpApplicationName As String, ByVal lpKeyName As Any, ByVal lpDefault As Any, ByVal lpReturnedString As String, ByVal nSize As Integer, ByVal lpFileName As String) As Integer #End If 

Answer 3

Can you post offensive lines of code?

If it's a constant like vbYes or xlFileFormat or something else, use the appropriate numeric value.

Show me what you have, I'll see if I can reorganize it.

Bill