15

I would like to add a value to the end of a VBA array. How can I do this? I was not able to find a simple example online. Here's some pseudocode showing what I would like to be able to do.

Public Function toArray(range As range)
 Dim arr() As Variant
 For Each a In range.Cells
  'how to add dynamically the value to end and increase the array?
   arr(arr.count) = a.Value 'pseudo code
 Next
toArray= Join(arr, ",")
End Function
1
  • Is the idea to add values to the end of an existing array? Or is it like your example where you just want to load a range into an array? If the latter, why not use the one-liner arr = Range.Value?
    – Excellll
    Sep 9, 2014 at 20:13

7 Answers 7

11

I solved the issue by using a Collection and copy it afterwards to an array.

Dim col As New Collection
For Each a In range.Cells
   col.Add a.Value  '  dynamically add value to the end
Next
Dim arr() As Variant
arr = toArray(col) 'convert collection to an array

Function toArray(col As Collection)
  Dim arr() As Variant
  ReDim arr(0 To col.Count-1) As Variant
  For i = 1 To col.Count
      arr(i-1) = col(i)
  Next
  toArray = arr
End Function
1
  • 3
    If you're going to use a Collection, you might as well use a Dictionary Object. ` Set col = CreateObject("Scripting.Dictionary") ` Then you can output the Keys directly as an array and skip your added function: ` arr = col.keys ` <= Array
    – B Hart
    Oct 5, 2016 at 15:13
10

Try this [EDITED]:

Dim arr() As Variant ' let brackets empty, not Dim arr(1) As Variant !

For Each a In range.Cells
    ' change / adjust the size of array 
    ReDim Preserve arr(1 To UBound(arr) + 1) As Variant

    ' add value on the end of the array
    arr (UBound(arr)) = a.value
Next
8
  • Thanks but unfortunately this does not work the UBound(arr) requires that arr is initialized with some dimenstion e.g. Dim arr(1) As Variant but then later the ReDim Preserve is failing and says that the array already is dimensioned? with other words you can not redim an array in VBA?
    – megloff
    Sep 9, 2014 at 8:41
  • According to msdn.microsoft.com/library/w8k3cys2.aspx you should can ... Sep 9, 2014 at 8:48
  • Well the example from msdn does also not work in excel vba. same error, complains that array already is dimensioned
    – megloff
    Sep 9, 2014 at 9:18
  • It looks like that I should use instead of an array a Collection and convert it afterwards to an array. Any other suggestions?
    – megloff
    Sep 9, 2014 at 9:21
  • 2
    Thank you but it still does not wor k this way because as mentioned earlier the UBound(arr) requires an already a dimensioned array. Well It looks like that I have to use a collection instead. Thank you anyway
    – megloff
    Sep 9, 2014 at 11:57
4

This is how I do it, using a Variant (array) variable:

Dim a As Range
Dim arr As Variant  'Just a Variant variable (i.e. don't pre-define it as an array)

For Each a In Range.Cells
    If IsEmpty(arr) Then
        arr = Array(a.value) 'Make the Variant an array with a single element
    Else
        ReDim Preserve arr(UBound(arr) + 1) 'Add next array element
        arr(UBound(arr)) = a.value          'Assign the array element
    End If
Next

Or, if you actually do need an array of Variants (to pass to a property like Shapes.Range, for example), then you can do it this way:

Dim a As Range
Dim arr() As Variant

ReDim arr(0 To 0)                       'Allocate first element
For Each a In Range.Cells
    arr(UBound(arr)) = a.value          'Assign the array element
    ReDim Preserve arr(UBound(arr) + 1) 'Allocate next element
Next
ReDim Preserve arr(LBound(arr) To UBound(arr) - 1)  'Deallocate the last, unused element
1
  • thanks, useing ReDim arr(0 To 0) and then allocating the next element worked for me Jul 15, 2018 at 10:20
1

If your range is a single vector, and, if in a column, the number of rows is less than 16,384, you can use the following code:

Option Explicit
Public Function toArray(RNG As Range)
    Dim arr As Variant
    arr = RNG

    With WorksheetFunction
        If UBound(arr, 2) > 1 Then
            toArray = Join((.Index(arr, 1, 0)), ",")
        Else
            toArray = Join(.Transpose(.Index(arr, 0, 1)), ",")
        End If
    End With
End Function
0

Thx. Doing the same with 2 functions if it can help other noobs like me :

Collection

Function toCollection(ByVal NamedRange As String) As Collection
  Dim i As Integer
  Dim col As New Collection
  Dim Myrange As Variant, aData As Variant
  Myrange = Range(NamedRange)
  For Each aData In Myrange
    col.Add aData '.Value
  Next
  Set toCollection = col
  Set col = Nothing
End Function

1D Array :

Function toArray1D(MyCollection As Collection)
    ' See http://superuser.com/a/809212/69050


  If MyCollection Is Nothing Then
    Debug.Print Chr(10) & Time & ": Collection Is Empty"
    Exit Function
  End If

  Dim myarr() As Variant
  Dim i As Integer
  ReDim myarr(1 To MyCollection.Count) As Variant

  For i = 1 To MyCollection.Count
      myarr(i) = MyCollection(i)
  Next i

  toArray1D = myarr
End Function

Usage

Dim col As New Collection
Set col = toCollection(RangeName(0))
Dim arr() As Variant
arr = toArray1D(col)
Set col = Nothing
0

The answer is in the accepted response in(without the ReDim problem):

https://stackoverflow.com/questions/12663879/adding-values-to-variable-array-vba

In resume:

Dim aArray() As Single ' or whatever data type you wish to use
ReDim aArray(1 To 1) As Single
If strFirstName = "henry" Then
    aArray(UBound(aArray)) = 123.45
    ReDim Preserve aArray(1 To UBound(aArray) + 1) As Single
End If
0
0
Dim arr()  As Variant: ReDim Preserve arr(0) ' Create dynamic array

' Append to dynamic array function
Function AppendArray(arr() As Variant, var As Variant) As Variant
    ReDim Preserve arr(LBound(arr) To UBound(arr) + 1) ' Resize array, add index
    arr(UBound(arr) - 1) = var ' Append to array
End Function

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.