Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
excel sqr function vba
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
excel sqr function vba
-----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) |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
excel sqr function vba
"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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel Data Validation/Lookup function does function correcty | Excel Worksheet Functions | |||
copy of excel file not showing formulal/function in the function b | Excel Discussion (Misc queries) | |||
Adding a custom function to the default excel function list | Excel Programming | |||
When I Import an Access Table With an Excel Function in a Cell it Displays as Text not as a Function in Excel | Excel Programming | |||
Excel - saving result of a function without showing the function | Excel Programming |