Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Optional Arguments in Custom Fuctions
I am trying to build a custom function and could use some advice. There is
more to it than this, but at the core what I am trying to do is a custom sum function that lets you put any number of cell references in the formula. This is a very simplified version, but if I can get this to work, then I can get the rest to work. Thanks in advance Function MySum(Range1, Optional Range2, Optional Range3, Optional Range4) For X = 1 to 4 if ismissing(RangeX) then Exit Function Else MySum = MySum + Cells(RangeX.Row, RangeX.Column) Next X |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Optional Arguments in Custom Fuctions
You need to use a paremarray:
Public Function MySum(ParamArray v() As Variant) Dim sum As Double For i = LBound(v) To UBound(v) Select Case TypeName(v(i)) Case "Range" Set r = v(i) For Each Cell In r If IsNumeric(Cell) Then sum = sum + Cell End If Next Case "Variant()" For j = LBound(v(i)) To UBound(v(i)) If IsNumeric(v(i)(j)) Then sum = sum + v(i)(j) End If Next Case "Integer", "Long", "Double", "Single" sum = sum + v(i) Case Else End Select Next i MySum = sum End Function Usage examples: [from the immediate window in the VBE:] ? mysum(1,"A",2) 3 ? mysum(range("A1:A10"),1,"B",Array("A",1,2,3,"Z"),R ange("A11"),10) 83 from the worksheet =mysum(A1:A10,{1,2,3,4,5},A11) returned 81 -- Regards, Tom Ogilvy "eggman" wrote: I am trying to build a custom function and could use some advice. There is more to it than this, but at the core what I am trying to do is a custom sum function that lets you put any number of cell references in the formula. This is a very simplified version, but if I can get this to work, then I can get the rest to work. Thanks in advance Function MySum(Range1, Optional Range2, Optional Range3, Optional Range4) For X = 1 to 4 if ismissing(RangeX) then Exit Function Else MySum = MySum + Cells(RangeX.Row, RangeX.Column) Next X |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Optional Arguments in Custom Fuctions
From
http://www.cpearson.com/excel/Writin...ionsInVBA.aspx Variant ParamArray The second method for working with optional parameters is to use a ParamArray Variant parameter. A ParamArray allows any number of parameters, including none at all, to be passed to the function. You can have one or more required parameters before the ParamArray, but you cannot have any optional parameters if you have a ParamArray. Moreover, the ParamArray variable must be the last parameter declared for a function. The ParamArray variables must be Variant types. You cannot have a ParamArray of other types, such as Long integers. If necessary, you should validate the values passed in the ParamArray, such as to ensure they are all numeric. If your function requires one or more inputs followed by a variable number of parameters, declare the required parameters explicitly and use a ParamArray only for the optional parameters. For example, the function SumOf below accepts any number of inputs and simply adds them up: Function SumOf(ParamArray Nums() As Variant) As Variant '''''''''''''''''''''''''''''''''' ' Add up the numbers in Nums '''''''''''''''''''''''''''''''''' Dim N As Long Dim D As Double For N = LBound(Nums) To UBound(Nums) If IsNumeric(Nums(N)) = True Then D = D + Nums(N) Else SumOf = CVErr(xlErrNum) Exit Function End If Next N SumOf = D End Function In your function code, you can use: Dim NumParams As Long NumParams = UBound(Nums) - LBound(Nums) + 1 to determine how many parameters were passed in the ParamArray variable Nums. This will be 0 if no parameters were passed as the ParamArray. Of course, the code above counts the number of parameters within the ParamArray, not the total number of parameters to the function. -- HTH, Bernie MS Excel MVP "eggman" wrote in message ... I am trying to build a custom function and could use some advice. There is more to it than this, but at the core what I am trying to do is a custom sum function that lets you put any number of cell references in the formula. This is a very simplified version, but if I can get this to work, then I can get the rest to work. Thanks in advance Function MySum(Range1, Optional Range2, Optional Range3, Optional Range4) For X = 1 to 4 if ismissing(RangeX) then Exit Function Else MySum = MySum + Cells(RangeX.Row, RangeX.Column) Next X |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Optional Arguments in Custom Fuctions
Perhaps you should be using a ParamArray for the optional arguments. A
ParamArray accepts any number of arguments (including none) and these must be Variant data types. Function MySum(Range1 As Range, ParamArray Args() As Variant) Dim N As Long Dim NumArgs As Long NumArgs = UBound(Args) - LBound(Args) For N = LBound(Args) To UBound(Args) ' do something Next N End Function -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting www.cpearson.com (email on the web site) "eggman" wrote in message ... I am trying to build a custom function and could use some advice. There is more to it than this, but at the core what I am trying to do is a custom sum function that lets you put any number of cell references in the formula. This is a very simplified version, but if I can get this to work, then I can get the rest to work. Thanks in advance Function MySum(Range1, Optional Range2, Optional Range3, Optional Range4) For X = 1 to 4 if ismissing(RangeX) then Exit Function Else MySum = MySum + Cells(RangeX.Row, RangeX.Column) Next X |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Optional Arguments in Custom Fuctions
Perhaps you should be using a ParamArray for the optional arguments. A
ParamArray accepts any number of arguments (including none) and these must be Variant data types. Function MySum(Range1 As Range, ParamArray Args() As Variant) Dim N As Long Dim NumArgs As Long NumArgs = UBound(Args) - LBound(Args) You don't make use of it in your code, but you accidentally left off the +1... NumArgs = UBound(Args) - LBound(Args) + 1 Rick For N = LBound(Args) To UBound(Args) ' do something Next N End Function -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting www.cpearson.com (email on the web site) "eggman" wrote in message ... I am trying to build a custom function and could use some advice. There is more to it than this, but at the core what I am trying to do is a custom sum function that lets you put any number of cell references in the formula. This is a very simplified version, but if I can get this to work, then I can get the rest to work. Thanks in advance Function MySum(Range1, Optional Range2, Optional Range3, Optional Range4) For X = 1 to 4 if ismissing(RangeX) then Exit Function Else MySum = MySum + Cells(RangeX.Row, RangeX.Column) Next X |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Optional Arguments in Custom Fuctions
Thanks all - much appreciated
"Rick Rothstein (MVP - VB)" wrote: Perhaps you should be using a ParamArray for the optional arguments. A ParamArray accepts any number of arguments (including none) and these must be Variant data types. Function MySum(Range1 As Range, ParamArray Args() As Variant) Dim N As Long Dim NumArgs As Long NumArgs = UBound(Args) - LBound(Args) You don't make use of it in your code, but you accidentally left off the +1... NumArgs = UBound(Args) - LBound(Args) + 1 Rick For N = LBound(Args) To UBound(Args) ' do something Next N End Function -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting www.cpearson.com (email on the web site) "eggman" wrote in message ... I am trying to build a custom function and could use some advice. There is more to it than this, but at the core what I am trying to do is a custom sum function that lets you put any number of cell references in the formula. This is a very simplified version, but if I can get this to work, then I can get the rest to work. Thanks in advance Function MySum(Range1, Optional Range2, Optional Range3, Optional Range4) For X = 1 to 4 if ismissing(RangeX) then Exit Function Else MySum = MySum + Cells(RangeX.Row, RangeX.Column) Next X |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Optional arguments and IsMissing | Excel Programming | |||
Passing Constant Arguments to custom Subroutine & Functions | Excel Programming | |||
Custom functions calculating time arguments Help Desperate | Excel Worksheet Functions | |||
Representation of optional parameters in Function arguments window | Excel Programming | |||
Custom functions using arguments with same name | Excel Programming |