How to map a shortcut key to a routine in COM AddIn for Excel 2007
"GS" wrote in message
...
Peter T has brought this to us :
"GS" wrote in message
...
I use COMAddins but I don't build my menus there; I use a xla for this
and redirect all controls' OnActions through a single procedure that
serves as an entry point to the COMAddin's procedures. It uses the
appropriate calls from within Excel to use the procedures inside the
COMAddin. (No code is in the xla other than what's required to
create/remove menus/toolbars.
Curiosity, why bother making it a ComAddin if it's only going to be
called from a UI created by your xla, with Onactions to your xla. IOW
simply the xla as a wrapper to call the aX dll. You could dispense with
the Connect class altogether.
Regards,
Peter T
Hi Peter,
The only thing i use the xla for is to create/remove the menus/toolbar.
The UI Setup is created/removed by the COMAddin. There are 2 reasons I use
the xla to handle menus/toolbar[s]:
1. I use Rob Boveys commandbar builder table;
2. I provide plugin support for my apps. These are 'addins for my addin'
so to speak, whereby clients can add their own user-specific features and
functionality to the core app to enhance it specific to their needs. I
haven't yet figured out the VB6 mechanics of how to get this to work
in-process to my COMAddins and so using an xla to modify the menus/toolbar
with its own menus facilitates this easily. Also, since these are paid for
by the client then they actually own the source as well, and so this
facilitates them being able to work with that more easily than VB6 source.
Why I 'bother' making COMAddins:
1. Security!
Much of what I do comprises proprietary stuff of mine OR my clients. (ie:
business logic, dbase access passwords, user/password logins, etc)
2. Separate Threading
As you know, VBA stops when Excel starts working. COMAddins don't have
this limitation.
Actually I didn't know that, are you sure! And even if it does, how does it
help in practice.
3. Multiple Designer Class support
Not a major whistle but I do get requests for solutions that work in Excel
and Word. (Though, there's nothing much Word can do that can't be
duplicated with Excel, and without the 'bloat'!<IMO)
If you are not using any of the functionality of a ComAddin, other than
installing it as such, this is a nor issue (I would have thought)
4. A COMAddin is the only current means to set up the ribbon in v12 or
higher via code. You must provide this through the designer. Sure, I could
make a separate xlsm/xlam just for that purpose, need to use the Custom UI
utility and all, but that's more bother than having a designer and less
secure to boot. Since the COMAddin is essentially the same as a DLL, the
advantages (for me at least) are worth it. (IOW, "the juice is worth the
squeeze!"<g)
I don't follow what you mean by ComAddin is the only way to set up the
ribbon. Actually for me I found it quite a learning curve how to figure out
how to do all that, the XML and the call-backs entirely in a ComAddin (much
easier in an xlam!). I'm also confused, you say your xla handles all the
menus in 2000/3 (even though can be done without an xla), yet in v12 you go
the hard way and do it all in the ComAddin without an xlam.
So are you saying are saying in v12 there is no VBA as the ribbon menu is
entirely handled in the ComAddin. Or all the menu, callbacks etc is handled
by VBA in an addin, if so I still done't follow why the need to install the
ComAddin (vs simply an aX dll with xla wrapper).
5. Menus we create in Excel need event hooking for callbacks, etc. and the
single OnAction in the xla obviates need for this. So.., the trade off for
this class is the designer, less code (and so smaller compiled size) since
there's no control event hooking or menu/toolbar building, and no need to
store button images/masks in a res.
Swings and roundabouts I suppose, the menu builder and Click event (or
Ribbon + callback) has to go somewhere. Likewise the button images (if you
use custom ones) have to go somewhere.
Regards,
Peter T
|