excel - 当我不知道上限值时如何在excel vba中使用 'ReDim Preserve'

Question

我需要从处理 .txt 期间生成的变量值填充数组文件。然而，上限取决于输入线。我怎样才能使以下事情起作用？

Dim myArr As Variant
ReDim Preserve myArr(1)  ' this line does not work without a value

Do While (some condition)
    myArr=Trim(variable_name)
Loop

Answer 1

检查循环中的条件并增加一个计数器，然后在 1 行中重新调整数组。将有 2 个循环，但这仍然会更快。这是一种方法( 未经测试 )

Dim myArr As Variant
Dim counter As Long

Do While (some condition)
    counter = counter + 1
Loop

ReDim myArr(counter) '<~~ No need of Preserve

counter = 0

Do While (some condition)
    myArr(counter) = Trim(variable_name)
    counter = counter + 1
Loop