I can't find a way to practically define an array, push some values and output them as a string in VBA? (new array, push, join)

Question

I've found out how to create an array, but I cannot push items easily. I must maintain a index to the next position and increment every time I push an item;

I've also found the collection which has the nice .Add method that acts exactly like a push method. But how I join them? The global Join method doesn't work with Collections.

What I'm missing here? anybody could help me to define an array, push items easily without an index and then output them to a string spaced by ", "?

Thanks

Answer 1

You can't do that directly. Arrays in VBA usually need to be indexed and dimensioned before use.

You can use a dynamic array and resize before assigning a variable:

Dim arr() As String
ReDim arr(0)

arr(UBound(arr)) = "Some String"
ReDim Preserve arr(UBound(arr) + 1)
arr(UBound(arr)) = "Some Other String"
ReDim Preserve arr(UBound(arr) + 1)
arr(UBound(arr)) = "Some 3rd String"

MsgBox Join(arr, ",")

The preserve key word maintains the values in the array rather than overwriting them. The above approach is generally not recommended however since Preserve is costly and only allows you to resize the last dimension of an array.

Collections are different, are slower and generally less flexible in a VBA environment (you haven't said which environment, but I'll assume Excel)

Dim coll As Collection
Dim itm As Variant
Dim tempS As String


Set coll = New Collection
coll.Add "Some String"
coll.Add "Some Other String"
coll.Add "Some 3rd String"

For Each itm In coll
    tempS = tempS & itm & ","
Next itm

MsgBox Left(tempS, Len(tempS) - 1)

You need to loop through them to build an array.

There are a numerous of other options depending on your needs

Built in method

for strings, have a look at split:

Const stri As String = "Some String, Some Other String, Some 3rd String"
Dim arr() As String

arr = Split(stri, ",")

MsgBox Join(arr, ",")

Using External objects

Scripting Dictionary

Dim dic As Object

Set dic = CreateObject("scripting.Dictionary")
dic.Add "1", "Some String"
dic.Add "2", "Some Other String"
dic.Add "3", "Some 3rd String"

Debug.Print Join(dic.items, ",")

.Net arrayList

Dim al As Object

Set al = CreateObject("System.Collections.Arraylist")
al.Add "Some String"
al.Add "Some Other String"
al.Add "Some 3rd String"

MsgBox Join(al.ToArray(), ",")