Solved

Where do I put my code within MS Excel - Sheet module or Standard Module

Posted on 2012-03-18
11
221 Views
Last Modified: 2012-06-22
What are some guidelines/reccomendations with code in the MS Excel VBA editor.  Do I insert a "New Module" so it is available to all the workbooks or do I put the code in the Sheet1 module section.  If I only have a workbook that has 1 sheet then what difference does it make (other than from an organizational perspective) if I create a new module or place it in the Sheet1 module (Underneath MS Excel Objects/Sheet1(Sheet1)
0
Comment
Question by:upobDaPlaya
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
  • +2
11 Comments
 
LVL 48

Assisted Solution

by:Martin Liss
Martin Liss earned 50 total points
ID: 37736183
It makes no difference with one sheet but if you ever plan on more than one sheet you might as well put it in a module.
0
 
LVL 50

Assisted Solution

by:Ingeborg Hawighorst (Microsoft MVP / EE MVE)
Ingeborg Hawighorst (Microsoft MVP / EE MVE) earned 150 total points
ID: 37736187
Hello,

it is good practice to use the Sheet modules only for code that are event related, for example for Worksheet_Change or Worksheet_SelectionChange events.

All other code should go into standard modules, where you will also need to qualify the references to ensure the correct sheet is manipulated by the code.

If your code starts out in the Sheet module and you later move it to a standard module, you may find that the references need re-work. Better to place the code in the suitable modules from the start.

Of course, with only one sheet, it does not make a difference, but then, there's the ThisWorkbook module as well (which should only contain workbook related event code).

cheers, teylyn
0
 

Author Comment

by:upobDaPlaya
ID: 37736218
Hi Teylyn,

Thanks for your input..when you say "qualify the references" what does this mean..can you provide an example ?
0
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 
LVL 50
ID: 37736275
In a sheet module, if you work only on the cells of the sheet, you only need to use

Range("A1") = "Hello World!"

But when you place code in a standard module, you need to make sure that you address the correct sheet (and not the one that happens to be the active sheet at the time the macro is running). So, if you the active sheet is Sheet1, but you want the macro to write something in Sheet2, the above won't cut it. You need something like

Dim wks As Worksheet
Set wks = ThisWorkbook.Sheets("Sheet2")

wks.Range("A1") = "Hello World!"

Open in new window


This will make sure that Sheet2 will be written to, regardless of what the active sheet is when the code runs.

There are a few dozen different ways of setting up code with qualified references. The above is only one example. Many coders have their personal styles and preferences.

In a sheet module, you don't need the qualified references, but if you ever move your code from a sheet module to a standard module, qualified references will save many headaches (speaking from experience).

cheers, teylyn
0
 
LVL 42

Accepted Solution

by:
dlmille earned 150 total points
ID: 37736640
Not for points, but I would also note that Sheet codepages house private routines.  If you create a UDF function that you need to use in your worksheet, you won't be able to reference it unless you put that function in a public module.

You'll also have a hard time connecting an MS Forms command button to a subroutine you have in a sheet's codepage as well (I'm pretty sure that's not possible, either).

And you wouldn't be able to reference the code from other modules without explicitly referencing the source module, which can make it hard to support/document for others to use effectively.

Here's a very good primer on the subject and Chip Pearson agrees with teylyn on the subject of what goes in the Sheet/ThisWorkbook code modules:
http://www.cpearson.com/excel/codemods.htm

Dave
0
 
LVL 50
ID: 37736669
I met Chip two weeks ago, and, seeing how young he is, I think he must have started that web site when he was about twelve. It's hard to imagine how else he could have put so much content into it by the time he arrived at whatever age he looks now.

I'm glad to see that he recommends the same approach I suggested.

I don't, however, agree on the "not for points", Dave. You raise some important issues that I clearly missed, and that should be acknowledged.

cheers, teylyn
0
 
LVL 85

Assisted Solution

by:Rory Archibald
Rory Archibald earned 150 total points
ID: 37737262
>>"Sheet codepages house private routines"

Not necessarily. The scoping is the same as any other class module.

>>"You'll also have a hard time connecting an MS Forms command button to a subroutine you have in a sheet's codepage as well (I'm pretty sure that's not possible, either)."

Again, this is entirely possible - you just need to enter the routine name yourself and specify the sheet qualifier. Much the same as if you want to assign a macro that takes arguments to a Forms control.


A worksheet code module is basically just a class module (as is ThisWorkbook) with a few special properties. Hence, it makes sense to keep anything specific to that sheet in its code module. In general Excel usage, this tends to only be the built-in events and many, if not most, programmers do tend to put everything else in normal modules (in my opinion this is as much to do with ease of exporting and reuse as anything else). As you say though, UDFs do need to be in normal modules (at least, I haven't yet found a way to use them otherwise).
0
 
LVL 42

Expert Comment

by:dlmille
ID: 37738498
Let me clarify my statements, as this is I believe an important point

Sheet codepages actually do necessarily house private routines:  Worksheet_Change() and other routines couldn't function unless they were private.  If they were public, it might be hard for the events to find their callback routines, correct?  But, I agree, they can also house public routines as well.

I did mis-state that you couldn't connect to a sheet routine from a command button.  If the sub is private, you won't find it, but that's regardless of where the sub is housed.

Regardless what you can do, I always try to follow Chips advise on this one, "These modules should contain the event procedures for the object, and that's all."

Dave
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 37738539
>>"Sheet codepages actually do necessarily house private routines:  Worksheet_Change() and other routines couldn't function unless they were private.  If they were public, it might be hard for the events to find their callback routines, correct?  But, I agree, they can also house public routines as well."

Nope - try it. If you make an event procedure public, it makes no difference to its function. It still belongs to its container class. Scope only affects visibility.

>>"I did mis-state that you couldn't connect to a sheet routine from a command button.  If the sub is private, you won't find it, but that's regardless of where the sub is housed."

You won't see it in the macros dialog regardless of whether it is public or private but, either way, you can still assign it to a button. The 'assign macro' function runs a lot like Application.Run and has no respect for privacy.


Note: I don't disagree with Chip's recommendations - it's what I do - but there are arguments to the contrary, and I think it is important to be aware of what will and what won't work.
0
 
LVL 42

Expert Comment

by:dlmille
ID: 37738603
I appreciate your interjections here, and it is important, agreed.  I did try both and was a bit surprised that both did work!  Very interesting...

Things are not as black and white as they once seemed, lol.

Dave
0
 

Author Closing Comment

by:upobDaPlaya
ID: 37761955
Excellent discussion and the Chip reference was great.  I'. not sure I fully grasped the private vs public discussion, but that will come :)
0

Featured Post

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

689 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question