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

It works for me, regardless of number of arrays.

What does your data look like?

--
---
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

gud morning

1.233 33.22 1 1 1 1 1 1 1 1 1 1 1 1
0.245 11.21 1 1 1 1 1 1 1 1 1 1 1 1
0.122 -3.215 1 1 1 1 1 1 1 1 1 1 1 1

this is my current test data range in my BOOK1.xls [looking for the varying
limit]
A1:N3 [C1:N3 = 1]

=mySPs("ALL",2,A1:A3,B1:B3,C1:C3,D1:D3,E1:E3,F1:F3 ,G1:G3,H1:H3,I1:I3,J1:J3,K1:K3,L1:L3,M1:M3,N1:N3)
=mySPs("POS",2,A1:A3,B1:B3,C1:C3,D1:D3,E1:E3,F1:F3 ,G1:G3,H1:H3,I1:I3,J1:J3,K1:K3,L1:L3,M1:M3,N1:N3)
=mySPs("NEG",2,A1:A3,B1:B3,C1:C3,D1:D3,E1:E3,F1:F3 ,G1:G3,H1:H3,I1:I3,J1:J3,K1:K3,L1:L3,M1:M3,N1:N3)

TfH
dribler2

"Bob Phillips" wrote:

It works for me, regardless of number of arrays.

What does your data look like?

--
---
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

Here's one more

when i save-as the default BOOK1.xls [by another filename] having the three
formulas [for different number of array factors]. Some of the correct results
are retained and some goes wild and refreshed with a #VALUE! result.

When I open again a default BOOK1.xls file, I copy the sheets, the correct
results are again displayed!

Really weird...please advice...

"dribler2" wrote:

gud morning

1.233 33.22 1 1 1 1 1 1 1 1 1 1 1 1
0.245 11.21 1 1 1 1 1 1 1 1 1 1 1 1
0.122 -3.215 1 1 1 1 1 1 1 1 1 1 1 1

this is my current test data range in my BOOK1.xls [looking for the varying
limit]
A1:N3 [C1:N3 = 1]

=mySPs("ALL",2,A1:A3,B1:B3,C1:C3,D1:D3,E1:E3,F1:F3 ,G1:G3,H1:H3,I1:I3,J1:J3,K1:K3,L1:L3,M1:M3,N1:N3)
=mySPs("POS",2,A1:A3,B1:B3,C1:C3,D1:D3,E1:E3,F1:F3 ,G1:G3,H1:H3,I1:I3,J1:J3,K1:K3,L1:L3,M1:M3,N1:N3)
=mySPs("NEG",2,A1:A3,B1:B3,C1:C3,D1:D3,E1:E3,F1:F3 ,G1:G3,H1:H3,I1:I3,J1:J3,K1:K3,L1:L3,M1:M3,N1:N3)

TfH
dribler2

"Bob Phillips" wrote:

It works for me, regardless of number of arrays.

What does your data look like?

--
---
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

Excuse me Sir Bob,
do I need to forget this helpful UDF from you. You've work-out for this help
from a
long way. I may have missed something, that u surely know, specially in
storing this in the personal.xla file...Will it be possible to make it like a
function that can be paste on any workbook and work-out independent with
other workbook....Please don't leave me from the dark this New Year...really
i need your kind help.

wishing you a very powerful new year,
dribler2

"Bob Phillips" wrote:

It works for me, regardless of number of arrays.

What does your data look like?

--
---
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