Home |
Search |
Today's Posts |
#1
|
|||
|
|||
About User Defined Functions
Gentle Men/Ladies: Thanks a lot for answering my question concerning my previous question of user defined function. Now I have another question with which I would like to have your kind help. I am trying to create an user defined function with a lot of variables. However, I don't want to do it like "UDF(V1,V2,V3,...)", since this will make the formula bar quite ugly. This is at all not elegant. Also, the number of variables is not fixed, it really depends on actual data I want to apply the operation. So, I want to write it like some internal functions and leave the number of variables open. For an example, the internal function Sum, the way we use it is simply to enter "=sum(A1:A1000)", which is very elegant and flexible in how many data points you want to sum. I am looking forward to hearing from you guys. Thanks -- linzhang426 ------------------------------------------------------------------------ linzhang426's Profile: http://www.excelforum.com/member.php...o&userid=27932 View this thread: http://www.excelforum.com/showthread...hreadid=474883 |
#2
|
|||
|
|||
These are not really the same thing. A variable number of arguments is not
the same as a range argument. If you really mean the latter, all you need to do is declare the argument as type range, and then iterate through the range in your function. As a (very silly) example Function mySum(rng As Range) Dim temp Dim cell As Range For Each cell In rng If IsNumeric(cell.Value) Then temp = temp + cell.Value End If Next cell mySum = temp End Function duplicates the Sum function, but it should show what I mean. You call like =mysum(A10:A12) If you really want a variable number of arguments, then somthing like Function mySum2(ParamArray inVal()) Dim temp Dim i As Long For i = LBound(inVal()) To UBound(inVal()) If IsNumeric(inVal(i)) Then temp = temp + inVal(i) End If Next i mySum2 = temp End Function You call this like =mysum2(A10,A12,G10) -- HTH Bob Phillips "linzhang426" wrote in message ... Gentle Men/Ladies: Thanks a lot for answering my question concerning my previous question of user defined function. Now I have another question with which I would like to have your kind help. I am trying to create an user defined function with a lot of variables. However, I don't want to do it like "UDF(V1,V2,V3,...)", since this will make the formula bar quite ugly. This is at all not elegant. Also, the number of variables is not fixed, it really depends on actual data I want to apply the operation. So, I want to write it like some internal functions and leave the number of variables open. For an example, the internal function Sum, the way we use it is simply to enter "=sum(A1:A1000)", which is very elegant and flexible in how many data points you want to sum. I am looking forward to hearing from you guys. Thanks -- linzhang426 ------------------------------------------------------------------------ linzhang426's Profile: http://www.excelforum.com/member.php...o&userid=27932 View this thread: http://www.excelforum.com/showthread...hreadid=474883 |
#3
|
|||
|
|||
Bob Phillips wrote...
.... If you really want a variable number of arguments, then somthing like Function mySum2(ParamArray inVal()) Dim temp Dim i As Long For i = LBound(inVal()) To UBound(inVal()) If IsNumeric(inVal(i)) Then temp = temp + inVal(i) End If Next i mySum2 = temp End Function .... Since when can variable numbers of arguments only consist of scalars? If you're going to do this right, accept variable numbers of scalar, range and array arguments. Function mysum(ParamArray a()) As Double Dim x AS Variant, Y As Variant On Error Resume Next For Each x In a If TypeOf x Is Range Then For Each y In x.Cells mysum = mysum + CDbl(y.Value) Next y ElseIf IsArray(x) Then For Each y In x mysum = mysum + IIf(IsArray(y), mysum(y), CDbl(y)) Next y Else mysum = mysum + CDbl(x) End If Next x End Function Doing the latter right eliminates the need for the former. |
#4
|
|||
|
|||
It was an example Harlan, one step at a time.
Bob "Harlan Grove" wrote in message oups.com... Bob Phillips wrote... ... If you really want a variable number of arguments, then somthing like Function mySum2(ParamArray inVal()) Dim temp Dim i As Long For i = LBound(inVal()) To UBound(inVal()) If IsNumeric(inVal(i)) Then temp = temp + inVal(i) End If Next i mySum2 = temp End Function ... Since when can variable numbers of arguments only consist of scalars? If you're going to do this right, accept variable numbers of scalar, range and array arguments. Function mysum(ParamArray a()) As Double Dim x AS Variant, Y As Variant On Error Resume Next For Each x In a If TypeOf x Is Range Then For Each y In x.Cells mysum = mysum + CDbl(y.Value) Next y ElseIf IsArray(x) Then For Each y In x mysum = mysum + IIf(IsArray(y), mysum(y), CDbl(y)) Next y Else mysum = mysum + CDbl(x) End If Next x End Function Doing the latter right eliminates the need for the former. |
#5
|
|||
|
|||
About User Defined Functions
Thanks a lot, Bob and Harlan. The code provided by Harlan is very elegant. I like it very much. However, I got a hard time to fully understand it. I wonder if there is a place for me to get some helps. -- linzhang426 ------------------------------------------------------------------------ linzhang426's Profile: http://www.excelforum.com/member.php...o&userid=27932 View this thread: http://www.excelforum.com/showthread...hreadid=474883 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I have had difficulty in creating user defined functions in Excel | Excel Worksheet Functions | |||
Default User Defined Functions - How? | Excel Discussion (Misc queries) | |||
User Defined Functions | Excel Worksheet Functions | |||
User Defined Functions | Excel Worksheet Functions | |||
I have user defined functions in sprdsheet. I want to put sprdsh. | Excel Worksheet Functions |