Benjamin,
Rather than a global array, you could create a dictionary object although how you name things might be problematic.
Read up on the dictionary object by looking at this reference and browsing around in the examples of how to create, populate, use, and destroy such objects.
https://msdn.microsoft.com/en-us/vba/language-reference-vba/articles/dictionary-object
As for the case of a global array, this object also exists in memory and has to be rebuilt at each app restart.
Arnel is correct that public variables tend to be destroyed after a run-time error, but that isn't always the case. The trick is that every form needs an OnError event to trap an error not handled from individual event codes. Then you can still pop up a message box and force the form to exit - but since YOU handled the error, the globals would be preserved. Here's why:
Every subroutine and function COULD have error trapping code in it. That would almost entirely eliminate "global destruction." But sometimes that's not practical. You can trap things where you know what to do about the error, perhaps, but it quickly gets burdensome to fully establish traps everywhere.
Lots of people take the other approach and trap nothing. That is because they know that Access will catch the trap. And that is actually quite true. But... Access isn't kind to you when you feed it a load of trap.
A trap handler exists as an element of what is called a "Call Frame." When you call something as a subroutine, a new call frame is created. All variables declared locally in what you call are instantiated in that frame (as part of the stack). You can see the call frame in a code/debug window because there is a menu-bar View option to show the call stack. When you declare an "ON ERROR <do something>" you are creating a trap handler. However, that exists ONLY on the call stack. If your subroutine returns to its caller, your trap handler is disestablished when its frame is collapsed by the EXIT SUB or whatever you use.
If you have several layers of calls, EACH layer can have its own handler and if you call something and then return from it, your handler for that layer is still in force when you return - no need to re-declare it.
But what if you have a layer that doesn't have a handler? What Windows does, and therefore what Access does, is they establish a rule that says if you don't have a handler, your current call frame is toast. They clean it up and return to your caller with an error signal. If the caller of the failing routine ALSO doesn't have a handler, IT gets collapsed on the stack - much like a string of dominoes.
The collapse sequence stops when a handler accepts the trap. IF the handler that accepts the trap is within Access itself (rather than one of yours) then you get the popup for which one of the options is RESET. At the moment you take the reset option, you just blew away your global context. That's because to Access, YOUR CODE is a subroutine for which its call context just got blown away.
Which is why I told you to have a form-based error handler to intercept the trap BEFORE it gets to the Access "Last Chance" error handler. That would protect your globals.
For a personal project, taking the Access handler isn't such a big deal. But for a project in full production status, you really want to stop that if you can, and you want your code to be stable even in the face of adversity. Which is why extra care is needed if you are going to play around with global variables.
NOTE: It is NOT TOTALLY WRONG to use global variables despite the lively thread on whether global variables are a good or a bad thing. After all, it is your project, not ours. However, it IS suicidal to use globals without assuring some level of error protection.
