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.
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"
It sounds like you want to make use of VB's Redim keyword.
Redim allows you to redefine the size of the array at runtime to a given upper bound.
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.
Instead we use the redim keyword as below:
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
Dim myArray(NIteration) as Double. If not Phil has a complete detail.