VBA global variable contents clear?

[broad_run]

I've inherited a large Excel application that cashes data retreived from an Access database in VBA global variable arrays when the application starts up. It then builds worksheets from scratch by adding a default sheet to the workbook and inserting all formating and data. All calculations are done with VBA modules so no Excel formula are inserted into the sheets. When finished with a sheet, it is deleted from the workbook.

This was working before I got into the code. I added a new feature that would not compile until I changed the object library references. Now all that cashed data in global VBA variables clears out apparently mostly when sheets are deleted but not always then and sometimes at other unknown events. So I am the goat who has broken the application.

Everything I have read and tried tells me the original design is a real bad idea. I have read posts on this and other forums denouncing the idea of using any global VBA variables for any reason. I have never seen them used before in Excel on this scale. My personal preference would be to use templates rather than default sheets, retrieve data from Access as I needed it, and use worksheet formulae in preference to VBA. But I can't rewrite this application. I have rewritten one function to use a template that avoids writing controls to the sheet. Inserting controls onto a sheet seemed to be an intermitent cause of global variable clearing.

I have gone through the code and put a lot of non array variables onto a hidden worksheet with named ranges that are the same name as the global variable and am trying to reload the data each time it clears. This application is large and complex and has been "designed" and written by many people who have preceded me. Getting this reload to work correctly is not easy. Figuring out what to load and what calculations to perform for each application feature is also a long term effort.

Does anyone have any ideas on how to stop the global variables from clearing so I can get on with life? I can slowly rewrite code as I understand the functions over a long period of time once I get some semblance of credibility back.

 

[Richard Schollar]

I agree with you: large global arrays are not the way to go as their values will be cleared out every time there is a loss of state. You get a loss of state when Excel goes into design mode for one, and there are other causes (you highlighted the insert/delete controls one). I'm afraid I can't help you much other than to advise you to, within the VBE, go Tools>Options>General tab and make sure you check "Notify Before State Loss" under "Edit and Continue". This won't prevent it from happening, but at least you'll be given a warning when you are about to lose your values. This might help you pinpoint exactly what is causing it.

Good luck

 

[RoryA]

If you need to add controls to the sheets at runtime, try and use the forms controls rather than the activex ones and you shouldn't lose your globals.

 

[S Meaden]

I have solve this issue and posted solution on StackOverflow and might as well post here.

The solution is to have a simple container, I choose Scripting.Dictionary, compiled into a DLL and make accessible to VBA using COM. In the old days one could have used VB6.


These days, one can also use C++ but here I present a C# solution (uses COM interop).

    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();
            }
        }
    }

And here is some client VBA code to demonstrate. Requires a Tools->Reference to the type library (.tlb file) created alongside the 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