Re: Populate Array For Later Use In Macro Code
Well, it's kind of hard to actually advise without further knowing the architecture of your program.
However, using accessor methods to a fixed, global array is not good code reuse, and it's not really refactoring any functionality either. It's just displacing access to a public variable behind a procedure call, and as such obfuscating the fact that you are modifying a global object.
That would work if i had everything in one procedure which is what i'm trying to get away from...
Well, I am assuming you have an entry-point or points, where you can declare the array. If you want to extract functionality into separate procedures, you cannot really escape the two options: passing the array along wherever necessary, or using a global variable. I would personally use the former, as even though it doesn't seem elegant, it is the "correct" form to do this.
I Can't redim the array to an expected size because that is an unknown factor.
In general VBA procedures tend to run top-down, and I can't see any conceivable circumstance where you would not know a ballpark estimate on how many indexes your array needs. It is still vastly cheaper to overallocate your array, and shave off the unused indexes when the values have been populated.
I don't mean to seem like I think I know better than you, of course you are the one with the intimate knowledge of the application structure. However to me it certainly seems your design is not well thought out, and while I'm sure you are improving the code quality from the original monster proc, it still leaves a lot to be improved.
Perhaps sit down with a pen and paper and try to streamline your design?