Conditional public variable declaration

**RedWing** · 11-13-2012, 09:07 AM

Hi there,

I'm trying to conditionally declare a public variable. The reason for this is to allow my code to progress to the stage where the user can be notified by the project that their Excel version is incompatible with the workbook being opened, the public variable is a RibbonUI Type (the ribbon isn't supported < Office 2007 and instead yields a compile error in these cases if the workbook involves a ribbon UI declaration, I'd rather it display my own message which is in the Workbook Open sub but the project compiles before the first line of the Workbook Open sub can be applied and the error shows up).

So is it possible I can conditionally declare a public variable e.g. (11.0 corresponds to Office 2003) where ExcelVersion is a function yielding the Excel version.

Please Login or Register  to view this content.

The above doesn't seem to work :<, can anybody help me out?

Thanks.

**JosephP** · 11-13-2012, 09:30 AM

you can put all the code that is specific to 2007 into its own module and then call it after testing application.version

you still won't be able to manually compile the project from within the vb editor but the compiler should not complain when the workbook is opened or code run since it won't attempt to load that module in earlier versions

**RedWing** · 11-13-2012, 09:42 AM

Thanks for your reply. All the Ribbon UI code is in its own module already, with the public variable declarations (all of them ,including the Ribbon UI) in a separate module specifically for public declarations. Running on 2003 simply produces a compilation error before any code is run when opening the workbook.

The workbook open event itself does not call anything from the ribbon (although as I understand, the XML file loads a macro from the ribbon module after the WB Open event to initialize the ribbon).

**JosephP** · 11-13-2012, 09:54 AM

I apologize-I overlooked the mention of public variables. the iribbonui variable does not need to be public - it should be declared at the top of the 2007+ code module and not public. public variables will be compiled when the project is loaded

**RedWing** · 11-13-2012, 10:11 AM

Thanks again for your reply (and no need for the unnecessary but courteous apology :p). I dimmed the IRibbonUI variable at the modular level this time as suggested but I run into the same issue, so I presume something must be forcing that module to compile (again this occurs before any line of the WB Open sub is run so none of my code is referencing this module at runtime). So I'm not sure what is going on.

Another alternative I have found simply involves declaring IRibbonControls and IRibbonUIs as Variant types, everything seems to work fine in 2007/10 with this and 2003 does compile without errors. I prefer your method since it is tidier but it doesn't seem to be working for me, any idea what could be going on?

Thanks again.

**JosephP** · 11-13-2012, 10:29 AM

I'm not sure-I would need to see the code I reckon. I've just double-checked with a quick test and it works ok for me in 2003. perhaps you can just check whether this workbook opens ok for you.

**RedWing** · 11-13-2012, 10:52 AM

Thanks for the reply again.

Yep, works just fine for me too. I realised the workbook open event was calling something from the ribbon module (a macro that has nothing to do with the ribbon but was misplaced in that module), it works just fine via the method you suggested now. I still get a compile error on activation where I need to reference the ribbon module but I can let the user know what's gonna happen before that and advise them accordingly.

**JosephP** · 11-13-2012, 11:16 AM

if you do need to work around the compilation chain you can use application.run but that does have side effects (such as breaking the error handling stack) so should be avoided if possible. you can also put all the ribbon code into a separate workbook that you load after an application version check