I have an add-in which I'm trying to make compatible with XL2007 while still
supporting earlier versions of Excel. I've replaced my commandbars and
menus with new ribbon stuff. Each of my ribbon button's has an onAction
attribute that specifies the name of a VBA Sub in my add-in.

I'd like to code the subroutine as:

Sub ButtonClick(Control As Object)
or
Sub ButtonClick(Control As Variant)

to maintain compatibility with earlier versions of Excel which I can't do if
I code it as:

Sub ButtonClick(Control As iRibbonControl)

since iRibbonControl isn't defined in the earlier Excel libraries.

Unfortunately my approach doesn't work! When I click the button I get the
message:

run-time error 424
Object required

when my code tries to access one of the public properties of Control. If I
put Control into a Watch window, the type looks OK ("Object/iRibbonControl")
and if I expand it I see the public properties (Context, Id & Tag) but each
property has a value of "<Object required".

Anybody got any explanation?

TIA,

josh

BTW, if I do code the Control argument as iRibbonControl then the rest of my
code works OK.

This is what I do, Josh:

Sub BtnOnActionCall(Ctrl As Variant)
Dim Ctrl1 As IRibbonControl
Set Ctrl1 = Ctrl
MsgBox Ctrl1.ID ''Test

Since this sub is only called when run in Excel 2007 there is no problem
declaring Ctrl1 as IRibbonControl within the sub. If you try to compile this
project in Excel 2003 (Debug, Compile) you would get an error, but there is
no reason to do this.

--
Jim
"Josh Sale" <jsale@tril dot cod wrote in message
...
I have an add-in which I'm trying to make compatible with XL2007 while
still supporting earlier versions of Excel. I've replaced my commandbars
and menus with new ribbon stuff. Each of my ribbon button's has an
onAction attribute that specifies the name of a VBA Sub in my add-in.

I'd like to code the subroutine as:

Sub ButtonClick(Control As Object)
or
Sub ButtonClick(Control As Variant)

to maintain compatibility with earlier versions of Excel which I can't do
if I code it as:

Sub ButtonClick(Control As iRibbonControl)

since iRibbonControl isn't defined in the earlier Excel libraries.

Unfortunately my approach doesn't work! When I click the button I get the
message:

run-time error 424
Object required

when my code tries to access one of the public properties of Control. If
I put Control into a Watch window, the type looks OK
("Object/iRibbonControl") and if I expand it I see the public properties
(Context, Id & Tag) but each property has a value of "<Object required".

Anybody got any explanation?

TIA,

josh

BTW, if I do code the Control argument as iRibbonControl then the rest of
my code works OK.

But Jim,

Do you know why (using your example) I can't just do a MsgBox Ctrl.ID? Why
doesn't Ctrl just late bind to the iRibbonControl object as it would to any
other object?

For reasons that go beyond the scope of this ng posting, my add-in does need
to compile without error under XL2003.

josh




"Jim Rech" wrote in message
...
This is what I do, Josh:

Sub BtnOnActionCall(Ctrl As Variant)
Dim Ctrl1 As IRibbonControl
Set Ctrl1 = Ctrl
MsgBox Ctrl1.ID ''Test

Since this sub is only called when run in Excel 2007 there is no problem
declaring Ctrl1 as IRibbonControl within the sub. If you try to compile
this project in Excel 2003 (Debug, Compile) you would get an error, but
there is no reason to do this.

--
Jim

Do you know why

I do not, Josh. I was as surprised as you to see the passed object had to
be recast to be usable.

my add-in does need to compile without error under XL2003.

A sticky wicket, as the Brits say. What I'm doing with my apps that I want
to work in all XL environments is have my main app file load another add-in
under XL2007 with RibbonX, etc. Messy but I think unavoidable.

--
Jim
"Josh Sale" <jsale@tril dot cod wrote in message
...
But Jim,

Do you know why (using your example) I can't just do a MsgBox Ctrl.ID?
Why doesn't Ctrl just late bind to the iRibbonControl object as it would
to any other object?

For reasons that go beyond the scope of this ng posting, my add-in does
need to compile without error under XL2003.

josh




"Jim Rech" wrote in message
...
This is what I do, Josh:

Sub BtnOnActionCall(Ctrl As Variant)
Dim Ctrl1 As IRibbonControl
Set Ctrl1 = Ctrl
MsgBox Ctrl1.ID ''Test

Since this sub is only called when run in Excel 2007 there is no problem
declaring Ctrl1 as IRibbonControl within the sub. If you try to compile
this project in Excel 2003 (Debug, Compile) you would get an error, but
there is no reason to do this.

