How to create multiple arrays dynamically in VBA

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 | )

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)

Next

Next

 

 

Note: Please accept this answer, if it is helpful for you.  

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. 

 

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  

1. For i = 1 To 6
2. Dim oFirst_family As MaterialFamily
3. Dim ifno As Integer
4. ifno = i
5. Set oFirst_family = cFamilies_list.Item(ifamily_no)
6. Dim sFamilyName As String
7. sFamilyName = oFirst_family.Name
8. MsgBox sFamilyName
9. Set cMaterials_list = oFirst_family.Materials
10. iNb_materials = cMaterials_list.Count
11. //Here i want to create multiple arrays for every iteration .
12. For j = 1 To iNb_materials
13. imaterial_no = j
14. Set oMaterial1 = cMaterials_list.Item(imaterial_no)
15. ReDim Preserve test(j)
16. test(j) = oMaterial1.Name
18. Next
19. 
    
20. Next

 

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.

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.

If you are looking for multi-dimensional array it is here