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

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

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

Ugh!

I agree, but I've found that there are some subtle differences in how VBA
commands work, and to me, it's less confusing to maintain two different
versions that work properly in their own environments, without a lot of
conditional overhead to make sure any contingency is properly met.

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


"Josh Sale" <jsale@tril dot cod wrote in message
...
or parallel versions for Excel 2007 and the Excel we've known and used
for a decade.

Ugh!

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

I hear you ... but I'm not there yet.

Thanks.




"Jon Peltier" wrote in message
...
I agree, but I've found that there are some subtle differences in how VBA
commands work, and to me, it's less confusing to maintain two different
versions that work properly in their own environments, without a lot of
conditional overhead to make sure any contingency is properly met.

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

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

Hi Jim,

I'm relieved to read your comments! I must admit though I was surprised to
read that IRibbonControl must be fully declared Early bound and fails if 'As
Object'.

Josh,
Is the reason you need to fully compile your project is because it's an xls
that might end up being saved by a user in XL2007 and subsequently used in
an earlier version. Indeed I can see that could be a problem, perhaps not
only due to version specific code but other reference problems.

If it's just an xla I find the best compromise between file size and
performance is, with a brand new or cleaned project, run one or two
procedures in each module (not any version specific code) before saving and
distributing, ie not a full compile. Saved of course in the earliest XL
version.

Regards,
Peter T


"Jim Rech" wrote in message
...
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

I'm relieved to read your comments! I must admit though I was surprised to
read that IRibbonControl must be fully declared Early bound and fails if
'As
Object'.

It turns out that iRibbonControl isn't the only thing that is a bit odd in
all of this. RibbonX supports a callback named getEnabled which takes two
arguments. An IRibbonControl and a Boolean. However if you define the
boolean as a Boolean you get a type error when the callback occurs. The
boolean must be defined as a Variant.

Go figure!

Josh,
Is the reason you need to fully compile your project is because it's an
xls
that might end up being saved by a user in XL2007 and subsequently used in
an earlier version. Indeed I can see that could be a problem, perhaps not
only due to version specific code but other reference problems.

If it's just an xla I find the best compromise between file size and
performance is, with a brand new or cleaned project, run one or two
procedures in each module (not any version specific code) before saving
and
distributing, ie not a full compile. Saved of course in the earliest XL
version.

My project is an xla. I've found that certain bits of my code seem to be
timing dependent. If the project hasn't been compiled then certain bits of
code when executed cause Excel to get an exception and then die. If the
user waits a while after launching Excel and before running the add-in code,
then the background compile seems to take care of things.

So my strategy is to compile all my projects for release under XL97 and then
to dynamically recompile the add-ins when run under later versions of Excel.
But obviously this strategy depends on the recompile not generating a
compile error.

So are you saying that running a procedure in each module of the add-in
causes each module to be completely compiled but without compile error
checking taking place?

josh

Hi Josh,

Go figure!

My strategy of slight delay into XL2007 was to shamelessly wait whilst the
likes of you others do all the figuring and save me a whole bunch of time
<g

So are you saying that running a procedure in each module of the add-in
causes each module to be completely compiled but without compile error
checking taking place?

No. As I understand, in a fresh clean project with no code ever run, when
you run one or two procedures in a module those proc's are fully compiled
together with other some other stuff in the module, such as the location of
other proc's and module level variables, also dependencies to proc's in
other modules. The "as is" extent of compiled code is saved with the project
(also to avoid other garbage only run once to compile as necessary before
saving & distributing).

So doing what I described doesn't fully compile the project. However
typically I don't notice any difference in execution speed between partially
and fully compiled. In any case any difference would only exist the first
time code was run in a session, once an uncompiled proc' has run it remains
compiled for the rest of the session.

A lot of my understanding was based on a very old and possibly out of date
white paper by Don Baarns. Unfortunately his site no longer appears active,
not sure if that's temporary or permanent. However if you paste this link
into Google and select "Cached" you can still read it.

http://archive.baarns.com/excel/develop/vbaperfm.asp

Apart from the above the point re catering for different versions, is if
later version code has not been compiled its existence in the project
shouldn't cause a problem in an earlier version, providing of course that
code is only called subject to app.version. I think it's also worthwhile
placing such code in the last inserted module.

It would though be a good idea to do a full compile in XL2007 merely as an
error check, and similar in each earlier version commenting out any later
stuff. Then do the partial compile in a 'clean' project in the earliest
version before distributing.

Finally just to re-iterate, all this is only relevant for a file that's not
going to be saved by a user in a later version and subsequently in an
earlier version, typically that means an addin not an xls.

Regards,
Peter T


"Josh Sale" <jsale@tril dot cod wrote in message
...
I'm relieved to read your comments! I must admit though I was surprised
to
read that IRibbonControl must be fully declared Early bound and fails if
'As
Object'.

It turns out that iRibbonControl isn't the only thing that is a bit odd in
all of this. RibbonX supports a callback named getEnabled which takes two
arguments. An IRibbonControl and a Boolean. However if you define the
boolean as a Boolean you get a type error when the callback occurs. The
boolean must be defined as a Variant.

