After working hard to create and implement a custom ribbon tab for your Office© application it is annoying to find it is not as robust as it should be. This problem may show up as an Error 91 (Object variable not set) or simply the tab not loading in the ribbon. If you had a working custom ribbon tab and now it starts throwing error (mostly intermittently, and unexpectedly) there may be a fix. I have found a solution for my VBA code.

The Source

All the credit for this fix should go to Rory Archibald who came up with it and shared it in a forum post here. Another implementation and a great example workbook comes from Ron de Bruin. I used Ron’s workbook code as it was easy to follow and commented well. What I have in this post is my application of their work. I simplified the code and added some error handling to suit my needs. Read both of these other posts and see if you are a candidate for this remedy. Hopefully, it works for you, too.

The Fix

First, you will need to save the memory pointer reference for your ribbon. I put mine in a “hidden” column which also serves as a spot to store other dynamically created numbers which are used in the worksheet’s calculations. You could also use a custom document property of the workbook. It’s your choice, in this case, the unused cell is easy. The following code shows how this is done…an explanation follow…

' Force explicit variable declaration
Option Explicit
Public estRibbonUI As IRibbonUI
Public Declare PtrSafe Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" (ByRef destination As Any, ByRef source As Any, ByVal length As Long)

'***************************************
'       Start of the Ribbon code       *
'***************************************

Public Sub OnLoad_EstimatorTab(ribbon As IRibbonUI)
' Set the estRibbonUI to a workbook property for later use

    Set estRibbonUI = ribbon
    ' set the memory pointer
    Sheet1.Range("T14").value = ObjPtr(ribbon)
    ' open the Estimator tab at startup
    estRibbonUI.ActivateTab ControlID:="EstimatorTab"
    
End Sub

There is more code in the example then you need to understand. However, it is better to see the required statements in their native surroundings. This sample shows my workbook’s OnLoad sub which is referenced in the ribbon XML. It runs when the ribbon initiates and displays your custom tab.

• First, you will need to reference a windows library, “kernel32” with the Public Declare statement. This library will give you access to the function “ObjPtr”…
• Now that you have the ability to use the function it can be used to save the pointer from the IRibbonUI. In this case, we call “ObjPtr(ribbon)”. Where “ribbon’ is another variable name for the IRibbonUI (passed as an argument ribbon As IRibbonUI.)
• This work is done in this line: Sheet1.Range(“T14”).value = ObjPtr(ribbon), as the function is called with the IRibbonUI passed to it. Now cell T14 contains a Long number representing the place in memory where the IRibbonUI belongs.

Now the real work begins

The problems that we see with our custom ribbon (IRibbonUI) are they “lose” track of where it is in memory. It does become an Object variable which isn’t set. With the pointer to the IRibbonUI saved we can retrieve it when our ribbon gets “Lost.” The following code shows how I did this:

Public Function GetRibbon(ByVal lRibbonPointer As LongPtr) As Object
' retrieve the ribbon memory pointer from the holding cell, T14
Dim objRibbon As Object
        
    CopyMemory objRibbon, lRibbonPointer, LenB(lRibbonPointer)
    Set GetRibbon = objRibbon
    Set objRibbon = Nothing
        
End Function

Public Sub RefreshRibbon()
' check and fix the ribbon
On Error GoTo errHandler

    If estRibbonUI Is Nothing Then
        If Not IsEmpty(Sheet1.Range("T14").value) Then
            Set estRibbonUI = GetRibbon(Sheet1.Range("T14").value)
            estRibbonUI.Invalidate
        End If
    Else
        estRibbonUI.Invalidate
    End If
    
errHandler:
    If Err.Number > 0 Then
        If Err.Number = 91 Then
            MsgBox "An Excel Ribbon menu error has occurred which can be fixed by saving your estimate, closing it, closing Excel, and then " _
                & "reopening Excel and your saved estimate. Sorry for the inconvenience!", vbInformation, "Excel Ribbon error..."
            Resume Next
        End If
    End If

End Sub

In the previous code, the function GetRibbon is called by the sub RefreshRibbon. The error handler at the end checks for error 91 which is an error that occurs when you have ribbon issues such as discussed here. How this works…

• GetRibbon retrieves the value which was previously saved (cell T14) passes it back to the calling statement.
• Then the IRibbonUI is reset to this old value which simply returns it to the previous (correct) pointer. It copies over a false or missing value with the correct one.
• Then the ribbon Invalidate command is issued and the ribbon should be back to normal, unless…
• …we still have the error 91. Then a message is displayed telling the user to save their work and restart Excel. However, with this fix in place, this is seldom the case anymore. The following code shows how the RefreshRibbon sub can be used.

' Adjust the Ribbon checkboxes and labels
    If Not estRibbonUI Is Nothing Then
        estRibbonUI.Invalidate
    Else
        Call RefreshRibbon
    End If

I use the previous code at the end of the Worksheet_Activate and Change events and whenever a form makes a change to the worksheets or closes. It merely says if the ribbon object is not set run the RefreshRibbon sub, otherwise refresh it with the Invalidate command. This code keeps things running smooth. I have yet to resort to restarting Excel because of the ribbon losing its way. Except when I am coding and something crashes. But that is the way things are when developing software…