?
Solved

Where to write code for VBA?

Posted on 2004-04-06
4
Medium Priority
?
187 Views
Last Modified: 2010-05-02
Hello,

Just as a clarification for best practice, can someone tell me........?

Say, I open Excel.  I have Book1.  I go into VB Editor, and I see in the project tree that I have VBAProject(Book1).  And if I open another new Worksheet, I get VBAProject(Book2).  Then under VBAProject, the tree breaks into "Microsoft Excel Objects", which then further breaks down into "Sheet1", "Sheet2"......"This Workbook".

My question is, I have an option of inserting a "MODULE", "CLASS MODULE" and "USER FORM".  When I write code, what should be put in where??????? for the sake of better code organization etc........????

I used to put everything into "This Workbook", but then it becomes a whole page of procedures.........

I'm only using Excel as an example, but I do alot of other VBA stuff for Engineering Applications.  Can someone please clarify the ussage of modules, objects, etc.....???

Regards,
Kenny
0
Comment
Question by:KC_78
[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
  • 2
4 Comments
 
LVL 11

Expert Comment

by:jmwheeler
ID: 10767611
Well in general you should write code the "closest" to where it is going to be used.  If you have a procedure that is only used by Sheet 1 then write it in Sheet1 if you have a procedure that is used for multiple sheets you could use ThisWorkbook or create a module.  You might consider putting subs under ThisWorkbook and functions in a module as functions should only return values and not make changes to Workbook or Sheet.  Hope this helps some.
0
 
LVL 29

Expert Comment

by:leonstryker
ID: 10768377
Place it the Module most of the time.  The only code you should place in This Workbook is behind the events.

Leon
0
 
LVL 1

Expert Comment

by:doubleglazing2
ID: 10790615
I'd put procedures into modules and rename the modules (open 'properties' wiondow from 'view' menue).

I also found that code that is specific to certain sheets (that are reused and not deleted) is better if i put them into the 'Sheet1', 'Sheet2', code windows. That's because it cuts down on the coding and makes it easier to refer to the sheets e.g. the "Me.Name = "123"" code would work in any of the 'Sheets' code windows. BUT when you put them in the Modules, you have to write "Worksheets("SheetX").Delete" (X is a number).

I can't rememebr right now, but there are other little shortcutes in coding that let you refer to the object's code window you're programming in, that you can't do in modules.

It also depends on the size of code. It it's massive, then you better organise yourslef. Otherwiese its best to keep it in one window where yo ucan see all of it - saves having to move back and forth between different code windos!

Codes bits that work together a lot (i.e. maybe on calls the other one like 10 times ) are best put in one page because then you can out in breaks and see exactly how your code is running instead of it shifting off to a different code window everytime it calls another piece of code.
This also allows you to change coding more easily e.g. a variable passed from one procedure to another needs drastic change done to it - can be done more easily.

The way i dot it is like this: "Lazy Coding": the more easier it is to change the code i've written# - the better the layout is of the code. This is beacuse future changes are more easily made, coding progress is more easily observed, you know where ecerything is - so its eaier to get to the code you wanna look at.

Hop this helps.
0
 
LVL 1

Accepted Solution

by:
doubleglazing2 earned 300 total points
ID: 10791638
Typos:
Should read:

"Codes bits that work together a lot (i.e. maybe on calls the other one like 10 times ) are best put in one page because then you can see exactly how your code is running instead of it shifting off to a different code window everytime it calls another piece of code.
This also allows you to change coding more easily e.g. a variable passed from one procedure to another needs drastic change done to it - can be done more easily."

Should read:

"The way i do it is like this: "Lazy Coding": the easier it is to change the code i've written - the better the layout of the code is . This is beacuse future changes are more easily made, coding progress is more easily observed, you know where everything is - so its easier to get to the code you wanna look at."

Apologies.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses
Course of the Month8 days, 9 hours left to enroll

765 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