User defined function

Y

Yugi Gokavarapu

Hello There,

When I write User Defined Function as below and use it, it returns
the output in the function argument dialouge box, but the out put is
not shown in the cell I wanted, instead I still see the input function
with arguments.

Function test(num)
Select Case num
Case Is > 0
test = "Positive"
Case Is = 0
test = "Zero"
Case Is < 0
test = "Negative"
End Select
End Function

Your help is appreciated!!
 
G

GS

Try:
Function test(num) As String
Select Case num
Case Is > 0
test = "Positive"
Case Is = 0
test = "Zero"
Case Is < 0
test = "Negative"
End Select
End Function

You must declare the 'data type' to be returned as shown.
 
G

GS

Yugi Gokavarapu formulated on Monday :
Sorry, I do not see any better

Sorry!!!
I forgot to include that you must declare your function with 'public'
scope...

Public Function test(num) As String
'...code
End Function
 
R

Ron Rosenfeld

Hello There,

When I write User Defined Function as below and use it, it returns
the output in the function argument dialouge box, but the out put is
not shown in the cell I wanted, instead I still see the input function
with arguments.

Function test(num)
Select Case num
Case Is > 0
test = "Positive"
Case Is = 0
test = "Zero"
Case Is < 0
test = "Negative"
End Select
End Function

Your help is appreciated!!

This can happen if you enter the UDF into a cell, subsequently change the cell format to Text; and then edit the cell.
 
R

Ron Rosenfeld

Hello There,

When I write User Defined Function as below and use it, it returns
the output in the function argument dialouge box, but the out put is
not shown in the cell I wanted, instead I still see the input function
with arguments.

Function test(num)
Select Case num
Case Is > 0
test = "Positive"
Case Is = 0
test = "Zero"
Case Is < 0
test = "Negative"
End Select
End Function

Your help is appreciated!!

Actually, this can also happen if you enter the formula, using the dialog box, while the cell is formatted as TEXT.

My initial response is a subset of the above.
 
R

Ron Rosenfeld

Try:
Function test(num) As String

You must declare the 'data type' to be returned as shown.

I'm guessing your recommendations are from your background in VB. And it may be that I'm incorrect in how this relates to the OP's problem, but:

With VBA, if you don't declare a return data type, it will be of type Variant and should be OK. So, although it is good practice, it is not necessary and I don't believe it is the reason for his problem.
Also, in VBA, if a Function is not declared as Private, it will be Public. It is usually not necessary to explicitly declare it as Public.
 
G

GS

Ron Rosenfeld laid this down on his screen :
I'm guessing your recommendations are from your background in VB. And it may
be that I'm incorrect in how this relates to the OP's problem, but:

With VBA, if you don't declare a return data type, it will be of type Variant
and should be OK. So, although it is good practice, it is not necessary and
I don't believe it is the reason for his problem. Also, in VBA, if a Function
is not declared as Private, it will be Public. It is usually not necessary
to explicitly declare it as Public.

Thanks, Ron. You're eval is correct since I tend to think in terms that
leave nothing to be ambiguous to the task at hand. I don't normal use
UDFs and so my lack of savvy!<g>
 
R

Ron Rosenfeld

Hello There,

When I write User Defined Function as below and use it, it returns
the output in the function argument dialouge box, but the out put is
not shown in the cell I wanted, instead I still see the input function
with arguments.

Function test(num)
Select Case num
Case Is > 0
test = "Positive"
Case Is = 0
test = "Zero"
Case Is < 0
test = "Negative"
End Select
End Function

Your help is appreciated!!

And if that is the problem, to correct it, FIRST change the cell format to General; THEN edit the cell. You don't need to change anything. Just put your cursor at the end of the formula in the function bar; or open the dialog box, and hit <enter>
 
Y

Yugi Gokavarapu

And if that is the problem, to correct it, FIRST change the cell format to General; THEN edit the cell.  You don't need to change anything.  Just put your cursor at the end of the formula in the function bar; or open the dialog box, and hit <enter>

Ron,

Thank you for your swing into this!

I have changed the cell type to General, and THEN edit the cell. I
see cell value as FALSE. Whereas I see the right output of function in
the function argument box.
Also, I noticed that when the menu Formulas -> Show formula option
enabled, I see the formula in the cell. When I disable to Show
Formula, I see FALSE.

Any suggestions?
 
R

Ron Rosenfeld

Ron,

Thank you for your swing into this!

I have changed the cell type to General, and THEN edit the cell. I
see cell value as FALSE. Whereas I see the right output of function in
the function argument box.
Also, I noticed that when the menu Formulas -> Show formula option
enabled, I see the formula in the cell. When I disable to Show
Formula, I see FALSE.

Any suggestions?

I don't see any method whereby the UDF you stated you were using initially can result in an output of FALSE, unless you have some unusual formatting in the cell.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top