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.

Back to VBA Arrays