在VBA中动态附加一个数组

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.

显然，这是一个例子，不是最优雅的表达方式，但它不起作用，因为我似乎不能重划数组，因为它最初没有ubound或lbound。当我把它调暗为myArr(0到0)时，它也失败了。

Any ideas?

什么好主意吗?

2 个解决方案

#1

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

在第一次使用myArr数组之前，运行以下命令:

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.

然后当您使用dynamic ReDim语句时，只在满足某些条件时才使用ReDim，例如UBound(myArr) > 1等等。

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

#2

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

如果您感兴趣的话，Olle的解决方案可以通过更多的检查和平衡来扩展。

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

请参阅InsertElementIntoArray函数:http://www.cpearson.com/excel/VBAArrays.htm

#1