Error Converting Passed Range into Array in VBA for Excel
 

Kurt M. Sanger wrote:

For years I've been passing ranges into Excel functions, then converting them
into an array so that I can use them. But today I'm getting an empty array
instead of a full one. If I pass in a 4x4 array my function seems to work.
But when I pass in a 27x10 array I get nada. After the assignment "arrX =
arrayXIn.Value" the debugger shows arrX is the correct size but its empty.
Here's my code. Any suggestions?

Public Function funcExcelGenXXInv(arrayXIn As Range)
Dim arrX As Variant '27 x 10
Dim arrayXPrime As Variant '10 x 27
Dim arrayXpX As Variant '10 x 10
Dim arrayXpXInv As Variant '10 x 10
Dim intNRows, intNCols As Integer

arrX = arrayXIn.Value
'Find size of X array.
intNRows = UBound(arrX, 1)
intNCols = UBound(arrX, 2)

arrayXPrime = funcTranspose(arrX)
arrayXpX = funcMultM(arrayXPrime, arrX)
arrayXpXInv = funcInvMatrix(arrayXpX)
funcExcelGenXXInv = arrayXpXInv
End Function

The following works fine for me in xl2000

Sub Test12345()
Dim arr2
arr2 = funcExcelGenXXInv(Range("A1:J27"))
End Sub

Public Function funcExcelGenXXInv(arrayXIn As Range)
Dim arrX As Variant '27 x 10
Dim arrayXPrime As Variant '10 x 27
Dim arrayXpX As Variant '10 x 10
Dim arrayXpXInv As Variant '10 x 10
Dim intNRows, intNCols As Integer

arrX = arrayXIn.Value
'Find size of X array.
intNRows = UBound(arrX, 1)
intNCols = UBound(arrX, 2)
Debug.Print intNRows, intNCols, arrX(1, 4)

'arrayXPrime = funcTranspose(arrX)
'arrayXpX = funcMultM(arrayXPrime, arrX)
'arrayXpXInv = funcInvMatrix(arrayXpX)
'funcExcelGenXXInv = arrayXpXInv
End Function

If I change the calling procedure to

Set rng = Range("A1:J27")
arr2 = funcExcelGenXXInv(rng)

then I need to change the function to

Public Function funcExcelGenXXInv(ByVal arrayXIn As Range)

Alan Beban