Excel Macro – Create 1D Array from Vba Array
This is one of the easy methods to initialize an array. If you convert data in worksheet directly to vba array, then it will create a 2d array by default.
If you would like to convert Excel range to Array of 1D, you could use one of these code snippets.
- Use Transpose
- While or For loop
The first method is direct. But it can handle only 1 row or 1 column of data. If you have a table data then use this code to convert an array of 2D to 1D.
1. VBA Transpose to Convert Range to 1D Array
For the below code to work, enter some value in Excel worksheet range A1 to A10.
Sub RangeToArray1D() 'Declare Array as variant & Range Dim rArray() As Variant, rRange As Range, iRow As Double, iCol As Double Set rRange = ThisWorkbook.Sheets("sheet1").Range("A1:A10") 'Convert Range to Array directly rArray = Application.Transpose(rRange.Value) 'Print values in array Debug.Print VBA.Join(rArray, ",") End Sub
Press F5 to run this Vba code. Then, you could view the output in immediate window (Ctrl+G).
Note: In this example data in single column is populated in a 1d array. To populate data in single row, you have to use the Transpose function twice like this.
rArray = Application.Transpose(Application.Transpose(rRange.Value))
This way, the data in column gets transposed twice & creates proper output. Otherwise, it will create a 2d array.
2. VBA Loop to Assign values to Array
This is a very basic code. It may not be as faster as previous one. But still this is flexible to filter only required values.
Sub RangeToArray1DLoop() 'Declare Array as variant & Range Dim rArray() As Variant Dim i As Integer 'Loop Thru each value in Range to create array For i = 0 To 9 ReDim Preserve rArray(i) rArray(i) = ThisWorkbook.Sheets("sheet1").Range("A" & i + 1) Next End Sub
You could insert If conditions to include any criteria while loading the data to array.