You really need to stick to one thread, you have started at least 6 on this
same topic in the past few days, and now you have moved on to posting the
same problem to many newsgroups.

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"dribler2" wrote in message
...
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

Firing off new threads at the drop of a hat is unlikely to get better or
more responses, it will probably have the opposite effect.

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"dribler2" wrote in message
...
Sorry for that, I just really need help with myExcel!

I hope you will understanding mine problem..

"Bob Phillips" wrote:

You really need to stick to one thread, you have started at least 6 on
this
same topic in the past few days, and now you have moved on to posting the
same problem to many newsgroups.

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"dribler2" wrote in message
...
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