Prev: Sort by one column then another.
Next: Figuring out MAX LEN in a given column
From: buddylake on 12 May 2010 00:09
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!

From: GS on 12 May 2010 03:49
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
--


From: Peter T on 12 May 2010 05:16
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" <buddylake(a)discussions.microsoft.com> wrote in message
news:02FAA391-6D83-46D3-8DBF-0A05F26C7E8D(a)microsoft.com...
> 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!
>


From: Peter T on 12 May 2010 05:23
"GS" <GS(a)discussions.microsoft.com> wrote in message
news:euEPvea8KHA.3880(a)TK2MSFTNGP04.phx.gbl...

> 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


From: GS on 12 May 2010 06:49
Peter T has brought this to us :
> "GS" <GS(a)discussions.microsoft.com> wrote in message
> news:euEPvea8KHA.3880(a)TK2MSFTNGP04.phx.gbl...
>
>> 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


 |  Next  |  Last
Pages: 1 2 3 4 5
Prev: Sort by one column then another.
Next: Figuring out MAX LEN in a given column