获取用户输入并将其添加到数组VBA的最后一个索引中

Question

我需要知道如何使用用户输入将项添加到现有数组中。

我试过这样的东西：

Dim MyArray(100) as String
UBound(MyArray) = Inputbox("What's the name of your new array item?")

Answer 1

Ubound(myArray)返回Long数据类型，它实际上返回数组的长度/大小。

如果你想读取或分配一个值，那么你必须完全限定数组，即

MyArray(ubound(myArray)) = "new Value"或MyArray(100) = "new Value"

Sub Main()
    Dim MyArray(100) As String
    MyArray(UBound(MyArray)) = InputBox("What's the name of your new array item?")
    MsgBox MyArray(UBound(MyArray))
End Sub

Answer 2

您可以使用动态数组：

Dim MyArray() As String
' Then to add a new element:
Redim Preserve MyArray(1 to SafeUBound(MyArray) + 1)
MyArray(SafeUBound(MyArray)) = InputBox("Who's yer daddy?")
Function SafeUBound(vArray As Variant) As Long
Dim x As Long
On Error Resume Next
x = UBound(vArray)
If Err.Number = 0 Then
    SafeUBound = x
Else
    SafeUBound = 0
End If
End Function

对SafeUbound的调用是必要的，因为在未初始化的数组上调用UBound或LBound会引发错误。