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.
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.
The set property takes care of this. Setting the ribbon property to nothing removes the pointer from the property list.
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.