PDA

View Full Version : [SOLVED] User Defined Function vba



Edmond
11-01-2018, 09:52 AM
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
11-01-2018, 10:00 AM
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
11-01-2018, 10:07 AM
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
11-01-2018, 10:13 AM
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
11-01-2018, 10:33 AM
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