VBA Function

[Tom]

I testing the function below from John
Walkenbach's "Excel 2002 Functions".

&&&&&&&&&&&&&
Function GreetMe()
Select Case Time
Case Is < 0.5
GreetMe = "Good Morning"
Case 0.5 To 0.75
GreetMe = "Good Afternoon"
Case Else
GreetMe = "Good Evening"
End Select
End Function
&&&&&&&&&&&&&

I added this function to VBAProject/Module1 (right click
the sheet's tab und click View Code).

In the worksheet, I then enter the following:
- Cell A1: 0.5
- Cell B1: =GreetMe(A1)

I did expect to see the value of "Good Afternoon" in cell
B1. However, it shows "#Name?". What am I doing wrong?

Thanks,
Tom

 

[Bob Umlas]

the GreetMe function doesn't take any arguments/parameters. It needs to be
entered as =Greetme(), not =Greetme(A1)

 

[JohnI in Brisbane]

Tom,

Function GreetMe(dtTime As Date)
Select Case dtTime
Case Is < 0.5
GreetMe = "Good Morning"
Case 0.5 To 0.75
GreetMe = "Good Afternoon"
Case Else
GreetMe = "Good Evening"
End Select
End Function

The function previously accepted no arguments. Try the above version & it
should do what you want.

Please post again to say how you went. It's good to know whether this
suggested solution worked or required more thought.



Regards,



JohnI

 

[Anders S]

Also, if you right-click the sheet tab, you open the worksheet code window, not
a code module. That's why you get the #Name? error. In the VBA editor, do
Insert>Module and paste the code.

 

[Daniel.M]

Hi Tom,

I added this function to VBAProject/Module1 (right click
the sheet's tab und click View Code).

I did expect to see the value of "Good Afternoon" in cell
B1. However, it shows "#Name?". What am I doing wrong?

1. Argument problem (don't need any). See previous answers.
2. You put the function in a sheet module. You need to put it in a standard
module.
In the VBA editor (Alt-F11), Menu Insert/Module. Put the code there.

Regards,

Daniel M.

 

[Tom]

Bob:

Thanks for the feedback. I removed the A1 in between
the parenthesis.

It still didn't work. However, if it would have, how
would it have known to cross-reference the entered value
in A1?

Tom

-----Original Message-----
the GreetMe function doesn't take any

arguments/parameters. It needs to be

 

[Daniel.M]

Dan:

It's Daniel.

Last try.

Right-clicking on a sheet tab /View code will bring you into a SHEET module.
Error message received (#NAME) is in accordance with this.

Follow my instructions. Cause if you did what YOU say you did : you did
wrong!
That's all I'm saying.

Regards,

Daniel M.

 

[Tom]

John:

Thanks for the feedback... that works great! Now, I
have only one follow-up questions.

If I modify the query (see below) and I want enter a text
value, did I select the proper data type (Test As String)?

Right now, this function does not work... I thought it
would be easy to modify it...

I appreciate if you have any additional pointers?

Tom



Function Tom(Test As String)
Select Case Test
Case Is = "Good Morning"
GreetMe = "1"
Case Is = "Good Afternoon"
GreetMe = "2"
Case Else
GreetMe = "3"
End Select
End Function

 

[JohnI in Brisbane]

Tom,

Function Tom(Test As String)
Select Case Test
Case Is = "Good Morning"
Tom = "1"
Case Is = "Good Afternoon"
Tom = "2"
Case Else
Tom = "3"
End Select
End Function

If you change the Function Name, you also have to change the return value
names to "Tom".

in any cell enter-

=tom("Good Morning")

and it returns 1.

Please post again to say how you went. It's good to know whether this
suggested solution worked or required more thought.



Regards,



JohnI

 

[Tom]

That's what I exactly did...

Please refer to the other thread from JohninBrisbane.
His suggested to include the (dtTime As Date) was the
solution.

Tom

 

[Tom]

Dan:

I had put include the (dtTime as Date)... please refer to
John in Brisbane's suggestion.

Anyhow, thanks for your feedback.
Tom

 

[Tom]

John:

This worked... but I can't believe that I don't get the
3rd one (number value... integer) not to work. I feel
bad to ask you each time I'm trying a different data type.

Tom



Function Tom(Testing As Integer)
Select Case Testing
Case Is = "Mike"
Tom = 1
Case Is = "Frank"
Tom = 2
Case Else
Tom = 3
End Select
End Function

 

[JohnI in Brisbane]

Tom,

The problem with this UDF is that the paramater is an integer, which will
not accept any other text.
Then, the Case statement only works on text, so never the twain shall meet.
You have to be consistent, either integers or text for the overall macro to
work.

regards,

JohnI

 

[Tom]

Got it!

Thanks for sharing this w/ me... I truly appreicate it.

Tom

 

[Tom Ogilvy]

The function as originally posted worked fine for me when entered into a
general module. No need to add an argument to get it to work.

Adding Application.Volatile will make it update which it won't otherwise.

Function GreetMe()
Application.Volatile
Select Case Time
Case Is < 0.5
GreetMe = "Good Morning"
Case 0.5 To 0.75
GreetMe = "Good Afternoon"
Case Else
GreetMe = "Good Evening"
End Select
End Function

 

[Tom Ogilvy]

You say right here in your original post:

I added this function to VBAProject/Module1 (right click
the sheet's tab und click View Code).

While you say Module1, when you do those actions, the active code module is
a sheet module. Not a general module.

If you really want to learn how to do this, you might pay attention to
Daniel.

Just because you got it to work, what about the next time, when you need a
function with no argument?