« Previous Topics Debugging Code On A FormNext Topics » Debugging Code In A Module Breakpointing Code Using The Object Browser And Help System

•  Interview Questions
•  
•  Practice Test
•  
•  Pragnyameter Exam
•  
•  All Tutorials
•  
•  Job Search

In the last section, you saw that when the program code goes into Debug mode, the Editor window is displayed. However, you can access the editing environment by using several different methods, as described in the following list:

• Press Alt+F11 (this applies to all Microsoft Office products).
• Press Ctrl+G. This displays the Immediate window in the Editor and automatically opens the Editor window, if it is not already open.
• On the ribbon, on the Create tab, click Module. This creates a new module and enters the Editor.
• In a form or report, on the ribbon, on the Design tab, click the View Code icon.
• Click any of the modules shown in the Navigation window.

Right-click a Form/Report’s sections or controls, and then select Build Event, where there is code written behind an event. If you are not already in the Editor, then open the sample database and press Alt+F11 to go there.

The VBA Editor comprises a number of windows. If you accidently close one, or need to show a window that is not already displayed, click View on the menubar to open the window, as shown in Figure.

From the View menu, you can open different types of Editor windows. Note the Project window in the background with its expandable folders. This is a map of all the code modules in the application. Double-click any form or report to open the document’s code module.

The Project pane normally contains two folders. The first folder, Microsoft Access Class Objects, contains your forms and reports (only objects with an associated code module are shown). Clicking one of these objects displays the existing code module. The term Class refers to the special nature of a Form/Report module; it handles the events for the object. These are sometimes simply called Form/Report modules. The separate Modules folder below the Form/Report modules contains general purpose program code that can be used in various parts of your application; these are sometimes called general or global modules (this folder is only shown after you have created a module).

Below the Project pane is the Properties pane for the project. You can use this window to change the name of the project or of a module . The VBA project name property should be changed if you use the operating system to copy a database to create a new file, as the file copy operation does not change the VBA project name inside the database

The Project pane displays all forms and reports that have code modules.

You can use the Modules tab for writing code that is not tied to a particular form or report. Creating a Module You can use the Project window to create a new module. There are several different ways to add a new module; the method shown in Figure involves right-clicking the Modules tab, and then selecting Insert | Module from the shortcut menu that appears. This method is used when you want to concentrate on setting up new modules when you are in the middle of writing and debugging code.

Creating a new module or class module from the Project pane.

When you create a new module, it is automatically assigned a default name (for example Module1). When you click the save button, you will be prompted to give the module a permanent, more meaningful name.

Figure shows the new module before it has been saved with an alternative name. You might also notice that when you save the new module, it contains two special Option keyword lines of text.

After creating a new module, it will be displayed using a default name such as Module1, Module2, Module3, and so on.

When you click the save option on the toolbar or close the database, you are prompted to replace the default module name with something more meaningful.

Creating a Procedure

Modules contain procedures, and the procedures contain program code. Use the Insert menu to open the Add Procedure dialog box, in which you can add a new Sub (subroutine), Function, or Property (class modules only). There is also an option to prefix the procedure with the keyword Static, which makes variables hold their value when repeatedly executing the procedure .

The Add Procedure dialog box.

There is another, quicker mechanism for creating a new procedure: click any empty area, type the keyword Sub {name} or Function {name} (be sure you are not inside an existing sub or function), and then press the Enter key. The VBA environment adds an End Sub keyword automatically to complete the procedure block, as shown in Figure).

Creating a new procedure by using the Sub keyword.

The window in the background shows the keyword and the procedure name typed in; the foreground window shows the result after pressing return. Type the word MsgBox, enter a space, and then type a double quotation mark. As you do this, IntelliSense assists you as you type in each part of the syntax for the MsgBox procedure, as shown in Figure.

Executing a Subroutine

The subroutine code you created can be executed two ways. The first way is to click the green Continue button on the toolbar menu or press the F5 key (you need to have the cursor positioned inside the procedure on any part of the code). This should then display the message box.

The second way is to type the name of the subroutine into the Immediate window, and then press Return, as demonstrated in Figure.

You can type a subroutine name into the Immediate window, and then press the Return key to execute it.

The second type of procedure in VBA is called a function. The key difference between a function and a subroutine is that functions are always expected to return a value. To create a function, you can type Function {name}, similar to the way you entered your subroutine (you should try this). INSIDE OUT Figure

Because a function returns information, you are going to modify the program code to match Figure so that it returns a value.

