Where should the code be stored? (Excel97)  
Author Message
mazdaboy1





PostPosted: Thu May 11 12:28:10 CDT 2006 Top

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

Excel336  
 
 
Chip





PostPosted: Thu May 11 12:28:10 CDT 2006 Top

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




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


 
 
tomcee





PostPosted: Thu May 11 12:40:52 CDT 2006 Top

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

 
 
Chip





PostPosted: Thu May 11 12:45:14 CDT 2006 Top

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




> Thanks for the prompt reply.
>
> I am curious though: What is the usage for code that is
> defined on
> 'Sheet1'?
>
> Thanks again,
> TomCee
>


 
 
tomcee





PostPosted: Thu May 11 13:43:26 CDT 2006 Top

Excel Programming >> Where should the code be stored? (Excel97) Chip:

Thanks again for the prompt, helpful replies.
TomCee.