1. Background:

I have a COM addins developed in Visual basic 6.0 for EXCEL 2007. The COM
addin has its toolbar visible. It exposes customized 'Copy' as a button on
the toolbar, which will call MyCopyAction routine in the COM addin.

2. What I want:

I want to map a shortcut key for the customized 'copy' operation, which
indeed invoke the same routine as that on toolbar, i.e. MyCopyAction.

3. What I tried in the COM addin:

I added the following line in

Private Sub AddinInstance_OnStartupComplete(custom() As Variant)

....

Application.OnKey "+^c", "MyCopyAction"
....

End Sub

Problem was: Message box from EXCEL "cannot run the macro MyCopyAction ...".
MyCopyAction is not a macro. It's a routine in the COM AddIn. What options
do I have to make it work?

Thanks!

buddylake used his keyboard to write :
1. Background:

I have a COM addins developed in Visual basic 6.0 for EXCEL 2007. The COM
addin has its toolbar visible. It exposes customized 'Copy' as a button on
the toolbar, which will call MyCopyAction routine in the COM addin.

2. What I want:

I want to map a shortcut key for the customized 'copy' operation, which
indeed invoke the same routine as that on toolbar, i.e. MyCopyAction.

3. What I tried in the COM addin:

I added the following line in

Private Sub AddinInstance_OnStartupComplete(custom() As Variant)

...

Application.OnKey "+^c", "MyCopyAction"
...

End Sub

Problem was: Message box from EXCEL "cannot run the macro MyCopyAction ...".
MyCopyAction is not a macro. It's a routine in the COM AddIn. What options
do I have to make it work?

Thanks!

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.

The first thing I see in your code is that Application is not referring
to your COMAddin. (Assumes you have 'Set' a global variable initialized
to ref Excel in the OnConnection routine) So.., if appXL is your
COMAddin's global variable that holds its ref to Excel then the line of
code from your COMAddin should be:

appXL.OnKey...

Also, I don't see where you tell Excel that "MyCopyAction" is located
in your COMAddin. In this case, I think it will have to be a public
method in order for Excel to access it. I know that from Excel, we must
ref the COMAddin same as we would ref executing a macro in another
workbook:

Application.COMAddIns(gsAPP_NAME & ".Connect").Object.MyCopyAction

Here again, you need to replace 'Application' with your object var. So
give this a try:

With appXL
.OnKey "+^c", _
.COMAddins("gsAPP_NAME & ".Connect").Object.MyCopyAction
End With

I don't know if it will work because I set these up in my xla file to
go through the common entry point procedure.

Good luck
Garry
--

"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

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.

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)

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)

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.

Plus all the perks that go with working in VB6 over VBA. (forms,
control arrays, built-in MAPI support, ...) 'also available to DLL<g

So the short answer to your Q is: It has way more advantages than a
using a xla to call into a DLL. I suspect, though, that you already
knew all this, right?<g

regards,
Garry

"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

Peter T submitted this idea :
"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.

Excerpt from Professional Excel Development, Ch21:
Exploiting Separate Threading
"One of the more interesting things about COM Add-ins is that each one
is given its own execution thread. The vast majority of Excel and VBA
is single-threaded, meaning that VBA code stops when Excel is working
(such as showing one of its dialogs) and vice versa. COM Add-ins dont
have this limitation. A COM Add-in can initialize a Windows timer
callback, tell Excel to display a dialog (or Print Preview or
whatever), then continue processing (in the callback function) while
Excel is still displaying the dialog. This allows us to (a)
pre-populate the dialog, (b) watch what the user is doing within the
dialog (and respond to it) and even (c) change the layout of the dialog
itself!"



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)

Excerpt from Professional Excel Development, Ch21:
Multi-Application Add-ins
"A COM Add-in can contain multiple Designer classes, each handling the
connection to a different Office application. Imagine an €˜Insert
Customer Details add-in, which displayed a form allowing you select a
customer from a central database and then inserted their name, address
and/or telephone number in the current place in the document. By
including multiple Designer classes in the add-in, we could easily make
the add-in available to all the Office applications. Each classs
OnConnection event would be used to add a standard menu item to the
host applications command bars, with the click event handled by a
single class. When clicked, it would display the form and would only
branch into application-specific code when the €˜Insert button was
clicked to insert the selected details into the cell, paragraph, field,
presentation or web page."



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 stipulated 'via code'! It's actually easier to do in the COMAddin,
NOT harder, because I don't need to use a separate xlam file for v12+,
I don't need to use M$'s special Custom UI editor, and the xml is
secure (as opposed to being in a workbook that anyone can edit as they
please).

