Nitesh

01-06-2007, 01:49 AM

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.....

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

Edit by Lucas: VBA tags added to code. When posting, select your code and hit the VBA button...

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

Edit by Lucas: VBA tags added to code. When posting, select your code and hit the VBA button...