Conditional Compilation results in: "Cannot run the macro" error

[dervander]

I have moved a couple of ribbon callback routines (that worked previously) into a conditional compile block so that I don't get compile errors when I work in Excel 2003. Compiling works fine now in Excel 2003 but when I run my program in Excel 2007 it doesn't seem to find the routines and thus throws the error "Cannot run the macro 'Ribbon_Loaded'. The macro may not be available in this workbook or all macros may be disabled."

Macros are certainly enabled. I am running this in Windows XP SP3 and Excel 2007 SP2.

Here is the code that I'm using as the conditional compile:

#If ExcelVersion >= 12 Then

    Dim Rib As IRibbonUI

    'Callback for customUI.******
    Sub ribbon_Loaded(ribbon As IRibbonUI)
        Set Rib = ribbon
    End Sub
    
    'Callback for OfficeMenu buttons when they are clicked (onAction event)
    Sub OfficeButton_Click(control As IRibbonControl)
        Select Case control.ID
            Case "customFileNew"
                AccessNewTest
            Case "customFileOpen"
                AccessOpenTest
            Case "customFileSave"
                AccessSaveTest
        End Select
    End Sub

#End If

The code for the ExcelVersion function used above is:

Public Function ExcelVersion() As Integer

    Dim varVersion As String
    
    varVersion = Application.version
    'strip off any text etc. return number only
    ExcelVersion = CInt(Left(varVersion, InStr(1, varVersion, ".") - 1))
    
End Function

I wonder if there is some problem with using a user defined function as the condition for compilation. If so is there any built in function that would bring back a value to help me distinguish between excel versions that use the ribbon and ones that don't?

Thank you for whatever time you spend considering this problem,

Derrick

 

[shg]

You can't use a function in conditional compilation; it has to be a literal value or a constant declared with #Const.

 

[Jaafar Tribak]

To avoid compile errors , place the xl2007 Ribbon code in a seperate module ie: Not in the same module as in the one where you have the ExcelVersion Function.

 

[dervander]

Hi Shg, Thanks for your reply. I was afraid someone would confirm that functions don't work for conditional compilation. Are there any literal values available that distinguish between XL 2007 and predecessors? Where might I look?

I only want this block of code to be compiled if the ribbon object is present (i.e. excel 2007 or later) because this code causes a compile error in earlier versions.

Thanks,

Derrick

 

[shg]

Use a regular If statement:

Sub x()
    If CInt(Application.Version) > 12 Then
        DoMyExcel2007Stuff
    End If
End Sub

Put the code forExcel 2007 in a separate module:

Sub DoMyExcel2007Stuff()
    Dim Rib As IRibbonUI
 
    MsgBox "Hello!"
End Sub

The code will not compile in Excel 2003-, but VA does compile on demand, so it should run in both versions.

 

[dervander]

Hi Shg,

Thanks for replying so quickly. I guess you're encouraging me not to fuss about whether the code can be compiled ahead of time in excel 2003. I was considering settling for this and am glad it doesn't keep me from running the program as need be. I guess that's the best that can be done. It is a bit of a shame, as I sometimes hit that compile button as a quick test for errors in my code before I run it. This will be incentive to do my develpment in Excel 2007 or later unless I'm solving a 2003 related issue.

As the code I'm worried about is a callback from the ribbon I don't have to worry about it being called in versions that can't handle it, so no need to wrap it in another function. I am hearing that it's important to have the 2007 code in a separate module though and I'm guessing that's because Excel compiles on demand one module at a time.

Thanks for sharing the knowledge you have gained through your experience,

Derrick

 

[shg]

You're welcome.

There may be another way (it surprises me that VBA doesn't include a constant for Excel version), but the way I showed is the only way I've seen it addressed.

EDIT: Name the 2007 module to make it appear last on the list; if that's the first error you find, the others are good.

 

[dervander]

EDIT: Name the 2007 module to make it appear last on the list; if that's the first error you find, the others are good.

Hey that's great! What a relief to have a solution that doesn't compromise using compile as a tool to check for coding errors. How I appreciate such straight forward logic applied to the outside of the box. I also have class modules to worry about but I can see from trying this out that they are compiled first and then the regular modules. That makes sense as the classes are used inside the regular modules and not the other way around.

Thanks again.

 

[shg]

You're welcome, I'm pleased that it helped.