This is my code:

Function test(data As Range)

Const nobs As Integer = data.Columns.Count

'Const nobs As Integer = 2  <- this one works, but not dynamic

Dim AMatrix(1 To nobs, 1 To nobs) As Variant

End Function

Dim needs a constant value in it's argument. How can I use my dynamically changing nobs variable instead?

1 Answers

Pᴇʜ On Best Solutions

You must use the ReDim statement and your Nobs must be a variable not a constant.

Dim Nobs As Long
Nobs = Data.Columns.Count

ReDim AMatrix(1 To Nobs, 1 To Nobs) As Variant

Note that Columns.Count returns a Long not an Integer.

Also note that your Function should either return a value or you should switch it to a procudure Sub.