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

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

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.

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.

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