Adding an element to the end of an array in VBA

Question

Q&A: How can one add an element to a the end of a (dynamic) array in VBA?

This same question was for a list in stead of an array: Adding an element to variant list/array in VBA This same question was about redimensioning arrays with 2 dimensions: ReDiming an array in VBA This same question did not ask for the general simple case: https://superuser.com/questions/808798/adding-an-element-to-the-end-of-an-array

a.t. · Accepted Answer · 2018-09-15 19:26:04Z

2

This is a short VBA code to:

Create a dynamic array.
Redimension a dynamic array to 3 elements.

Fill the newly added element with a value.

Sub ChangingArrayLengthShort()
    ' First create array:
    Dim arr() As Variant
    Dim u As Integer
    u = 3

    ReDim arr(1 To u + 1) ' Redimension:
    arr(UBound(arr)) = "SomeValue" ' Fill last element
End Sub

This is that code explained:

Sub ChangingArrayLength()
    Dim arr() As Variant ' One can only redimension a dynamic array, so:
    'Dim arr(43 to 65) As Variant ' Will yield an error since it is not a dynamic- but a static array.

    Dim NrOfElements As Integer

    NrOfElements = 3
    ReDim arr(1 To NrOfElements) ' Re-dimension array length
    MsgBox (UBound(arr)) ' Show what the last element index is in array arr.

    NrOfElements = NrOfElements + 1 ' Increase the variable that is used to increase the nr of elements in array arr.
    ReDim arr(1 To NrOfElements) ' Re-dimension array length
    MsgBox (UBound(arr)) ' Show what the last element index is in array arr. (And that it increased by 1)

    ' Add value at element to the end of an array:
    arr(UBound(arr)) = "This string is added as last element of array named arr"
End Sub

answered Sep 15, 2018 at 19:26

a.t.

2,9077 gold badges49 silver badges99 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user3012708 Over a year ago

And of course, you can always get the current upper bound of the array, and add 1 to it, to get that last "empty" item. (No need to guess the new array size, as shown by "NrOfElements = 3" here.

a.t. Over a year ago

Thank you! That is indeed a safer way if the array contains an element. However if the array does not contain any dimensions/elements yet, the UBound(arr) throws an error in ReDim. So the choice/safety could become situation dependent.

zokaee hamid · Accepted Answer · 2018-10-11 15:14:57Z

1

I've already encountered this problem and I solved this way

 aArray(Array.IndexOf(aArray, aArray.Last)) 'this same reDim action for resize

It also solves the error of the reDim

answered Oct 11, 2018 at 15:14

zokaee hamid

5425 silver badges17 bronze badges

Collectives™ on Stack Overflow

Adding an element to the end of an array in VBA

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related