RunTagMacro
Manfred Usselmann
RunTagMacro doesn't work for me. The button appears, bothing happens,
when I click on it. The other way does work, so no big issue. I just
wonder, what might be wrong.
I've also tested the provided example TestRibbon.dna, same result:
onAction='RunTagMacro' tag='ShowHelloMessage' does not work, but
onAction='OnButtonPressed' does.
Environment: Excel 2007, Win XP SP3.
Thanks,
Manfred
--
Manfred Usselmann <ussel...@icg-online.de>
ICG IT Consulting GmbH, Kelkheim
Govert van Drimmelen
There might be an issue with RunTagMacro if there is no workbook open.
Is this what you find?
Certainly if I have a workbook open, then both buttons in
TestRibbon.dna work fine under my Excel 2007.
Under the covers, RunTagMacro just calls
ExcelDnaUtil.Application.Run(tagName). Perhaps the issue with running
macros when no workbook is open has to do with how I get the
Application object in ExcelDnaUtil.Application. If this is the
problem, then for the ribbons there is another way to get the
Application object, but I'd have to check the COM lifetime issues.
Regards,
Govert
On Apr 17, 9:07 pm, Manfred Usselmann <usselman...@icg-online.de>
wrote:
>
> RunTagMacro doesn't work for me. The button appears, bothing happens,
> when I click on it. The other way does work, so no big issue. I just
> wonder, what might be wrong.
>
> I've also tested the provided example TestRibbon.dna, same result:
>
> onAction='RunTagMacro' tag='ShowHelloMessage' does not work, but
> onAction='OnButtonPressed' does.
>
> Environment: Excel 2007, Win XP SP3.
>
> Thanks,
> Manfred
>
> --
Manfred Usselmann
no, this is not the issue, a workbook is open, but only one button
works (using the unmodified example from the distribution).
Unfortunately I'm also facing issues with the other method, when I try
to run my own function from a VB DLL. This does not work either,
although it works for a function defined in the DNA file.
In addition, when I remove the Public Class MyRibbon which Inherits
ExcelRibbon (copied from your example) from my DNA file (which
specifies my library as ExternalLibrary and the ribbon tabs/buttons in
<CustomUI>), the new ribbon tab is not drawn at all, although the class
of my DLL Inherits ExcelRibbon as well (since the RunTagMacro solution
did not work I tried the other solution).
Running my ExcelCommand from the (Add-Ins) menu via MenuName and
MenuText attributes is currently the only way how I can execute it, but
I would like to use a ribbon button and my own tab.
Regards,
Manfred
> --
> You received this message because you are subscribed to the Google
> Groups "Excel-DNA" group. To post to this group, send email to
> exce...@googlegroups.com. To unsubscribe from this group, send email
> to exceldna+u...@googlegroups.com. For more options, visit
> this group at http://groups.google.com/group/exceldna?hl=en.
>
--
Manfred Usselmann <ussel...@icg-online.de>
Govert van Drimmelen
On my Excel 2007, with the released version of Excel-DNA 0.28 the
TestRibbon.dna sample work fine when any Workbook is open. So there is
something strange with this on your side.
Can you try this with the add-in loaded and a workbook open:
1. Press Alt+F8 to show the Run Macro dialog.
2. Type "ShowHelloMessage" (without the quotes) into the Macro Name
box.
3. This should enable the Run button - press it and check for a
message box to show.
Next test:
1. Open the VBA IDE (Alt+F11).
2. Go to the Immediate window (Ctrl+G).
3. Type: Run("ShowHelloMessage")
4. Press enter and checl for the message box to show.
I'm trying to figure out if there's an issue with the macro, since the
Ribbon is showing OK.
Are any other add-ins loaded into your Excel when you test this?
Could you attach a debugger to Excel and see if anything interesting
is shown in the Output window when you press that button?
--------
The onAction="RunTagMacro" will run a VBA macro (Sub) named in
tag="MyVBAMacro" too (it does on my machine), but the
onAction="MyMacro" attribute will only run methods on the ExcelRibbon-
derived class.
--------
Your compiled ExternalLibrary is probably marked as
[assembly:ComVisible(false)] in the AssemblyInfo.cs/AssemblyInfo.vb
file (it is set in the Project properties page -> Assembly Information
-> Make assembly COM visible). This makes the types in your project
invisible to COM by default, and thus your ExcelRibbon-derived class
does not make the Ribbon appear.
To fix this, change the assembly setting, or just make the class in
your compiled project look like this:
[ComVisible(true)]
public class MyRibbon : ExcelRibbon { ...}
After recompiling the ribbon should appear.
Regards,
Govert
On Apr 17, 11:52 pm, Manfred Usselmann <usselman...@icg-online.de>
Manfred Usselmann
On Sun, 17 Apr 2011 15:31:48 -0700 (PDT)
Govert van Drimmelen <gov...@icon.co.za> wrote:
> On my Excel 2007, with the released version of Excel-DNA 0.28 the
> TestRibbon.dna sample work fine when any Workbook is open. So there is
> something strange with this on your side.
Looks like it.
> Can you try this with the add-in loaded and a workbook open:
> 1. Press Alt+F8 to show the Run Macro dialog.
> 2. Type "ShowHelloMessage" (without the quotes) into the Macro Name
> box.
> 3. This should enable the Run button - press it and check for a
> message box to show.
>
> Next test:
> 1. Open the VBA IDE (Alt+F11).
> 2. Go to the Immediate window (Ctrl+G).
> 3. Type: Run("ShowHelloMessage")
> 4. Press enter and checl for the message box to show.
>
> I'm trying to figure out if there's an issue with the macro, since the
> Ribbon is showing OK.
>
> Are any other add-ins loaded into your Excel when you test this?
> Could you attach a debugger to Excel and see if anything interesting
> is shown in the Output window when you press that button?
I will do all this and let you know.
> --------
>
> The onAction="RunTagMacro" will run a VBA macro (Sub) named in
> tag="MyVBAMacro" too (it does on my machine), but the
> onAction="MyMacro" attribute will only run methods on the ExcelRibbon-
> derived class.
Yes, got that.
> --------
>
> Your compiled ExternalLibrary is probably marked as
> [assembly:ComVisible(false)] in the AssemblyInfo.cs/AssemblyInfo.vb
> file (it is set in the Project properties page -> Assembly Information
> -> Make assembly COM visible). This makes the types in your project
> invisible to COM by default, and thus your ExcelRibbon-derived class
> does not make the Ribbon appear.
>
> To fix this, change the assembly setting,
I've set ComVisible to true, but I get the following warnings when I
build the project:
"[long path removed]\mytest.dll" does not contain any types that can be
registered for COM Interop. (MSB3214)
"[long path removed]\mytest.dll" does not contain any types that can be
unregistered for COM Interop. (MSB3391)
> or just make the class in
> your compiled project look like this:
>
> [ComVisible(true)]
> public class MyRibbon : ExcelRibbon { ...}
This would be
<ComVisible(True)> _
Public Class MyRibbon
Inherits ExcelRibbon
in my case, I assume? I will try this as well.
> After recompiling the ribbon should appear.
Unfortunately it does not work for me so far.
Thanks,
Manfred
> this group at http://groups.google.com/group/exceldna?hl=en.
>
--
Manfred Usselmann <ussel...@icg-online.de>
Manfred Usselmann
I did some more investigation.
1. Custom defined Ribbon tabs not displayed issue
-------------------------------------------------
> > Your compiled ExternalLibrary is probably marked as
> > [assembly:ComVisible(false)] in the AssemblyInfo.cs/AssemblyInfo.vb
> > file (it is set in the Project properties page -> Assembly
> > Information -> Make assembly COM visible). This makes the types in
> > your project invisible to COM by default, and thus your
> > ExcelRibbon-derived class does not make the Ribbon appear.
> >
> > To fix this, change the assembly setting,
>
> I've set ComVisible to true
Sorry, I had *not* set it to true, I mixed it up with 'Register for COM
Interop' (Do I need this?). :-(
> > After recompiling the ribbon should appear.
What does appear now with ComVisible(True) is a second ribbon tab
defined in the dll with
Public overrides Function GetCustomUI(ByVal RibbonID As String) As String
The ribbon defined in the DNA script still disappears when I remove the
Public Class MyRibbon from the DNA file.
The onAction="RunTagMacro" still doesn't work, but the
onAction="MyMacro" from the dll ribbon does work so I have at least one
working solution now. :-)
If the ribbon tab from the DNA script is displayed, I still can't call
the onAction="MyMacro" defined in the dll, so it looks like these are
two separated areas and everything has to be defined either in the DLL
or in the DNA script.
2. RunTagMacro not working issue
-------------------------------------------------
> Can you try this with the add-in loaded and a workbook open:
> 1. Press Alt+F8 to show the Run Macro dialog.
> 2. Type "ShowHelloMessage" (without the quotes) into the Macro Name
> box.
> 3. This should enable the Run button - press it and check for a
> message box to show.
Working.
> Next test:
> 1. Open the VBA IDE (Alt+F11).
> 2. Go to the Immediate window (Ctrl+G).
> 3. Type: Run("ShowHelloMessage")
> 4. Press enter and checl for the message box to show.
Working.
> I'm trying to figure out if there's an issue with the macro, since the
> Ribbon is showing OK.
>
> Are any other add-ins loaded into your Excel when you test this?
Excel Options, Add-Ins: Only "Ribbon Tests (Ribbon Helper)".
> Could you attach a debugger to Excel and see if anything interesting
> is shown in the Output window when you press that button?
Nothing interesting in the output window, but I compiled the
ExcelDna.Integration library and set a breakpoint on RunTagMacro
(IRibbonControl control). The application is retrieved, but
app.GetType().InvokeMember("Run", BindingFlags.InvokeMethod, null, app, new object[] { control.Tag })
seems to fails, nothing happens, but I can't tell exactly why.
Using the Console doesn't help much either:
> app.GetType().InvokeMember("Run", System.Reflection.BindingFlags.InvokeMethod, null, app, new object[] { control.Tag })
Object is not of type ExcelDna.Integration.CustomUI.IRibbonControl
Probably because the System.__ComObject type. When I replace
control.Tag with the macro name, I get "No applicable overload found".
Regards,
Manfred
Govert van Drimmelen
When making another ExcelRibbon-derived class in your add-in,
everything you see is pretty much 'by design', or at least makes sense
to me. You have to set ComVisible(true), but need not register the
assembly for COM (that's taken care of at runtime by Excel-DNA).
Each ExcelRibbon-derived class is instantiated, and when the ribbon
xml is requested by Excel, it returns either the CustomUI section
defined in the parent .dna file, or if the GetCustomUI method is
overridden, your derived class returns the xml string.
Now a .dna file with an ExternalLibrary pointing to a compiled .dll
will still be the 'parent' DnaLibrary for the classes in that
assembly. If you want totally separate ribbons, you can set it in a
different .dna file, loaded from the main .dna as <ExternalLibrary
Path="MyOtherDnaLib.dna" />.
So you should be able to define the ribbon .xml in the .dna file, and
only have an ExcelRibbon-derived class in your .dll. This works as
long as you don't override ExcelRibbon.GetCustomUI.
Some users prefer the Ribbon xml in a resource of the .dll which they
extract in the GetCustomUI override.
----------------------------------
On the other hand, I still don't understand why RunTagMacro doesn't
work for you.
It might be important, because that Application.Run call is the way to
get into the macro context where you can call the C API functions (the
C API doesn't work directly from the ribbon event handlers).
Are you perhaps running a localised version of Excel?
Since you're using VB, you might like to make your own version of
RunTagMacro inside your ExcelRibbon-derived class, just as a test.
Something like:
Sub MyRunTagMacro(control As Object)
Dim xlApp As Object
xlApp = ExcelDnaUtil.Application
xlApp.Run(control.Tag)
End Sub
And then set onAction="MyRunTagMacro" tag="ShowHelloMessage"
in the .xml for one of your buttons in the .dna.
Then maybe experiment with the call wrapped in an Try-Catch block?
It's strange and I've had no other reports of it not working (and
certainly it works on more than only my installation).
-Govert
On Apr 19, 1:27 am, Manfred Usselmann <usselman...@icg-online.de>
wrote:
Manfred Usselmann
thanks for the clarification. Not sure what I did differently before,
but now the ribbon XML does indeed work exactly as you describe. :-)
Both ways (GetCustomUI() from DLL or <CustomUI> from DNA) do work and
allow me to call "MyMacro" from the DLL via onAction="MyMacro".
The RunTagMacro issue still exists:
> Are you perhaps running a localised version of Excel?
Win XP: English
Excel: English
Regional Settings: German
Using my one version of RunTagMacro with exception handling as you
suggested I get
System.Runtime.InteropServices.COMException (0x800A01A8): Exception
from HRESULT: 0x800A01A8 at
Microsoft.VisualBasic.CompilerServices.LateBinding.LateGet(Object o,
Type objType, String name, Object[] args, String[] paramnames, Boolean
[] CopyBack) at
Microsoft.VisualBasic.CompilerServices.NewLateBinding.LateGet(Object
Instance, Type Type, String MemberName, Object[] Arguments, String[]
ArgumentNames, Type[] TypeArguments, Boolean[] CopyBack) at
order_status.OrderStatus.MyRunTagMacro(Object control)
Setting Thread.CurrentThread.CurrentCulture = new CultureInfo("en-US"),
which solved another runtime exception, did not help in this case.
Regards,
Manfred
On Tue, 19 Apr 2011 13:18:37 -0700 (PDT)
Govert van Drimmelen <gov...@icon.co.za> wrote:
Govert van Drimmelen
This article on StackOverflow (http://stackoverflow.com/questions/
779363/how-to-use-use-late-binding-to-get-excel-instance) does make it
look like
System.Threading.Thread.CurrentThread.CurrentCulture = new
CultureInfo("en-US");
could be required.
I'm not sure how to see whether the late-bound call fails when it
tries to resolve the "Run" method, or when it actually calls it. Maybe
you could check what happens when you change the spelling from
xlApp.Run to xlApp.RRRun(...)
Another test - you can add a reference to the
Microsoft.Office.Interop.Excel assembly in your VB.NET project. Then
change the method to
Imports Microsoft.Office.Interop.Excel
'.......
Sub MyRunTagMacro(control As Object)
Dim xlApp As Application
xlApp.Run(control.Tag)
End Sub
Govert
On Apr 20, 3:09 pm, Manfred Usselmann <usselman...@icg-online.de>
Manfred Usselmann
On Wed, 20 Apr 2011 06:57:43 -0700 (PDT)
Govert van Drimmelen <gov...@icon.co.za> wrote:
> This article on StackOverflow (http://stackoverflow.com/questions/
> 779363/how-to-use-use-late-binding-to-get-excel-instance) does make it
> look like
> System.Threading.Thread.CurrentThread.CurrentCulture = new
> CultureInfo("en-US");
> could be required.
I've included this, but it does not help in this case.
> I'm not sure how to see whether the late-bound call fails when it
> tries to resolve the "Run" method, or when it actually calls it. Maybe
> you could check what happens when you change the spelling from
> xlApp.Run to xlApp.RRRun(...)
The error message is identical:
-----------------------------------------------
Dim xlApp As Object
xlApp = ExcelDnaUtil.Application
xlApp.Run(control.Tag)
System.Runtime.InteropServices.COMException (0x800A01A8): Exception from HRESULT: 0x800A01A8
at Microsoft.VisualBasic.CompilerServices.LateBinding.LateGet(Object o, Type objType, String name, Object[] args, String[] paramnames, Boolean[] CopyBack)
at Microsoft.VisualBasic.CompilerServices.NewLateBinding.LateGet(Object Instance, Type Type, String MemberName, Object[] Arguments, String[] ArgumentNames, Type[] TypeArguments, Boolean[] CopyBack)
at [...].MyRunTagMacro(Object control) in C:\[...].vb:line 103
-----------------------------------------------
Dim xlApp As Object
xlApp = ExcelDnaUtil.Application
xlApp.RRRun(control.Tag)
System.Runtime.InteropServices.COMException (0x800A01A8): Exception from HRESULT: 0x800A01A8
at Microsoft.VisualBasic.CompilerServices.LateBinding.LateGet(Object o, Type objType, String name, Object[] args, String[] paramnames, Boolean[] CopyBack)
at Microsoft.VisualBasic.CompilerServices.NewLateBinding.LateGet(Object Instance, Type Type, String MemberName, Object[] Arguments, String[] ArgumentNames, Type[] TypeArguments, Boolean[] CopyBack)
at [...].MyRunTagMacro(Object control) in C:\[...].vb:line 103
-----------------------------------------------
> Another test - you can add a reference to the
> Microsoft.Office.Interop.Excel assembly in your VB.NET project. Then
> change the method to
>
> Imports Microsoft.Office.Interop.Excel
>
> '.......
>
> Sub MyRunTagMacro(control As Object)
> Dim xlApp As Application
> xlApp = ExcelDnaUtil.Application
> xlApp.Run(control.Tag)
> End Sub
>
>
> Maybe the error is different?
Then I get:
-----------------------------------------------
Dim xlApp As Application
xlApp = ExcelDnaUtil.Application
xlApp.Run(control.Tag)
System.InvalidCastException: Unable to cast COM object of type 'System.__ComObject' to class type 'System.Windows.Forms.Application'. Instances of types that represent COM components cannot be cast to types that do not represent COM components; however they can be cast to interfaces as long as the underlying COM component supports QueryInterface calls for the IID of the interface.
at [...].MyRunTagMacro(Object control) in C:\[...].vb:line 102
-----------------------------------------------
With
Dim xlApp As Microsoft.Office.Interop.Excel.Application
xlApp = ExcelDnaUtil.Application
xlApp.Run(control.Tag)
I get the previous error:
System.Runtime.InteropServices.COMException (0x800A01A8): Exception from HRESULT: 0x800A01A8
at Microsoft.VisualBasic.CompilerServices.LateBinding.LateGet(Object o, Type objType, String name, Object[] args, String[] paramnames, Boolean[] CopyBack)
at Microsoft.VisualBasic.CompilerServices.NewLateBinding.LateGet(Object Instance, Type Type, String MemberName, Object[] Arguments, String[] ArgumentNames, Type[] TypeArguments, Boolean[] CopyBack)
at [...].MyRunTagMacro(Object control) in C:\[...].vb:line 103
Regards,
Manfred
Govert van Drimmelen
1. I suspect the problem might be that you are running an English
Excel on a non-English Windows.
In this configuration you might need the language pack.
I suggest:
Check that you have Office SP2 installed: http://support.microsoft.com/kb/953195
and be sure to also install the Microsoft Office Language Pack 2007
Service Pack 2 (SP2):
http://www.microsoft.com/downloads/en/details.aspx?FamilyID=E1203DB2-1CC9-4809-9B6E-3F232CB8899F
This article also talks about these locale issues:
http://msdn.microsoft.com/en-us/library/Aa168494
And it also suggests you might need to set the CultureInfo. So please
check again with and without it.
-------------------------------------------
2. Lets try it from outside Excel-DNA and .NET:
Open Word, Alt+F11 to get to VBA, Insert a new Module and add this
code:
Sub test()
Dim xlApp
Dim result
Set xlApp = CreateObject("Excel.Application")
MsgBox xlApp.Version
xlApp.RegisterXLL "c:\...path to Excel-DNA....\distribution
\exceldna.xll"
result = xlApp.Run("AddThem", 2, 3)
MsgBox result
End Sub
Then run with F5 or F8.
I get a message box showing 12.0, then 5.
This might show whether the problem has to do with .NET at all.
----------------
Let me know what you find.
Regards,
Govert
On Apr 26, 11:37 am, Manfred Usselmann <usselman...@icg-online.de>
>
> read more »
Manfred Usselmann
On Tue, 26 Apr 2011 03:55:21 -0700 (PDT)
Govert van Drimmelen <gov...@icon.co.za> wrote:
> 2. Lets try it from outside Excel-DNA and .NET:
>
> Open Word, Alt+F11 to get to VBA, Insert a new Module and add this
> code:
>
> Sub test()
> Dim xlApp
> Dim result
>
> Set xlApp = CreateObject("Excel.Application")
> MsgBox xlApp.Version
>
> xlApp.RegisterXLL "c:\...path to Excel-DNA....\distribution
> \exceldna.xll"
> result = xlApp.Run("AddThem", 2, 3)
>
> MsgBox result
>
> End Sub
>
> Then run with F5 or F8.
> I get a message box showing 12.0, then 5.
>
> This might show whether the problem has to do with .NET at all.
>
> ----------------
>
> Let me know what you find.
I Get
1. A message box showing 12.0
2. Then a message box
'Run-Time error'-2147417838(80010112)':
Method 'RegisterXLL'of Object'_Application' failed.
3. A message box with 5, but only if I click on the debug button
and then press F5 again.
Office SP / LP I still need to check.
Regards,
Manfred
> --
> You received this message because you are subscribed to the Google
> Groups "Excel-DNA" group. To post to this group, send email to
> exce...@googlegroups.com. To unsubscribe from this group, send email
> to exceldna+u...@googlegroups.com. For more options, visit
> this group at http://groups.google.com/group/exceldna?hl=en.
>
--
Manfred Usselmann <ussel...@icg-online.de>
ICG IT Consulting GmbH, Kelkheim
Per aspera ad astra - Vun nix kütt nix
Govert van Drimmelen
Sounds a bit funny that you get an error, but the function call still
works - maybe you don't have the .xll path right? And if another add-
in has the "AddThem" function, that may be doing the calculation.
Anyway, so it seems to work from Word on your machine.
I'm not sure whether the language pack SP can be installed if there is
no MUI pack installed.
Another thing you might try is to run it under .NET 4. To do this,
just change the .dna file to start with:
<DnaLibrary RuntimeVersion="v4.0" >
...
Another thing you can try is to change the Windows locale to English-
US as a test.
I can't recall any other reports of this problem, so I keep thinking
it is particular to your machine or installation. Do you have any
other place you can test the behaviour?
-Govert
On Apr 26, 4:11 pm, Manfred Usselmann <usselman...@icg-online.de>
>
> read more »