.OnAction = "Macro" Syntax for COM Addin

Ok, hey guys, since there's such a lopsided degree of Talent vs. Questioners here at this point, I thought I might throw this one out, and maybe someone here would have a clue.

(I've also put this on Experts-Exchange, but not having much success...)

Here's my Q:

I wish to call a Macro within a COM Addin. Effectively I wish to let

Code:

---

OnAction = "MyCOMAddin.MySub"

---

From what I understand, my syntax should be something like

Code:

---

.OnAction = "!<MYCOMADDIN.MYSUB>"

---

But it does not seem to work.

If this were for a CommandBarButton or CommandButton on a Worksheet, then I could use WithEvents and trap the _Click() Event. However, I seek to trap Application.OnKey() which requires a String name of the macro to be called.

Any ideas on what the correct Syntax would be? (Or if this can be done at all.)

Thanks,
Mike

Well, I have to say that I haven't tried it (I'm too lazy right now to create a test COM Addin), but Stephen Bullen to the rescue:


http://tinyurl.com/28djp

Hey, JP.

Just so you know, this board software abbreviates links for us (I think!).

Here's the same link you posted, without having to use TinyURL, which is of course, collecting data:
http://groups.google.com.co/groups?h...%2Bcom%2Baddin

Juan,

Thank you so much for that... (My god, I can't get over the assemblage of talent over here, wow.)

That's really neat and now I see how to expose an Addin's Class to Excel at run-time. Cool :cool

I think that what you provided is in the right direction, but I need to take this a bit further though... How do you think we could construct a "Macro String" so that I could call something like [vba]Call oExcel.OnKey("{TAB}", "MyMacro")[/vba] Thanks so much for your help so far, I just can't quite see how to do this....

Mike

Ok JP,

Now I've tried all sort of combinations again, including

(1) oExcel.OnKey "{TAB}", "MyCOMAddin.ConnectExcel.MySub<MYCOMADDIN.CONNECTEXCEL<MYCOMADDIN.CONNECTEX CEL.MYSUB>"
(2) oExcel.OnKey "{TAB}", "MyCOMAddin.MySub<MYCOMADDIN.MYSUB<MYCOMADDIN.MYSUB>"

(Note: I had to remove the leading "!" and {GreaterThan} and {LessThan} characters in the above due to HTML parsing issues...)

And no luck.

If anyone wants to try kicking this around themselves, I've made a sample COM Addin along with a Workbook (see attached!) that successfully calls the Addin using the technique suggested in the Steven Bullen thread, above. The attempt to trap the TAB key by using oExcel.OnKey(), however, still eludes me...

Thanks all,
Mike

You could create a short stub function that calls the COM addin sub and call this stub from the OnKey. Not ideal but should work!
:)

Ah, yes, thanks Mark! Yeah, I should have said... That is my current solution: a Macro within an XLA Addin that is (1) called by oExcel.OnKey() and which (2) calls my Sub that resides in my COM Addin.

It all works quite fine, but, well, I was just wondering if it's possible to keep it completely within the COM Addin without having to place something at the Excel Level.

Maybe it just amounts to a "technical challenge" instead of a "real problem", but it would be neat to figure this out.

The only other ideas I have (other than trying to get that "Macro String" right) would be to somehow trap various keys without using oExcel.OnKey()? An API maybe, but I'm woefully poor at API's to even know where to begin or if this is even possible in a COM situation like this (versus a pure VB6 App).

Hmm....

-- Mike

Mike,

Thanks for sharing the work :)

I've asked Ivan to have a look on it ;)

Kind regards,
Dennis

Thanks Dennis, any and all :help is thoroughly appreciated! :yay

:),
Mike

Mike,

I don't see a way to call 'MySub' from Excel directly, without resorting to a small macro in the format that Stephen points out. I'll try to dig around to see if it is possible or not...

BTW, you can remove all those "Calls" from your code. Unless you *want* them there, there's really no point in having them....

Hey JP,

Thanks for the feedback, I also think at this point that a small Sub within an XLA will be required. (Which is actually what I'm currently doing.) But I thought that I'd throw it out among the Guru's to see if I wasn't missing something... Thanks. :)

As for the use of the "Call" keyword, I'm simply a big fan of parentheses, which .Net allows (actually, enforces) without the use of "Call". I guess, though, within VB6 and VBA that this keyword is "old" school and out-of-date. I've now changed it in the above...

Thanks again for your thoughts and time!
Mike

Well, True, but .Net also puts the parenthesis for me... (at least VS.Net 2003 does !), which I love... as I've told Dennis a few times, I love coding in .Net, it makes things very easy...

Quote:

---

Originally Posted by Juan Pablo Gonz?lez

Well, True, but .Net also puts the parenthesis for me... (at least VS.Net 2003 does !), which I love... as I've told Dennis a few times, I love coding in .Net, it makes things very easy...

---

Yeah, I love .Net as well... Unless you're trying to code Excel, LOL.
The COM Interop is a bear. :mad:

Was this ever solved, guys?

No, I'm stumped. :( It might not be do-able? It feels like it should be do-able, but well, the syntax remains elusive... if it can be done at all...

Quote:

---

Originally Posted by Dreamboat

Was this ever solved, guys?

---

Just thought I'd ask again. Mike? Anything yet?

Hey Zack,

Yeah, it definately would be interesting to be able to do this. The best I've come up with is either:

(1) Have the Button call a Macro in a standard XLA, say "MyMacro". Then MyMacro() then calls MyComAddin.MySub().

(2) Avoid the entire .OnAction() concept and instead hook onto whatever Buttons are required via 'WithEvents'. So a COM Addin during startup needs to either identify the buttons it is interested in and hook into it, and/or create it's own buttons as needed.


Calling "MySub()" directly from the .OnAction would be neat, but I have not succeded in working that out nor has anyone I've seen been able to do it. It would be nice, for you would be able to type in the .OnAction for a CommandBar button manually and it would be saved for future sessions, but, well, I can't quite see how to make it work. And it might not be possible, I don't know.

But since there are other ways of dealing with it, I don't think it's a major issue. A curiousity though...