Issue with the ribbon

Jul 12, 2012 at 8:04 PM

Hi everybody. I am considering using Excel DNA to move from a pure VBA add in to an .Net add in. Excel DNA could help me a lot to do it. I was trying the different samples, and I can not work with the ribbon. The commandbar works well, but the ribbon does not appear, and Excel (2007) crash when quitting (asking me if I want to report issue to MS).

 

Here is the code I used to test:

 

<DnaLibrary Name="Ribbon Tests - Shared Group 1">
   <![CDATA[
 
  Imports ExcelDna.Integration.CustomUI
 
  ' Can make a class that implements ExcelDna.Integration.CustomUI.ExcelRibbon
  ' to get full Ribbon access.
  Public Class MyRibbon
    Inherits ExcelRibbon
 
    Public Sub  OnButtonPressed(control as IRibbonControl)
        MsgBox("My Button Pressed in AddIn 1 on control " & control.Id,,"ExcelDna Ribbon!")
    End Sub
  End Class
 
 
  Public Module Module1
    Function AddThem(x, y)
        AddThem = x + y
    End Function       
 
    ' Regular Macros can be called from the Ribbon using the onAction='RunTagMacro' helper,
    ' which calls a macro named in the tag attribute of a button.
    Sub ShowHelloMessage
        MsgBox("Hello There!",,"ExcelDna Ribbon!")
    End Sub
  End Module
 
]]>
 
<CustomUI>
<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui"
  xmlns:x="myNameSpace" >
  <ribbon>
    <tabs>
      <tab idMso="TabAddIns">
        <group idQ="x:Contoso" label="Contoso">
          <button id="C1" label="Contoso Button 1" size="large"
            imageMso="FileSave" onAction="OnButtonPressed" />
        </group>
      </tab>
    </tabs>
  </ribbon>
</customUI>
</CustomUI>
</DnaLibrary>

Note that I inserted directly the customUI in a xlsm file using the right editor, and it works perfectly. But nothing happen when trying to use the ribbon with Excel DNA. 

I looked to the Excel DNA source code, but I couldn't find what could be wrong with my script.

Any clue?

Grizar.

Coordinator
Jul 12, 2012 at 8:28 PM

Hi Grizar,

Your .dna file works perfectly on my Excel 2010 - both using the Excel-DNA v0.29 and the latest source check-in.

The crashing under Excel 2007 sounds very unusual. What version of Excel-DNA are you using?

Some suggestions:

* Check under File->Options->AddIns, Manage: Disabled Items. Sometimes, while developing, your add-in crashes a few times, and Excel sends it to the dog box. This affects subsequent loading 

* Do any of the sample add-ins with Excel-DNA work in your Excel?

* Is the =AddThem(2,4) function that you also have in there registered successfully?

* Are you perhaps using Excel-DNA v0.29, and running Excel 'As Administrator' in a UAC elevated context? This had a bug that was fixed subsequent to the v0.29 release, which would affect the ribbon loading.

These are some initial ideas.

Let me know if this helps, or if we need to explore further.

Regards,

Govert

Jul 13, 2012 at 7:43 AM

Govert,

Thank you for your prompt reply.

I use the latest available version (0.29). I just checked with the previous one (0.28) and get still the same behaviour.

To answer your suggestion: Excel dog box: nothing in it, other samples work perfectly as far as they to do work with the ribbon, AddThem function works well.

You says "running Excel 'As Administrator' in a UAC elevated context". I don't really understand what it means and how to check it. I just checked on another PCs and it works perfectly well. I am a local admin of my PC. This is not the fact on the other PCs. 

You mention the subsequent version of Excel DNA 0.29, but I can not see any 0.30 planned version. Do you have a preview version I could test?

 

Regards,

Grizar.

Coordinator
Jul 13, 2012 at 7:56 AM

Hi Grizar,

You can download the latest development version from the Source Code tab here on CodePlex. It includes binaries too, so you won't have to recompile.

Under Vista or Windows 7, your administrator account is typically set to run under a restricted security token. The name for this Windows feature is 'User Account Control' (UAC). Then you can right-click a program to run it 'As Administrator', which gives the program an 'elevated' token with full privileges. It is this elevated context that caused some ribbon problems. Typically Excel-DNA does not require admin privileges, but in this elevated context some of the registry access was a bit confusing. However, you're unlikely to hit this bug in normal operation - the problematic 'As Administrator' elevation option is one you'd have to choose explicitly.

I've had a similar report of the ribbon not working recently, and would like to get to the bottom of it. So please let me know if the new version fixes it, else we have to try some other things.

Just to be clear - do you mean the TestRibbon.dna sample in the distribution works on your machine? This was not so in the other problem case I have.

Regards,

Govert

Jul 13, 2012 at 8:29 AM

Govert,

I just downloaded version 0.30.16.1. Still issues with ribbon on my PC. For info, I still run Win XP.

Here are the scripts that fail: TestRibbon.dna, TestDynamic.dna,TestCSharp.dna, SharedGroup2.dna, SharedGroup1.dna

Here are the scripts that work: CommandBarsAllWays.dna, CommandBars.dna

Here is what I get when Excel crash: 

