How to preserve or regain the Id of my custom ribbon UI?

[WernerGg]

I have a little Excel 2007 application with a standard ribbon UI. See the CustomUI.xml and the VBA-code below.

I have uploaded an Excel 2007 TestRibbonUI.xlsm to box.net from where you can get it with the link http://www.box.net/shared/8uznug7s3r

My new tab with name "My Tab" and id="tabCustom" has a group "grpToggle" with two buttons "btn1" and "btn2". Their enabled-states shall be controlled at runtime. Pressing any of them calles "DoButton" which just toggles both states.

The problem is that the UI is initialized only once during load. To change enabled-state or visibility, controls must be forced to reinitialize. For that we have to store the UIs Id during load by means of the o n L o a d -callback (sorry, the vBulletin-software replaces onL... with asterix. God knows why. Is this a dirty word?)

But this Id can only be stored in a static variable (Private guiRibbon As IRibbonUI), which gets lost after errors or during reset from the VBA-IDE.

The third button "Force Error" in our group just produces a zero division. After that the ribbon UI does no longer work and the workbook must be closed and reopen by the user.

Is there really no possibility to regain that guiRibbon value at runtime or store it somewhere else where it is save from being reset?

In the VBA help we can find an example which uses a method "guiRibbon.Refresh". I think this is exactly what we would need instead of the current ribbon UI design which is more than weak in that point. Unfortunatly that Refresh is not implemented.

Sorry I am not able to enter the xml-code and not the VBA code within code-tags. The vBulletin software always corrupts the things. Stupid!

 

[RoryA]

Is there really no possibility to regain that guiRibbon value at runtime or store it somewhere else where it is save from being reset?

No, I'm afraid not.

 

[WernerGg]

No, I'm afraid not.

I think about serializing the "guiRibbon As IRibbonUI" and write it to some persistent storage (file, worksheet cell, workbook defined name, DLL or the like). The problem is that I can convert an Object-variable to some VBA datatype for serialization (e.g. Object -> Long) but not vice versa (e.g. Long -> Object).

In a classical programming language one would use an overlay for that. But in VBA I know no means. The solution would be a little DLL which does that in C or so. But I have no such DLL.

Maybe you remember some similar thing?

 

[RoryA]

No, I really did mean there is no way to do it, at least not that I have ever seen mentioned by any of the people, including many MVPs, who have discussed it.

 

[WernerGg]

No, I really did mean there is no way to do it, at least not that I have ever seen mentioned by any of the people, including many MVPs, who have discussed it.

Hmm, sad. I think it means that one cannot use the ribbon UI in any dynamic sense (visibility, enablement, process dependent widgets, etc). Apart from trivial problems it means that one cannot use it at all.

Do you know any statement from Microsoft? For them it would be trivial to solve that. Maybe a new method "Workbook.GetRibbonID". Is the situation better in Office 2010?

 

[RoryA]

No, the situation is the same in 2010 as far as I know. Since MS' primary focus is more on .Net than on VBA, I don't necessarily expect to see a resolution soon, but fingers crossed for Office 15...

 

[RoryA]

OK, you got me thinking! It occurred to me that if you got a pointer to the IRibbonUI and stored it in a cell (or Name or wherever) then you could use CopyMemory to get it back again. Seems to work in a quick test:

Public Declare Sub CopyMemory Lib "kernel32" Alias _
    "RtlMoveMemory" (destination As Any, source As Any, _
    ByVal length As Long)

Public Sub ribbon L o a ded(ribbon As IRibbonUI)
   ' Store pointer to IRibbonUI
   Dim lngRibPtr As Long
' Store the custom ribbon UI Id in a static variable.
' This is done once during load of UI. I.e. during workbook open.
    Set guiRibbon = ribbon
    lngRibPtr = ObjPtr(ribbon)
    ' Write pointer to worksheet for safe keeping
    Tabelle2.Range("A1").Value = lngRibPtr
End Sub
Function GetRibbon(lngRibPtr as Long) As Object
   Dim objRibbon As Object
   CopyMemory objRibbon, lngRibPtr, 4
   Set GetRibbon = objRibbon
   ' clean up invalid object
   CopyMemory objRibbon, 0&, 4
End Function

Then:

