Where should the code be stored? (Excel97)

  • Thread starter Thread starter tomcee
  • Start date Start date
T

tomcee

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
 
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
 
Thanks for the prompt reply.

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

Thanks again,
TomCee
 
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.
 
Back
Top