EventType : clr20r3     P1 : excel.exe     P2 : 12.0.6661.5000     P3 : 4f7cda6dP4 : mscorlib     P5 : 2.0.0.0     P6 : 4ef6c16f     P7 : dd     P8 : 10     P9 : system.argumentexception 

 

I am doing test using vb.net and SharpDevelop. Just by implementing a simple ribbon class doing nothing crash Excel.

 

Option Strict On
Option Explicit

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


Public Class clsRibbon
	Inherits ExcelRibbon
	
End Class

Regards,

Grizar.

Coordinator
Jul 13, 2012 at 8:38 AM
Edited Jul 13, 2012 at 10:00 AM

Hi Grizar,

OK - so no Excel-DNA ribbon example works on that machine. The CommandBars work quite differently, so the fact that they work doesn't tell us much. And those administrator elevation issues won't be relevant to your Windows XP.

Maybe we can try with the add-in running on .NET 4 (if you have it installed)?

To do this, just change the first line of the .dna file, to add a RuntimeVersion="v4.0" tag, like this:

 

<DnaLibrary RuntimeVersion="v4.0" >
...

 

That could eliminate anything related to the .NET version or installation as possible causes.

Another thing you can try is to attach the SharpDevelop debugger to the Excel process before you load the add-in. Then you might see some interesting output in the debugger Output window.

Thanks for the patience.

Regards,

Govert

Coordinator
Jul 13, 2012 at 9:49 AM

You might also check under File->Options->Add-Ins, Manage: COM Add-Ins, and Remove anything related to these sample add-ins you see there (in particular anything with a (Ribbon Helper) suffix. Then close and restart Excel.

-Govert

Jul 13, 2012 at 11:41 AM

Tring  RuntimeVersion="v4.0" did not solve the issue.

When debugging with SharpDevelop, I get the following error stack:

System.ArgumentException: Cannot delete a subkey tree because the subkey does not exist.   at System.ThrowHelper.ThrowArgumentException(ExceptionResource resource)   at Microsoft.Win32.RegistryKey.DeleteSubKeyTree(String subkey, Boolean throwOnMissingSubKey)   at Microsoft.Win32.RegistryKey.DeleteSubKeyTree(String subkey)   at ExcelDna.ComInterop.ComRegistration.ProgIdRegistration.Deregister()   at ExcelDna.ComInterop.ComRegistration.Registration.Dispose(Boolean disposing)   at ExcelDna.ComInterop.ComRegistration.Registration.Finalize()

I think the COM Add-In could not be de-registred because Excel can not register the Add-In. Using the Com Add-In manager, I was able to remove one corporate com add in, but I am unable to re-install it.  I think there is something wrong with my Excel and it has nothing to do with Excel DNA.

I will check with my support team to understand what's wrong.

Grizar.

 

Coordinator
Jul 13, 2012 at 12:15 PM

Hi Grizar,

To make the ribbon support 'registration-free' Excel-DNA writes the COM entries, asks Excel to load the add-in and then removes the registry entries again. In version 0.29, this is in the HKEY_CURRENT_USER hive, but in the more recent check-ins I first try to write to the HKEY_LOCAL_MACHINE, and only if that fails do I write to HKEY_CURRENT_USER.

So I'm wondering if the writing is silently failing, and then the delete fails as you see in the debugger.

I've not seen many reports of problems with the ribbon (across tens of thousands of installs of Excel-DNA based add-ins that have ribbon), but also this is not the first one. So anything that sheds more light on the situation would be very valuable.

I guess a next step could be to make a little program or a (CommandBar-based) macro in a .dna file that just simulates the registry access, as a test. But I can't promise that in the next few days.

I look forward to hearing back if you find anything more.

Regards,

Govert

Jul 18, 2012 at 8:12 AM

Hi Govert,

I looked to Excel DNA source code ... and I think I found the issue. It seems that the following lines in the ComRegistration.cs fails: Registry.SetValue(@"HKEY_CURRENT_USER\Software\Classes\" + ProgId + @"\CLSID", null, clsId.ToString("B").ToUpperInvariant(), RegistryValueKind.String);

 

When looking to the registry, it appears I had no access right on the HKEY_CURRENT_USER\Software\Classes registry key. Unable to create a subkey. I added permissions on this key and everything seems to be fine now. 

I just look to one ribbon example. I will try with others and let you know. I won't do that before and of Aug.

 

Grizar.

Coordinator
Jul 25, 2012 at 3:16 PM

Hi Grizar,

It's still a bit of a mystery to me how you ended up with the limited permissions in your user hive. If you find any indication of what makes that happen under normal operation, I'd love to know.

I'll try to add a better diagnostic message for this case in the next Excel-DNA version.

Another approach, that should work already but has not been tested yet, is to allow the Ribbon classes to be persistently registered via regsvr32. Your add-in would then need to be installed by an administrator, e.g. from an .MSI installer, but would sidestep the need to write to the registry at runtime to load the Ribbon or RTD servers.

However, the current case where we don't have the requirement of an admin-based installation to the Excel-DNA add-ins is quite a nice and unique feature. So it would be best if we could get to the bottom of the very unusual restricted permissions you found. 

Regards,

Govert