Executing Ribbon buttons within VBA

Add-in Express™ Support Service
That's what is more important than anything else

Executing Ribbon buttons within VBA
 
Tyson Stolarski




Posts: 38
 Joined: 2006-12-01
A few of the users of my Excel add-in like to use VBA scripts to automate their work.

For Excel 2003, I have told them to simply use: 

Application.CommandBars("MyProduct Add-in").Controls("Some Button").Execute


This also used to work for Excel 2007 because I used to set the UseForRibbon property on my CommandBar object to true. Now that I have created a proper ribbon, this is now set to false, and the above VBA code throws an exception (presumably because it cant find the specified CommandBar).

I am wondering how to access the Ribbon and execute the buttons from within VBA?

Alternatively, is there any way to expose some public methods within my classes back into the VBA COM API?
Posted 20 Sep, 2007 04:56:30 Top
Sergey Grischenko


Add-in Express team


Posts: 7233
 Joined: 2004-07-05
Hi Tyson.

To execute the ribbon control you can use the ExecuteMso method of the
Microsoft.Office.Core._CommandBars interface.
Posted 20 Sep, 2007 10:28:26 Top
Tyson Stolarski




Posts: 38
 Joined: 2006-12-01
Thanks Sergey, thats what I was looking for.

I have tried it and I can get it to work for built-in Office ribbon controls: 

Application.CommandBars.ExecuteMso ("Copy")


But I cant seem to get it to work with my own custom ribbon controls. What property of my Ribbon controls specifies the idMso string that is passed to the ExecuteMso method?

I tried setting the ADXRibbonButton.IdMso property, and then using that string as the idMso parameter, but that doesn't seem to work.

Cheers.
Posted 20 Sep, 2007 23:25:26 Top
Sergey Grischenko


Add-in Express team


Posts: 7233
 Joined: 2004-07-05
Hi Tyson.

The fact is that the ExecuteMso method was designed for the built-in Ribbon UI. To execute the custom code from VBA you can use CallProxyXXX methods of the addinmodule. Please look at the following example: http://www.add-in-express.com/projects/vbaexample-cs.zip
Posted 21 Sep, 2007 18:27:31 Top
Tyson Stolarski




Posts: 38
 Joined: 2006-12-01
Thanks Sergey, thats excactly what I was looking for - Giving my users access to an actual interface object, rather than telling them to simply execute my add-in's buttons via VBA code. I can see all sorts of new features to include now!

However, the first thing I went and did before I got too excited was test the same code within Office 2003... and unfortunatly it didn't work:

I get a reference to your COM addin interface object ok: 


Dim addinModule As Object
Set addinModule = Application.COMAddIns.Item("MyExcelAddin").Object


However, calling any of your COM objects methods: 


Call addinModule.CallMethod0("SomeExposedMethod")


throws an error:

"object doesn't support this property of method"

I'm assuming the object I get a reference to using the first 2 lines is a COM object you guys have written for this interface ability, so why does it behave differently within Office 2003?

Cheers.

Tyson.
Posted 04 Oct, 2007 03:29:40 Top
Sergey Grischenko


Add-in Express team


Posts: 7233
 Joined: 2004-07-05
Hi Tyson.

I suppose you use Add-in Express v3.6.1881. If so, please try the following code:

addinModule.SomeExposedMethod()

Now you get an instance of the adxloader in the Object property of the COMAddIn interface. The loader catches your calls and forwards them to the managed add-in.
In the managed code you need to use the Type.Invoke method to communicate with the loader.
Posted 04 Oct, 2007 08:25:24 Top
Tyson Stolarski




Posts: 38
 Joined: 2006-12-01
hmmm... Im using v3.6.1879 - That is what is listed on your downloads page. Where do I find v3.6.1881?

Regardless, the above works brilliantly with v3.6.1879, in both Excel 2003 and 2007. And it is so much nicer that the CallMethod0(...) example!

Thankyou!

Tyson.
Posted 04 Oct, 2007 22:47:49 Top
Tyson Stolarski




Posts: 38
 Joined: 2006-12-01
On a side note, would it be possible for the ADX package to create a .tlb file (COM type library) for the exposed managed classes during the install. This would allow add-in users accessing the managed code via VBA to use the autocomplete functionality.

Or does it already do that and Im simply missing a step?

Cheers.

Tyson.
Posted 04 Oct, 2007 22:56:29 Top
Sergey Grischenko


Add-in Express team


Posts: 7233
 Joined: 2004-07-05
Hi Tyson.

All .tlb files are located in the .\Program Files\Common files\Add-in Express directory. As to the new build, I will send you an email with the link.
Posted 05 Oct, 2007 06:58:15 Top