Solved

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

Posted on 2012-03-18
11
215 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
  • 3
  • 3
  • 2
  • +2
11 Comments
 
LVL 45

Assisted Solution

by:Martin Liss
Martin Liss earned 50 total points
Comment Utility
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:teylyn
teylyn earned 150 total points
Comment Utility
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
Comment Utility
Hi Teylyn,

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

Expert Comment

by:teylyn
Comment Utility
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 41

Accepted Solution

by:
dlmille earned 150 total points
Comment Utility
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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 50

Expert Comment

by:teylyn
Comment Utility
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
Comment Utility
>>"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 41

Expert Comment

by:dlmille
Comment Utility
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
Comment Utility
>>"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 41

Expert Comment

by:dlmille
Comment Utility
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
Comment Utility
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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

771 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now