Excel – Array in excel vba

arraysexcelvba

I want to have an array list in vba, hence I have a variant declared in excel vba like:

Dim Students(10) as variant

Now I want to store numbers in Students list. the numbers are not continuous. Sometime like:

Students(2,7,14,54,33,45,55,59,62,66,69)

How can I do this in vba? also how can I access the list items?

Best Solution

Students must be declared as a dynamic array. That is, an array whose bounds can be changed. Dim Students(10) gives an array whose bounds cannot be changed and cannot be loaded from an array.

Dim Students() As Variant

To load Students:

Students = Array(2,7,14,54,33,45,55,59,62,66,69)

To access the elements:

Dim Inx As Long

For Inx = LBound(Students) to UBound(Students)
  Debug.Print Students(Inx)
Next

LBound (Lower bound) and UBound mean that the for loop adjusts to the actual number of elements in Students.