Select Case As A Function

[Kevsim]

Is there any way of writing a Function for “Select Case”.
I would appreciate your advise

kevsim

 

[fumei]

Not sure what you mean. You certainly can use Select Case within a Function. Can you be more specific about what you are asking?

Gerry
My paintings and sculpture

 

[Kevsim]

fumei,
Thanks for the reply.
I wanted it as a function so when I changed a value in a cell it would instantly update without pressing any macro keys or pressing any buttons to update.
I finally have it working, I wrote it in VB as a function.
kevsim

 

[fumei]

If you find a solution to something you post, it is polite to also post what you found as a solution.
[quoyte]I wanted it as a function so when I changed a value in a cell it would instantly update without pressing any macro keys or pressing any buttons to update.
[/quote]This could be using a Change event, and could be a Sub as well as a Funtion...so you did not really answer the question. Please post what you did to make it work for you.

Thanks.

Gerry
My paintings and sculpture

 

[Kevsim]

fumei,
Sorry I did not provide the solution, it is as follows -

Cell function
=MotorSize(A1)

Code
Function MotorSize(T)

Select Case T
Case 1 To 12
MotorSize = "14 KW"
Case 12.001 To 16
MotorSize = "18 KW"
Case 16.001 To 20
MotorSize = "22 KW"
Case 20.001 To 24
MotorSize = "26 KW"

Case Else
MotorSize = "Motor size out of range"
End Select

End Function

 

[firefytr]

Declare your T as a Double ...

Function MotorSize(T as Double)

-----------
Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a Microsoft MVP?
- Leonardo da Vinci

 

[fumei]

Functions are normally used to return a single value, and that value is usually defined.

Function MotorSize(T As Double) As String

as the returned value is a string.



Gerry
My paintings and sculpture

 

[PHV]

And often used in an UDF:
Application.Volatile

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[fumei]

Care to expand on that PH? Oh....never mind. One of those.....Excel things. Interesting. If I understand this correctly, the Function would fire if anything changes in any cell????

Gerry
My paintings and sculpture

 

[fumei]

Just for perspective...PHV has received (so far) 5,314 stars.

Over FIVE THOUSAND.....

Now that is.....is......is.....

a) a very busy person
b) a rather obsessive-compulsive helper

I am in awe. Do you really like to help people that much, or do you just like to type?

You must live here. <grin>

Gerry
My paintings and sculpture

 

[PHV]

Just for perspective
The funnest thing (IMHO) is that I'm NOT a windows guy ...
I just love to play with the F1 and F2 keys.

 

[fumei]

oooh, oooh, my sides are hurting. That just cracked me up laughing.

That reminded me of when I used to beta test for our in-house applications guys. (Ages ago....MicroFocus Cobol....ahem....)

They used to make all these back door exits to applications. It would really p^&s them off when I found them.

"No one would ever press Alt-G-Shift-F7!!!"

"I did."

One of the first things I would do with new applications is run through all possible (up to) six key press combinations. Took me all day. Then I would just lean my elbow on the keyboard.....

Amazing how many "secret" combinations I found.

I like to press keys as well, but maybe being a sort of flakey artist I am a bit more random about it.

Gerry
My paintings and sculpture