Running ribbon routines from xll

[ajwillshire]

Hi,

I've spent some time looking through the other forum posts but I
haven't been able to find quite what I need - apologies if it's
already here somewhere!

I'm creating my ribbon interface within the Excel file itself because
I only want it to show up on the particular workbook that I've opened.
Up until now I've just used VBA macros to run on the ribbon callbacks
etc.

What I would like to do is move these subroutines into my VB xll file
which is linked via Excel DNA but I can't seem to get it right.

Based on some other posts here I've tried this:


Imports System.Runtime.InteropServices
Imports ExcelDna.Integration.CustomUI

Namespace DNAAddin
<ComVisible(True)>
Public Class Ribbon
Inherits ExcelRibbon

Public Shared ribbonUI As IRibbonUI

Public Shared Sub RibbonOnLoad(ByVal control As IRibbonUI)

ribbonUI = control

End Sub


Public Shared Sub VBRibbonTest1(ByVal control As
IRibbonControl)

MsgBox("Hello - This is coming from VB!!", vbOKOnly)

End Sub

End Class

End Namespace


Unfortunately, the ribbon does not see the "RibbonOnLoad" sub when it
loads, nor can it see the VBRibbonTest1 sub.

Thanks,
Andrew

[Govert van Drimmelen]

Hi Andrew,

I think those ribbon handlers must not be 'Shared'.

-Govert

[ajwillshire]

Hi Govert,

Thanks for that, but it doesn't seem to make a difference. I think I
have an additional problem in that the add-in is loaded when the book
is loaded so there might be a conflict there too.

I suspect I'll need to write a VBA interface which might call the VB
code. Or I might just keep it in the workbook!

I'm reading up on using the ribbon with the VSTO tools to see if
there's a way to do it - if I discover a solution I'll post it in
here.

Cheers,
Andrew

> > Andrew- Hide quoted text -
>
> - Show quoted text -

[Govert van Drimmelen]

Hi Andrew,

Maybe there is a problem with your ribbon xml? How is it loaded - from
a .dna file?

You should be able to make exactly the same ribbon interface in Excel-
DNA as you can in VBA.
(Of course the Excel-DNA ribbon will be loaded when you load
the .xll.)

VSTO has some high-level wrapper classes that make the ribbon a bit
easier to use. But I believe you get some deployment challenges in
return.

If you post a more complete example, maybe a single .dna file with the
ribbon xml and the handlers in, based on one of the samples, I'd be
happy to dig in a bit to see what might be wrong.

Regards,
Govert

[ajwillshire]

Hi Govert,

The ribbon customisations were actually in the Excel workbook itself -
edited with the free Custom UI editor.

This is the xml from the workbook:

<?xml version="1.0" encoding="utf-8"?>
<!-- This is an attempt at customising the Ribbon for the D2D
modelling tool -->
<customUI onLoad="RibbonOnLoad" xmlns="http://schemas.microsoft.com/
office/2006/01/customui">
<commands>
<command idMso="ApplicationOptionsDialog" enabled="true" />
</commands>
<ribbon startFromScratch="false">
<tabs>
<tab id="CustomTab" label="Testing" insertAfterMso="TabHome">
<group id="MyGroup" label="Test Group">
<button id="BigButton1" label="Test1" size="large"
onAction="VBRibbonTest1" imageMso="CreateFormInDesignView" />
</group>
</tab>
</tabs>
</ribbon>
</customUI>

So, it's really just one big button wanting to call "VBRibbonTest1".

My VB code is then the same as above, thoguh i've now taken out the
"shared":

Imports System.Runtime.InteropServices
Imports ExcelDna.Integration.CustomUI

Namespace DNAAddin
<ComVisible(True)>
Public Class Ribbon
Inherits ExcelRibbon

Public Shared ribbonUI As IRibbonUI

Public Sub RibbonOnLoad(ByVal control As IRibbonUI)

ribbonUI = control

End Sub

Public Sub VBRibbonTest1(ByVal control As IRibbonControl)

MsgBox("Hello - This is coming from VB!!", vbOKOnly)

End Sub
End Class
End Namespace

When I press the button though, Excel tells me that the macro cannot
be found in the current workbook.
I suspect that I'm missing something in the setting up of the ribbon,
so any help you could give me would be much appreciated.

I didn't get very far with the VSTO solution either - I was struggling
to get it to scope to just one workbook which is important for my
application.

Thanks,
Andrew

[ajwillshire]

Doing a little bit more experimenting...

The VB macro doesn't appear to be visible to Excel with the "control
as IRibbonControl" in the declaration. If I change it to "object" then
it becomes visible but I get an error message which just says "400"
when I call it from the ribbon. I'm able to run the macro if I pass it
a string or a number so it seems to object to the control object being
passed over. (I have reverted to making the VB subs Public Shared as
before.

[Govert van Drimmelen]

Hi Andrew,

The methods on your ExcelRibbon-derived class must not be 'Shared'.

Your xml in the workbook won't go with the ExcelRibbon class in
the .xll.
There are two ways to get Excel to load the ribbon xml:
1. Put the xml in a .dna file.
2. Return the xml from an overload of ExcelRibbon.GetCustomUI.

Below I paste a .dna file that contains both the VB code and
your .xml. Just add a .xll and run and everything should work.
To use this with your own library, replace the code in the .dna file
with an <ExternalLibrary Path="..."/>.

-Govert


<DnaLibrary Name="Ribbon Tests">
<![CDATA[

Imports System.Runtime.InteropServices
Imports ExcelDna.Integration.CustomUI
Namespace DNAAddin

<ComVisible(True)> _

Public Class Ribbon
Inherits ExcelRibbon
Public Shared ribbonUI As IRibbonUI
Public Sub RibbonOnLoad(ByVal control As IRibbonUI)
ribbonUI = control

MsgBox("RibbonOnLoad", vbOKOnly)

End Sub
Public Sub VBRibbonTest1(ByVal control As IRibbonControl)
MsgBox("Hello - This is coming from VB!!", vbOKOnly)
End Sub
End Class
End Namespace

]]>
<CustomUI>
<!-- Inside here is the RibbonX xml passed to Excel -->

<customUI
onLoad="RibbonOnLoad"
xmlns="http://schemas.microsoft.com/office/2006/01/customui">
<commands>
<command idMso="ApplicationOptionsDialog" enabled="true" />
</commands>
<ribbon startFromScratch="false">
<tabs>
<tab id="CustomTab" label="Testing"
insertAfterMso="TabHome">
<group id="MyGroup" label="Test Group">
<button id="BigButton1" label="Test1" size="large"
onAction="VBRibbonTest1" imageMso="CreateFormInDesignView" />
</group>
</tab>
</tabs>
</ribbon>
</customUI>

</CustomUI>
</DnaLibrary>

[ajwillshire]

Hi Govert,

Thanks for your help. My problem is that the ribbon code goes with the
individual workbook rather than the add-in. This is because some of
the ribbon code is for the purpose of navigating through the workbook,
so if I open another book of a different structure the ribbon won't
apply.

I can get round it by setting up an interface in VBA to call the
macros in the xll.

I don't know much about how the ribbon works, but in a future version
of ExcelDNA would it be possible to pass an IRibbonUI or
IRibbonControl object through the ExcelDNA interface to a .NET
subroutine? Or is that not possible due to the structure?

Thanks,
Andrew