Question posted 2012 · +6 upvotes
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?
Accepted answer +8 upvotes
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.
3 code variants in this answer
- Variant 1 — 1 lines, starts with
Dim Students() As Variant - Variant 2 — 1 lines, starts with
Students = Array(2,7,14,54,33,45,55,59,62,66,69) - Variant 3 — 5 lines, starts with
Dim Inx As Long
Top excel-vba Q&A (6)
- How to clear the entire array? +58 (2010)
- How to change Format of a Cell to Text using VBA +55 (2011)
- Download attachment from Outlook and Open in Excel +43 (2012)
- Can a VBA function in Excel return a range? +36 (2009)
- 2 Dimensional array from range +34 (2013)
- Hiding an Excel worksheet with VBA +33 (2009)
excel-vba solutions on this site
.