--
Jim

I was going to suggest this, or parallel versions for Excel 2007 and the
Excel we've known and used for a decade.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Jim Rech" wrote in message
...
Do you know why

I do not, Josh. I was as surprised as you to see the passed object had to
be recast to be usable.

my add-in does need to compile without error under XL2003.

A sticky wicket, as the Brits say. What I'm doing with my apps that I
want to work in all XL environments is have my main app file load another
add-in under XL2007 with RibbonX, etc. Messy but I think unavoidable.

--
Jim
"Josh Sale" <jsale@tril dot cod wrote in message
...
But Jim,

Do you know why (using your example) I can't just do a MsgBox Ctrl.ID?
Why doesn't Ctrl just late bind to the iRibbonControl object as it would
to any other object?

For reasons that go beyond the scope of this ng posting, my add-in does
need to compile without error under XL2003.

josh




"Jim Rech" wrote in message
...
This is what I do, Josh:

Sub BtnOnActionCall(Ctrl As Variant)
Dim Ctrl1 As IRibbonControl
Set Ctrl1 = Ctrl
MsgBox Ctrl1.ID ''Test

Since this sub is only called when run in Excel 2007 there is no problem
declaring Ctrl1 as IRibbonControl within the sub. If you try to compile
this project in Excel 2003 (Debug, Compile) you would get an error, but
there is no reason to do this.

--
Jim

or parallel versions for Excel 2007 and the Excel we've known and used for
a decade.

Ugh!

A sticky wicket, as the Brits say. What I'm doing with my apps that I
want to work in all XL environments is have my main app file load another
add-in under XL2007 with RibbonX, etc. Messy but I think unavoidable.


Which is exactly the path I've been pursuing.

I wonder why MS isn't more generous in creating conditional constants? How
much can it cost them to create a VBA12 constant to keep poor lonely VBA6
company?

j

Hi Jim,

Is it still a problem if all specific XL2007 code is in a dedicated module,
preferably the last one inserted. If the addin is developed in a lower
version, any time need to do a compile temporarily comment code within
procedures in that module.

I don't have XL2007 so only asking, however that's what I do enable an addin
with later version stuff to work in earlier versions, eg

Dim rb as Rubbish

- even at module level this doesn't seem to cause a problem providing no
code in the module is ever run while developing (in the lower version).

However there probably would be a problem if any code in that module had
been merely run then saved in a later version that does include 'Rubbish'.
If subsequently run in an earlier version it might blow.

Regards,
Peter T

"Jim Rech" wrote in message

my add-in does need to compile without error under XL2003.

A sticky wicket, as the Brits say. What I'm doing with my apps that I
want
to work in all XL environments is have my main app file load another
add-in
under XL2007 with RibbonX, etc. Messy but I think unavoidable.

--
Jim

Hi Peter-

My experience with XL2007 is just the same as yours is with other version
issues - as long as you do not _run_ a sub where an unknown variable type is
declared, all is cool. That's the point of the code I posted in my original
response to Josh. He's aware of this but says he _has_ to do a Debug,
Compile in a pre-2007 version unfortunately.

My group at work just released our first version of an app (I'm the main XL
developer) that supports all Excel versions from 2000 to 2007. The only
difference is that the app loads an Excel 2007 add-in with RibbonX when run
under Excel 2007, else it builts its menus with Commandbars. So this
technique is tried and true, at least as far as our in-house testing goes.
We don't actually have any users/clients on Excel 2007 yet<g.

--
Jim
"Peter T" <peter_t@discussions wrote in message
...
Hi Jim,

Is it still a problem if all specific XL2007 code is in a dedicated
module,
preferably the last one inserted. If the addin is developed in a lower
version, any time need to do a compile temporarily comment code within
procedures in that module.

I don't have XL2007 so only asking, however that's what I do enable an
addin
with later version stuff to work in earlier versions, eg

Dim rb as Rubbish

- even at module level this doesn't seem to cause a problem providing no
code in the module is ever run while developing (in the lower version).

However there probably would be a problem if any code in that module had
been merely run then saved in a later version that does include 'Rubbish'.
If subsequently run in an earlier version it might blow.

Regards,
Peter T

"Jim Rech" wrote in message

my add-in does need to compile without error under XL2003.

A sticky wicket, as the Brits say. What I'm doing with my apps that I
want
to work in all XL environments is have my main app file load another
add-in
under XL2007 with RibbonX, etc. Messy but I think unavoidable.

--
Jim