What's the best????

N

nrage21

is there a difference between writing VBA code in...
1) This Workbook
2) Sheet
3) Module

Does any affect excel performance????

-Larry-
VBA Amateur
 
C

Chip Pearson

Larry,

While there is no performance impact of the location of the code,
where you put it is important. In general, you should put all
your macros and functions in standard code modules. The only
thing that should be in the ThisWorkbook and the Sheet modules
are the event procedures for those objects.


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

Ken Wright

All different and have different uses:-

1) ThisWorkbook - Often used for things such as macros that will execute
automatically when you open the file. They typically have to go in here.

2) Sheet - Often used for Event macros, eg on sheet 1, anytime someone types
something and you want it converted to CAPS/Lowercase, numbers validated etc, or
clicks somewhere on that sheet and you want something to happen.

3) Module - Where 95% of the macros you use will likely go. These macros are
available to you via the Tools / Macro / Macros option when in any worksheet.

For a far better description of all these and more:-

http://cpearson.com/excel/codemods.htm
 
Top