The MsgBox statement can be written in two different ways: the first is to write it when you want to display a message with an OK button (where it looks like a Sub ); the second way is illustrated in Figure, where you want to gather input from a user (it behaves like a function).

The MsgBox function prompts the user with two buttons (Yes and No), and then tests to see which button the user pressed.

After you have typed in a call to either a built-in procedure or your own procedure, you can right-click the shortcut menu to display information on the parameters for the procedure or get assistance with selecting constant values. The MsgBox function has alternative constants for the second parameter (vbYesNo) shown in Figure, which control the buttons and graphics displayed in a message box. To change a constant value in the MsgBox routine, hover the mouse over the existing value, right-click to display the shortcut menu, and then select List Constants. This simplifies entering a new constant value.

Accessing the shortcut menu to display information about the parameters for the procedure. Other options on this menu include providing quick information on the function.

Executing a Function

To run a function, you can press the F5 key, but this will not display the returned value. You can also call the function from the Immediate window by using the “?” (question mark) symbol adjacent to the function name to display the returned value, as shown in Figure . Notice that when you execute a function you need to add parentheses "( )" after the function name; a function needs to show that it accepts parameters even when it has no parameters.

Executing a function from the Immediate window. Use the ? (question mark) character to return a value from the function.

In this section, you have seen how program code can be written in a module that is not connected to a form or report. These code units are called standard modules, or sometimes general modules or global modules.Figure illustrates how a standard module is an object that is independent of any form or report. Compare this to Figure, which showed a class module of a form or report that is attached to the Form/Report. Code written in these procedures can link to other procedures in the same or different modules. The code will normally not be specific to a single form. Form-specific code is better written inside a form’s class module

A schematic view of a module, which can contain one or more procedures. The procedures can be a combination of functions and subroutines.

You should now understand that program code can be written either in the class module of a form or report (when the code is specific to the Form/Report), or it can be written in a standard module (when it is not specific to a Form/Report).

Viewing and Searching Code

Module code can be viewed either showing the code for a single procedure (Procedure view) or the entire module (Full Module view), using the scrollbars to browse through its contents, as shown in Figure.

Using the buttons in the lower-left corner of the code window, you can display either a single procedure or a scrollable list of all the procedures in the module.

Split Window

The module code window can also be switched to a Split view . This gives you the ability to compare code in two different procedures, one above the other.

Use the Window menu to enable the Split view option.

Drag the splitter bar in the center of the screen up or down to change the proportion of the screen that is used to display each procedure. The scrollbars and the gUp/PgDown buttons can be used independently in each window to browse through the procedures in the module. Figure illustrates the split window view.

Viewing two procedures at the same time in Split view mode.

Dragging the splitter bar to the very top of the screen and releasing it will remove the split view. Similarly, by moving the mouse to the top right, just above the vertical scroll bars, the mouse pointer will change shape and you can drag down the splitter bar (this can be a little tricky to do and you will find the Window menu easier to use for this).

Use the drop-down menu located on the upper-right portion of the window to select any procedure within a module. This applies to each of the windows when using the split view, as well.

Use the drop-down menu to quickly display any function or subroutine in a module. For standard modules the drop-down on the top left only has one available choice called General; for class modules there will be other values shown in the drop-down.

In a Form/Report class module, the drop-down menu on the left lists the controls and sections in the document. The drop-down menu on the right shows all possible events for the selected section or control. Events that have code associated with them are displayed in a bold font.

If you have multiple code windows open, you can use the Windows menu to change between the open windows. You also have the option to tile (horizontally or vertically) or cascade the open windows, as shown in Figure.

The Window menu in the Editor allows multiple, open module windows to be viewed in Tile mode or Cascade mode.

Searching Code

If you need to find a procedure or a piece of code, press Ctrl+F to open the Find dialog box and locate the code in the current procedure, module, project, or block of selected text (use the mouse to select and highlight the text before pressing Ctrl+F), as demonstrated in Figure.

Use the Find dialog box to search and replace code fragments within a procedure, module, project, or selected text.

To view the definition of a variable or procedure , position your cursor on it, right-click to open the shortcut menu, and then click Definition. Alternatively, again with your cursor on the procedure or variable, press Shift+F2 to go to the definition. If the code is in a different module, the appropriate module will be opened automatically.

Viewing the definition of a procedure or variable.

Additionally, referring still to Figure , if you click the text modUtilites_GetFirstName in the subroutine ProcessNames, and then press Shift+F2, the body of the code for the procedure is displayed.