3.3 Code Input in Modules

VBA code can be input into any code window. As mentioned in the preceding section, there are numerous objects that can be fitted out with code: the Excel file as a whole (module This Workbook in the project explorer), every worksheet ( Sheet 1, Sheet 2, etc.), forms, normal modules, and class modules.

While the objects This Workbook and Sheet n are provided by Excel, forms and modules must be created via InsertUserform, Module, or Class Module in the development environment. (When a macro is recorded a module is automatically created.)

Finally, you should give the form or module a meaningful name . To do this you use the properties window (F4). Blank characters and most special characters are prohibited .

While for a normal module the name is principally for orientation in negotiating large projects, the names of forms and class modules are also important for the execution of code. Subsequent changes then lead to additional expenditure of effort and should be avoided as much as possible.

First Experiments

For our first experiments with VBA open a new workbook, with via Alt+F11 into the development environment, and there execute InsertModule to create a new module. The shortest possible macro that you can create for test purposes should look something like this:

 Sub example()   Debug.Print "Wow, my very first program!" End Sub

With the method Print , applied to the object Debug , this program will output the text "Wow, my very first program!" in the immediate window. You can launch this program simply by pressing F5 (with the input cursor placed within the procedure itself). The result will be displayed in the immediate window. Since this window is out of view under normal circumstances, it must be activated with ViewImmediate Window or Ctrl+G .

When you input the program, you will observe that Excel emphasizes ”by means of color ”certain parts of the program, namely the keywords Sub , Debug , Print , End, and Sub . This not only improves clarity, it indicates that the program lines are syntactically correct.

If Excel detects an error in the input of a program line, it announces that an error has occurred. You may ignore such announcements temporarily. Then the entire line will be displayed in red. The macro cannot be executed until all syntax errors have been removed.

Automatic Completion of Keywords and Variable Names

Code input is simplified by the so-called IntelliSense functions. Thus during input listboxes are automatically displayed offering for your selection all possible completions of the input ”in the current context. In the input of functions and methods their parameter lists are displayed, and so on.

IntelliSense has brought with it some new keyboard shortcuts: When no list-box is displayed, you can use Ctrl+spacebar to complete the input with the given keyword (or variable name). If there is more than one option, then the listbox automatically appears.

Once the listbox has been displayed, you can select the desired input with the cursor key. The Tab (not Return!) key completes the selection. With Esc you can avoid having to select from the listbox and can complete the input manually (for example, for input of a variable name that has yet to be defined).

Definition of New Procedures

There are many ways of writing a new procedure. For general procedures (subroutines, functions) you can create a template for a new procedure with a click of the mouse using INSERTPROCEDURE. (The meaning of the keywords Sub , Function , Property , Public , Private, and Static will be described in detail in Chapter 4, in the section on procedural programming.)

Once you have acquired some facility with Visual Basic, you will be able to carry out the definition of new procedures more quickly by typing the instructions Function Name or Sub Name in the code window. Visual Basic completes the procedure definition automatically with End Function or End Sub .

Cursor Movement in the Program Code Window

The text cursor can be moved within a subprogram or function as usual with the cursor keys. Page Up and Page Down move the text cursor horizontally through the procedure. When the cursor reaches the start or end of the subprogram, the previous or following procedure is shown. (The sequence of procedures is arranged in the order in which they were defined.)

Ctrl+ ‘ and Ctrl + Page Up, Ctrl + “ and Ctrl+ Page Down point independently of the current position of the cursor to the previous or next subprogram. F6 changes the active region if the window is split.

Shift+F2 moves the cursor to the procedure on whose name the cursor is currently located (command ViewDefinition). If the procedure in question was defined in another file of the project, Visual Basic automatically shifts into the relevant code window. Ctrl+Shift+F2 shifts back to the previous position. In this connection Visual Basic manages a multilevel buffer for return positioning.

For quickly jumping to another part of the program you can also use the object browser, in which (among other things) all procedures that you have programmed are visible. (See the next section.)

Indenting

To make program code easier to read, blocks of code within branches and loops are generally indented (as in all the program listings in this book). This indentation does not occur automatically, but must be done with the spacebar or tab key. If later you alter the structure of the program (for example, by an additional "If" test), it is then necessary to change the indentation pattern on large numbers of lines. Instead of doing this manually for each line, you can ask Visual Basic to help: Select the entire block of lines with the mouse, and then press Tab or Shift+Tab. Visual Basic then moves the entire block either in or out by one tab position.

The tab width can be set in the options window (TOOLSOPTIONSEDITOR) up to a single character. The default setting is four characters, though in this book we use two, which leads to more compact program code. (Visual Basic works with genuine tabs. The tab width setting indicates only how many spaces are passed over by Tab.)

Declaration of Variables

In empty modules there is usually to be found the instruction Option Explicit in the first line. The effect of this instruction is to require that all variables be declared before they are used. If this instruction does not automatically appear, then go at once to the TOOLSOPTIONSEDITOR window and activate "Require Variable Declaration." Excel then adds the instruction to newly created modules. (In modules currently open you will have to type in the keywords yourself.)

Commentary

The apostrophe ( ' ) is used to introduce a comment. Comments are allowed both at the start of a line and after an instruction. The comment symbol can be used, for example, temporarily to transform lines of code containing errors into comment lines. Comments are usually shown in green.

Note

During the testing phase of program development it is often practical to deactivate certain lines of code temporarily by prefixing them with the comment symbol. The development environment has designated two tools in the Edit toolbar, but no menu command, for commenting out a number of lines and for undoing this command. You can remedy this lack with ViewToolbarsCustomize.

Multiline Instructions

Long instructions can be divided over several lines. To accomplish this simply type anywhere (except within a keyword) a space followed by the underscore ( _ ) symbol. For example:

 Selection.Sort Key1:=Range("A3"), Order1:= xlAscending, _   Header:=xlGuess, OrderCustom:=1, MatchCase:=False, _   Orientation:=xlTopToBottom

Note

Variables can be declared to be accessible only within a given procedure or within an entire module or even within the entire program (global variables). Details on the range of effect of variables can be found in Chapter 4.

If you provide multiline instructions with comments, you can do so only in the last line of the instruction. The following instruction is syntactically incorrect:

 Selection.Sort Key1:=Range("A3"), _  'not allowed!   Order1:= xlAscending, _            'not allowed!   Header:=xlGuess                    'this comment is ok

Undoing Changes

If you inadvertently delete a selected region of cells or wish to undo a change in program code, you can restore the previous state of the program with the command EDITUNDO or with Alt+Backspace . With EDITREDO or Ctrl+Z you can undo the effect of the undo command. The undo and redo functions are multilevel functions; that is, you can take back several changes.

Automatic Saves

Excel is a stable program by and large, but the occasional crash in Excel 2000 is not unheard of. Therefore, you should save your workbook as often as possible! You can also activate the add-in extension "autosave" to save your workbook at regular intervals. To do this, execute in Excel (not the development environment) TOOLSADD-INS and activate the option AUTOSAVE . Now every ten minutes you will be asked whether you wish to save unsaved files. Beginning with Excel 2002 a comparable function has been integrated directly into Excel (that is, it is no longer an ADD-IN). The save interval can now be set with TOOLSOPTIONSSAVE.