multidimensional arrays


New Member
Jan 28, 2010
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.
Last edited:


MrExcel MVP, Moderator
May 2, 2008
Office Version
365, 2019, 2016, 2010
Windows, MacOS
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.


New Member
Jan 28, 2010
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?

Forum statistics

Latest member

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...