CODEWITHSUNDEEP
excelvba
Übel Yildmar
Übel Yildmar

Reputation: 491

How to define Dim using dynamic variables as arguments in Excel VBA?

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?

Upvotes: 0

Views: 229

Answers (1)

Pᴇʜ
Pᴇʜ

Reputation: 57683

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.

Upvotes: 3

Related Questions