How ignore ribbon code when in Excel 2003

[donh]

I have a spreadsheet that is to be useable in both Excel 2007 and 2003. In
the 2007 version, there is customized Ribbon support. I have isolated all
the ribbon callback functions in a separate VBA module, and it seems that
when the spreadsheet is opened in Excel 2003 (which of course does not invoke
any of the callback functions) that module is never compiled, and so
everything works.

Except: There is one place where I need to have code that might call one of
the ribbon functions: In a Worksheet_Activate method, I need to invoke the
IRibbonUI.Invalidate method when in 2007. Even though the code can test for
the Application.Version and not call the invalidation procedure, the very
presence of the statement that references that procedure makes VBA want to
compile the module that has ribbon code in it. Of course, that doesn't work
in Excel 2003 -- none of those objects is known.

Is there any way to "trick" VBA in Excel 2003 into allowing code that
references ribbon things to compile. I have no intention of actually calling
or executing any of that code, but I can't figure out how to keep it from
being referenced and thus causing compile errors.

One obvious solution is to change the VBA code itself between the 2007 and
2003 versions of the spreadsheet. But the whole point here is to have a
common set of stuff that works in both places, unchanged.

Thanks,
-don h

 

[Jon Peltier]

The Worksheet_Activate code can call other procedures. Use some kind of test
to decide whether to go to a procedure in the 2007 module, and only this
procedure does the ribbon-invalidation.

- Jon

 

[donh]

Jon, Thanks, but that's what I've tried. The Worksheet_Activate is
(conditionally) calling a procedure in the 2007 module, which is where the
ribbon-invalidation happens. Under 2003, that call would never be made.
However, it appears that simply having the reference to the 2007 module
procedure in the code causes that procedure to be compiled when the
W_Activate is invoked. Because there is no knowledge of things like
IRibbonUI in Excel 2003, the compile fails and the code is not executed.

I had hoped that the 2007 module would only be compiled if any of its
procedures was actually invoked, but it appears that its compiled even if
they are referenced in other source, not just if they are really called.

Any other ideas? Thanks.

 

[OssieMac]

Hi Don,

Just a suggestion and not tested but is it possible to put the code in
another workbook and then only open the other workbook if required. That way
there should be no need for the code to compile if not required. However, it
might fail where you attempt to call it because the workbook is not open.

Following code from a post by Bob Phillips.

Application.Run "'another book2.xls'!test_msgbox"

 

[donh]

Hi OssieMac
Thanks for the idea. This might work. I don't like the notion of providing
a second workbook to our clients, just for the sake of supporting the
Ribbom... It would seem this opens up more chances for things to be installed
incorrectly, and is more to explain to everyone. But it might be a
workaround.

I still hold out hope that there is some clever way to solve the problem
within the bounds of what VBA supports.

I appreciate your taking the time and interest to suggest a solution.

 

[OssieMac]

Hi again Don,

Another suggestion. This time partially tested. Ensure that all xl2007 code
is in a module of its own. (Previous posts suggest that is what you already
have.)

In the VBA Editor select Tools -> Options -> General tab.

Check the box Compile on demand.
Uncheck the box Background compile.

My limited testing suggests that this works.

Unless the project is extremely large then it might not noticable affect the
speed.

Also, I believe that once the code has compiled once then it does not
continually re-compile every time it is used. You can test this by running
some of the code and then open the VBA editor and select Debug -> Compile and
you will see that compile is disabled because it is already compiled. Some of
the expert might be able to enlarge on this aspect.

 

[Jon Peltier]

If you are going to use another workbook, you don't need this complication.
However, as I was clicking on this post I was already thinking of a
solution, and it also uses Application.Run:

Application.Run "'" & ThisWorkbook.Name & '!Excel2007Procedure"

This won't cause anything to compile, because does not call the procedure
until run time.

- Jon

 

[exceluserforeman]

Public Sub veri()
Dim varVer

varVer = Application.Version

If varVer = 11 Then
'Do 2003 stuff
Else
'do 2007 stuff
End If
End Sub

Put all your code in the same module.

Have 2007 specifics in their own sub routines, even if it is similar to 2003.

You may have repetetive routines that only differ slightly but best to be
specific.

 

[Jon Peltier]

This won't eliminate the compile error caused in 2003 by objects and members
introduced in 2007.

- Jon

 

[donh]

Hi OssieMac,
Sorry for the delay in response; I've been away. I tried this, but it
seemed to make no difference. Also, I'm not sure that setting is
workbook-specific, so I'm not sure that I can ensure that my users who go to
run my spreadsheet in 2003 will necessarily have the setting correct all the
time.

 

[donh]

Jon,
This seems like it should work. I appreciate the suggestion; thanks. I'll
report back with results.

 

[donh]

Jon
It works! I put the Application.Run command inside an If statement so that
it only executes when in the Excel 2007 environment. In Excel 2003, the
statement is not executed, the module containing all the 2007 macros is not
compiled, and everything works correctly.

Thanks greatly for this advice.
--
Don H.

Jon,
This seems like it should work. I appreciate the suggestion; thanks. I'll
report back with results.