UDF with text as input without quotations?

[mattchew]

Hi guys,

I'm a beginner at vba and trying to create an UDF that involves text as input.


Function testing(input)
If input = "T" then
testing = 1
Elseif input = "F" then
testing = 0
Else
testing = 2
End if
End function

So the thing is everytime I'll have to type testing("T") or testing("F") to get the formula to work.. but is there anyway to make it work without typing in "quotations"??

Thanks guys~

Cheers,
Matt

[GTO]

Hi Matt,

At least for me, using "input" fouls straightaway. By shady memory, I believe this may be a keyword for i/o.

Anyways, if your are writing a UDF, shouldn't you be getting the input value from another cell? Maybe like:


Function testing(ByVal Cell As Range)
If Cell = "T" Then
testing = 1
ElseIf Cell = "F" Then
testing = 0
Else
testing = 2
End If
End Function

where the udf would be referred to / called on the sheet like:

=testing(D2)

[shrivallabha]

Change the declaration part:
From

Function testing(input)

to

Function testing(sInput As String)

[mattchew]

Hey guys,

thanks for the quick replies!

but Function testing(sinput as String) doesn't seem to work..
when I typed text into the UDF without quotations, excel just gives me #VALUE!

any other suggestions?

Cheers,
Matt

[mikerickson]

What do you mean "typing text into the UDF"?

If you enter =testing(someString) into a cell, Excel will look for the named value someString and pass that to the UDF as its argument

If you want someString to be passed, you must type =testing("someString") with the quotes to tell excel that someString is a literal string.

Alternatly, entering the formula =testing(A1) in a cell will allow you to type someString (no quotes) into A1 and have the result returned to the cell with the formula.

[Paul_Hossler]

but is there anyway to make it work without typing in "quotations"??


No

The UDF is just like any other worksheet function: text data needs the " " around it when you have =Testing("T") in a cell


If cell A1 has a T in it, then =Testing(A1) works because Excel know that T is a piece of text

When you have =Testing(T) in a cell it really gets confusing because T() is also a worksheet function

Other that having to find the " key, is there another problem?

Paul

[mattchew]

Hi Paul,

Thanks for the reply. I actually used M and F in the UDF, which I dun think are worksheet functions.

So using quotations for text in UDF is a must??

Matt

[Paul_Hossler]

'fraid so :yes

I don't know anyother way to handle text input

Paul

[Aflatoon]

So using quotations for text in UDF is a must??


Yes, as Paul said, quotations are required for text input to any function.