Consulting

Results 1 to 17 of 17

Thread: Sleeper: .OnAction = "Macro" Syntax for COM Addin

  1. #1
    VBAX Regular Mike_R's Avatar
    Joined
    May 2004
    Location
    Plainsboro, NJ
    Posts
    46
    Location

    Sleeper: .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
    OnAction = "MyCOMAddin.MySub"
    From what I understand, my syntax should be something like
    .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
    Last edited by Mike_R; 05-26-2004 at 12:54 PM.

  2. #2
    MS Excel MVP VBAX Regular
    Joined
    May 2004
    Posts
    30
    Location
    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
    Regards,

    Juan Pablo Gonz?lez

  3. #3
    Site Admin
    The Princess
    VBAX Guru Anne Troy's Avatar
    Joined
    May 2004
    Location
    Arlington Heights, IL
    Posts
    2,530
    Location
    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
    ~Anne Troy

  4. #4
    VBAX Regular Mike_R's Avatar
    Joined
    May 2004
    Location
    Plainsboro, NJ
    Posts
    46
    Location
    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

  5. #5
    VBAX Regular Mike_R's Avatar
    Joined
    May 2004
    Location
    Plainsboro, NJ
    Posts
    46
    Location
    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
    Last edited by Mike_R; 05-26-2004 at 01:32 PM.
    Try out the [VBA] tags!
    Option Explicit, don't leave home without it...


  6. #6
    BoardCoder
    Licensed Coder VBAX Expert mark007's Avatar
    Joined
    May 2004
    Location
    Leeds, UK
    Posts
    622
    Location
    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!
    "Computers are useless. They can only give you answers." - Pablo Picasso
    Mark Rowlinson FIA | The Code Net

  7. #7
    VBAX Regular Mike_R's Avatar
    Joined
    May 2004
    Location
    Plainsboro, NJ
    Posts
    46
    Location
    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
    Try out the [VBA] tags!
    Option Explicit, don't leave home without it...


  8. #8
    VBAX Mentor XL-Dennis's Avatar
    Joined
    May 2004
    Location
    ?stersund, Sweden
    Posts
    499
    Location
    Mike,

    Thanks for sharing the work

    I've asked Ivan to have a look on it

    Kind regards,
    Dennis
    Kind regards,
    Dennis

    ExcelKB | .NET & Excel | 2nd edition PED


  9. #9
    VBAX Regular Mike_R's Avatar
    Joined
    May 2004
    Location
    Plainsboro, NJ
    Posts
    46
    Location
    Thanks Dennis, any and all is thoroughly appreciated!

    ,
    Mike
    Try out the [VBA] tags!
    Option Explicit, don't leave home without it...


  10. #10
    MS Excel MVP VBAX Regular
    Joined
    May 2004
    Posts
    30
    Location
    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....
    Regards,

    Juan Pablo Gonz?lez

  11. #11
    VBAX Regular Mike_R's Avatar
    Joined
    May 2004
    Location
    Plainsboro, NJ
    Posts
    46
    Location
    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
    Try out the [VBA] tags!
    Option Explicit, don't leave home without it...


  12. #12
    MS Excel MVP VBAX Regular
    Joined
    May 2004
    Posts
    30
    Location
    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...
    Regards,

    Juan Pablo Gonz?lez

  13. #13
    VBAX Regular Mike_R's Avatar
    Joined
    May 2004
    Location
    Plainsboro, NJ
    Posts
    46
    Location
    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.
    Try out the [VBA] tags!
    Option Explicit, don't leave home without it...


  14. #14
    Site Admin
    The Princess VBAX Guru Anne Troy's Avatar
    Joined
    May 2004
    Location
    Arlington Heights, IL
    Posts
    2,530
    Location
    Was this ever solved, guys?
    ~Anne Troy

  15. #15
    VBAX Regular Mike_R's Avatar
    Joined
    May 2004
    Location
    Plainsboro, NJ
    Posts
    46
    Location
    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...
    Try out the [VBA] tags!
    Option Explicit, don't leave home without it...


  16. #16
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,940
    Location
    Quote Originally Posted by Dreamboat
    Was this ever solved, guys?

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

  17. #17
    VBAX Regular Mike_R's Avatar
    Joined
    May 2004
    Location
    Plainsboro, NJ
    Posts
    46
    Location
    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...
    Try out the [VBA] tags!
    Option Explicit, don't leave home without it...


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •