Preserve Ribbon Object References

It’s a common problem in VBA to lose reference to module level variables. For instance, untrapped errors will set object variables to nothing.

When dealing with ribbons in VBA this can be a serious problem : loss of a reference to the ribbon object means all further use of the object is impossible (Invalidate, etc.)

To work around this problem we have to store a reference to the ribbon object and retrieve it later if the reference is ever lost.
We can accomplish this using the CopyMemory, SetProp, GetProp , and RemoveProp APIs.

I store the ribbon in memory and save the memory pointer handle into Excel’s window property list.
The first event that fires when a workbook with a ribbon opens is the onload event of custom UI.

Private Sub RibbonOnLoad(Ribbon As IRibbonUI)
    #If VBA7 Then
        Dim lPtr As LongPtr
    #Else
        Dim lPtr As Long
    #End If
    
    If PointerExists Then Set RibbonObj = Nothing
    Set m_oRibbon = Ribbon
    lPtr = ObjPtr(Ribbon)
    If lPtr <> 0 Then
        If Not SetProp(Application.hWnd, m_sRibbonPtrName, lPtr) = 1 Then _
        Err.Raise vbObjectError + 1, , "Error setting window property"
    End If
End Sub

Here I check if there is already an instance of the ribbon saved to memory, and remove the old reference saved against the window property list.

Public Property Set RibbonObj(ByVal NewValue As Object)
    Dim lR As Long
    
    If NewValue Is Nothing Then
        lR = RemoveProp(Application.hWnd, m_sRibbonPtrName)
    End If
    Set m_oRibbon = NewValue
End Property

The set property takes care of this. Setting the ribbon property to nothing removes the pointer from the property list.

Public Property Get RibbonObj() As Object
    Dim obj As Object
    #If VBA7 And Win64 Then
        Dim lPtr As LongPtr
    #Else
        Dim lPtr As Long
    #End If
        
    If Not m_oRibbon Is Nothing Then
        Set RibbonObj = m_oRibbon
    Else
        lPtr = GetProp(Application.hWnd, m_sRibbonPtrName)
        If lPtr = 0 Then
            Err.Raise vbObjectError + 1, , "Error retrieving window property"
        Else
            CopyMemory obj, lPtr, 4
            Set m_oRibbon = obj
            Set RibbonObj = m_oRibbon
            CopyMemory obj, 0&, 4
        End If
    End If
End Property

Later when a reference to the ribbon object is needed, a still valid module reference will be returned. Otherwise the pointer reference is retrieved and used to instantiate the ribbon object from memory.

Preserve Ribbon Object References
Preserve Ribbon Object References

Download workbook with source here.

RefEdit Control Alternative

I have found using Excel’s built in RefEdit crashtastic, especially when a user’s machine has had multiple versions of Office installed, and upgraded.

REFEDIT CONTROL ALTERNATIVE
Excel’s Ref Edit Control

To get around all the errors including “Error -2147467259 (&H80004005) : “Unspecified error”, I have found that re-installation of Office, or running an Office repair will fix the issue.

Ain’t nobody got time for that, so I created a class to manually create a custom RefEdit control alternative. The class hosts a textbox control with it’s Drop Button Style set. A formula type input box capture’s the actual range selection.

The class hosts 4 events :
DropButtonClickStart – The user has clicked the refedit’s drop down
DropButtonClickEnd – The user has clicked the refedit’s OK button
Cancel – The user clicked the refedit’s Cancel button
ValueChange – the user has modified the refedit’s value