multidimensional arrays

[goman19]

Hi all, I've got 2 related questions:

(1) I am trying to define a two-dimensional array. The size of the array will vary somewhat, and each element of the array will have 3-5 subelements (overall, probably 75x5 max). Is there a way to build an array that flexes in size like that? Right now I'm trying to define a fixed array, but I haven't managed to get very far with this. Right now I'm trying to populate the array like this:

Dim data as variant
 
data(1)(1) = "Bob"
data(1)(2) = 35
...
 
data(2)(1) = "John"
data(2)(2) = 42
...

At this point I don't know if that will even work, but I'm hoping it will. Are array dimensions automatically flexible based on the number of entries?

(2) I have two different procedures that need access to the same data, which is being entered into the array above. Is it possible to call a third procedure which actually defines the array, and then pass that array up to the main procedure? Generally I thought this needed to be done with a function since I'm basically returning a parameter, but I didn't think you could define a function outside the context of an established procedure, i.e. the array-building function would need to be separately defined for each procedure, forcing me to maintain two identical arrays in separate procedures.

I know these are probably pretty basic coding questions but I am entirely self-taught with VBA so my knowledge is fairly sporadic in places (lots of trial by error) - sorry.

 

[RoryA]

The only way to have a jagged array in vba is to use an array of arrays. You need to declare the array dynamically and then resize it using redim preserve.
You can certainly have a function declared on its own and called from several other subs.

 

[goman19]

I can probably make it dynamic but have a constant number of subelements to the array.

I think there may be a better way to do what I want to do than arrays. I want to check a cell for some value, search against an array (or something, maybe a case?) to match it, and then populate other cells based on what else is associated with that value. For example, I want to match the value of cell A1 ("Bob") with some 2D array value or a case, and then populate cells A2 - A6 with the data from the array/case associated with "Bob". It's sort of like a lookup table.

I'm afraid that by defining it as an array in a function, I will bog down the macro because the array gets created every time the function is called, and I only really need a small portion of the total data at any given time.

Is there a better way to do what I want?