Simple VBA Accumulator (Function?)

[jnez367]

Can a function get repeated values, add them, and return a total to
the calling procedure?
I need to reuse this code with differing numbers of items.

Example

Pass these parameters

add_to(1)
add_to(2)

Get the returned total from function

function accum(number)
total = total + number

RETURN TOTAL to Procedure

 

[Don Guillett]

Does this simple macro help. If not, details.

Sub addto()
var1 = 2
var2 = 3
MsgBox var1 + var2 '5
End Sub

 

[Patrick Molloy]

not sure what you mean by "repeated values"
you can pass a variable list of parameters if thats what you mean?

or use a ParamArray in your parameter list

 

[Chip Pearson]

I'm not sure what you are really looking for, but perhaps the
following might get you started:

Function AddThemUp(ParamArray Args() As Variant) As Double
Dim N As Long
Dim L As Long
For N = LBound(Args) To UBound(Args)
If IsNumeric(Args(N)) = True Then
L = L + Args(N)
End If
Next N
AddThemUp = L
End Function


This function takes any number of input parameters and returns the sum
of those parameters. E.g,.,

Dim X As Long
X = AddThemUp(10,20,30)
Debug.Print X

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[Chip Pearson]

On further reflection, I might know what you're looking for. You might
want declare a module-level variable and use that as your accumulator.
In the code module, before and outside of any procedure, enter

Dim X As Long

Any code in any procedure within this code module can read and change
the value stored in X. The value of X will remain even when a Sub or
Function ends.

So, your code could do something like

Dim X As Long
Sub ABC()
X = 1234
End Sub

Sub DEF()
X = X + 1
End Sub

Here, ABC() sets X to an initial value, 1234, which will be retained
even after ABC terminates. Then, DEF() adds 1 to X and X will retain
that new value after DEF terminates.

If you really do what an Increment function, you could use

Function IncrementX(Optional N As Long = 1) As Long
X = X + N
IncrementX = X
End Function

If N is omitted, X is increment by 1. If N is specified, X gets the
new value X+N. In either case, the function returns the new value of
X.

Using a variable that is declared outside of a procedure is a matter
of what is called the "scope" of the variable. See
www.cpearson.com/Excel/Scope.aspx for more information about scoping
variables and procedures.


Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)