How to return large multi-dimensional arrays from VBA Excel functions?

[Nitesh]

I have tried a code to return multi-dimensional arrays from VBA excel functions. it works with small amount of data but in large amounts of data it only pastes the first column or row of the array and leaves the other rows as such. Desperatetly need a solution.....

[VBA]
Private Function XTX(iStart As Range, iMatrixR As Object, iMatrixC As Object) As Variant
Dim temp() As Double
Dim iAlpha As Integer
Dim iRemainder As Integer

i = iMatrixR.Count
j = iMatrixC.Count

ReDim temp(j - 1, j - 1)

irow = iStart.Row
iCol = iStart.Column
iColF = iCol
iColS = iCol

For n = 0 To j - 1

iAlpha = Int(iCol / 26)
iRemainder = iCol - (iAlpha * 26)
If iAlpha > 0 Then
strColF = Chr(iAlpha + 64)
End If
If iRemainder > 0 Then
strColF = strColF & Chr(iRemainder + 64)
End If

If iRemainder = 0 Then

iAlpha = Int(iCol / 27)
iRemainder = iCol - (iAlpha * 26)

If iAlpha > 0 Then

strColF = Chr(iAlpha + 64)

End If

If iRemainder > 0 Then

strColF = strColF & Chr(iRemainder + 64)

End If

End If

For k = 0 To j - 1

iAlpha = Int(iCol / 26)
iRemainder = iCol - (iAlpha * 26)

If iAlpha > 0 Then

strCol = Chr(iAlpha + 64)

End If

If iRemainder > 0 Then

strCol = strCol & Chr(iRemainder + 64)

End If

If iRemainder = 0 Then

iAlpha = Int(iCol / 27)
iRemainder = iCol - (iAlpha * 26)

If iAlpha > 0 Then

strCol = Chr(iAlpha + 64)

End If

If iRemainder > 0 Then

strCol = strCol & Chr(iRemainder + 64)

End If

End If


ival = 0
irow1 = irow

For l = 1 To i

str1 = strCol & irow1
str2 = strColF & irow1

ival = ival + Range(str1).Value * Range(str2).Value

irow1 = irow1 + 1

Next l

iCol = iCol + 1
temp(n, k) = ival
strCol = ""

Next k

iCol = iColS
iColF = iColF + 1
strColF = ""

Next n
XTX = temp

End Function
[/VBA]
Edit by Lucas: VBA tags added to code. When posting, select your code and hit the VBA button...

[Bob Phillips]

What does it do, how does it get called, what does it return?

[tstom]

Also...
iMatrixR As Object, iMatrixC As Object
...what are these objects being passed to your function?

[matthewspatrick]

I have tried a code to return multi-dimensional arrays from VBA excel functions. it works with small amount of data but in large amounts of data it only pastes the first column or row of the array and leaves the other rows as such. Desperatetly need a solution.....

(Side note: please use the VBA tags to highlight your code. It will really help with the readability.)

Sounds to me like you are not entering the worksheet formula as an array formula.

Let's say I have a UDF, Foo(), that returns a (1 To 20, 1 To 4) array. Let us further say that I want the results returned to A2 : D21 (I guess I want headers in Row 1). I would need to:

1. Select A2 : D21
2. Enter my formula, e.g. =Foo(arg1, arg2, ...)
3. Instead of hitting Enter, hit Ctrl+Shift+Enter to indicate to Excel that it's an array formula