Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Mark Dvorkin
 
Posts: n/a
Default 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

  #2   Report Post  
Bob Phillips
 
Posts: n/a
Default

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

--
HTH

Bob Phillips

"Mark Dvorkin" wrote in message
...

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



  #3   Report Post  
KL
 
Posts: n/a
Default

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" wrote in message
...

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



  #4   Report Post  
JE McGimpsey
 
Posts: n/a
Default

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

  #5   Report Post  
Mark Dvorkin
 
Posts: n/a
Default

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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel 2003 Slow Function Argument Window [email protected] Excel Discussion (Misc queries) 2 June 28th 05 06:53 PM
Excel option to store trendline's coefficients in cells for use Miguel Saldana Charts and Charting in Excel 9 June 20th 05 08:45 PM
Simplify Vlookup function in Excel Budman Excel Worksheet Functions 7 March 27th 05 04:17 PM
I cant use englisch function names in a swedich version of excel PE Excel Discussion (Misc queries) 2 December 7th 04 02:00 AM
Missing function in Excel 2003 Galldrian Excel Discussion (Misc queries) 2 November 30th 04 01:34 PM


All times are GMT +1. The time now is 10:46 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"