calling a new function Excel gives me #NAME?

[Mark Dvorkin]

I wrote a simple function using VBA Editor.

Function prevDay(Ref)
Application.Volatile
prevDay = Sheets(Application.Caller.Parent.Index - 1).Range(Ref.Address)
End Function

When I call it Excel gives me #NAME? error.

Do I need to register it somehow?

sorry for a bloody beginner question
and thanks in advance for any help.

/mark

 

[Bob Phillips]

Did you put the code in a standard code module of the same workbook.

 

[KL]

Hi Mark,

Make sure you place your code in a standard module (e.g. Module1) and not in
class module (e.g. ThisWorkbook, Sheet1, UserForm1, etc.)

Regards,
KL

 

[JE McGimpsey]

Where is your function stored?

To call it with just its name, it should be in a regular code module in
your workbook (in the VBE, Insert/Module), rather than in the
ThisWorkbook or a worksheet module.

If you want to leave it in ThisWorkbook or a sheet module (and there's
no reason to), you'll have to qualify the location of the function:

=ThisWorkbook.prevDay(A1)


If it's in a regular code module in a different workbook (like the
Personal.xls workbook), you'll need to add the workbook name:

=Personal.xls!prevDay(A1)

Alternatively, you could create an add-in, with the code in a regular
code module, and you can use the name just as if it were in your
workbook.

 

[Mark Dvorkin]

thanks to all of you.
Indeed I placed the function code into class module ThisWorkbook.
Once I inserted Module1 and placed it there everything works fine.

Thanks again for your help and patience.

/mark