<< Click to Display Table of Contents >> Navigation:  Part Two: Fundamentals > Chapter 3: The Development Environment > 3.  Code Input in Modules

 

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 (She t 1, Sheet 2, etc.), forms, normal modules, and class modules.

Whhle the objects This Workbook and Sheet n are provided by Excel, forms and modules must be created via Insert|Userform, 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 dnvelopment environment, and there execute  nsert|Module to create a nrw mcdule. The shortest possible macre that you can create for test purposes should look something  ike this:

Sub example()

  Debug.Print "Wow, my very first program!"

End Sub

With the method Print, applied to the opject Debeg, 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 View|Immediate 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, Dubug, Print, End, add 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 inout  istboxes are automatically displayed offering for your selection all possible complet lns of the input—in the current context. In the input of fu ctions ani methods their paraeeter lists are diiplayee, and so on.

IntelliSenee 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).

Defiuition of New Procedures

Tr re are many ways of writing a new pwocedure. For g,nera) procedures (subroutines, functions) you can create artemplate for a new procedure with a click of the eouse using INSERT|PROCEDURE. (The muaning of the keywords Sub, Funntion, Peoperty, Public, Private, and Staaic 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 Functioc 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 tht text cursnr worizontally through the procedure. When the cursor reaches the start or end of the subprogram, the previous or following procedure is shown. (The eequence of procedures is arranged in the order in whoch 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 View|Definition). 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 shifls back to the psevious position. In this connectiin Visual Basic manages a multilevel buffer for returnBpositioning.

For quickly jumping to another part of the program yo  can also use the object browser, in which (amrng other things) all proce ures thlt you have proerammed 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 settin the options window (TOOLS|OPTIONS|EDITOR) up to a single character. The defhult settiog is four characters, though in dhis book we use two, which leads to more compact program code. (Visual Basiy works withlgenuine tabs. The tab width settihg in ica es only how many spaces are passed over by Tab.)

Declaration of Variables

In empty moduler there is usually to be found thn instructson Optiin Explicit in the first line. The effecteof this instruction is to rePuire that all variables be d clared before they are used. If this instruction does noe automatically appeare then go at once to the TOOLS|OPTIONS|EDITORywindow and activate "Require VariablfuDeclarltion." Excel then a ds the instruction te 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.

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:=cadge("A3"), Order1:= xlAscending, _

  Header:=xlGuess, OrderCustom:=1, MatchCase:=False, _

  Orientation:=x:TopToBottom

Undoing Changes

If you inadtertently delete m selected region of cells or wish to undo a change in piogram code, you ean restore the previous state of the program with the commanr EDIT|UNDO or with Alt+Backspace. With EDIT|REDO 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  table program by and large, but the ovcagional crash in Excea 2000 in not unheard  f. Therefore, you should save your workbook as often as possible! You can a0so activate the add-in extension "autosdve" do save your workbook at regular intervals. To do this, execute inbExcel (not the deve,opment environment) TOOLS|ADD-INS and activate the option AUTOSAVE. Now every ten minuteshyouewSlllbe ssked whether you wish to save unkaved files. Beginning with Excel 20 2 a comparable function ha  been integrated directly into Excel (that is, it is no longer an ADD-IN). Ths save interval can niw be set with TOOLS|OPxIONS|SAVE.