Dynamically appending an array in VBA

Question

I want to append an array with a number depending on the condition of various variables. Here is the code I've come up with: I begin with an empty array.

Sub makeArr()
Dim myArr() As Integer
If box1 = True Then
    ReDim Preserve myArr(LBound(myArr) To UBound(myArr) + 1)
    myArr(UBound(myArr)) = 1
End If

If box2 = True Then
    ReDim Preserve myArr(LBound(myArr) To UBound(myArr) + 1)
    myArr(UBound(myArr)) = 2
End If
End Sub

Obviously this is an example so not the most elegant way of putting it but it doesn't work as I can't seem to reDim the array as it doesn't initially have a ubound or an lbound. When I dim it as myArr(0 to 0) this also fails.

Any ideas?

Another approach to testing lbound is to write a little SafeLBound function. It simply looks at the lbound of the array passed to it, but traps any errors. If no error, it returns the lbound, if error, it returns 0 or -1 or whatever makes sense as an impossible lbound in your context — Steve Rindsberg
– Steve Rindsberg, Commented Jan 30, 2013 at 17:25

Olle Sjögren · Accepted Answer · 2013-01-30 11:46:21Z

6

Before using the myArr array the first time, run this:

ReDim Preserve myArr(0 To 1)

Then when you come to the dynamic ReDim statement, only use ReDim if certain conditions are met, e.g. If UBound(myArr) > 1 then etc.

If box1 = True Then
    If UBound(myArr) > 1 Then
        ReDim Preserve myArr(LBound(myArr) To UBound(myArr) + 1)
    End If
    myArr(UBound(myArr)) = 1
End If

answered Jan 30, 2013 at 11:46

Olle Sjögren

5,3923 gold badges35 silver badges55 bronze badges

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

Comments

Community · Accepted Answer · 2018-05-08 21:49:10Z

1

Olle's solution can be expanded upon with some more checks and balances, if it interests you.

see the InsertElementIntoArray Function here: http://www.cpearson.com/excel/VBAArrays.htm

edited May 8, 2018 at 21:49

CommunityBot

11 silver badge

answered Apr 4, 2014 at 17:54

ecoe

5,4028 gold badges62 silver badges84 bronze badges

Collectives™ on Stack Overflow

Dynamically appending an array in VBA

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related