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!

 

[Jibse]

Hello,

Nobody to answer my question ? I store the pointer in the worksheets(1) of my Addin, in the range("A1"). Is there an inconvenience ? Thanks.

 

[RoryA]

No that's fine.

 

[Jibse]

Thank you very much RoryA !

 

[anthony.mcgovern]

Very clever method. Thanks, Rory.

Any idea how to call a "loadImage" callback after the IRibbonUI.Invalidate method is invoked? I have a callback in the Ribbon XML that points to a custom image I have stored outside the application.

The parent node in my custom Ribbon XML has two callbacks:

<customUI loadImage="******Image" ******="Ribbon******" xmlns="[unique namespace]">

When the application first loads, both callbacks work fine. However, when I run the VBA procedure that invokes the .Invalidate method, only the GetEnabled and GetVisible callbacks in the rest of the XML are invoked, not the callbacks in the parent node.

Any ideas on how to call at least the "loadImage" callback from the XML after I refresh the ribbon?

 

[anthony.mcgovern]

I'm having trouble putting in the XML with the CODE tags. Anyway, here are the callbacks:

customUI loadImage="[VBA image load procedure]" [XML attrubute called o n L o a d]="[VBA ribbon load procedure]" xmlns="[unique namespace]"

 

[anthony.mcgovern]

I figured it out:

I removed the loadImage attribute from the parent node and instead used the getImage attribute in the button node to dynamically change the button image after invoking the iRibbonUI.invalidate method when refreshing the ribbon.

Problem solved.

 

[Meckinism]

Hi Rory,

First, thank you! I've been looking for this code for quite a while, probably like most users.
Now, is there a chance that the pointer changes over time without the change being captured?
(I remember pointers held for too long being an issue with many apis in the past)

I was thinking about storing the value in Excel's Hidden Name Space:
<tt>

''' Write the pointer 
Application.ExecuteExcel4Macro   _
    "SET.NAME(""RibbonXPointer"",lngRibPtr)"
  
''' Read the pointer
</tt><tt>lngRibPtr=</tt><tt>Application.ExecuteExcel4Macro("</tt><tt>RibbonXPointer</tt><tt>")</tt>

<tt>
The advantages would be:
- the value of the pointer is centralized and shared across all books, and during the entire Excel session, even when all books are closed but excel is kept open.
- when a book opens or when it rebuilds the ribbon AND when it has ribbon code, the pointer value can be updated for all books to use (?less chance of a bad pointer?)

Thanks,
Sebastien
</tt>

Rory, thanks for the original solution of storing a pointer to the ribbon!

Sebastien, that is a great idea that has resolved an issue I was facing with the Add-in I am developing currently:
- I have multiple users all running a read-only add-in file that is stored on a network drive.
- Sometimes a user may have multiple instances of Excel open and this enables the pointer to be stored within the application object, where it is most relevant for my add-in!

Private Function StoreObjectPointer(ByRef TargetObject As Object, ByVal ObjectName As String) As Boolean
    Application.ExecuteExcel4Macro "SET.NAME(""" & ObjectName & """, """ & CStr(ObjPtr(TargetObject)) & """)"
End Function

Private Function GetObjectFromStoredPointer(ByVal ObjectName As String) As Object
    Dim objRetrieved As Object
    Dim lngObjectPointer As Long
On Error GoTo ErrTrap
    lngObjectPointer = CLng(Application.ExecuteExcel4Macro(ObjectName))
    CopyMemory objRetrieved, lngObjectPointer, 4
    Set GetObjectFromStoredPointer = objRetrieved
    CopyMemory objRetrieved, 0&, 4
ErrTrap:
    Set objRetrieved = Nothing
End Function

Cheers!

 

[ct27gt]

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

 

[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

BIG THANKS *ct27gt!
You are my guest for a beer.

 

[ct27gt]

No problem! I'm glad I was able to pay it forward. I am not nearly as advanced as the other gentlemen in this thread, so all the little nuances to make this work for 64 bit took me quite a while to figure out. I figured I would create a "64 bit for dummies like me" post. haha.