Using a VBA UDF in an array formula

[Will T.]

I am trying to use a user defined formula within an array formula and keep getting an error message. I am trying to replicate a formula which returns a covariance matrix in a logistic regression problem. The matrix formula is X'WX where X' is the transpose of X. The X matrix is a matrix of independent variables of dimension n x K where n is the number of observations and k is the number of variables. A very simplified example of the situation is attached which has 3 independent variables in columns A, B, and C. The W matrix is a diagonal matrix of dimension n x n. The diagonal entries of this matrix are the product of three separate column vectors and the off diagonal values are all zeros. I have written a user defined formula to fill the W matrix. The values for the W matrix are in columns D, E, and F. The actual array formula is "=MMULT(TRANSPOSE(A2:C6),MMULT(DIAGONAL(D2:D6,E2:E6,F2:F6),A2:C6))" where DIAGONAL is the user defined function. The code for this function is shown below. The function as written always returns #VALUE in each cell. The subroutine to test the function on the example worksheet is below the function. Note that both of these routines must be in a general VBA module. They cannot be in a spreadsheet module. Any help would be appreciated.

Public Function Diagonal(rngA As Range, rngB As Range, rngC As Range) As Variant

Dim TempArray() As Variant
Dim I, J As Integer
Dim intN As Integer
Dim bolEqual
Dim strA, strB, strC As String
Dim strD, strE, strF As String


strA = Split(rngA.Address, "$")(1)
strB = Split(rngB.Address, "$")(1)
strC = Split(rngC.Address, "$")(1)
intN = Split(rngA.Address, "$")(4)

ReDim TempArray(1 To intN, 1 To intN)

For I = 1 To intN
For J = 1 To intN
If I = J Then
strF = strC & Format(I + 1)
TempArray(I, J) = ActiveSheet.Range(strD).Value * ActiveSheet.Range(strE).Value * ActiveSheet.Range(strF).Value
Else
TempArray(I, J) = 0#
End If
Next J
Next I
Diagonal = TempArray

End Function

Private Sub TestUDF()


Dim strFormula As String
Dim strRange As String

strRange = "G7:I9"
strFormula = "=MMULT(TRANSPOSE(A2:C6),MMULT(DIAGONAL(D2:D6,E2:E6,F2:F6),A2:C6))"

ActiveSheet.Range(strRange).FormulaArray = strFormula


End Sub