VBA Store Variant Values to Array in Excel

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 lower-bound 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
End Sub

Output: Here is the output screen shot for the above example macro code.

Store Variant Values to an Array

Instructions to Run VBA Macro Code or Procedure:

You can refer the following link for the step by step instructions.

Instructions to run VBA Macro Code

Other Useful Resources:

Click on the following links of the useful resources. These helps to learn and gain more knowledge.

VBA Tutorial VBA Functions List VBA Arrays in Excel Blog

VBA Editor Keyboard Shortcut Keys List VBA Interview Questions & Answers

Leave a Reply