Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
pls advice
Hello,
Is there any genius out there who can decipher this code which is accepted/evaluated by my VB, yet do not function as it is in my worksheet. this is written first in my personal.xls file (on a module) and the file was save-as "personal.xla" file located in the XLSTART. Add-in checked in the toolbars add-in menus. here is the code... '--------- Function mySPS(test, dec As Long, ParamArray rng()) Dim sConditions As String Dim sRanges As String Dim i As Long sConditions = "--(" & rng(0).Address(, , , True) sRanges = "ROUND(" & rng(0).Address(, , , True) For i = LBound(rng) + 1 To UBound(rng) sConditions = sConditions & "*" & rng(i).Address(, , , True) sRanges = sRanges & "*" & rng(i).Address(, , , True) Next i If test = "POS" Then mySPS = Evaluate("=SumProduct(" & sConditions & "0)," & sRanges & "," & dec & "))") ElseIf test = "NEG" Then mySPS = Evaluate("=SumProduct(" & sConditions & "<0)," & sRanges & "," & dec & "))") ElseIf test = "ALL" Then mySPS = Evaluate("=SumProduct(" & sRanges & "," & dec & "))") End If End Function 'Call expected like (must be good for max# of array factors) '=mySPS("POS",2,L1:L3,M1:M3,N1:N3,,,,) 'for sum of negative products '=mySPS("NEG",2,L1:L3,M1:M3,N1:N3,,,,) 'for sum of positive products '=mySPS("ALL",2,L1:L3,M1:M3,N1:N3,,,,) 'for sum of both -------- The following are multi-tested in my excel 2003 workbook since yesterday. 1. for =mySPS("POS",2,A1:A3,B1:B3,C1:C3) <<<<this do not works if ARRAY factor is more than two(2): result is #VALUE! 2. for =mySPS("NEG",2,A1:A3,B1:B3,C1:C3) <<<<this do not works if ARRAY factor is more than two(2): result is #VALUE! 3. for =mySPS("ALL",2,A1:A3,B1:B3,C1:C3,D1:D3,E1:E3,F1:F3 ,G1:G3)<<<<this do not works if factor is more than six(6): result is #VALUE! ------ please advice if there is a BUG in my VBe since the 3 formulas had been tested on different array address and the workable number of arrayed factors goes wild and weirdoo - sometimes more or sometimes less.. your professional advice is requested...I am short in checking vb codes. happy holidays? dribler2 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Spell check: advise and advice are used in the wrong context. | Excel Discussion (Misc queries) | |||
Importing data, and need formatting advice | Excel Worksheet Functions | |||
Newbie - needing advice on formatting webform data | Excel Discussion (Misc queries) | |||
building invoice - strategy advice | Excel Discussion (Misc queries) |