EXCEL VBA User Defined Function - why do I get the error "function not defined" while I use it in the Workbook where it is defined?

Question

In the same Workbook, I have defined the function:

Public Function toto(val As Integer) As Integer
    toto = val * 2
End Function

Here is the editor view:

And in the Worksheet, in a cell, I use the formula:

But the result is (translation: #NAME?):

Also, in the VBA exec Window itself, opened from Excel, I get the following error when trying to execute:

toto(2)

Translation : Compilation error: Sub or Function not defined

What do I miss?

Where did you put the code? It need to be in a public module. — Vincent G
– Vincent G, Commented Mar 7, 2019 at 14:55
FWIW the Val parameter is shadowing the VBA.Conversion.Val function and should probably be renamed. — Mathieu Guindon
– Mathieu Guindon, Commented Mar 7, 2019 at 15:16

Harvey Ellis · Accepted Answer · 2019-03-07 15:01:18Z

3

Expanding on what @Jo.lass and @vincent-g said, you need to have your code in a module, not in worksheet or workbook code. See here:

answered Mar 7, 2019 at 15:01

Harvey Ellis

6061 gold badge4 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Harvey Ellis Over a year ago

Have you tried just putting it in a module by itself? Also have you called the module the same thing? The other instance where you can get the #NAME error is when there is a name conflict.

Mathieu Guindon Over a year ago

@lalebarde no, that's not what you have. Per your screenshot, your code is in the code-behind of Feuil5, not in a standard module.

lalebarde Over a year ago

Sorry, I missed the left part "Module 1". Now it works !

JLCH · Accepted Answer · 2019-03-07 14:58:05Z

2

Instead of writing the function in a worksheet in VBE, try writing it in a new module. Works for me at least.

answered Mar 7, 2019 at 14:58

JLCH

8031 gold badge10 silver badges15 bronze badges

Collectives™ on Stack Overflow

EXCEL VBA User Defined Function - why do I get the error "function not defined" while I use it in the Workbook where it is defined?

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related