Microsoft Office Excel 2003 Programming Inside Out (Inside Out (Microsoft))

Keeping code organized and easy to find is just as important within an Excel project as keeping the files located on your hard drive organized. Fortunately, the Visual Basic Editor Project Explorer presents the elements of a project in the same tree view that is used by Windows Explorer. The Project Explorer is not as flexible as Windows Explorer, but it does keep similar elements of a project grouped together within a common folder. This familiar organization makes it easier to find a particular module, class module, user form, or other Excel object when needed.

Handling Windows

Each individual module, class module, or user form will be displayed within its own code window, which means that as you add more modules, the number of windows needed to work with them will grow. Regardless of the number of modules a project contains, you do not need to open them all to be able to work with the project. Only the module or user form you want to edit needs to be opened.

To assist you with navigating the various windows, the Visual Basic Editor provides some tools to open, switch, and close windows. Opening a module can be accomplished by double-clicking the module from within the Project Explorer window. You can also use this method to switch to a module if it has already been opened.

To switch among the various open windows, you can use the Window menu to select the desired window. You can also scroll forward through the various windows by pressing Ctrl+F6 or backward by pressing Shift+Ctrl+F6. When you’re done working with the contents of a window, you can close it by clicking the Close Window button or pressing Ctrl+F4.

Each of the windows can also be minimized or resized within the parent Visual Basic Editor window, allowing you to view more than one module window at a time. The Window menu provides commands to tile the open windows horizontally or vertically or to cascade them. If you need to view two different sections of the same module, you can split the module window by using the split window handle or Split command from the Window menu.

Adding Code

Adding code to a project can be done several ways, but the first consideration is to have a container to hold the code, such as a code module, a class module, or a user form. The container you use depends upon the tasks that will be performed by the code you intend to add. Although it is possible to keep all of your code within one module, it is best to keep your code separated into different modules grouped by tasks. To add a new module, select the type of module (module, class module, or user form) needed from the Insert menu or the Insert button on the Standard toolbar.

To add a new procedure to a module or class module, you can type directly into the code window of the module. You can also click Insert, Procedure to open the Add Procedure dialog box, shown in Figure 4-8, which will prompt you for the minimum information needed to create a procedure.

Figure 4-8: The Add Procedure dialog box lets you determine the basic outline of your procedure.

Notes on Programming Style and Code Readability

Programmers come from many different backgrounds, and each has their own style of writing code. Programmers who work for or have worked for large software companies might follow a strict format specified by the company, while solo programmers might follow no set format. While neither the company nor the solo programmer is necessarily right or wrong, it is absolutely vital that you write code that is easy to read and understand. Even if you understand the code you write today, you or another programmer might need to modify it in the future. Unless you have a photographic memory, you might have difficulties understanding the logic you wrote earlier and find it that much harder to make your changes.

There are several steps you can take to make your code as readable as possible.

• Keep your procedures as short as possible. If you need a macro that will open a workbook, ask the user for information, make changes to the workbook, then save and close the workbook, and then break the macro up into several procedures: one procedure for each task. Then have one main procedure to call the other procedures. This format of constructing a series of subprocedures that are called by a main procedure will also make finding and correcting programming errors (debugging) easier because you will be able to narrow it down to a much smaller section of code.

• If a line of code goes beyond the edge of the Code window, you can type a space followed by an underscore to indicate that the code on the following line belongs with the code on the current line. The PermissionLoop procedure on page 81 is the first of many procedures that use this convention.

• Pick a naming convention, and stick with it. Projects, procedures, variables, and other programming elements all need names. The name should be something meaningful that conveys what the element is used for. There are a number of naming conventions that you could choose from, and most are usually some form of Hungarian notation.

  Note

  NoteHungarian notation is named after the native country of Dr. Charles Simonyi, the inventor of this naming style. Dr. Simonyi was Chief Architect at Microsoft when he designed the convention. The original chapter of Programming Windows that described how to use it can be found by going to http://msdn.microsoft.com/ and typing Simonyi Hungarian Notation in the Search box.

• Choose names that are as concise as possible. While descriptive names are desired, names greater than 10 to 15 characters become cumbersome to type. Use abbreviations where they make sense to keep names short. Remember: The longer the name, the greater the likelihood of it being mistyped.