sqr function in vba

[Gabriel]

Hi,

When I add sqr function to the below myFormula it doesn't work anymore.

So, this works fine:
Function MyFormula(Array1, Array2) As Variant
With Application.WorksheetFunction
MyFormula = .MMult(.Transpose(Array1), .MMult(Array2, Array1))
End With
End Function

This doesn't work
MyFormula = Sqr(.MMult(.Transpose(Array1), .MMult(Array2, Array1)))

Does anyone know what am I missing here?

Much obliged
Gabriel

 

[Tom Ogilvy]

Function Sqr(Number As Double) As Double

are you passing a Number as Double to the sqr function?

 

[DennisE]

Gabriel,

The MMult function does a matrix multiplication of two arrays and results in
yet another matrix. You cannot take the square root of a matrix, only of a
scalar number (like sqr(2) = 1.414...).

-- Dennis Eisen

 

[J.E. McGimpsey]

MMULT returns an array. When a function returns an array to a cell,
the first element is put in the cell.

Sqr() however, takes a value, not an array, as an argument. If you
want to take the square root of the array's first element, use

MyFormula = Sqr( _
.MMult(.Transpose(Array1), .MMult(Array2, Array1))(1, 1))

 

[DennisE]

Gabriel,

This extends my previous response, in that because the MMult function returns a
matrix (even if it's a 1 x 1 matrix), the sqr function cannot be used. The
appropriate formula you want to use is to incorporate the SUMPRODUCT function,
which when applied to two arrays results
in a scalar (rather than a 1 x 1 matrix), for which the sqr function can be
applied.

-- Dennis Eisen