Understanding Class Variable Lifetimes in relation to an add-in

Hi all.

I have an add-in that currently retrieves user settings from an external file and stores them in a worksheet (within the add-in) for later use. It works but seems like a cumbersome way of doing it.

I'm expanding my use of Class Modules and need somebody to help me clarify something.

I've written a Class that can store the details of a setting (name, value, default). Then I've written a class which will serve as a collection of those settings.

What is the lifetime of the classes?

I get that lifetime and scope are closely related, but they're not the same. In the example of a procedure level "Static" vs "Dim", the scope is the same, that it can only be used in the procedure it was defined, but their lifetime is different. The lifetime of Dim will end when the procedure ends, the lifetime of Static will end when the macro ends, which means it will keep it's value between calls to the procedure it is defined in.

My add-in is controlled by a Ribbonx Ribbon. There are various "tools" in the add-in that use lots of the same procedures as each other. When a tool is launched from the ribbon by the user, the selected macro executes, it accesses the values that were saved to the settings worksheet when the add-in loaded, it does it's thing, then it ends.

Any other variable would be destroyed once the macro ends so each tool needs to read in the settings again upon launch. But because the add-in is still loaded, are my class variables still filled with data? If I was to replace the bit where a macro accesses the settings worksheet with accessing a class variable, would it work?

What I want to do is get rid of the need to keep a worksheet holding the settings while the add-in is loaded. I want to import from the external file straight to the collection and then have that collection available at all times to any module (add-in "tool") that needs to use it.

Not entirely sure I'm making myself clear on this one. I'm happy to clarify if you can help.

Cheers.

Can anyone shed some light on this?

Anyone...?

If you declare the objects at the top of a module, either as private or public, they will remain in memory until either you destory the instances in code or all variables are cleared because you Reset via VBE or code used the End command.

As you appear to be reading from an external file you could always check, via code, that your objects have been created and loaded. If not then reload as required.

So you can make use of the IsNothing keyword and then have a property within the class to confirm file has been read

Originally Posted by Andy Pope

...they will remain in memory until either you destory the instances in code or all variables are cleared because you Reset via VBE or code used the End command...

That's what I keep reading "until all instances are destroyed" but I'm not sure how that applies to a Ribbon.

The only code that references the class modules is the code in the add-in. Only specific parts of the code are run at the user's command from buttons etc on the Ribbon.

Once a "tool" has finished executing, there is no other code running until the user interacts with the Ribbon again, so as far as I can tell, once a tool's code has finished executing there should be no more references to the class so it should be destroyed. So does this mean that while the Ribbon is active, the class remains in memory? I don't get it because the Ribbon itself has no code, no references to Class Modules, just callbacks.

P.S. Andy, your Ribbon Designer has been invaluable to me during this project!!!! Excellent idea to produce an Excel Add-in to do this instead of a stand alone application that I wouldn't be able to install at work.

Originally Posted by Kyle123

...The module level and static will keep their values unless explicitly set to nothing or the project is reset

Originally Posted by Kyle123

If you initialize it at module level, it will stay until the project is reset

Unless the project is reset? So, the project starts and Workbook_Open runs (which calls a sub in a Standard Code Module) instantiating class objects. So regardless of what the ribbon is doing, until the project is closed again (or I specifically destroy them), the objects remain in memory? If I've got that right, that seems to make sense.

Glad you like it

Thanks Andy and Kyle. That's definitely cleared up my understanding so I'll mark this as solved. Cheers guys.