What are pros & cons of Passed Arrays vs Global Arrays in Excel VBA

Question

Ok, 2nd attempt at writing a Stack Overflow Question, so forgive me if this seems familiar.

I am rewriting an Excel Macro that was built over a 2 1/2 year period, frankenstein style (added to piecemeal). One of the things I need to do is load the data into an array once and only once for data accuracy and speed. For my skill level I am going to stick with the Array methodology.

My two approaches are:

• Use Global dimmed dynamic Arrays
• Dim the dynamic arrays in my Main procedure and pass them to the called procedures

So, what is Stack Overflow's take on the Pros vs Cons of these two methods? Thanks, Craig...

Answer 1

You need to be careful with globals in Excel VBA, because if your application hits any kind of bug, and does some kind of soft reset (but the app still functions), then the globals will have been erased.

I had to give up on globals, since I don't write perfect apps.

Answer 2

First, to answer the question you specifically didn't ask: Set up a custom class and load the data in that. Seriously, you'll thank me later.

OK, on to your question. I start by limiting the scope as much as possible. That means that I'm passing variables between procedures. When all your variables have the most restrictive scope possible, you run into the fewest problems down the line.

Once a variable passes two levels deep (calling procedure to 1st tier, 1st tier to 2nd tier), then I start taking a critical look at my structure. Usually (but not always) if all three procedures are in the same module, I'll create a module-level variable (use the Private keyword instead of Dim). If you separate your modules correctly (not arbitrarily) you can have module-level variables without much risk.

There are some variables that are always global right from the start: the variable that holds the app name and app version; the top-level class module that should never lose scope as long as the app is running; the constants (I know they're not variables) that hold things like commandbar names. I know I want these global, so they start that way.

I'm going to go out on a limb and say that module-level variables never migrate to global variables. Global variables start out that way because of their nature. If using a module-level variable seems cumbersome, it's probably because I've split a module up for no good reason or I need to rethink my whole framework.

That's not to say I've never cheated and used a global when I shouldn't have. We've all done it and you shouldn't lose any sleep if you do it too.

So to properly book-end this post: I quit using arrays unless I'm forced to. I use custom classes because

ActiveCell.Value = Invoice.LocalSalesTaxAmount

is so much nicer to debug than

ActiveCell.Value = aInvoice(35,2)

Just in case you think you need more skill to work with custom classes - so did I. I bit the bullet and so can anyone else.