Developers who have done any kind of programming with the Office 2007 (and later) Ribbon architecture have encountered almost certainly a scenario that resulted in the loss of their pointer to the ribbon. This happens because the ribbon object has to be stored in a global variable and any kind of unhandled error leads to a “loss of state,” which includes the loss of all global variables.
In http://www.tushar-mehta.com/publish_train/xl_vba_cases/1017%20Save%20a%20global%20in%20an%20Excel%20workbook.shtml I describe a way, first proposed by Rory Archibald, to save the handle to an object in an Excel cell (or named constant). That is an easy to implement and relatively self-contained approach.
At the same time, I wanted a solution that worked not only with Excel but also other applications such as PowerPoint and Word. I also wanted a solution that worked with variables other than objects declared outside of the VBA project. Consequently, I opted to use a separate add-in that did nothing more than save global variables in a VBA collection. While this requires coordination between two add-ins, the benefits include the ability to extend the solution to platforms other than Excel, the ability to save the state of any global, and also a solution that does not require a Windows API. This note documents such an approach.
As long as there is at least one reference to an object, the system keeps the object ‘alive.’ When there is no reference to an object, the system marks the object as ‘ok to delete.’ [This is a conceptual statement and not necessarily how a particular OS / language runtime actually implements object deletion.] So, the idea is to save a reference to the global object and to use that saved reference to restore the original variable if it is ever lost. Such a loss occurs when a VB project experiences an unhandled fault. After such a fault the VB project suffers a “state loss” and all variables are lost. So, clearly, for such a project to recover a global variable, that variable must be saved in another VB project. And, that is what the solution in this note does.
In a workbook named TM Save Globals.xlsm, in a standard code module add the code below. The first procedure, saveGlobal, takes whatever was passed to it in the first parameter and saves it in a global collection with the key provided in the 2nd parameter. The second procedure, getGlobal, retrieves the saved entity identified by the 1st parameter. Optionally, it also removes the retrieved variable. The third procedure, savedGlobalIsObject, returns a Boolean indicating whether the variable identified by the parameter is an object or not.
The only purpose of this workbook (and the associated add-in) is to save variables in the SavedGlobals collection. Consequently, it is extremely important that there be no fault in the execution of code in this workbook.
Option Explicit
Dim SavedGlobals As Collection
Public Function saveGlobal(ByVal Glbl As Variant, ByVal GlblName As String)
If SavedGlobals Is Nothing Then Set SavedGlobals = New Collection
On Error Resume Next
SavedGlobals.Add Glbl, GlblName
saveGlobal = Err.Number
On Error GoTo 0
End Function
Public Function getGlobal(ByVal GlblName As String, _
Optional RemoveIt As Boolean = False)
On Error Resume Next
If TypeOf SavedGlobals(GlblName) Is Object Then
Set getGlobal = SavedGlobals(GlblName)
Else
getGlobal = SavedGlobals(GlblName)
End If
If RemoveIt Then SavedGlobals.Remove GlblName
End Function
Public Function savedGlobalIsObject(ByVal GlblName As String) As Boolean
On Error Resume Next
savedGlobalIsObject = TypeOf SavedGlobals(GlblName) Is Object
End Function
Create a new Excel file and save it as TM Save Globals Consumer.xlsm. In the workbook’s VBA project create a new standard module, name it saveGlobals, and paste the code below.
The function getSaveGlobalsName tries to identify the add-in / workbook containing the global store, i.e., the workbook / add-in described above. The saveGlobal and GetGlobal procedures interact with the corresponding routines in the above code to save or retrieve a variable.
Option Explicit
Function getSaveGlobalsName() As String
Static Rslt As String
If Rslt <> "" Then getSaveGlobalsName = Rslt: GoTo XIT
Rslt = ThisWorkbook.Path & Application.PathSeparator _
& "TM Save Globals.xlsm"
If Dir(Rslt, vbDirectory) = "" Then
Rslt = Application.UserLibraryPath _
& "TM Save Globals.xlam"
If Dir(Rslt, vbDirectory) = "" Then Rslt = ""
End If
If Rslt <> "" Then
Rslt = "'" & Rslt & "'"
getSaveGlobalsName = Rslt
End If
XIT:
End Function
Public Sub saveGlobal(Glbl As Variant, GlblName As String)
Dim Rslt As Long
Rslt = Application.Run(getSaveGlobalsName & "!saveGlobal", _
Glbl, ThisWorkbook.Name & "!" & GlblName)
If Rslt <> 0 Then MsgBox "Unable to save global. Err=" & Rslt
ErrXIT:
End Sub
Function GetGlobal(GlblName As String) As Variant
On Error Resume Next
If Application.Run(getSaveGlobalsName & "!savedGlobalIsObject", _
ThisWorkbook.Name & "!" & GlblName) Then
Set GetGlobal = Application.Run(getSaveGlobalsName & "!getGlobal", _
ThisWorkbook.Name & "!" & GlblName)
Else
GetGlobal = Application.Run(getSaveGlobalsName & "!getGlobal", _
ThisWorkbook.Name & "!" & GlblName)
End If
End Function
[By modularizing the code as above, the working example, including the code, is identical to the one described in http://www.tushar-mehta.com/publish_train/xl_vba_cases/1017%20Save%20a%20global%20in%20an%20Excel%20workbook.shtml!]
The callback routines in the code below are associated with the ribbon elements shown in Figure 1.
Figure 1
The ribbon tab is TM Examples and the group is Excel and VBA Cases. The first element in the group is a toggle button with the label provided by a VBA callback routine, named getLabel. It shows the time of the last call to the getLabel routine. The Cause Fault button calls a routine that causes a VBA fault.
Excel calls the ribbonLoaded procedure when opening the workbook (or loading the add-in). The procedure updates the global handle (guiRibbon) with the parameter passed to it. In addition, it saves the value of the handle using the saveGlobal procedure.
Clicking the Cause Fault button calls the causeFault procedure, which simply causes a fault. The result is a loss of state and guiRibbon will become nothing.
When the user presses the testToggle button (the label is the time when it was last updated), the toggleAction procedure invalidates the ribbon. However, if the Cause Fault button was pressed before the call to toggleAction, the variable guiRibbon will be nothing. In that case, toggleAction calls the getGlobal function to reinstate the guiRibbon handle. Once toggleAction invalidates the ribbon, Excel calls the getLabel procedure, which, in turn, updates the label of the toggle button with the current time.
Option Explicit
Public guiRibbon As IRibbonUI
Dim BtnPressed As Boolean
Public Sub ribbonLoaded(ribbon As IRibbonUI)
Set guiRibbon = ribbon
saveGlobal ribbon, "RibbonPtr"
End Sub
'Callback for testToggle onAction
Sub toggleAction(control As IRibbonControl, pressed As Boolean)
'Debug.Print Now, BtnPressed, pressed
BtnPressed = pressed
If guiRibbon Is Nothing Then Set guiRibbon = GetGlobal("RibbonPtr")
guiRibbon.Invalidate
End Sub
'Callback for testToggle getPressed
Sub getPressed(control As IRibbonControl, ByRef returnedVal)
returnedVal = BtnPressed
End Sub
Sub getLabel(control As IRibbonControl, ByRef returnedLabel)
returnedLabel = Format(Now(), "hh:mm:ss")
End Sub
'Callback for causeFault onAction
Sub causeFault(control As IRibbonControl)
Debug.Print 1 / 0
End Sub
The XML associated with the ribbon elements in Figure 1 is below.
<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui"
xmlns:TMPub="_TMPub"
onLoad="saveGlobalRibbonX.RibbonLoaded">
<ribbon startFromScratch="false">
<tabs>
<tab idQ="TMPub:TMPublish" label="TM Examples">
<group idQ="TMPub:TMCases"
label="Excel and VBA Cases">
<toggleButton id="testToggle"
onAction="toggleAction"
getPressed="getPressed"
getLabel="getLabel" />
<button id="doFault"
label="Cause fault"
onAction="causeFault" />
</group>
</tab>
</tabs>
</ribbon>
</customUI>
