VBA Store Variant Values to Array in Excel Explained with Examples

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.

Table of Contents:

Overview

VBA Example to Store Variant Values to Array in Excel

Instructions to use macro

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.