excel sqr function vba

[Gabriel]

Hi,


In an excel spreadsheet I manage to get the result but I just don`t
get why the VBA function that I`ve created just doesn`t work.

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

Why? Why in a spreadsheets it works and in vba it doesn`t?

Thank you
Gabriel

 

[KDales]

-----Original Message-----
Hi,


In an excel spreadsheet I manage to get the result but I just don`t
get why the VBA function that I`ve created just doesn`t work.

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

Why? Why in a spreadsheets it works and in vba it doesn`t?

Thank you
Gabriel
.

Haven't tried this, but perhaps the problem is combining
two worksheet functions in one step (your 3rd line,
above) - you could try breaking it down into two steps
(storing the intermediate result temporarily)

 

[Harlan Grove]

...
...

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