What is the alternative for CopyMemory API to recover Excel IRibbonUI

Question

I use below code to retrieve the IRibbonUI that was set on ribbon callback in Template_Rib global variable that randomly losing its value.

But the CopyMemory API makes the Excel crash randomly. Is there any alternative for this CopyMemory API?

#If VBA7 Then
  Public Declare PtrSafe Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" (lpDest As Any, lpSource As Any, ByVal cBytes&)
#Else
  Public Declare Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" (lpDest As Any, lpSource As Any, ByVal cBytes&)
#End If
Public Template_Rib As IRibbonUI
Public Sub CallbackOnLoad(ribbon As IRibbonUI)
#If VBA7 Then
  Dim StoreRibbonPointer As LongPtr
#Else
  Dim StoreRibbonPointer As Long
#End If
Set Template_Rib = ribbon
Template_Rib.ActivateTab "TemplateTab" 'Name of the tab to activate
'Store pointer to IRibbonUI in a Named Range within add-in file
StoreRibbonPointer = ObjPtr(ribbon)
ThisWorkbook.Names.Add Name:="RibbonID", RefersTo:=StoreRibbonPointer
End Sub
Sub TryToRetrieveRibbon()
On Error GoTo ErrorHandler
If Template_Rib Is Nothing Then
  Set Template_Rib = GetRibbon(Replace(ThisWorkbook.Names("RibbonID").RefersTo, "=", ""))
  'Set Template_Rib = GetRibbon(Evaluate(ThisWorkbook.Names("RibbonID").Value))
End If
ErrorHandler:
Err.Clear
End Sub
#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
  CopyMemory objRibbon, lRibbonPointer, LenB(lRibbonPointer)
  Set GetRibbon = objRibbon
  Set objRibbon = Nothing
End Function

DecimalTurn · Answer 1 · 2023-08-01T02:56:35.943

I don't think there is an alternative to CopyMemory for that, but I might have an idea why your implementation might be unstable.

First, have you made sure that CallbackOnLoad is actually called? It happened to me in the past where I would edit the custom XML for the ribbon, but forget to specify the onload property in the top tag. Eg.:

<customUI onLoad="CallbackOnLoad" xmlns="http://schemas.microsoft.com/office/2006/01/customui" >

It's probably not your case since refreshing the ribbon would never work in that case, so it wouldn't be random in that sense, but I thought I'd mention it just in case.

Secondly, in theory the pointer for the ribbon is a 64-bit integer (assuming 64-bit OS) and it could be bigger than what Excel Names can store. (I'm saying in theory because while I was testing it, I wasn't able to get anything bigger than a 42-bit integer.)

For instance, you could not store the upper limit of an unsigned 64-bit integer (9,223,372,036,854,775,807) inside a name because it will only store the first 15 significant digits, which limits you to a 999,999,999,999,999 which is a 50-bit integer).

To prevent this from happening randomly, you could store the pointer as a string like this:

ThisWorkbook.Names.Add Name:="RibbonID", RefersTo:=Chr(34) & StoreRibbonPointer & Chr(34)

Then, you can retrieve and use the pointer with something like this:

Dim StringPtr As String
StringPtr = Replace(Replace(ThisWorkbook.Names("RibbonID").RefersTo, "=", ""), Chr(34), "")
#If VBA7 Then
    Set ObjRibbon = GetRibbon(CLngPtr(StringPtr))
#Else
    Set ObjRibbon = GetRibbon(CLng(StringPtr))
#End If

Thirdly, you've defined the 3rd parameter for RtlMoveMemory as a Long using the & type declaration suffix. However, when you are passing LenB(lRibbonPointer), you are actually passing an Integer. You can convince yourself of that by putting a breakpoint on the line with CopyMemory and then run the following in the Immediate Window:

?TypeName(LenB(lRibbonPointer)) '-> Integer

To make sure that you pass a Long variable, you can use a Long to make sure that the conversion occurs in your code instead of inside the Windows API function which might lead to an unstable outcome.

Dim Length As Long
Length = LenB(lRibbonPointer)
CopyMemory objRibbon, lRibbonPointer, Length

What is the alternative for CopyMemory API to recover Excel IRibbonUI

1 Answers1