PDA

View Full Version : Solved: Global Unknown Size Array



sam314159
01-06-2011, 01:57 PM
I have a small VBA project that consists of a userform and 5 different modules. Each one of the modules processes a certain set of data and produces results.

As an afterthought, I wanted to create a single array (or some kind of data storage structure) that can hold all the data produced by the 5 different modules. So at the end of the run, I would like to have an array that contains the data produced by all the modules.

Here is what I need help with please:

1. Is there a way to create a global array in a project that is recognized by the 5 modules and is not just defined in one context?

2. Is an array the best way to do this? Seems like VBA arrays get a little more complicated when you don't know the exact number of elements and in my case, I don't. I was hoping for something similar to a java Vector with methods such as .addElement but I can't find that for VBA, does something similar exist?

Thanks guys.

Simon Lloyd
01-06-2011, 03:23 PM
What are you hoping to do with the data once you have collected it in an "array"? can you supply a sample workbook to illustrate what you want?

macropod
01-06-2011, 03:24 PM
hi Sam,

You can declare a public variable in any of the modules (or a new one) by placing it at the top of the relevant code module (just below 'Option Explicit'). For example:

Option Explicit
Public MyData As String
As to what type that variable should be, there is any number of possibilities, including String and Variant. It really depends on how the data are structured and what you want to do with them.

Bob Phillips
01-06-2011, 05:19 PM
Create a clas of the data type, and instantiate a new instance of the class for each module. Those instances can be in an array if you want.

Paul_Hossler
01-06-2011, 06:25 PM
You could Dim a Public Collection of a User Defined Type

'Public' makes it's scope availalble to all modules

Collection allows you add and delete elements

Paul

sam314159
01-07-2011, 01:33 PM
Thanks so much for the replies guys, that was exactly what I needed!

I declared a Public Collection and working with it was a piece of cake. The only little snag I hit was that I didn't know that I had to do a set = new Collection but that was fixed with 5 minutes of Googling :D

Thanks again for all the replies guys, made my job super easy on this Friday!