"Gabriel" wrote...
...
Basically, I have two arrays, the formula below and the result

1
2
3

4 5 6
7 8 9
10 11 12

{=SQRT(MMULT(TRANSPOSE(A1:A3);MMULT(C1:E3;A1:A3)) )}=18.33

Then in VBA, it just doesn`t work

Function MyFormula(Array1, Array2) As Variant
With Application.WorksheetFunction
MyFormula = Sqr(.MMult(.Transpose(Array1), .MMult(Array2, Array1)))
End With
End Function
...

Useful to say how this doesn't work. It returns #VALUE!, right?

The problem is in MMULT. It *always* returns an array. In the case of applying
the bilinear form tr(x) A x, the result is a degenerate 1-by-1 array. Since you
array-enterd the SQRT(...) formula in the worksheet, no problem. You can select
the cell containing that formula, press [F2] then press [F9] and you'll see that
what's left in the formula bar is ={18.3303027798234} .

The same thing happens in VBA. The outer .MMult call returns a 1-by-1 array, but
built-in VBA functions that expect nothing but scalar arguments, such as Sqr,
just can't handle array arguments, even degenerate ones.

Two alternatives. First, just to force the outer .MMult call's result to scalar,


Function bar(a As Variant, b As Variant) As Double
With Application.WorksheetFunction
bar = Sqr(.Sum(.MMult(.Transpose(a), .MMult(b, a))))
End With
End Function


Second, dispense with unnecessary function calls.


Function foo(a As Variant, b As Variant) As Double
With Application.WorksheetFunction
foo = Sqr(.SumProduct(a, .MMult(b, a)))
End With
End Function

--
To top-post is human, to bottom-post and snip is sublime.