Matrix Dimension as variable/constant

[gonurvia]

Hello, I am trying to make a function which would use a matrix of (N ,2) dimensions. The function (incomplete) is as following:

Function APPROACH(P1 As Range, x1 As Double, y1 As Double) As Double 
N = P1.Rows.Count 
Dim A1(1 To 3, 1 To 3) As Double 
Dim A2(1 To [B]N[/B], 1 To 2) As Double  

I get an error message which tells me N must be a constant. N is already defined as P1.Rows.Count and I won't change so, is there a way to change N from variable to constant? Also, I read something about matrices having a 32 dimension limit, is this true?

Thanks for the help!

[xlnitwit]

Hi,

You cannot convert N to a constant since it is not constant. You need to declare a dynamic array and then resize it thus

Please Login or Register  to view this content.

you can omit the initial Dim and just use ReDim but I prefer to use both.

To the best of my knowledge the VBA limit is 60 dimensions (32 is for VB) though if you use anything like that number you must be doing something very strange indeed in my opinion, and have a fairly unique mind to keep them straight in your head!

[gonurvia]

Thanks xlnitwit, it worked!