I'm saying that INSTEAD of editing xml in a xlam, I pass the xml to
Excel VIA CODE in the Designer. This is purely to handle the ribbon
config, which (in most cases) basically removes everything except the
Addins tab. My xla merely provides the menus/toolbar used for the
COMAddin's runtime UI. These have a common entry point into the
COMAddin. No callbacks are used or required (but I could optionally do
that if/when needed).

This requires setting refs to MSO12 AND Excel12 LIBs in the VB6
COMAddin's References dialog, and using the special interface designed
for this purpose:
Implements IRibbonExtensibility

and using this function to load the xml:
IRibbonExtensibility_GetCustomUI(ByVal RibbonID As String) As String
IRibbonExtensibility_GetCustomUI = SetRibbonXML
End Function
SetRibbonXML is basically the same as what would be in a xlam's xml.

In summary:
The COMAddin handles all aspects of the UI except menus/toolbar.
The XLA only handles menus/toolbar creation/removal. (for now)
No separate xlam required for v12+.

I'd be happy to help you in any way I can if you have interest in
exploring this further. Essentially, besides a lot of back-and-forth
with Rob, my main learning resources we
Professional Excel Development
Excel 2007 VBA Developer's Reference

and so I highly recommend these. Though, I suspect you may already have
them.<g Most of the back and forth with Rob concerned issues relavent
to my goals that weren't covered in either book. I also studied Ron de
Brun's materials to get ideas for setting up different UI scenarios.
(Ditto the recommend/suspect you already have...)

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.

Nope! I'm saying I use the same xla for all versions. In earlier vers,
my menus/toolbar are basically setup like dictator apps. In v12+ they
appear on the Addins tab, which is the only tab I make available at
runtime once the COMAddin passes the xml to Excel via the above
function.

Bear in mind that all my apps now use their own instance of Excel,
except in cases where the client wants to add functionality to their
default instance. In those cases I will usually provide a xla because
that's likely going to be the only format they'll be able to access the
source code in (if need be). Since they pay for it, it's their
property. Every client, by default, has a VBIDE that they can use to
access that source code. This is not likely to be the case for a VB6
COMAddin!


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).

I'm saying that in any version there is no VBA in my xla other than
whats necessary to create/remove the menus/toolbar. That is the only
purpose (for now) of using the xla at all. I haven't got the mechanics
for using the table-driven methodology figured out yet for doing this
in VB6, but working on it so as to (eventually) not need XLAs. Once I
resolve that part it's all going to be packaged inside the COMAddin for
all x32 versions.



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.

No, that's not entirely true. If we create our menus for v12+ in our
COMAddin then WE HAVE NO CHOICE but to implement a mechanism to handle
hooking, callbacks, and icon images. Using the xla to manage our
menus/toolbar obviates all need for that. They have a single entry
point into our COMAddin. The COMAddin handles all its internal
messaging as well as communications with/from Excel as per normal. So
things like error handling and normal Excel events can be done by our
COMAddin in a usual manner. The XLA commandbar builder utility has its
own error handling and shutdown cleanup, and so our COMAddin doesn't
need to be concerned with any of that either.

Rob said he used to do commandbar building from a text file. I might do
something similar with ADO and dump the file contents into a grid on a
form, and just modify the code to walk through the grid as it now does
a spreadsheet. Seems simple enough!

Likewise the button images (if you use custom ones) have to go somewhere.

Yes they do. They (if custom) currently exist on the worksheet table
for building menus/toolbar. Once I figure how I want to duplicate this
table-driven methodology inside a VB6 COMAddin I will move them into a
res. Meanwhile, all works flawlessly as expected to work and so no
interuption for getting solutions to my clients. Everything we do can
always be updated (or replaced with a newer ver).

Regards,
Peter T

One thing I did not elaborate on is that my apps use their own instance
of Excel. I guess that means I'll be disclosing some of my app
architecture...