Public Sub DoButton(ByVal control As IRibbonControl)
' The onAction callback for btn1 and btn2
   
    ' Toggle state
    Toggle12 = Not Toggle12
   
    ' Invalidate the ribbon UI so that the enabled-states get reloaded
    If Not (guiRibbon Is Nothing) Then
        ' Invalidate will force the UI to reload and thereby ask for their enabled-states
        guiRibbon.Invalidate 'Control ("tabCustom") InvalidateControl does not work reliably
    Else
      Set guiRibbon = GetRibbon(CLng(Tabelle2.Range("A1").Value))
      guiRibbon.Invalidate
        ' The static guiRibbon-variable was meanwhile lost
'        MsgBox "Due to a design flaw in the architecture of the MS ribbon UI you have to close " & _
'            "and reopen this workbook." & vbNewLine & vbNewLine & _
'            "Very sorry about that.", vbExclamation + vbOKOnly
      MsgBox "Hopefully this is sorted now?"
        ' Note: In the help we can find
        ' guiRibbon.Refresh
        ' but unfortunately this is not implemented.
        ' It is exactly what we should have instead of that brute force reload mechanism.
    End If
   
End Sub

 

[WernerGg]

OK, you got me thinking! It occurred to me that if you got a pointer to the IRibbonUI and stored it in a cell (or Name or wherever) then you could use CopyMemory to get it back again. Seems to work in a quick test:

Whow! That's exactly what we need. I will build that into my program.

Thanks a lot.

 

[WernerGg]

This problem is solved now. Thanks to rorya!

I have uploaded TestRibbonUI_3.xslm to Box.net http://www.box.net/shared/8omsyxrkmj. Sheet IRibbonUI explains the problem and the solution.

The same sheet also shows the solution to "How to interrupt VBA execution through the ribbon UI" (http://www.mrexcel.com/forum/showthread.php?t=518631). See sheet InterruptUI.

Basically we do:

[SIZE=2][FONT=Consolas][COLOR=black]Private Declare Sub CopyMemory Lib "kernel32" _[/COLOR][/FONT][/SIZE]
[SIZE=2][FONT=Consolas][COLOR=black] Alias "RtlMoveMemory" (destination As Any, source As Any, ByVal length As Long)[/COLOR][/FONT][/SIZE]
 
[SIZE=2][FONT=Consolas][COLOR=black]Sub StoreObjRef(obj As Object)[/COLOR][/FONT][/SIZE]
[SIZE=2][FONT=Consolas][COLOR=black]' Store an object reference[/COLOR][/FONT][/SIZE]
[SIZE=2][FONT=Consolas][COLOR=black] Dim longObj As Long[/COLOR][/FONT][/SIZE]
[SIZE=2][COLOR=black][FONT=Consolas][B] longObj = ObjPtr(obj)[/B][/FONT][/COLOR][/SIZE]
[SIZE=2][FONT=Consolas][COLOR=black] Range("...") = longObj[/COLOR][/FONT][/SIZE]
[SIZE=2][FONT=Consolas][COLOR=black]End Sub[/COLOR][/FONT][/SIZE]
 
[SIZE=2][FONT=Consolas][COLOR=black]Function RetrieveObjRef() As Object[/COLOR][/FONT][/SIZE]
[SIZE=2][FONT=Consolas][COLOR=black]' Retrieve the object reference[/COLOR][/FONT][/SIZE]
[SIZE=2][FONT=Consolas][COLOR=black] Dim longObj As Long, obj as Object[/COLOR][/FONT][/SIZE]
[SIZE=2][FONT=Consolas][COLOR=black] longObj = Range("...")[/COLOR][/FONT][/SIZE]
[SIZE=2][COLOR=black][FONT=Consolas][B] CopyMemory obj, longObj, 4[/B][/FONT][/COLOR][/SIZE]
[SIZE=2][FONT=Consolas][COLOR=black] Set RetrieveObjRef = obj[/COLOR][/FONT][/SIZE]
[SIZE=2][FONT=Consolas][COLOR=black]End Function[/COLOR][/FONT][/SIZE]

 

[Adders]

Found this today. Fantastic, works a treat. Just wanted to say thank you rorya. You've saved me a great deal of time.