An Interesting Question: How to Have Mutliple Variable Input

  • Thread starter Thread starter Randy Numbers
  • Start date Start date
R

Randy Numbers

Greetings!

I would like to define a user-function that takes a range (or Yes/No values)
as input, and returns a 1 if any of the values is yes.

I'm something like thinking...

FUNCTION YorN (choices)
yorn=0
counter=1 to length(choices)
yorn=yorn.OR.index(choices(counter)="YES")
Next counter
End Function

I know the syntax is wrong -- can someone help me out? Thanks.../Randy
 
Function yorn(a As Range)
Dim i As Long
yorn = 0
For i = 1 To a.Count
If a(i) = "yes" Then
yorn = 1
Exit Function
End If
Next i
End Function


--

Kind Regards,

Niek Otten

Microsoft MVP - Excel
 
Frank,

Thanks for the suggestion - looking for this as a prototype for other types
of functions too. Niek answer helped as well. ./RN
 
Nick

HELP! This almost does what I need... it works fine if the input is a range
like A5:A10; but I want to be able to slect discontiguous cells (A5, A9,
B3...) I get a value error when I try. Thanks./RN
 
Nick

HELP! This almost does what I need... it works fine if the input is a range
like A5:A10; but I want to be able to slect discontiguous cells (A5, A9,
B3...) I get a value error when I try. Thanks./RN
Click to expand...

Take a look at ParamArray, or at Optional (used in the Function statement).
One of them should do what you require.


--ron
 
Ron,

Thanks for the help but I can't seem to get it to work. I entered:



Function Yorn (byvalue paramArray Ans() as string)

and I get an error about ParamArray... in the header.

Thanks../RN
 
Function Yorn (byvalue paramArray Ans() as string)
Click to expand...

From HELP (with some emphasis by me to point out where you went wrong):

ParamArray

Optional.
Used only as the last argument in arglist to indicate that the final
argument is an Optional array of *VARIANT* elements. The ParamArray keyword
allows you to provide an arbitrary number of arguments. It may *NOT* be used
with ByVal, ByRef, or Optional.

So your UDF might read:

==============================
Function Yorn(ParamArray Ans())
Dim i As Long
Dim c As Range
Yorn = 0

For i = 0 To UBound(Ans)
For Each c In Ans(i)
If c.Text = "yes" Then
Yorn = 1
Exit Function
End If
Next c
Next i

End Function
============================


--ron
 
Back
Top