VBA Express : Excel - Adding an item to a dynamic Array of strings

Excel

Adding an item to a dynamic Array of strings

Ease of Use

Easy

Version tested with

2003

Submitted by:

Teeroy

Description:

The function will return the supplied Array with the supplied new element added to the end and optionally removes empty elements of the array. An initialized Array must be sent to the function.

Discussion:

I was trying to do with arrays what I'd previously done with python lists;adding elements on the fly. I was getting frustrated that arrays didn't have methods to append elements so I came up with a simple function to do it. The function call sends a dynamic Array and a new element and returns the Array as the same variable including the new element . If you send the optional third parameter (as True) empty elements of the Array are removed and the Array size reduced accordingly. The function is set up to work with strings but could be easily modified to handle other data types. I use this mainly to build lists to join into a single string (e.g. file paths) or populate list boxes.

Code:

instructions for use

			Function append(arr() As String, sNew As String, Optional collapse As Boolean = False) 
    Dim aTemp() As String 
    Dim i As Integer 
    Dim count As Integer 
    If arr(0) = "" Then 
        arr(0) = sNew 
    Else 
        ReDim Preserve arr(UBound(arr) + 1) 
        arr(UBound(arr)) = sNew 
    End If 
    If collapse Then 
        ReDim aTemp(UBound(arr)) 
        count = 0 
        For i = 0 To UBound(arr) 
            If arr(i) <> "" Then 
                aTemp(count) = arr(i) 
                count = count + 1 
            End If 
        Next i 
        ReDim Preserve aTemp(count - 1) 
        append = aTemp() 
    Else 
        append = arr() 
    End If 
End Function 
 
Sub Test1() 
    Dim aMyArray() As String 
    ReDim aMyArray(0) 
    aMyArray = append(aMyArray, "New Member") 
    Range("A1").Value = aMyArray(0) 
End Sub 
 
Sub Test2() 
    Dim aMyArray() As String 
    Dim i As Integer 
    ReDim aMyArray(0 To 1) 
    aMyArray(0) = "First Member" 
    aMyArray(1) = "Second Member" 
    aMyArray = append(aMyArray, "New Member") 
    For i = 0 To UBound(aMyArray) 
        Cells(i + 1, 1).Value = aMyArray(i) 
    Next i 
End Sub 
 
Sub Test3() 
    Dim aMyArray() As String 
    Dim i As Integer 
    ReDim aMyArray(0 To 3) 
    aMyArray(0) = "First Member" 
    aMyArray(1) = "Second Member" 
     'in this example there are two unasigned members
    aMyArray = append(aMyArray, "New Member") 
    For i = 0 To UBound(aMyArray) 
        Cells(i + 1, 1).Value = aMyArray(i) 
    Next i 
End Sub 
 
Sub Test4() 
    Dim aMyArray() As String 
    Dim i As Integer 
    ReDim aMyArray(0 To 3) 
    aMyArray(0) = "First Member" 
    aMyArray(1) = "Second Member" 
    aMyArray = append(aMyArray, "New Member", True) 
    For i = 0 To UBound(aMyArray) 
        Cells(i + 1, 1).Value = aMyArray(i) 
    Next i 
End Sub

How to use:

Copy the above code.
Open new workbook.
Press Alt + F11 to open the Visual Basic Editor (VBE).
From the Menu, choose Insert-Module.
Paste the code into the right-hand code window.
Close the VBE, save the file if desired.

Test the code:

Run the test codes above and they show;
Test1: How you can append to an empty array.
Test2: How you append normally
Test3: What occurs if you send an array that has empty members (without the optional third parameter as True)
Test4: What occurs if you send an array that has empty members with the optional third parameter as True

Sample File:

ArrayAppend.zip 7.75KB

Approved by Jacob Hilderbrand

This entry has been viewed 15 times.