User-defined functions in Excel don't work

[Durkin]

I have written a function in excel vba to change months in the format "Jan", "Feb" etc. to numbers ie. 01, 02. This works fine except that when I try to use it in a cell it doesn't recognise it and returns #NAME?. When I wrote the function originally it worked fine and I can't think of anything I might have changed that could affect it. Any help would be appreciated. Durkin

 

[ide]

is your function a Public function?
or is it in the module of current sheet or in a separated module? You can`t see the code of an other worksheet..

 

[acron]

Unless you create a Function for a once of task in a single workbook, you should store them in Personal.xls, which in turn should be saved to your xlStart folder, so that it is always loaded on startup. It is normal to have it hidden (before you save it Select Windows, Hide).

You then shoud have all your functions available and they should be kisted in the function wizards User Defined category

AC

 

[Durkin]

Thanks for your quick responses. I have the code in the ThisWorkbook module. The code is definitely visible to the sheet as I have a sub written in the same module which I can run no problem. The strange thing is that if I run this sub and halt the code midway through it, I can then call the function from the debug window and it works fine. If I put the code in the module of the worksheet itself the exact same happens. Also, the function is declared as public. Durkin

 

[WilMead]

The drawback to Personal.XLS is it's personal.

Remember where the functions you depend on come from. For a personal implemetation of XLS it's seldom a problem. Imagine the embarasment when you give the sheet to someone who doesn't have your macros.

Wil Mead
wmead@optonline.net

 

[SkipVought]

Hi,
If your function is in the Workbook with the Worksheet you are in, insert a Module and Copy the fuction into the Module.

If it in another Workbook, then add that workbook's Project as a Reference in your Project -- in the VBE, Menu Item - Tools/References - then select the Project (Must have a unique name)

Hope this helps Skip,
metzgsk@voughtaircraft.com

 

[acron]

As far as I know, Functions in the ThisWorkbook or Worksheet code modules are not available as worksgheet functions, and can only be referenced outside the module (class) in which they reside by VBA, and then only by using ThisWorkbook.MyFunction(args) type syntax.

AC

 

[Durkin]

Thanks acron. The simple answers are always the best!B-) Durkin

 

[Rougy]

What a coincidence, I just had the same problem today and stumbled upon the answer.

Move your code from the "ThisWorkbook" module and create a seperate module within the same VBA project.

Works like a charm!

Rougy