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:
23115

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

23116

[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/office/vba/language/reference/user-interface-help/option-private-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