Is there any way of writing write code in an Excel95 workbook which
creates a CommandBar object (as distinct from a Toolbar object) when
opened in later versions i.e. code must work in Excel97 onwards but
not blow up in Excel95?

My first thought was conditional compilation but I find it's not
supported in Excel95 (VBA4?)

My second thought was to create an instance using late binding but
this won't work either. Not surprising because I know I can't
instantiate a CommandBar using early binding anyway i.e.

Dim oBar As Office.CommandBar
Set oBar = New Office.CommandBar ' << compile error

Any other ideas (or confirmation this just isn't possible)?

--

Hi Jamieuk,

Is there any way of writing write code in an Excel95 workbook which
creates a CommandBar object (as distinct from a Toolbar object) when
opened in later versions i.e. code must work in Excel97 onwards but
not blow up in Excel95?


Write the code in a separate sub that is only called when the later
version opens your workbook.

You will get a compile error when you manually compile (not sure if
Excel 95 could do that), but it should run OK on both systems, since
VBA only compiles a sub when it is called.

Regards,

Jan Karel Pieterse
Excel MVP
www.jkp-ads.com

Thanks for the suggestion but it didn't work I'm afraid. I wrote this
sub which is never called:

Sub NeverNever()
Dim c As CommandBar
End Sub

But when I tried to run my sub main I got an error, 'User Defined type
not defined', which I suppose is the xl95 equivalent of a compile
error because my sub main didn't run at all.

--

Jan Karel Pieterse wrote in message ...
Hi Jamieuk,

Is there any way of writing write code in an Excel95 workbook which
creates a CommandBar object (as distinct from a Toolbar object) when
opened in later versions i.e. code must work in Excel97 onwards but
not blow up in Excel95?


Write the code in a separate sub that is only called when the later
version opens your workbook.

You will get a compile error when you manually compile (not sure if
Excel 95 could do that), but it should run OK on both systems, since
VBA only compiles a sub when it is called.

Regards,

Jan Karel Pieterse
Excel MVP
www.jkp-ads.com

Hi Onedaywhen,

Thanks for the suggestion but it didn't work I'm afraid. I wrote this
sub which is never called:

Sub NeverNever()
Dim c As CommandBar


Maybe defining c as an object will pass the test?
Or maybe putting all non-95 stuff in a separate module?

Regards,

Jan Karel Pieterse
Excel MVP
www.jkp-ads.com

JKP,
Good thinking! When I put sub NeverNever in a separate module I get no
error. Many thanks.

--

Jan Karel Pieterse wrote in message ...
Hi Onedaywhen,

Thanks for the suggestion but it didn't work I'm afraid. I wrote this
sub which is never called:

Sub NeverNever()
Dim c As CommandBar


Maybe defining c as an object will pass the test?
Or maybe putting all non-95 stuff in a separate module?

Regards,

Jan Karel Pieterse
Excel MVP
www.jkp-ads.com

Hi Onedaywhen,

When I put sub NeverNever in a separate module I get no
error.


Good, another problem solved!

Regards,

Jan Karel Pieterse
Excel MVP
www.jkp-ads.com