Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simple VBA Accumulator (Function?)
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simple VBA Accumulator (Function?)
Does this simple macro help. If not, details.
Sub addto() var1 = 2 var2 = 3 MsgBox var1 + var2 '5 End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simple VBA Accumulator (Function?)
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 " wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simple VBA Accumulator (Function?)
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) On Mon, 7 Sep 2009 05:03:54 -0700 (PDT), " wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simple VBA Accumulator (Function?)
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) On Mon, 7 Sep 2009 05:03:54 -0700 (PDT), " wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Accumulator | Excel Worksheet Functions | |||
Simple problem, simple formula, no FUNCTION ! | Excel Worksheet Functions | |||
accumulator with other than one | Excel Worksheet Functions | |||
Please help fix my accumulator | Excel Programming | |||
Please help me get this accumulator working | Excel Programming |