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!

 

[rbrenner]

Anyone know of a version of RoryA's approach (or anything at least 50% as elegant) that works on a Mac (Microsoft 365)?

 

[Thatisfree]

12 years later and this thread is still a GEM!!! I use this on 64bit excel. I would just like to add a summary of everything necessary to make this work with 64 bit, in addition to ktrasler super helpful 64 bit comments. I borrowed various ideas from all the previous posts. It took me awhile to realize the "CopyMemory" function required replacing 4 with 8 (the length parameter) when using 64 bit.

64 bit declaration:

'///////////////Code is Compilation of ideas//////////////////
'https://www.mrexcel.com/board/threads/how-to-preserve-or-regain-the-id-of-my-custom-ribbon-ui.518629/

#If VBA7 Then
    Public Declare PtrSafe Sub CopyMemory Lib "kernel32" _
    Alias "RtlMoveMemory" (destination As Any, source As Any, ByVal length As Long)
#Else
    Public Declare Sub CopyMemory Lib "kernel32" _
    Alias "RtlMoveMemory" (destination As Any, source As Any, ByVal length As Long)
#End If

Creating and storing memory pointer from ribbon xml onLoad callback:

'from ribbon xml OnLoad callback
Public Sub LoadCustomRibbon(thisRibbon As IRibbonUI)

Set customRibbon = thisRibbon 'assign ribbon to public object

StoreObjectPointer customRibbon, "AppNameRibbon" 'get memory pointer and store it in Excel's hidden namespace

End Sub

Private Function StoreObjectPointer(ByRef TargetObject As Object, ByVal ObjectName As String) As Boolean
    Application.ExecuteExcel4Macro "SET.NAME(""" & ObjectName & """, """ & CStr(ObjPtr(TargetObject)) & """)" 'get memory pointer and store it
End Function

If ribbon object is nothing, copy it from memory:

Sub RefreshRibbon()

If customRibbon Is Nothing Then Set customRibbon = GetObjectFromStoredPointer("AppNameRibbon") 'if ribbon object is nothing, get object from memory
If Not customRibbon Is Nothing Then customRibbon.Invalidate 'refresh the ribbon if object exists

End Sub

Private Function GetObjectFromStoredPointer(ByVal ObjectName As String) As Object
Dim objRetrieved As Object
Dim lngObjectPointer As LongPtr

On Error GoTo ErrHandler

lngObjectPointer = CLngPtr(Application.ExecuteExcel4Macro(ObjectName)) 'get pointer from storage
CopyMemory objRetrieved, lngObjectPointer, 8 'copy object from memory
Set GetObjectFromStoredPointer = objRetrieved 'set ribbon as retreived object
CopyMemory objRetrieved, 0&, 8 'clean up invalid object

ErrHandler:
    Set objRetrieved = Nothing
   
End Function

I modified the CopyMemory calls in the GetObjectFromStoredPointer function to be compatible with both 64-bit and 32-bit systems:

VBA code:
Private Function GetObjectFromStoredPointer(ByVal ObjectName As String) As Object
Dim objRetrieved As Object
Dim lngObjectPointer As LongPtr

On Error GoTo ErrHandler

lngObjectPointer = CLngPtr(Application.ExecuteExcel4Macro(ObjectName)) 'get pointer from storage
CopyMemory objRetrieved, lngObjectPointer, LenB(objRetrieved) 'copy object from memory
Set GetObjectFromStoredPointer = objRetrieved 'set ribbon as retreived object
CopyMemory objRetrieved, 0&, LenB(objRetrieved) 'clean up invalid object

ErrHandler:
Set objRetrieved = Nothing

 

[diw74]

Hello. I found this post via Ron de Bruin's page which mentions the solution he shares was suggested by Rory Archibald here. I was very happy to find that his example file worked perfectly on my Mac, but when I copied his code to my file, Excel would crash on the line:

CopyMemory_byVar objRibbon, lRibbonPointer, LenB(lRibbonPointer)

of the code:

#If VBA7 Then
Function GetRibbon(ByVal lRibbonPointer As LongPtr) As Object
#Else
Function GetRibbon(ByVal lRibbonPointer As Long) As Object
#End If
        Dim objRibbon As Object
        #If Mac Then
                CopyMemory_byVar objRibbon, lRibbonPointer, LenB(lRibbonPointer)
           #Else
                CopyMemory objRibbon, lRibbonPointer, LenB(lRibbonPointer)
        #End If

        Set GetRibbon = objRibbon
        Set objRibbon = Nothing
End Function

I even tried replacing my xml custom user ribbon code with his to have everything like for like, but still Excel would crash.
Happy to provide any more information if that would be helpful. I have seen some references to CopyMemory in this thread, but there are a lot of proposals over the years and I'm unsure what to try.

 

[ct27gt]

I modified the CopyMemory calls in the GetObjectFromStoredPointer function to be compatible with both 64-bit and 32-bit systems:

A much more robust solution. Thanks for the feedback!