Home > Publications & Training > Case Studies > Protect a global variable in another VBProject
Web This Site

Protect a global variable in another VB project

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.

Basic idea

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.

The code to save a variable

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)


        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


Using the code to save a global variable

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


    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


    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)


        GetGlobal = Application.Run(getSaveGlobalsName & "!getGlobal", _

            ThisWorkbook.Name & "!" & GlblName)

        End If

    End Function


A working example

[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")


    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 Ribbon XML

The XML associated with the ribbon elements in Figure 1 is below.

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



      <ribbon startFromScratch="false">


                  <tab idQ="TMPub:TMPublish" label="TM Examples">

                        <group idQ="TMPub:TMCases"

                                    label="Excel and VBA Cases">

                              <toggleButton id="testToggle"



                                    getLabel="getLabel" />

                              <button id="doFault"

                                    label="Cause fault"

                                    onAction="causeFault" />












Share Your Comments