Creating an Array from a Range in VBA

Question

I m having a seemingly basic problem but can t find any resources addressing it   Simply put  I just want to load the contents of a Range of cells  all one column  into an Array   I am able to accomplish this by means of   DirArray   Array Range  A1    Range  A2      But for some reason  I cannot create the array when expressed this way   DirArray   Array Range  A1 A2      My real Range is much longer  and may vary in length   so I don t want to have to individually enumerate the cells this way  Can anyone tell me how to properly load a whole Range into an Array   With the latter code   MsgBox UBound DirArray  1    And  MsgBox UBound DirArray    Return 0  whereas with the former they return 1

User · Answer

This function returns an array regardless of the size of the range.

Ranges will return an array unless the range is only 1 cell and then it returns a single value instead. This function will turn the single value into an array (1 based, the same as the array's returned by ranges)

This answer improves on previous answers as it will return an array from a range no matter what the size. It is also more efficient that other answers as it will return the array generated by the range if possible. Works with single dimension and multi-dimensional arrays

The function works by trying to find the upper bounds of the array. If that fails then it must be a single value so we'll create an array and assign the value to it.

Public Function RangeToArray(inputRange As Range) As Variant()
Dim size As Integer
Dim inputValue As Variant, outputArray() As Variant

    ' inputValue will either be an variant array for ranges with more than 1 cell
    ' or a single variant value for range will only 1 cell
    inputValue = inputRange

    On Error Resume Next
    size = UBound(inputValue)

    If Err.Number = 0 Then
        RangeToArray = inputValue
    Else
        On Error GoTo 0
        ReDim outputArray(1 To 1, 1 to 1)
        outputArray(1,1) = inputValue
        RangeToArray = outputArray
    End If

    On Error GoTo 0

End Function

User · Answer

If we do it just like this  Dim myArr as Variant myArr   Range  quot A1 A10 quot    the new array will be with two dimensions  Which is not always somehow comfortable to work with   To get away of the two dimensions  when getting a single column to array  we may use the built-in Excel function    Transpose     With it  the data becomes in one dimension   If we have the data in a row  a single transpose will not do the job  We need to use the Transpose function twice   Note  As you see from the screenshots  when generated this way  arrays start with 1  not with 0  Just be a bit careful

User · Answer

In addition to solutions proposed  and in case you have a 1D range to 1D array  i prefer to process it through a function like below  The reason is simple  If for any reason your range is reduced to 1 element range  as far as i know the command Range   Value will not return a variant array but just a variant and you will not be able to assign a variant variable to a variant array  previously declared    I had to convert a variable size range to a double array  and when the range was of 1 cell size  i was not able to use a construct like range   value so i proceed with a function like below   Public Function Rng2Array inputRange As Range  As Double        Dim out   As Double         ReDim out inputRange Columns Count - 1       Dim cell As Range     Dim i As Long     For i   0 To inputRange Columns Count - 1         out i    inputRange 1  i   1   loop over a range  row      Next      Rng2Array   out   End Function

User · Answer

Adding to  Vityata  s answer  below is the function I use to convert a row   column vector in a 1D array  Function convertVecToArr ByVal rng As Range  As Variant  convert two dimension array into a one dimension array          Dim arr   As Variant  slicedArr   As Variant     arr   rng value    arr   rng works too  https   bettersolutions com excel cells-ranges vba-working-with-arrays htm           If UBound arr  1   gt  UBound arr  2  Then         slicedArr   Application WorksheetFunction Transpose arr      Else         slicedArr   Application WorksheetFunction index arr  1  0    If you set row num or column num to 0  zero   Index returns the array of values for the entire column or row  respectively                                                                       To use values returned as an array  enter the Index function as an array formula in a horizontal range of cells for a row                                                                       and in a vertical range of cells for a column                                                                      https   usefulgyaan wordpress com 2013 06 12 vba-trick-of-the-week-slicing-an-array-without-loop-application-index      End If convertVecToArr   slicedArr End Function

User · Answer

Using Value2 gives a performance benefit   As per Charles Williams blog      Range Value2  works the same way as Range Value  except that it does not check the cell format and convert to Date or Currency  And thats probably why its faster than  Value when retrieving numbers    So  DirArray    a1 a5  Value2   Bonus Reading   Range Value  Returns or sets a Variant value that represents the value of the specified range  Range Value2  The only difference between this property and the Value property is that the Value2 property doesn t use the Currency and Date data types

User · Answer

Just define the variable as a variant  and make them equal   Dim DirArray As Variant DirArray   Range  a1 a5   Value   No need for the Array command

[arrays] Creating an Array from a Range in VBA

Examples related to arrays

Examples related to excel

Examples related to vba

Examples related to range