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