Where should the code be stored? (Excel97)
 

Pardon the rather 'green' user here...I am trying to understand how
Excel finds user written VBA code.

I am defining functions in Excel 97.

When I open the VBE and write code, it is automatically stored in the
'Sheet1' object. When I reference the defined function (on Sheet1), I
get a #NAME? error in the cells. It appears that the code is not
found.

When I copy the code in the 'Module1' object, the code works.

Why doesn't Excel find this code in the Sheet1? Should I store all
code in the Module1 object? Why didn't Excel do this automatically for
me?

thanks for your patience and assistance,
TomCee

Where should the code be stored? (Excel97)
 

All your function should be placed in Module1 or any module that
you create from the Insert menu in VBA.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"tomcee" wrote in message
oups.com...
Pardon the rather 'green' user here...I am trying to understand
how
Excel finds user written VBA code.

I am defining functions in Excel 97.

When I open the VBE and write code, it is automatically stored
in the
'Sheet1' object. When I reference the defined function (on
Sheet1), I
get a #NAME? error in the cells. It appears that the code is
not
found.

When I copy the code in the 'Module1' object, the code works.

Why doesn't Excel find this code in the Sheet1? Should I store
all
code in the Module1 object? Why didn't Excel do this
automatically for
me?

thanks for your patience and assistance,
TomCee

Where should the code be stored? (Excel97)
 

Thanks for the prompt reply.

I am curious though: What is the usage for code that is defined on
'Sheet1'?

Thanks again,
TomCee

Where should the code be stored? (Excel97)
 

The only code that you should put in the Sheet1 modue are event
procedures for that sheet. In the top of the code window, you
should see two dropdown boxes. In the one on the left, choose
Worksheet. In the one on the right, choose the event you want to
insert into the code module. VBA will automatically insert the
Sub and End Sub lines of code.


"tomcee" wrote in message
ups.com...
Thanks for the prompt reply.

I am curious though: What is the usage for code that is
defined on
'Sheet1'?

Thanks again,
TomCee

Where should the code be stored? (Excel97)
 

Chip:

Thanks again for the prompt, helpful replies.
TomCee.