VBA array length depending on a user input

Question

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"

Answer 1

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