Automating my own instance allows me to lock it down however I want,
design whatever UI elements I want, and prevent users from using my
instance for any other purpose than what it was designed for. (As said
previously, some clients don't even realize they are using Excel!)

Implementing this requires using a VB 'FrontLoader.exe'. This does all
kinds of tests before it even allows startup, and if all tests pass it
creates our instance, loads the COMAddin, and passes a 'ValidStartup'
flag so the user can access functionality via the menus/toolbar. some
tests include making sure the respective MSO apps are installed, making
sure all required files/runtime components are available, and license
validation.

What I mean by 'loads the COMAddin' is that its StartupBehavior in the
designer is deliberately set to '(None)' so that it doesn't appear in
any Excel dialogs, or if manually added to a user instance it will not
persist. Additionally, the COMAddin will not execute any internal code
without the var gbValidStartup (flag) set to TRUE. This flag is an
encrypted key that the frontloader passes after loading the COMAddin.
Since COMAddins are a DLL just like any other DLL, its methods can be
accessed at any time by any app. The only way to prevent unauthorized
use is to restrict execution of its methods by any other means than our
project provides. (As I said, some of my clients are overly security
cautious; some are obsessed!) One side benefit of this to me is I can
use that to turn off access for any reason (like payment not received
within 30 days, for example). So by building this capability into the
project's structure it makes it easy to say to clients: "Try it out for
30 days. Let me know if you want any changes made within that trial
period. Otherwise, I expect to be fully paid if you plan to continue
using it!". Kind of puts my time and effort at risk somewhat, but it
hasn't been fruitless so far, thankfully.<bg This feature is
controlled by my licensing methodology, and so is included in the
frontloader pre-satartup tests.

Finally, the key to understanding why I use the xla at all is this: I
fully develop in VBA in the earliest version expected to be used. since
the commandbar builder components already exist, I just export all
other mod/cls components to import into a COMAddin shell and go from
there. All coding uses fully qualified refs (even with XL globals) so
the only chore after importing the code is replacing any instances of
'Application' with my global var used for that (appXL). This only
accounts for non-VB6 specific code. I continue development from their
using the COMAddin from within whatever version I want to dev/test in,
via a PERSONAL.XLS proc that mimics the frontloader startup routines.
(Another side benefit of setting its StartupBehavior as mentioned)

All code is made version-aware as required (by default). I can move
from one ver to the other on my dev machine because I have it set up
(currently) with v9 to v12 (no VM). I no longer dev for v9 and so I
have 3 'clean' test machines (2x XPx32, 1 Win7x64) for stand-alone
version testing. I did have a Vista machine but not long enough to
bother setting it up as a test unit because I hated it and so gave it
away to my daughter for business use.

So, Peter, there you have it! Lots to digest, huh? Hey, I got nothing
but time on my hands and so this is the sort of stuff I do with it.

Regards,
Garry

Unfortunately it's not possible to directly assign a shortcut for a routine
in an ActiveX dll, such as a ComAddin.

One way or another will need a bit of help from VBA. As your ComAddin has a
button to call your routine the simplest way would be to trigger its click
event, which I assume you are already trapping with WithEvents in your
ComAddin.

In VBA,
Sub MyCopyAction()
dim cbt as commandbarbutton
set cbt = commandbars.findcontrol(tag:=myTag)
' or maybe say
set cbt = myBar.Controls(myCaption)

' and the fire the button
cbt.Excecute
End Sub

Another way would again from a VBA macro to call a public method in a public
class (with it's instancing set to MultiUse or GlobalMultiUse). You'd
probably also want to set a reference to the dll in the VBA project, though
not necessary if use CreateObject to instanciate the class. You could also
use the Connect class if Public though I'd suggest use an ordinary class.

Regards,
Peter T


"buddylake" wrote in message
...
1. Background:

I have a COM addins developed in Visual basic 6.0 for EXCEL 2007. The COM
addin has its toolbar visible. It exposes customized 'Copy' as a button
on
the toolbar, which will call MyCopyAction routine in the COM addin.

2. What I want:

I want to map a shortcut key for the customized 'copy' operation, which
indeed invoke the same routine as that on toolbar, i.e. MyCopyAction.

3. What I tried in the COM addin:

I added the following line in

Private Sub AddinInstance_OnStartupComplete(custom() As Variant)

...

Application.OnKey "+^c", "MyCopyAction"
...

End Sub

Problem was: Message box from EXCEL "cannot run the macro MyCopyAction
...".
MyCopyAction is not a macro. It's a routine in the COM AddIn. What
options
do I have to make it work?

Thanks!