A few weeks ago I asked a question on StackOverflow whether there is a way to overcome the possibility of your VBA code being reset.  The problem with resetting your VBA is that any variable references will be lost; this means that your class objects are destroyed, and you are unable to recreate them until your code runs again.  Meanwhile the user can interact with your solution in a way you were not intending, possibly ruining it.  Programming defensively for this kind of possibility is frightening.

Your VBA project could be reset in one of the following ways.

1. Pressing the Reset Button or menu option.
2. An unhandled error produces an error dialog and the user selects End.
3. Your code uses the End statement.
4. Someone else’s code uses the End statement.

It’s easy to address the third possibility here – simply don’t use the End statement, because it’s probably overkill anyway; if you’re using the End statement, it could be argued that you’re trying to perform surgery with a hammer.

It’s also fairly straightforward to build in sufficient error handling to prevent unhandled errors, thus eliminating possibility two.

However, there doesn’t appear to be any way to prevent users from entering the VBE, selecting your VBA project, and pressing the reset.  Not even password protecting your VBA project will protect you from this.

The question then is, if the user goes into the VBA editor and kills your project intentionally, how much effort should the developer put in to defend against it?  We think the developer can reasonably ignore this possibility; this is justified by the fact that the user took a specific and deliberate action, which would only be expected from a savvy user who knows what they are doing.

So it seems it is possible to avoid or ignore scenarios 1-3 above.  However what if another VBA project, separate from your own, resets the project?  Or the user is working on their own macro and reset the VBA?  At the time I wrote my question on StackOverflow I was under the impression that doing so would result in resetting all open VBA projects.  After doing some testing, it now turns out I was wrong:  Resetting the VBA project only affects the Active VBA Project.

This is great news, because I felt that I would not be able to use persistent Class objects because they could be destroyed outside of my control – after some testing, I can now relax a bit, knowing that this isn’t a clear and present danger.  Note that what I mean by persistent Class object is one that we create then hold a global reference to, so that it is not garbage collected.  Hence it seems possible to create a variable and expect it to exist until you are ready to dispose of it.

Here is some code I wrote to confirm how the VBA reset feature works:

Private ABoolValue As Boolean
Private AClass As Class1 ' Note this is just an empty class I added.
 
Public Sub Init()
 
    ABoolValue = True
    Set AClass = New Class1
 
End Sub
 
 
Public Sub TestValuesOfGlobals()
 
    ' Reset VBA.
    End
 
    ' Check that our variables are not initialised.
    Debug.Assert ABoolValue = False
    Debug.Assert AClass Is Nothing
 
    ' Reinitialise the variables.
    Init
 
    ' Check they are initialised.
    Debug.Assert ABoolValue = True
    Debug.Assert Not AClass Is Nothing
 
End Sub
 
 
Public Sub PrintValues()
 
    Debug.Print "The boolean variable is: " & CStr(ABoolValue)
    If AClass Is Nothing Then
        Debug.Print "The Class is Nothing"
    Else
        Debug.Print "The Class is NOT Nothing"
    End If
 
End Sub

Just paste it into the ThisWorkbook module in an Excel workbook and add a Class module called Class1.  Init() sets the values of the two global variables declared at the top.   TestValuesOfGlobals() tests that everything works as intended.  PrintValues() allows you to print out the values of the global variables easily to the Immediate Window.

Once you are happy with the way that the code works, make sure that the variables are instantiated by running Init().  Then make another VBA project active in the VBE editor (e.g. double click on another ThisWorkbook Class in another open workbook).  Then try to reset the VBA code in any of the ways that I outlined at the beginning and use the PrintValues() method to confirm that the variables remain instantiated.