Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How ignore ribbon code when in Excel 2003
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 -- Don H. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How ignore ribbon code when in Excel 2003
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 ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ _______ "donh" wrote in message ... 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 -- Don H. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How ignore ribbon code when in Excel 2003
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. -- Don H. "Jon Peltier" wrote: 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 ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ _______ "donh" wrote in message ... 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 -- Don H. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How ignore ribbon code when in Excel 2003
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" -- Regards, OssieMac "donh" wrote: 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. -- Don H. "Jon Peltier" wrote: 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 ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ _______ "donh" wrote in message ... 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 -- Don H. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How ignore ribbon code when in Excel 2003
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. -- Don H. "OssieMac" wrote: 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" -- Regards, OssieMac |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
How ignore ribbon code when in Excel 2003
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. -- Regards, OssieMac |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
How ignore ribbon code when in Excel 2003
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 ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ _______ "OssieMac" wrote in message ... 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" -- Regards, OssieMac "donh" wrote: 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. -- Don H. "Jon Peltier" wrote: 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 ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ _______ "donh" wrote in message ... 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 -- Don H. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
How ignore ribbon code when in Excel 2003
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. "OssieMac" wrote: 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" -- Regards, OssieMac "donh" wrote: 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. -- Don H. "Jon Peltier" wrote: 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 ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ _______ "donh" wrote in message ... 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 -- Don H. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
How ignore ribbon code when in Excel 2003
This won't eliminate the compile error caused in 2003 by objects and members
introduced in 2007. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ _______ "exceluserforeman" wrote in message ... 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. "OssieMac" wrote: 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" -- Regards, OssieMac "donh" wrote: 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. -- Don H. "Jon Peltier" wrote: 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 ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ _______ "donh" wrote in message ... 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 -- Don H. |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
How ignore ribbon code when in Excel 2003
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. -- Don H. "OssieMac" wrote: 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. -- Regards, OssieMac |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
How ignore ribbon code when in Excel 2003
Jon,
This seems like it should work. I appreciate the suggestion; thanks. I'll report back with results. -- Don H. "Jon Peltier" wrote: 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 ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
How ignore ribbon code when in Excel 2003
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. "donh" wrote: Jon, This seems like it should work. I appreciate the suggestion; thanks. I'll report back with results. -- Don H. "Jon Peltier" wrote: 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 ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to protect excel workbook from macro starting from Developer - Code ribbon? | Excel Programming | |||
Modifying XLA to show toolbar in XL 2003 and ribbon tab in XL 2007 | Excel Programming | |||
Assign a Ribbon Control's Label from a Variable in VBA Code | Excel Programming | |||
Open Workbook Add In with 2007 & 2003, Which Changes Ribbon & Tool | Excel Programming | |||
Splitting up code ribbon | Excel Discussion (Misc queries) |