Code on Worksheet or Module? (VBA/Office 2000)

Topic

New Reply

Within a VBA project is a list of modules and a list of worksheets. If I double-click on a worksheet icon under a VBA project, it opens a page in the editor that one can write code on. What is the difference between writing a VBA procedure on a worksheet page vs. a module page?

Thanks.

Reply | Quote

Replies

Procedures coded to a Worksheet are private to that Worksheet Object; they can’t be called from anywhere else, and User Defined Functions coded at the Worksheet Object level cannot be used anywhere in the Workbook, they must be in a Module.

Generally speaking the Worksheet Object Code windows are useful primarily for Worksheet Object Events and possibly procedures that are called only by those Events for only that Worksheet.

Reply | Quote

[indent]

---

they can’t be called from anywhere else

---

[/indent]

Not true.

Sheet1.Macro1

calls Macro1 on sheet1’s code module.

Another good reason NOT to put other code than event code in the module of a sheet is because the code cannot be “cleaned”. Editing is known to cause pile-up of rubbish behind the scenes. This rubbish can normnally be removed from normal modules by exprting, deleting and reimporting them. Deleting a sheet’s module is impossible and if the sheet’s code starts to show corruptions, one has to create the sheet from scratch.

It is for that reason that I tend to only put calls to subs in normal modules (which do the actual processing) in my sheet modules.

Reply | Quote

Also, when writing code for a worksheet, sloppy coding will get you in more trouble, than in a module. In a worksheet an unqualified Cells(1,1) will give you A1 on the sheet containing the code, NOT the active sheet as in a module. When I started writing code for a worksheet, it was like learning to code all over again. Now, I just always qualify an object with a worksheet and/or a workbook: makes for a lot less debugging. HTH –Sam

Reply | Quote

I’ve recently adopted the practice of putting ALL code in a class in Excel.
I’ve coded the class so that it works unchanged in either VB 6 or Excel VBA.

The only code I include in a regular Excel VBA module is:

1. Auto_Open to instantiate the class.
2. Auto_Close to destroy the class.
3. A wrapper function for each function in the class that I wish to use as a worksheet function.

Reply | Quote