User Defined Function vba

[Edmond]

Hello Everyone,

I am trying to define a Function on excel depending on the variable x.

I wrote this code:

Function F(x As Double) As Double



Select Case x
Case Is < 0: F = -Sin(x)
Case 0 To 2: F = 2 * x
Case 2 To 4: F = x ^ 2
Case Else: F = 16
End Select
End Function

But the only results I get are those ones:
Capture.PNG

Would you have some idea on what I did wrong?

Thanks in advance!

Edmond

[Paul_Hossler]

Works for me

Make sure the function is in a Standard Module which is not Option Private Module, and that the function is not Private

Capture.JPG

[Kenneth Hobs]

Be sure to check the limits.

Function F(x As Double) As Double  
  Select Case True
    Case x < 0: F = -Sin(x)
    Case x >= 0 And x <= 2: F = 2 * x
    Case x > 2 And x <= 4: F = x ^ 2
    Case Else: F = 16
  End Select
End Function

[Edmond]

Thank you both for your answer.

Paul, I just googled "Option Private Module" but I didn't find any explanation how to activate it or not. I mean without using code.
Could you explain how you did to make it works?

Thank you!

[Paul_Hossler]

To use a UDF as a WS function, you do NOT want "Option Private Module" and you do NOT want "Private Function F()…"

https://docs.microsoft.com/en-us/off...vate-statement

It's a statement in the module, and not really controlled by any user code


Since you were getting #NAME errors, my GUESS was that you might not have F() in a Standard Module, like my screen shot and attachment

If that's not it, attach the troublesome workbook