In Excel VBA, how do I transfer key variables to "state loss" (without writing to a cell or file)?

Question

Excel VBA is a flexible development environment. It is written with pesudo. However, sometimes a “loss of state” may occur during development. "State loss" is when all variables break down. Indeed, VBA has the option "Notify before loss of state" for sorting. Not surprisingly, in all cases, you cannot edit and continue code. However, sometimes government losses occur during work, because opening any other book can lead to an injury to the session of your application (believe me, this happens!)

I know that you can save data in a sheet cell or even in a file, but this is not suitable for trying to save an instance of a class, especially if it is an anchor for the entire graph of objects.

So, in the case where you insist on a memory variable, how do you save state for state loss?

Answer 1

One way to save data that remains during Excel's lifetime is to save it to 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

Answer 2

Trying to answer my question ...

, , Scripting.Dictionary, DLL VBA COM. VB6.

++, # ( COM-).

using System.Runtime.InteropServices;

namespace VBAStateLossProofStorageLib
{
    // Code curated by S Meaden from Microsoft documentation

    // 1. C# Shared Class library
    // 2. In AssemblyInfo.cs set ComVisible(true)
    // 3. In Project Properties->Build check 'Register for Interop'
    // 4. Add Com reference to Microsoft Scripting Runtime

    public interface IVBAStateLossProofStorage
    {
        Scripting.Dictionary getGlobalDictionary();
    }

    [ClassInterface(ClassInterfaceType.None)]
    [ComDefaultInterface(typeof(IVBAStateLossProofStorage))]
    public class VBAStateLossProofStorage : IVBAStateLossProofStorage
    {
        public Scripting.Dictionary getGlobalDictionary()
        { return CVBAStateLossProofStorage.m_dictionary; }
    }


    // https://msdn.microsoft.com/en-gb/library/79b3xss3.aspx
    // "a static class remains in memory for the lifetime of the application domain in which your program resides. "
    [ComVisible(false)]
    static class CVBAStateLossProofStorage
    {
        public static Scripting.Dictionary m_dictionary;

        static CVBAStateLossProofStorage()
        {
            m_dictionary = new Scripting.Dictionary();
        }
    }
}

VBA . - > ( .tlb), Dll.

Option Explicit

Public gdicLossy As New Scripting.Dictionary
Public gdicPermanent As Scripting.Dictionary

Sub RunFirst()

    Set gdicLossy = New Scripting.Dictionary
    gdicLossy.add "Greeting", "Hello world!"

    Dim o As VBAStateLossProofStorageLib.VBAStateLossProofStorage
    Set o = New VBAStateLossProofStorageLib.VBAStateLossProofStorage

    Set gdicPermanent = o.getGlobalDictionary
    gdicPermanent.RemoveAll '* clears it down
    gdicPermanent.add "Greeting", "Bonjour!"

    End '* THIS PROVOKES A STATE LOSS - ALL VARIABLES ARE TORN DOWN - EVENT HANDLERS DISAPPEAR
End Sub

Sub RunSecond()

    Debug.Assert gdicLossy.Count = 0  '* sadly we have lost "Hello world!" forever

    Dim o As VBAStateLossProofStorageLib.VBAStateLossProofStorage
    Set o = New VBAStateLossProofStorageLib.VBAStateLossProofStorage

    Set gdicPermanent = o.getGlobalDictionary
    Debug.Assert gdicPermanent.Count = 1 '* Happily we have retained "Bonjour!" as it was safe in its compiled Dll
    Debug.Assert gdicPermanent.Item("Greeting") = "Bonjour!"

End Sub