Cumulative Sum

  • Thread starter Thread starter Alimbilo
  • Start date Start date
A

Alimbilo

Why am I getting a "Sub or Function not deifned" error when I am entering
this code:

PrivAte SuA D_Enter()
If B = 0 And C = 0 And A = 0 Then
D = 0
Else
If B = 0 And C = 0 Then
D = A
Else
If B = 0 And A = 0 Then
D = C
Else
If C = 0 And A = 0 Then
D = B
Else
If B = 0 Then
D = Sum(C * A)
Else
If C = 0 Then
D = Sum(B * A)
Else
If A = 0 Then
D = Sum(B * C)
Else
If B > 0 And C > 0 And A > 0 Then
D = Sum(B * C * A)
End If
End If
End If
End If
End If
End If
End If
End If
End Sub

The error highlight the value in the Sum(...)

Any Help?
 
is what is on the first line a typo? i think try Sub with a "b"...

or you can use Function as well..
 
Yes is a typo:

PrivAte Sub D_Enter()
If B = 0 And C = 0 And A = 0 Then
D = 0
Else
If B = 0 And C = 0 Then
D = A
Else
If B = 0 And A = 0 Then
D = C
Else
If C = 0 And A = 0 Then
D = B
Else
If B = 0 Then
D = Sum(C * A)
Else
If C = 0 Then
D = Sum(B * A)
Else
If A = 0 Then
D = Sum(B * C)
Else
If B > 0 And C > 0 And A > 0 Then
D = Sum(B * C * A)
End If
End If
End If
End If
End If
End If
End If
End If
End Sub

When I try with a function, how the value will change in the form. Do I need
any Event procedure?
 
should work the same in either a sub or a function. Create a module then add
the function then you can execute in code:

varValue = D_Enter(a,b,c,d)
 
Why am I getting a "Sub or Function not deifned" error when I am entering
this code:
Click to expand...

Because there is no VBA function named Sum.

If you want D to be the sum of A, B, and C, you need *one line of code*:

D = A + B + C

The asterisk operator *multiplies* values (i.e, 3*6 = 18).

You're making your job vastly harder than it needs to be, or else you're not
clearly explaining what you are trying to accomplish! As I've asked a couple
of times: what is the context? Where are these A, B and C coming from? Is this
code on a Form, or what?
 
John W. Vinson said:
Because there is no VBA function named Sum.

If you want D to be the sum of A, B, and C, you need *one line of code*:

D = A + B + C

The asterisk operator *multiplies* values (i.e, 3*6 = 18).

You're making your job vastly harder than it needs to be, or else you're not
clearly explaining what you are trying to accomplish! As I've asked a couple
of times: what is the context? Where are these A, B and C coming from? Is this
code on a Form, or what?
Click to expand...

A= 20%
B= 20
C = 80
D = 50%
E = 50
F = 50
G = 0
H = 0
I = 0%
R = Sum(A*D*I)

R = should be 10% (cumulative of A*D)...

My question is how can I have the formula not taking the 0?
Because with a 0, the answer will be 0% .
 
A= 20%
B= 20
C = 80
D = 50%
E = 50
F = 50
G = 0
H = 0
I = 0%
R = Sum(A*D*I)

R = should be 10% (cumulative of A*D)...

My question is how can I have the formula not taking the 0?
Because with a 0, the answer will be 0% .
Click to expand...

Again: I'll be glad to try to answer your question if I CAN UNDERSTAND IT.
Right now I cannot.

Please:

As I've asked a couple of times: what is the context? Where are these A, B
and C coming from? Is this code on a Form, or what? What does the word
"cumulative" mean to you? And what is it that you are trying to sum? "Sum" to
me means to add several values. It does not appear that you are adding
anything; are you trying to calculate a running PRODUCT?

If so try

IIF(A = 0, 1, A) * IIF(B = 0, 1, B) * IIF(C = 0, 1, C) * <etc>

This will calculate .2 * .2 * .8 * .5 * .5 * .5 * 1 * 1 * 1 = .004.

Or perhaps you need a customized function:

Public Function RunningProduct(dblIn() As Double) As Double
Dim iPos As Integer
RunningProduct = 1.0
For iPos = 0 to UBound(dblIn)-1
If dblIn(iPos) <> 0 Then
RunningProduct = RunningProduct * dblIn(iPos)
Next iPos

Call it using RunningProduct(A, D, I).
 
Back
Top