VBA Store Variant Values to Array in Excel
We can quickly create an array using variant type data by creating dynamic array. Let us see how to store variant values to an array quickly in the following tutorial. When we reading values from range, the range can have different types of data. It can be string, number, date, etc. The created array is two dimensional array. Always the lowerbound value starts from 0 not from 1. This value is applicable for both single and multiple columns.
VBA Example to Store Variant Values to Array in Excel
The below example read values from range A1 to A6. Array stored values displays in the immediate window.
'VBA Store Variant Values to an Array. Sub VBA_Store_Values_To_Array() 'Declare an array variable Dim sWeekDays As Variant Dim rRange As Range 'Define an Array values sWeekDays = Range("A1:A6") 'Loop through each item in an array For iCnt = 1 To UBound(sWeekDays) Debug.Print sWeekDays(iCnt, 1) iCnt = iCnt + 1 Next End Sub
Here is the output screen shot for the above example macro code.

Instructions to use Macro
Here are the instructions to use above macro in Visual basic editor.
- Open Visual Basic Editor(VBE) by clicking Alt +F11
- Go to code window by clicking F7
- Copy above specified macro or procedure
- Paste above copied code in code window
- Run macro by clicking F5 or Run command
- You can see output on the screen
- Find above specified output screenshot.
VBA Arrays other articles
You may also like the related VBA Array articles.