In Excel VBA how to persist key variables over a 'state loss' (without writing to a cell or a file)?

Question

Excel VBA is a flexible development environment. It is pesudo-compiled. However, sometimes during development a "state loss" can occur. A "state loss" is when all variables are torn down. Indeed, VBA has an option "Notify before state loss" option for triage. It is unsurprising that one cannot Edit and Continue code in all cases. However, sometimes state losses happen whilst running in production because opening some other workbook may cause trauma to your application session (trust me, it happens!)

I know one can persist data to a worksheet cell or even a file but this is inappropriate for trying to retain an instance of a class, especially if that is the anchor for a whole object graph.

So in the case where one insists on a memory held variable how do you persist state over a state loss?

Answer 1

One way to keep the data persistent during the lifetime of Excel is to store them on the default .Net domain attached to the instance:

Sub Usage()
    Dim dict As Object
    Set dict = GetPersistentDictionary()
End Sub

Public Function GetPersistentDictionary() As Object
    ' References:
    '  mscorlib.dll
    '  Common Language Runtime Execution Engine

    Const name = "weak-data"
    Static dict As Object

    If dict Is Nothing Then
      Dim host As New mscoree.CorRuntimeHost
      Dim domain As mscorlib.AppDomain
      host.Start
      host.GetDefaultDomain domain

      If IsObject(domain.GetData(name)) Then
        Set dict = domain.GetData(name)
      Else
        Set dict = CreateObject("Scripting.Dictionary")
        domain.SetData name, dict
      End If
    End If

    Set GetPersistentDictionary = dict
End Function