VBA TIPS
Programming for different versions of Excel VBA(Conditionally compiles)

The following code works on Excel 2000 and the later version without problem. But it does not work on Excel 97. because the vbModeless is not supported in Excel97. So when you run this code on Excel 97, Excel returns Run-time error.(shown in the picture below).

Sub ShowForm1()
        UserForm1.Show vbModeless
End Sub




Is there a way in VBA to write code that would compile in both Excel97 and Excel 2000 and the later version? The answer would be YES. Here is a sample code that when run in Excel97 will not compile.

Example

Place the following in a standard module.

Sub ShowForm2()
    #If VBA6 Then 'compiled in only Excel 2000 and the later version
        UserForm1.Show vbModeless
    #Else 'compiled in Excel 97
        UserForm1.Show
    #End If
End Sub



Remarks

In this case you can use #If...Then...#Else Directive.

#If...Then...#Else Directive conditionally compiles selected blocks of Visual Basic code and it is the same as the If...Then...Else statement.

VBA6 in the above code is called a conditional compiler constants. and it stands for the Version of Excel is Excel 2000 and the later.

(In fact, it results from the version of vbe.dll in C:\Program Files\Common Files\Microsoft Shared\VBA\vbe.dll.)

| HOME |
Copyright © cellmasters.net - colo's junk room All Right Reserved
ABOUT
WORKS
THE CELL MASTERS
CONTACT
LINKS
Tips and Information about Microsoft Excel|Masaru Kaji aka Colo