1

I have simple class modules that act as custom objects in my VBA project. The properties are simple variable types or equate to simple variable types (string/long/boolean etc) though some properties are other classes of simple variables. When one of the code threads in my project has finished executing and these objects are filled with info, I want to be able to save these objects for use (in the same workbook) at some point later by a different code thread.

It seems that Document.Properties is a good place to keep data for this reason, but it only accepts simple variable types. In javascript, I'd just JSON.stringify("the object") and I'd be good to go, but of course VBA doesn't play nicely with JSON.

Basically, I'm either looking for a way to turn my object into a string to store in Document.Properties or I'm looking for a different way to store an object that perhaps I haven't came across. Unless there's some way I can use the scope of the object to keep the object alive, until the document is closed say?

TourEiffel
  • 4,034
  • 2
  • 16
  • 45
Baddie
  • 305
  • 1
  • 3
  • 11
  • Make your object a Global variable and it will persist as long as the Workbook's VBA session is running. – Tim Williams Aug 27 '19 at 16:17
  • Your VBA project is embedded in an Excel host document... why not serialize to and deserialize from a hidden worksheet and have it persisted with the host document itself? FWIW VBA in most hosts is single-threaded, your code always runs on the UI thread. – Mathieu Guindon Aug 27 '19 at 16:18
  • Also if your classes' instance state is held in a `Private Type`, you can literally dump the instance state into a binary file, and deserialize the UDT from the binary data by reading/writing the raw bytes of the UDT address space - an interesting implication of [this way to write classes](https://stackoverflow.com/a/45570268/1188513). – Mathieu Guindon Aug 27 '19 at 16:21

1 Answers1

3

If none of the objects need to outlive the host application, then there's no need to look into any kind of serialization or persistence mechanism.

Just keep a reference to the object(s) you want to "keep alive" in global scope, in the (declarations) section of any standard module:

Option Explicit
Option Private Module

Public GlobalThing1 As Something
Public GlobalThing2 As Something
'...

Not the prettiest, but then if all these "global things" are related, you could wrap them all into a single class, and only need a single global instance of your wrapper object.

Objects in this scope remain in the execution context as long as you don't nuke that context by executing an End instruction, or quitting the host application.

Note that normally, object/variable scope should be as tight as possible - use global state judiciously.

Mathieu Guindon
  • 69,817
  • 8
  • 107
  • 235