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