Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Managing Macros
For the worksheet I am working on, do I need to keep all macro statements in
sheet 1 (VBA Project, MS Excel Objects)? After I execute Alt-F11. Most of the updates to the worksheet can be combined in the same macro. Different execution steps and logic required may require a different macro. Can I have multiple macros in sheet 1 (in VBA Project)? Is a separate sheet required in VBA editor only when a separate worksheet is used (ie. worksheet 3 reads the macros in sheet 3 (VBA Editor) only? Thanks for clearing this up for me - Tom |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Managing Macros
hi
that depends on what you are doing. most code goes into a standard module. sheet code usually pertains to the sheet that owns the code only. you can reference other sheets but that is about it. see this site for more details. http://www.cpearson.com/excel/codemods.htm also at the bottom is a link to another page. regards FSt1 "Stilltrader47" wrote: For the worksheet I am working on, do I need to keep all macro statements in sheet 1 (VBA Project, MS Excel Objects)? After I execute Alt-F11. Most of the updates to the worksheet can be combined in the same macro. Different execution steps and logic required may require a different macro. Can I have multiple macros in sheet 1 (in VBA Project)? Is a separate sheet required in VBA editor only when a separate worksheet is used (ie. worksheet 3 reads the macros in sheet 3 (VBA Editor) only? Thanks for clearing this up for me - Tom |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Managing Macros
Here are some general guidelines for organizing VBA code. These have
served me well over the years: First, the Sheet and the ThisWorkbook modules should contain ONLY event procedures (http://www.cpearson.com/excel/events.aspx). All other code should reside in regular code modules. ALWAYS use the Option Explicit directive to force explicit variable documentation. This can save you hours and headaches when debugging code. Name your modules to reflect the nature of the code within. When you are trying to track down some code, it is much easier to find it if you have a module name like modFileFunctions rather than Module1. Organize your modules to contain code of related functionality. There is no (practical) limit on the number of modules in a project. If you have procedures in a module that are called ONLY by other procedures in the same module, declare them with the Private modifier. It is better to have a large number of small procedures than a small number of large procedures. This makes it much easier to test and maintain a project. With small procedures, you can easily test them and then be done with them and use them as needed throughout the rest of the project code. It makes the code much easier to follow, debug, and maintain if you have smaller procedures designed to accomplish a specific task as opposed to a large procedure that tries to cover a number of different tasks.. (I once worked for a software company whose coding guidelines required that every procedure must be printable on a single sheet of paper. This was very obviously tremendous overkill, but the motivation was sound. Of course, if you set the font small enough, you can get a lot of code on one piece of paper.) For add-ins and for projects that might possibly be reference by other projects, change the project name from the default VBAProject to something meaningful, like projFileUtilities. Most of the updates to the worksheet can be combined in the same macro. I would recommend that you not combine disparate functionality into a single macro. This makes the code considerably more complex. Break each distinct piece of functionality into separate procedures and organize those procedures into separate modules. If your code is well written, well organized, and well documented, you gain the tremendous advantage of code re-use. Once a function is written, it can be re-used within the same module and project, as well as in other projects. Over the 12 years that I've been doing VBA/VB6/VBNET programming, I have accumulated a library of about 200 modules (over 100K lines of code) that I can import into a project as needed. I cannot overestimate the amount of time this has saved me. For example, if I need to use the system registry, I just import my modRegistry module and I have all the functions available, pre-tested, and documented. Code should be as self-documenting as possible. This goes beyond adding comments to the code, although this is very important. Choose your variable names and procedure names to be descriptive and to identify what type of active the procedure carries out. For example, all of my procedures that test for a condition begin with the work "Is". For example, Function IsWorkbookLocked(). Similarly, functions that return a setting begin with "Get". E.g., GetActiveProjectName(). Some programmers like to use what is called "Hungarian notation", in which the first few letters of a variable name indicate the data type of the variable. For example, a string might be named strWorksheetname and an integer might be named iPageCount. I have never embraced this style, but many programmers have, and on balance it is a good habit to get into. There is a lot of personal preference and style that goes into programming, and every programmer has their own style. As you code more and more, you'll develop your own style. Just be sure that it is based on sound principles. The suggestions above are just that, suggestions, not rules. They have served me well over the 20+ years I've been a professional programmer. Cordially, Chip Pearson Microsoft Most Valuable Professional, Excel, 1998 - 2010 Pearson Software Consulting, LLC www.cpearson.com On Sat, 10 Apr 2010 22:03:01 -0700, Stilltrader47 wrote: For the worksheet I am working on, do I need to keep all macro statements in sheet 1 (VBA Project, MS Excel Objects)? After I execute Alt-F11. Most of the updates to the worksheet can be combined in the same macro. Different execution steps and logic required may require a different macro. Can I have multiple macros in sheet 1 (in VBA Project)? Is a separate sheet required in VBA editor only when a separate worksheet is used (ie. worksheet 3 reads the macros in sheet 3 (VBA Editor) only? Thanks for clearing this up for me - Tom |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Managing Macros
There are mixed opinions about this. It may be easier to keep macros
pertaining to actions on that sheet in the sheet module. However, there are macros that will NOT work in the sheet module. Combine where it is practical. -- Don Guillett Microsoft MVP Excel SalesAid Software "Stilltrader47" wrote in message ... For the worksheet I am working on, do I need to keep all macro statements in sheet 1 (VBA Project, MS Excel Objects)? After I execute Alt-F11. Most of the updates to the worksheet can be combined in the same macro. Different execution steps and logic required may require a different macro. Can I have multiple macros in sheet 1 (in VBA Project)? Is a separate sheet required in VBA editor only when a separate worksheet is used (ie. worksheet 3 reads the macros in sheet 3 (VBA Editor) only? Thanks for clearing this up for me - Tom |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Managing Modules/Macros - More than one Module FOLDER? | Excel Programming | |||
Managing Add-Ins | Excel Programming | |||
Managing menus via VBA | Excel Programming | |||
Managing User Changes | Excel Programming | |||
MANAGING MACROS | Excel Programming |