Go figure!

Josh,
Is the reason you need to fully compile your project is because it's an
xls
that might end up being saved by a user in XL2007 and subsequently used
in
an earlier version. Indeed I can see that could be a problem, perhaps
not
only due to version specific code but other reference problems.

If it's just an xla I find the best compromise between file size and
performance is, with a brand new or cleaned project, run one or two
procedures in each module (not any version specific code) before saving
and
distributing, ie not a full compile. Saved of course in the earliest XL
version.

My project is an xla. I've found that certain bits of my code seem to be
timing dependent. If the project hasn't been compiled then certain bits
of
code when executed cause Excel to get an exception and then die. If the
user waits a while after launching Excel and before running the add-in
code,
then the background compile seems to take care of things.

So my strategy is to compile all my projects for release under XL97 and
then
to dynamically recompile the add-ins when run under later versions of
Excel.
But obviously this strategy depends on the recompile not generating a
compile error.

So are you saying that running a procedure in each module of the add-in
causes each module to be completely compiled but without compile error
checking taking place?

josh

Thanks Peter.

The Baarns site seems to be up now and I read the white paper. I searched
for but couldn't find the CallCompileNSave routine referenced in the white
paper.

My plate is full right now, but when I have a chance, I'll try your approach
to handling compilation.

Thanks,

josh




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

Go figure!

My strategy of slight delay into XL2007 was to shamelessly wait whilst the
likes of you others do all the figuring and save me a whole bunch of time
<g

So are you saying that running a procedure in each module of the add-in
causes each module to be completely compiled but without compile error
checking taking place?

No. As I understand, in a fresh clean project with no code ever run, when
you run one or two procedures in a module those proc's are fully compiled
together with other some other stuff in the module, such as the location
of
other proc's and module level variables, also dependencies to proc's in
other modules. The "as is" extent of compiled code is saved with the
project
(also to avoid other garbage only run once to compile as necessary before
saving & distributing).

So doing what I described doesn't fully compile the project. However
typically I don't notice any difference in execution speed between
partially
and fully compiled. In any case any difference would only exist the first
time code was run in a session, once an uncompiled proc' has run it
remains
compiled for the rest of the session.

A lot of my understanding was based on a very old and possibly out of date
white paper by Don Baarns. Unfortunately his site no longer appears
active,
not sure if that's temporary or permanent. However if you paste this link
into Google and select "Cached" you can still read it.

http://archive.baarns.com/excel/develop/vbaperfm.asp

Apart from the above the point re catering for different versions, is if
later version code has not been compiled its existence in the project
shouldn't cause a problem in an earlier version, providing of course that
code is only called subject to app.version. I think it's also worthwhile
placing such code in the last inserted module.

It would though be a good idea to do a full compile in XL2007 merely as an
error check, and similar in each earlier version commenting out any later
stuff. Then do the partial compile in a 'clean' project in the earliest
version before distributing.

Finally just to re-iterate, all this is only relevant for a file that's
not
going to be saved by a user in a later version and subsequently in an
earlier version, typically that means an addin not an xls.

Regards,
Peter T

"Peter T" <peter_t@discussions wrote in message
...

Go figure!

My strategy of slight delay into XL2007 was to shamelessly wait whilst the
likes of you others do all the figuring and save me a whole bunch of time
<g

LOL. Mine too!

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

"Josh Sale" <jsale@tril dot cod wrote in message
...

The Baarns site seems to be up now and I read the white paper.

Indeed it seems to be active now, not sure why it wasn't for me yesterday
though the design/interface seems different to what I recall.
http://archive.baarns.com/

Re-reading the white paper I'm not sure what I wrote yesterday was fully
accurate. It implies running one routine in each module does fully compile
the module. Purely from observation I'm not so sure about that.

I searched for but couldn't find the CallCompileNSave routine referenced
in the white paper.

"The routine for creating Excodes in all modules is called
"CallCompileNSave" and is found in the Baarns Developer Jump Start."

http://archive.baarns.com/excel/products/bdjs.asp

Except CallCompileNSave does not appear to be in this download "DJS_97.xls".
Though it does contain some interesting stuff, bearing in mind nothing has
been updated since early XL97 and without the benefit of knowledge
accumulated since it's impressive.

FWIW, I maintain a template (in earliest version) of the addin in
development with no code in it at all, just anything on sheets and file
properties. When I want to do a partial compile ready for distribution I
drag all modules (in particular order) from the working file into the
template and paste any code into the ThisWorkbook module. After partial
compile and save I don't change amend anything in that file.

Regards,
Peter T

"Jon Peltier" wrote in message

"Peter T" wrote in message

Go figure!

My strategy of slight delay into XL2007 was to shamelessly wait whilst
the
likes of you others do all the figuring and save me a whole bunch of
time
<g

LOL. Mine too!

- Jon
-------

Seems to be paying off nicely !

Regards,
Peter T