fonctions personnalisées provoquent une erreur #NOM?

Copper Contributor

Bonjour, mes fonctions personnalisées provoquent une erreur #NOM? dans la cellule.

Mes fonctions donnent un résultat correct lors d'un appel depuis un SUB en VBA.

Mes fonctions n'apparaissent pas dans la liste des fonctions Excel, ni dans les suggestions (autocomplete) lors de la saisie.

J'ai essayé le paramétrage de sécurité, l'utilisation d'un alias par le gestionnaire de nom (exemple des fonctions LAMBDA).

Pas de rubrique "Personnalisées" dans la Boite de dialogue Fonctions accessible depuis la Feuille de calcul.

9 Replies

@Jeanga 

Maybe I'm not the most appropriate to give you a suggested solution, but ...

With your permission, if I can recommend you, explain your problem in detail. So you can get a solution proposal to your problem much faster.

At the same time, it is much easier for someone who wants to help to understand the subject.

A win-win situation for everyone.

It is also helpful to know the operating system and Excel version, as different approaches may be required depending on the Office version and OS.

 

Thank you for your understanding and patience

 

Nikolino

I know I don't know anything (Socrates)

Well, how to say it in another way ?
I create a function in VBA, I use it by its name in a cell, Excel reply #NAME?
Excel 2019/W10

@Jeanga 

 

Is the function in a standard module?

 

JMB17_0-1617475393516.png

 

It is not a SUB that is needed. You need to declare the procedure with FUNCTION and return the result using the function name.
sorry if I'm not clear enough.
"I create a FUNCTION in VBA, I use it by its name in a cell, Excel reply #NAME?" (bis)
When I use it in a Sub it works, but of course I create a function.

I use VBA since 1998 in Word and Excel, even it was called with an other name.
I use to program Function, (with Application.Volatile method, I'm not a rookie).
I think I've got here a bad parameters meaning but I can't put the finger on.

Is there a way to have a MICROSOFT technicien online to verify, quickly, the way I can retrieve my complete Excel pleasure ?
Sorry, I misread your original post where it mentioned the SUB. Is the problem with a specific function or can't you define UDFs at all?
don't be sorry, no issue :)
It do the same with every customized functions.
When you write the equal sign (=) in a cell and a letter, Excel propose you a list of functions that begin with this letter. ie =s -> SEC, SECH, SECONDE, and so on.
Mine doesn't appear in this list.
If I bind my function with an alias (like in the LAMBDA example) by the Name Manager, the Alias appear in the list but it still generat a #NAME? error in the cell.
I defined the folder where my Workbook is stored as trust folder in the Excel parameters, it doesn't change anything.
I think the way for me to fix it is in the parameters but I didn't find it.

Sorry to be angry yesterday, for me computers HAVE TO work, and I have no patience for this meanings :(

@everyone

I found the way to use my customized Functions in my Sheet.

Simply declare the functions in a CLASS MODULE, not in the Workbook or Worksheets code modules.

 

Thanks to those who tried to help me.

If one (or more) of you gave me this trick, please apologize. I didn't understand your meaning.

I don't remember I had to declare my functions in a Class Module before.

Well, it works, I love to code.

Thanks to all :)

@Jeanga 

Thank you for the update, though  I have never had to use a Class module for the purpose of creating a UDF.  I notice an earlier post from @JMB17 that asked whether you were using a standard module.  It appears that he was at least on the right lines.