VBA數組長度取決於用戶輸入 - VBA array length depending on a user input -开发者知识库
I wish to create a simple VBA array of doubles, but I want the array to have a length that is specified by a worksheet's cell value.
我希望創建一個簡單的雙打VBA數組，但我希望數組的長度由工作表的單元格值指定。
I try to do:
我嘗試做：
Dim NIteration As Integer: NIteration = ActiveSheet.Cells(16, 2).Value
Dim myArray(1 To NIteration) As Double
It fails with this error: "requires constant expression"
它失敗了這個錯誤：“需要持續表達”
1 个解决方案
#1
9
It sounds like you want to make use of VB's Redim keyword.
聽起來你想要使用VB的Redim關鍵字。
Redim allows you to redefine the size of the array at runtime to a given upper bound.
Redim允許您在運行時將數組的大小重新定義為給定的上限。
Dynamic array variables
動態數組變量
Dynamic array variables are useful when you in advance don’t know how many elements that you need to store information about.
如果您事先不知道存儲信息所需的元素數量，則動態數組變量非常有用。
You declare dynamic array variables just like a static array variable, except that you don’t give any information about the array size.
您聲明動態數組變量就像靜態數組變量一樣，除了您沒有提供有關數組大小的任何信息。
As an example, if you have:
例如，如果你有：
Dim SheetNames(1 to 10) As String
an error will be thrown if the number of sheets exceeds 10 since SheetNames will not able to store more than 10 items in the collection.
如果工作表數量超過10，則會拋出錯誤，因為SheetNames將無法在集合中存儲超過10個項目。
Instead we use the redim keyword as below:
相反，我們使用redim關鍵字如下：
Sub Test()
Dim MyArray() As String ' declare an array
Dim iCount As Integer
Dim Max As Integer
Max = ActiveSheet.Cells(16, 2).Value ' finds the maximum array size
ReDim MyArray(1 To Max) ' redeclares the array variable with the necessary size
For iCount = 1 To Max
MyArray(iCount) = ThisWorkbook.Sheets(iCount).Name ' (or whatever you're storing in the array)
MsgBox MyArray(iCount)
Next iCount
Erase MyArray() ' deletes the variable contents
End Sub
最佳答案：
作者信息
