is there a myBug in my VB ?
 

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

is there a myBug in my VB ?
 

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

is there a myBug in my VB ?
 

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

is there a myBug in my VB ?
 

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

is there a myBug in my VB ?
 

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