How to create multiple arrays dynamically in VBA

Question

I need to create Multiple arrays to store my values .Can anyone please give me the solution?

For i = 1 To 6
Dim oFirst_family As MaterialFamily
Dim ifno As Integer
ifno = i
Set oFirst_family = cFamilies_list.Item(ifamily_no)
Dim sFamilyName As String
sFamilyName = oFirst_family.Name
MsgBox sFamilyName
Set cMaterials_list = oFirst_family.Materials
iNb_materials = cMaterials_list.Count
//Here i want to create multiple arrays for every iteration .
For j = 1 To iNb_materials
imaterial_no = j
Set oMaterial1 = cMaterials_list.Item(imaterial_no)
ReDim Preserve test(j)
test(j) = oMaterial1.Name
Next
Next

Madhanmohan Devarajan · Answer

Hi Dinesh, Hope you are trying to implement dynamic arrays in your macro. But would like to know where you have declared the array test in your code. like shown below: Dim test() as string Are you looking for multi-dimensional array or just dynamic array? If it is just dynamic array then you have already having it in your code. Here is the Example for dynamic array code. If you are looking for multi-dimensional array it is here Note: Please accept this answer, if it is helpful for you.

Madhanmohan Devarajan · Answer

6 single dimensional array ______________________ test1 = |0 | 1 | 2 | 3 | 4 | 5 | ______________________ test2 = |0 | 1 | 2 | 3 | 4 | 5 | ______________________ test3 = |0 | 1 | 2 | 3 | 4 | 5 | ______________________ test4 = |0 | 1 | 2 | 3 | 4 | 5 | ______________________ test5 = |0 | 1 | 2 | 3 | 4 | 5 | ______________________ test6 = |0 | 1 | 2 | 3 | 4 | 5 | Multi dimensional array: test (name of the multi dimensional array) ______________________ test(0, |0 | 1 | 2 | 3 | 4 | 5 | ) ______________________ test(1, |0 | 1 | 2 | 3 | 4 | 5 | ) ______________________ test(2, |0 | 1 | 2 | 3 | 4 | 5 | ) ______________________ test(3, |0 | 1 | 2 | 3 | 4 | 5 | ) test(4, |0 | 1 | 2 | 3 | 4 | 5 | ) ______________________ test(5, |0 | 1 | 2 | 3 | 4 | 5 | ) Multi dimensional array (Code): Dim test(5,5) as string For i = 1 To 6 Dim oFirst_family As MaterialFamily Dim ifno As Integer ifno = i Set oFirst_family = cFamilies_list.Item(ifamily_no) Dim sFamilyName As String sFamilyName = oFirst_family.Name MsgBox sFamilyName Set cMaterials_list = oFirst_family.Materials iNb_materials = cMaterials_list.Count //Here i want to create multiple arrays for every iteration . For j = 1 To iNb_materials imaterial_no = j Set oMaterial1 = cMaterials_list.Item(imaterial_no) ReDim Preserve test((i-1),(j-1)) test((i-1),(j-1)) = oMaterial1.Name Next Next Note: Please accept this answer, if it is helpful for you.

Madhanmohan Devarajan · Answer

Instead of creating 6 new array, you can use multi-dimensional array. Because the name of the array will be same, but the value stored can be a new array as you need. Please have a look at the below link. http://www.homeandlearn.org/multidimensional_arrays.html you may not be able to create new array with different name in each iteration, that is the reason for selecting multi dimensional array.

Dinesh Santhalingam · Answer

Madhan, Yes but i want six arrays to store the data. For every iteration new aray stores the value. Please help me to solve my problem.

Madhanmohan Devarajan · Answer

Dinesh, Hope I understood what you are saying, for every single iteration of first "For" loop you want run the inner "For loop" for n number of times. But what you are getting in "test array" is the final value stored while leaving the inner loop. That is the reason i asked you about whether you have declared test outside the inner loop like shown below. Dim test() as string For i = 1 To 6 Dim oFirst_family As MaterialFamily Dim ifno As Integer ifno = i Set oFirst_family = cFamilies_list .Item(ifamily_no) Dim sFamilyName As String sFamilyName = oFirst_family .Name MsgBox sFamilyName Set cMaterials_list = oFirst_family .Materials iNb_materials = cMaterials_list .Count //Here i want to create multiple arrays for every iteration . For j = 1 To iNb_materials imaterial_no = j Set oMaterial1 = cMaterials_list .Item(imaterial_no) ReDim Preserve test(j) test(j) = oMaterial1.Name Next Next

Dinesh Santhalingam · Answer

Hi madhan, I am assign whole thing in one array.But what i want is, the first for loop runs and enter into second for loop ,where i store my values to array and the for loop exits .Now the second iteration begins.Now the inner for loop again starts and stroes the value.But here i am using the array that updated for every iteration and gives the sollution of last iteration only.I hope you understand what am trying to do here.

How to create multiple arrays dynamically in VBA

Answers (6)