calling a new function Excel gives me #NAME?

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

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

--
HTH

Bob Phillips

"Mark Dvorkin" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>

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


"Mark Dvorkin" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>

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.

In article <[email protected]>, Mark Dvorkin <[email protected]>
wrote:

>
> 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.

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

Mark Dvorkin wrote:

>
> 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
>