VBE-Visual Basic Editor

Forward
The Majority of the following text was taken verbatim from a publication "The Visual Basic Editor" by Martin Green who has graciously given permission to republish in Wiki format.

About the Visual Basic Editor
The Visual Basic Editor is where you create, edit and manage your visual basic code. You can use the Visual Basic Editor from within most Microsoft Office programs from Office 97 onwards. From Office 2000 onwards Word, Excel, Access, PowerPoint, FrontPage and Outlook all use the same Visual Basic Editor. Access 97 retains the older style module editing window. Outlook 97 did not have a VBA code editing tool. The Visual Basic Editor can only be opened from within a Microsoft Office host application. All the programs use the same Visual Basic Editor, which adapts itself to suit the host program. You can have several copies of the Visual Basic Editor open at one time. For example, you might be working in Excel with the Visual Basic Editor window open and, at the same time, working in the Visual Basic Editor window in Access.

Opening the Visual Basic Editor
Open the Visual Basic Editor from the Tools menu by choosing Tools > Macro > Visual Basic Editor (Fig. 1). Alternatively, use the keyboard shortcut [Alt]+[F11].

If the file in which you are working already contains macros, you can go straight to the macro code by choosing Tools > Macro > Macros, or use the keyboard shortcut [Alt]+[F8], to open the Macros dialog box. Select a macro name and click the [Edit] button. If you do a lot of macro work in Word, Excel or PowerPoint, you might also find it useful to open the Visual Basic toolbar (View > Toolbars > Visual Basic) which has buttons offering quick access to several useful commands (Fig. 2).

In addition to these methods, in Access the VBA code can be viewed from either the main database window or from the design view of a form or report. To go direct to a module's code window click the Modules tab of the database window and double-click the name of the module. Alternatively, click the module name to select it and click the Design button (Fig. 3), or click the module name and choose View > Code.

To view the code module of a form or report from the database window, select its name and choose View > Code (Fig. 4). In addition to these methods you can use the View > Code command from the design view of an Access form or report.

All these methods can be applied to Access 97 which, although it does not have the Visual Basic Editor, will display the code window for the chosen module. When the Visual Basic Editor is open a button for it is displayed on the Windows taskbar (Fig. 5). If more than one Visual Basic Editor is open at the same time (because you are working with code in more than one program) then each will have its own button on the taskbar.

Closing the Visual Basic Editor
Since the Visual Basic Editor occupies its own program window, it can be closed as you would any other program by clicking the Close button in the upper-right corner of the window, or by choosing Close and Return to <...program name...> from the Visual Basic Editor's File menu (Fig. 6). If you close the host program its Visual Basic Editor window will close automatically. A program's Visual Basic Editor can not remain open after the program itself has closed. It is important to remember that closing the Visual Basic Editor window will not prompt you to save your work. Changes to a file's code module (including changes to code in Word's Normal.dot and Excel's Personal.xls) are considered changes to the file itself and therefore no warnings about saving changes are displayed until the file itself is closed. You can save the changes manually by clicking the Save button in either the Visual Basic Editor or the host program, or by choosing Save from the host program's File menu or Save <...filename...> from the Visual Basic Editor's File menu. In the case of Normal.dot and Personal.xls, any changes to their code will prompt a message to save the file when you close down Word or Excel (respectively).

The Menu Bar
The Menu Bar gives access to all the commands available in the Visual Basic Editor.

The Toolbar
The Toolbar has buttons giving access to the more commonly used commands. In the illustration above (Fig. 7) the Standard toolbar is shown. There are also available Debug, Edit and UserForm toolbars which can be displayed by choosing View > Toolbars or rightclicking on a toolbar or the menu bar and choosing from the context menu.

The Code Window
The Code Window is where the VBA code itself is displayed, written and edited. It is your main workspace when you are creating VBA procedures and works like a word processor. You can modify the appearance of your code by changing the font, point size and colour by choosing Tools > Options > Editor Format. Code is normally displayed in a monospaced font (Courier New) for legibility. If there are no files containing code open when you open the Visual Basic Editor, there will be no code window open. The Visual Basic Editor can have more than one code window open at a time. They can be managed in the usual way from either the Window menu or from the familiar Minimize, Restore/Maximize and Close buttons in the upper-right corner of the Visual Basic Editor window. At the top of the Code Window are two combo boxes, labelled "General" and "Declarations". The General combo box displays a list of all the objects to which the current code module applies (such as all the controls on a VBA UserForm or an Access form). In the case of an object that does not contain other objects, such as an Excel workbook or worksheet module or a Word document module only that item is shown. When an item has been chosen from the General list the Declarations combo box displays a list of that item's events for which a code procedure can be written. The illustration below shows a typical list for a VBA UserForm (General) and a command button (Declarations)(Fig. 8).

The Project Explorer
The Project Explorer is organized like the familiar Windows Explorer file manager, showing you the various objects, code modules, class modules or UserForms that are currently available. It uses a folder-tree structure to display the various elements. A plus symbol next to an object indicates that it contains other objects. Click the plus symbol to reveal the objects. The symbol then changes to a minus symbol which in turn hides the objects when clicked. Double-click an object in the Properties Window to see its code (if any) displayed in the Code Window. If code is password protected, you will be prompted to enter the password when you click the plus button, before the code is revealed. The Project Explorer is normally docked in the upper-left corner of the Visual Basic Editor window. Drag its title bar to dock it against the top, right or bottom edges of the Visual Basic Editor or to have it float anywhere within the main window. The Project Explorer can be resized by dragging its edges, and switched off by clicking its Close button. If the Project Explorer is not visible it can be switched on from the View menu.

The Project Explorer in Excel
In Excel the Project Explorer displays all the workbooks currently open in Excel as well as any loaded add-ins and, if present, the Personal Macro Workbook (Personal.xls). Note that, unless explicitly named, the Visual Basic Editor calls an Excel file VBAProject because a the code component of a file is referred to as a VBA Project. You can give your VBA Project a meaningful name by highlighting it in the Project Explorer and choosing Tools > VBAProject Properties > General and changing the Project Name (alternatively right-click on the item in the Project Explorer and choose from the context menu). The illustration below (Fig. 9) shows that an Excel add-in (Funcres.xla) is currently loaded. Third-party add-ins (this one is the Analysis ToolPak from Microsoft) are usually password protected so that you can not view or edit the code.

The Project Explorer in Access
Each instance of the Access application can open only one database file at a time, so the Project Explorer displays details for only one VBA project, the current database. Access is able to run multiple instances (i.e. to open another database you open a new copy of Access) and each instance can open its own copy of the Visual Basic Editor. In an Access database code can be stored in "class objects" like forms or reports, or in modules. The code stored in a class object is specific to that object, so each is listed separately in the Project Explorer. This makes it easy to find and view the code module of a form or report simply by double-clicking its name in the Project Explorer. A single database file might have many of these "class modules" (Fig. 10). Code for general use, such as custom functions and procedures likely to be used by more than one process, are stored in regular VBA code modules.

The Project Explorer in Word
Word bases all documents on the Normal template so this is always shown in the Project Explorer. The template object has its own code module ("ThisDocument") and any additional code modules contained in the Normal template (Normal.dot) are also displayed. If a document is open that is based on a template other than Normal.dot a reference is shown (Fig. 11) and the document's underlying template, its objects and any modules it contains are all listed in the Project Explorer window.

The Properties Window
The Properties Window displays the properties of the object currently selected in the Project Explorer. Don't confuse this with what happens to be on view in the Code Window. Like the Project Explorer, the Properties Window can be resized and docked against the edge of the main Visual Basic Editor window or floated anywhere within the window. Some objects have few properties, others have many. A code module has only one property, its name, whereas an Excel workbook or worksheet, a Word document (Fig. 12) or a VBA UserForm has many properties (Fig. 13). The main part of the properties window is divided into two columns. The left column shows the property name and the right column shows its current setting. The value of a property can be changed by either typing directly into the right-hand column or choosing from the list provided. You can opt to see the properties arranged in alphabetic order, or grouped by category. At the top of the Properties Window is a combo box. This displays a list of related items when the object whose properties are currently shown itself contains other objects. In the illustration below (Fig. 14) a list of UserForm control objects is shown.

Writing Code in the Visual Basic Editor
VBA code is stored in different places depending on its purpose. Word documents and templates, Excel workbooks and worksheets, Access forms and reports, and VBA UserForms all have their own "class modules" in which code can be kept. Code for general use is normally stored in a "module".

Creating and Naming Modules
If a suitable code module is not present, use the Project Explorer to select the file (the "VBAProject") to which you want to add a module, and choose Insert > Module. Alternatively click the Insert... button on the toolbar and choose Module from the list (Fig. 15). This button can also be used to insert a UserForm, a Class Module or a Procedure (into an existing module). The Insert button displays the icon representing whatever it did last, so it might not appear the same as in this illustration. New modules are automatically assigned a name, the first being "Module1", and sequentially numbered as you add more. To change the name, click on the module's current name in the Project Explorer and change its Name property in the Properties Window. A module name can be up to 30 characters long and must not contain spaces (although the underscore character can be used). Only alphanumeric characters may be used (A-Z, 0-9) and the name must start with a letter. When the name consists of more than one word some developers prefer to capitalize the first letter of each word (e.g. MyMacros) while others separate the words with an underscore (e.g. My_Macros). One of the naming conventions applied to VBA code is to prefix a module name with "bas" in lower case, indicating that the code is written in the "basic" language (e.g. basMyMacros) or "mdl" to indicate that the name refers to a module (e.g.mdlMyMacros").

Removing Modules
To delete a module right-click on its name in the Project Explorer and choose Remove <...modulename...> from the context menu. When asked if you want to export the module before removing it click No. The module will be deleted.

Exporting and Importing Modules
Code modules can be exported as text files, normally with the extension *.bas (e.g. MyMacros.bas). This is useful when exchanging code between applications or computers. It is often more appropriate to email someone a *.bas file when sharing code, rather than sending a complete workbook or document. To export a module right-click on its name and choose Export File... You will be prompted for a file name and a save location. A copy of the module is created, the original being left in place. Removing a module also offers the option to export it as well. A module can be imported in the same way. Right-click on the name of the VBAProject into which you want to import the *.bas file and choose Import File... Use the dialog box to locate the file and click Open to import the file as a new module. Being simple text files, exported modules can be read and edited in Microsoft Notepad or any other text editor.

A Note About Emailing Files Containing Code
When emailing attached *.bas files or any other file containing VBA code, it is advisable to first compress the file into a *.zip archive (using a suitable program such as WinZip). This prevents the attachment from being rejected by the recipient's anti-virus program or email client. Owing to the proliferation of viruses and other malicious code recent versions of Microsoft Outlook automatically remove attachments from incoming mail messages if they have a change to the Windows Registry. If you email an "unzipped" *.bas file to someone there is a strong likelihood that they will not receive it. Zipping the file should overcome this problem. Some anti-virus programs have the ability to read the contents of a *.zip archive. If this is the case make use of WinZip's encryption facility to encrypt the file before sending it (don't forget to tell the recipient the password required to de-encrypt the file!).
 * .bas extension. This feature can not easily be disabled by the user since it requires a

Creating Procedures
VBA code normally falls into two main categories, Procedures (often called "macros", "subroutines" or "subs") and Functions. The Visual Basic Editor provides a great deal of help to the code-writer, performing checks on grammar and syntax, and offering suggestions for completing code lines. The easiest way to create a procedure is to type directly into the code window. The code for a VBA procedure always starts with the name of the procedure, preceded by the word Sub, and ends with End Sub. The same rules apply to the naming of a procedure as to a module, but a procedure name can be up to 80 characters in length. It is good practice to make procedure names descriptive of their purpose, and to keep them reasonably short. Enter the start line of your procedure by typing the word Sub (or Function when creating a function) followed by a space and the name for your procedure, then press [Enter]. The Visual Basic Editor will automatically fill in the end line of your procedure, and position your cursor on the empty line between them (Fig. 16). Note that a pair of parentheses are added following the procedure name. These are used to enclose details of any arguments required by the procedure. This more usually applies to VBA functions. A procedure name or function name can be used only once in each module. If names are duplicated in a project, each procedure or function must be referred to by both its name and the name of its parent module to prevent confusion. It is better to avoid the duplication of names if possible. Procedures and functions can be declared as "Private" or "Public". This is done when entering the name, e.g. Private Sub, Public Function etc. If not explicitly declared as Private a procedure or function is assumed to be Public, unless it is contained in a Class Module such as the code module of an Access form, where all the code is Private. Private procedures and functions can only be utilized from within their host module. Public procedures and functions can be called from the host program. A Public procedure will be listed in the host program's Macros dialog but a Private one will not. When a code module contains more than one procedure or function, they are separated by a horizontal line (known as the Procedure Separator) across the Code Window (Fig. 17). This is for clarity when viewing the code and can be switched off if required by going to Tools > Options > Editor.

Typing Your Code
The code window of the Visual Basic Editor works like a word processor and will respond to the familiar word processing commands such as Cut, Copy, Paste, Undo, Redo etc.. There is no font control other than changing the standard font and point size settings. Text can be selected in the usual way. To select an entire line of code, click on the white part of the code window to the left of the line that you want to select (Fig. 18). To select multiple lines, click and drag up or down. Do not click on the grey bar at the left side of the window – this facility is for creating break points in the code and displaying bookmarks. Text is colour-coded automatically. You can customise the colour scheme, as well as change the standard font and point size of text, by going to Tools > Options > Editor Format. With the default settings VBA keywords are coloured blue and comments green (Fig. 19). All other text is black.

Knowing What You Can Type
The Visual Basic Editor offers two kinds of prompts to help you when you are typing your code: Auto List Members and Auto Quick Info. Both are enabled by default but can be switched on or off by going to Tools > Options > Editor.

AutoList Members
As you type code the Visual Basic Editor will offer, at appropriate points, a list of acceptable entries. In the example below, a variable is being declared. After you type the word "as" followed by a space, a list of possible entries is displayed (Fig. 20). To complete the entry you can scroll through the list and double-click on your chosen item. A quicker way to achieve this, if you know what you want to type, is to start typing the entry. As you do so the list automatically scrolls to the first item starting with the letters you typed (Fig. 21).

When you reach your chosen entry, press [Tab] to insert it into your code (Fig. 22). Do not press [Enter] unless you know that the item to be inserted completes a line of code, since doing so inserts the item then creates a new line, which may not be the appropriate thing to do at that point.

Auto Quick Info
The Visual Basic Editor also displays information to help you enter the arguments required by a VBA function or method. In the example below the Visual Basic Editor displays Auto Quick Info for the InputBox function (Fig. 23). The argument currently displayed in Bold (here the Prompt) is the argument that the Visual Basic Editor is expecting you to supply. Arguments shown enclosed by brackets such as [Title] are optional. Arguments not enclosed in brackets are required and must be supplied. After entering an argument type a comma to move to the next one, which is then displayed bold in the list (Fig. 24). Do not type a comma after an argument if you do not intend to supply any more arguments. Typing a comma tells the Visual Basic Editor to expect more information. If you fail to supply it the Visual Basic Editor will display an error message. All arguments you supply must be in the correct order, but you may want to skip optional arguments to get to the one you want to supply. To do this, type more commas until the argument you want to supply is shown in bold. In this example (Fig. 25) the optional "Title" argument has been skipped to reach the "Default" argument.

If Help Does Not Appear
The appearance of Auto List Members or Auto Quick Info is a useful indicator that your line of code makes sense (so far) but don't always assume that, if the help doesn't appear, something is wrong. Like any other language VBA often has many ways of saying the same thing. Sometimes the help doesn't appear because it isn't programmed to do so in those particular circumstances.

With experience you will know when to expect to see the help. If it doesn't appear when it should, look for a spelling mistake or a typo in your code statement.

If your are in process of writing code and Auto List Members or Auto Quick Info is already displayed, you will probably find that it disappears when you switch to another window and then back to the code window. You can easily make it reappear without having to rewrite your code. The Edit toolbar contains a number of useful commands including buttons to display these lists (Fig. 26). Just click the appropriate button to make the help reappear.

Alternatively press [Backspace] then retype the character (e.g. a comma, space or opening bracket) which prompted the list to appear originally.

Error Messages
The Visual Basic Editor checks much of your code as you write it and often alerts you to errors immediately. Each line of code is checked as you leave it by pressing [Enter] or clicking somewhere else in the code window. If an error is found the Visual Basic Editor displays a message and colours the offending code statement red. In this example, a closing bracket has been omitted from the InputBox function (Fig. 27). The message suggests that the Visual Basic Editor was expecting you to type a list separator (usually meaning a comma) or a closing bracket. When you acknowledge the message by clicking OK your cursor is returned to the location of the error. The text remains red until the error has been corrected.

The error messages are not always easy to interpret, but the Visual Basic Editor assists in diagnosing the problem by selecting the offending text, or by placing the cursor where a missing item should be. In the example below an additional dot has been typed by mistake (Fig. 28). Although the error message is rather general and does not explicitly identify the incorrect text, the offending piece has been selected and the error can clearly be seen. When you correct the offending code the red colour disappears.

Automatic syntax checking is a very useful feature but it can become an irritation, especially for the more experienced programmer. You may be in the process of writing a code statement when you remember you need to add something elsewhere. Moving the cursor away from a code statement prompts a syntax check and, since the code statement was incomplete the syntax checker colours it red and displays a message, and you have to dismiss the message before you can do anything else.

If you find that this bothers you go to Tools > Options, find the Code Settings section of the Editor tab and remove the tick from the Auto Syntax Check checkbox. When you have done this code which fails the syntax check is still coloured red as before but the Visual Basic Editor will not display a warning message.

Compiling Code
Before running any VBA procedure you should "compile" the code. Compiling is a process in which the Visual Basic language that you use to write your commands is checked for errors and translated into a form that is understood by the host program (this happens invisibly and your code will look no different). When you compile code, the compiler checks all the code for the entire VBAProject (in the case of Access the entire database). Access 97 offers to compile the Current Module, All Open Modules or All Modules. To compile code choose Debug > Compile VBAProject (or the project or database name)(Fig. 29). If the code does not need to be compiled, because it has already been compiled or because there have been no changes made since it was last compiled, the Compile menu option will be disabled (Fig. 30). When you give the instruction to compile a procedure the compiler performs a "dry run" of the code and highlights any errors it finds. This does not guarantee that your code will work perfectly. There may be logic errors that only arise in certain circumstances and can not be tested for in this way, but it is a good indication that there is nothing intrinsically wrong with it. If the compiler finds an error, such as an undeclared variable (perhaps arising from a typing error) it will notify you (Fig. 31). You should correct the error and compile again until no errors are found. It is good practice to compile the code each time you make a change. It is better to let the compiler find any errors, than to discover an error when you run your code the first time on real data! If you do not compile your procedure manually it will be compiled automatically the first time it is run. This is why you might notice a procedure taking slightly longer to run the first time if you did not compile it beforehand.

Typing in Lower Case
Many developers are in the habit of typing their code in lower case (except for variable names when they are first declared when you choose the cases). When a code statement is checked by the Visual Basic Editor it capitalizes words it recognizes. For example active cell becomes Active Cell. It also applies colors to the VBA keywords. If you type in lowercase you can quickly spot any typing errors by noticing when words that should become capitalized fail to do so. Here, a line is typed in lower case (except for the declaration of the variable name where the user is defining how it should appear) (Fig. 32). On pressing [Enter] to create a new line the Visual Basic Editor checks the syntax and, if no errors are found, capitalizes the text and colors it where necessary.

Using Bookmarks
VBA procedures can be anything from just a few lines to several hundred lines in length. When dealing with large amounts of code it is easy to lose your place and waste time looking for a particular statement or place in your code. To help you quickly locate a position in your code the Visual Basic Editor provides several Bookmark tools (Fig. 34) on the Edit toolbar and menu. To create a bookmark, place the cursor in the code statement that you wish to mark and click the Toggle Bookmark button. A blue rectangle (the bookmark) is displayed in the grey border on the left side of the Code Window adjacent to your selected line of code (Fig. 35). You can move from bookmark to bookmark by clicking the Next Bookmark and Previous Bookmark buttons. To remove a bookmark place the cursor in the bookmarked code line and click the Toggle Bookmark button again, or click the Clear All Bookmarks button to remove all the bookmarks from the current Code Window.

Find and Replace
The Visual Basic Editor has Find and Replace tools similar to those found in the various Microsoft Office programs. Use them to locate specific pieces of text in your code, or to make global changes to, for example, the names of variables.

To locate a specific piece of text choose Edit > Find (keyboard shortcut: [Control]+F) to open the Find dialog and enter the search text in the Find What: textbox. Click the Find Next button to find the first occurrence of the text. The Visual Basic Editor remembers the last search string so, if it is in the way, you can close the Find dialog box and continue the search by pressing the F3 key.

The Replace dialog is similar to the Find dialog with the addition of a Replace With: text box (Fig. 36). Open the Replace dialog by choosing Edit > Replace (keyboard shortcut: [Control]+H), or by clicking the Replace button on the Find dialog. When you choose to replace an item the Visual Basic Editor automatically searches for the next occurrence and moves to it. You can choose to replace all occurrences at one time by clicking Replace All.

Both tools offer the option to search the current procedure (i.e. the one in which the cursor is currently located), the current module or the current project.

Protecting Your Code
The Visual Basic Editor offers the ability to password-protect your code. Code protection works at project (i.e. file) level and prevents users from viewing any of that project's code modules. This can be desirable for a number of reasons:
 * It prevents inexperienced users from inadvertently making changes to the code.
 * It makes it impossible for people to add malicious code to your projects.
 * It protects your "intellectual property" so that people can not copy your ideas.
 * It denies access to the code in the event of an error.

Adding a Password to a Project
Protecting a project's code is very simple. In the Visual Basic Editor select the name of the project in the Project Explorer then go to Tools > VBAProject Properties (if you gave the project another name the menu item will be <...projectname...> Properties).

Select the Protection tab in the Project Properties dialog box then place a tick in the Lock project for viewing checkbox and enter and confirm your password in the textboxes below (Fig. 37). Click the OK button to complete the process. The password protection does not come into effect until you close and save the file.

Once a project's code has been locked in this way, its components do not appear expanded in the Visual Basic Editor's Project Explorer window. If you try to view the code by clicking the button next to the project's name the Visual Basic Editor asks for the project's password (Fig. 38).

It is very important to remember that if you forget the password you can't access the code! When distributing a password-protected file I usually keep an unprotected copy in a safe place for use as a backup.

Unprotecting the Project
Removing protection from a code project is a two-stage process. Return to the Project Properties dialog and remove the tick from the Lock project for viewing checkbox. If you leave the password in place the effect will be to allow users full access to the project's code modules but deny them access to the Project Properties dialog which will require the password to be supplied before it will open.

If you remove the password (remove it from both textboxes) then protection is cancelled completely and full access is restored.

As when protecting a project, removing the protection does not come into effect until the file is closed and saved.

Protecting the User
As well as providing security for your code, locking the project also prevents users from inadvertently opening the Visual Basic Editor and possibly make changes unintentionally.

If you add error handlers to your macros you can take control of what happens if an error occurs. If you don't add an error handler of your own then the Visual Basic Editor's own error handling tool takes over with possibly undesirable consequences. The illustration below (Fig. 39) shows what the user sees if an unhandled error occurs when they run a macro.

This dialog box is intended for the eyes of the developer, not the inexperienced user. The button marked Debug is highlighted indicating to the unknowing user that this is the one to click. If they do so they find themselves in the Visual Basic Editor with the macro in break mode. This is fine if you are the developer debugging your code. But the average user finds themselves in a place they certainly don't want to be!

However, if the project has been locked the error message looks slightly different (Fig. 40). The Debug button is disabled and the button marked End is highlighted. The user is now prompted to terminate the macro, leaving their file in a safe condition (albeit with something having gone wrong!).

Using the Help Menu
The Visual Basic Editor provides help in the usual way. Choose Help > Microsoft Visual Basic Help to open the familiar Help window with its Contents, Index and Answer Wizard or Search tool (depending on your version of Microsoft Office). Use this method to browse the help topics or search for a particular item in the usual way. VBA help is very comprehensive and usually provides examples of code (which you can copy and paste into your own modules) and cross-references to other related topics.

Context Sensitive Help with the F1 Key
If you are writing VBA code and find that you need help, the Visual Basic Editor can often take you straight to the correct help topic. To get help about a specific keyword (a VBA object, property, method or function) place your cursor in the word and press the [F1] key. This will open Help at the correct location. In the example below help is being sought on the Offset property (Fig. 41). In the unlikely event that there is not a relevant help topic the message Keyword Not Found will be displayed. Fig 41.

The Object Browser
The Object Browser is a hierarchically organized, cross-referenced dictionary of VBA with links to relevant help topics. It appears in a window of its own which is opened by clicking the Object Browser button on the Visual Basic Editor toolbar (Fig. 42), by choosing View > Object Browser or by using the keyboard shortcut [F2]. The main parts of the Object Browser window are shown below (Fig. 43). The Object Browser can be used in different ways:

Search on a Chosen Keyword
Near the top of the Object Browser window is the Libraries Box which lists all the Object Libraries to which there are references in the current Project. If you are working in Excel, for example, the VBA and Excel libraries will be listed. You might be writing a procedure that involves communicating with Outlook, for which you have set a reference to the Outlook object library. In this case Outlook would be listed here too. Unless you want to restrict your search to just one of the libraries, the Libraries box should be set to All Libraries. Underneath the Libraries Box is the Search Text box. Use the Search Text box to enter a key word (you can use wildcards to help your search). When you click the Search Button the Object Browser searches for all occurrences of your key word in the selected object libraries and displays them in the Search Results List. In the example above (Fig. 43) the keyword "offset" has been entered in the Search Text Box and the result of the search has been displayed. You can see that several occurrences have been found in a number of different contexts. You can scroll through the Search Results List, clicking on different items to see their details displayed below in the Classes List and Members of List. Here the user is interested in the use of "offset" in relation to the "Range" object so this entry is highlighted. As a result, "Range" is highlighted in the Classes List and "offset" is highlighted in the Members of List. To go straight to help on the selected item click the Help Button or press [F1]. The links in the Details Pane let you jump to the class or library to which the selected item belongs.

Browse a Class
You might want to explore what can be done with the Range object. Find "Range" in the Classes List. The lists are arranged alphabetically, but to save scrolling, a quick way to find your chosen word is to click anywhere on the list and start typing. The list will scroll by itself as you type.

When you find the item, click on it and see all its associated properties, methods, events, and constants (i.e. its "Members") displayed alongside in the Members of List.

What Do the Icons Represent?
In the Object Browser and elsewhere (e.g. Auto List Members) the Visual Basic Editor uses icons alongside keywords to indicate what they are:

Good Practice in Code Writing
A little extra care in laying out and presenting your code pays dividends when you, or someone else, later comes to read and perhaps edit it.

Indenting Text
It is good practice to indent blocks of code. This makes code considerably easier to read and understand (although it makes no difference to how the code runs). Pressing the [Tab] key indents a line by 4 spaces. Compare a procedure where code has not been indented with the same procedure where indentation has been used to mark loops and If statements (Fig. 44). Which do you think is easier to read and understand? How much or where you indent code is up to you. A discrete section of code, such as a complete If Statement has its contents indented to make it easier to read. If another If Statement is "nested" inside the first, it too is indented and its contents indented a further step, and so on. Code inside a loop is also usually indented. You can indent a several lines of text at once by selecting them and pressing the [Tab] key. Code can be "outdented" (moved to the left) by selecting it and pressing [Shift]+[Tab]. The Edit toolbar has buttons for indenting and outdenting blocks of code (Fig. 45).

Indenting code can make it easy to read, understand and edit later. The illustration below demonstrates the logic of this (Fig. 46).
 * 1) The start and end lines of the procedure sit against the left margin and are not indented.
 * 2) All the code between the start and end lines is indented one step.
 * 3) This code is inside a For...Next loop so it is indented another step.
 * 4) This code is inside an If Statement so it is indented a further step.

{clear}

Breaking a Line of Text
There is effectively no limit to how long a code statement (a single line of code) can be. The code window will continue to scroll to the right to accommodate your typing, but to facilitate reading, it makes sense to break lines of code so they fit inside the code window. Do not break a line of code simply by pressing enter. Each code statement should make sense and be fully self-contained. Physically splitting a statement on to another line will almost certainly result in an error. Instead use the "line break character" (a space followed by an underscore) to tell the Visual Basic Editor that from that point the statement continues on the next line. The message box code statement in the illustration below (Fig. 47) is shown as a single line (top) necessitating scrolling right to read the whole line; and as multiple lines broken using the [SPACE]+[UNDERSCORE] line break character (bottom) so that the entire statement is visible without scrolling. Note also that if text strings are broken in this way they must be joined (or "concatenated") using an ampersand (&) as shown on the first line of the lower example above (Fig. 47). Each section of text must be enclosed by a pair of quote marks. Breaking lines of code has no effect on its performance. Although they look very different in the Code Window, these two code statements generate identical message boxes (Fig. 48). Fig

Adding Comments to Code
It is good practice to annotate code by adding "comments". These notes can be placed anywhere in a procedure and can be used to explain what a procedure or function does, mark places where particular events happen, or even to credit the author and supply contact or copyright information. Comments can be very helpful reminders when returning to code that you wrote a long time ago, or when you are trying to decipher code that another developer has written. It is important that the code compiler (the engine that runs the code) knows that your comments are not themselves code that has to be executed. For this reason, begin each comment line with an apostrophe ('). When you do this the Visual Basic Editor automatically colours that line of text green. When you type an apostrophe in the Code Window everything following the apostrophe on the same line is "commented out", the text coloured green, and ignored by the code compiler. You can use the comment facility to temporarily disable one or more lines of code when you are testing your work. Comment lines can be inserted between lines of executable code or at the end of a line. The Edit toolbar provides buttons (Fig. 49) for commenting and uncommenting (i.e. removing the apostrophes from) code. These allow multiple lines to be commented or uncommented in one step and are very useful when working with large blocks of code. The illustration below demonstrates a number of ways in which comments can be used:

Running Code from the Visual Basic Editor
When you run procedures from the controlled environment of the Visual Basic Editor, stepping through the code and perhaps watching the values of variables and studying the effects, it is known as Debugging. Most of the tools you use to do this are located on the Debug menu or toolbar. Debugging your code is an important stage in the development of your work helping you understand and control what goes on when your code is executed.

Starting and Stopping Procedures
Procedures can be run directly from the Visual Basic Editor window. The Visual Basic Editor's Run menu has commands to Run, Break and Reset a procedure. These commands are also represented by toolbar buttons (Fig. 51) although the Break button in not shown by default. To run a procedure from the Visual Basic Editor click anywhere between the "Sub..." and "End Sub" lines of the procedure and click the Run button. You can also use the convenient keyboard shortcut [F5] to run a procedure. It is sometimes necessary to pause (or "Break") a procedure during its execution (e.g. when you have forgotten to tell a loop when to stop!). Either click the Break button or use the keyboard shortcut [Control]+[Break]. When you give the Break command the Visual Basic Editor (or the host program) displays a message asking what you want to do next (Fig. 52). Clicking the default button, Debug, returns you to the Visual Basic Editor displaying the Code Window for the module in which the procedure is located, and indicating the line of code at which the procedure is stopped (Fig. 53). The procedure is left in break mode. Clicking the Continue button resumes the procedure from the point at which it stopped, taking it out of break mode. Clicking the End button aborts the procedure. NOTE: If a procedure "crashes" in use the user will see a similar message. The safest thing for them to do is click the End button, but most often users click Debug because it is highlighted and their experience tells them that the highlighted button is usually the right one to choose! As a result they find themselves looking at the Visual Basic Editor and don't know where they are or what to do next. This is one reason why it is important to include error handling in you VBA procedures. When the execution of a procedure is paused the Visual Basic Editor goes into "Break Mode". The procedure is still running but paused. The line of code at which the procedure is paused is shown in the Code Window by a yellow highlight and yellow arrow on the left margin of the window (Fig. 53). The line that is highlighted has not yet been executed, and will be the first line to be executed if the procedure is restarted. In the event of a procedure crashing, the highlighted line is the one that could not be executed, and therefore caused the procedure to crash. The current status of the Visual Basic Editor is displayed in its title bar alongside the name of the VBAProject. During the course of a procedure the VBAProject name is followed by [running] or by [break] if the procedure is halted and in break mode (Fig. 54). Having paused a procedure (i.e. put it into break mode) you have the choice of restarting it or aborting the procedure. To restart the procedure click the Run button again (or press [F5]). To abort the procedure click the Reset button. When a procedure is in break mode, the yellow arrow that denotes the break point can be dragged up or down the margin to reposition the break point so that the code can be restarted at a different place if required (Fig. 55). If you move the break point, you should consider what effect the procedure will have if certain lines of code are omitted or repeated before you decide to restart the procedure.

Stepping Through a Procedure
When developing VBA code it sometimes helps to step through a procedure a line at a time and watch the effect of each statement. You can even tile the Visual Basic Editor window and the host application window on the screen so that you can watch both without having to switch back and forth between them. The Debug menu of the Visual Basic Editor (and the Debug toolbar) have a number of useful tools to help you do this (Fig. 56).

Step Into
This command moves through the code a statement at a time, highlighting the next statement yellow (as in break mode). To start stepping through a procedure place the cursor anywhere in the text of its code between the Sub... and End Sub lines and press the [F8] key (or click the Step Into button on the Debug toolbar). This highlights the first line of the procedure. Repeat the command to move through the code one statement at a time.

Step Over
Whilst stepping through a procedure you may come to a VBA function or a call to another procedure. If you continue to use the Step Into command ([F8]) the Visual Basic Editor will take you to that function or other procedure and proceed to step through it. If you don't want to do this, but nevertheless want the function or called procedure to run, use the Step Over command ([Shift]+[F8]). Make sure that the statement containing the function or the call to the other procedure is highlighted before giving the Step Over command.

Step Out
Use the Step Out command ([Control]+[Shift]+[F8]) to finish executing the current procedure in one step (i.e. execute all the remaining statements). If you are in the process of stepping through a function or called procedure the Step Out command will cause that process to be completed, returning you to the next statement of the host procedure.

Run To Cursor
This command is not normally shown on the Debug toolbar but can be found on the Debug menu. Using the Run To Cursor command ([Control]+[F8]) works like setting a break point in the code. You may wish to run your procedure up to a certain statement, then pause at that point (then perhaps continue a statement at a time). Place the cursor in the line at which you want code execution to stop, then give the Run To Cursor command. The code will run as far as that line then stop in break mode, without executing the statement, and highlight the statement yellow.

Setting Break Points
A procedure can be made to pause at a specific statement by the addition of a Break Point. You can insert as many break points into a procedure as you wish. They allow you to step through a procedure in the same way as described above, but each step can be as many lines as you want. To create a break point click on the grey bar at the left side of the Code Window, adjacent to the code statement at which you want the code execution to halt. When you do this a red dot appears on the bar and the line of code is highlighted red (Fig. 57). Break points can be added or removed before or during (if in break mode) the execution of a procedure. To remove a break point click the red dot, click the Toggle Breakpoint button on the Debug toolbar (Fig. 58), or place the cursor in the code statement and press the [F9] key. When a procedure contains one or more break points, it can be started in the usual way, either from the Visual Basic Editor or the host program, or called from another procedure. When the code execution reaches the break point it halts and enters break mode, the next statement to be executed is highlighted in yellow (Fig. 59). To restart the procedure click the Run/Continue button or press F5. The code will run as far as the next break point or to the end of the procedure if there are no more break points. In the example shown above (Fig. 59) break points have been inserted at the end lines of loops in the code. This causes the code to pause after each circuit of the loop.

Reading Values in Break Mode
When a procedure is in break mode you can read the values of variables, and the calculated values of functions, by placing the cursor over the variable or function name. The current value is displayed as a tool tip (Fig. 60).

The Locals Window
The Locals Window has the ability to display the changing values of variables in a procedure whilst the procedure is running but paused in break mode. Each variable in the procedure is listed and its current value and data type shown (Fig. 61). Display the locals window by choosing View > Locals Window or by clicking the Locals Window button on the Debug toolbar (Fig. 62). You can study the progress of a procedure by stepping though it and watching as the Locals Window displays changes to variables as you move from line to line, or by setting break points and checking the values of variables each time the procedure halts.

The Watch Window
The Watch Window is often used in conjunction with the Locals Window. Its function is to "watch" the value of one or more variables, like the Locals Window, but has the added ability to include an expression related to those values.

Display the watch window by choosing View > Watch Window or by clicking the Watch Window button on the Debug toolbar (Fig. 63).

For example, you might want to ascertain whether a certain expression evaluates to True, e.g. has a particular variable reached a certain value yet. It can use an expression to calculate values based on variables (rather than just look at the variable's own value) and display those calculations whilst the procedure is paused in break mode. To use the Watch Window, first add a "watch" by right-clicking on the procedure in the Code Window or in the Watch Window itself and choosing Add Watch. Enter an expression in the Add Watch dialog (Fig. 64), choose a Watch Type and click OK. You can step through the procedure or halt it using a break point and check the value of the watch in the Watch Window. It is more usual, however, to use a watch to halt a procedure by putting it into break mode when the watch expression evaluates to True. The illustrations below show the appearance of the Watch Window (Fig. 65) and the Locals Window (Fig. 66) when the procedure was halted by the watch. The watch expression, lngCounter>1000, caused the procedure to be halted when the value of the lngCounter variable exceeded 1000. Fig. 65 The Watch Window showing a watch at the point when the procedure halted Note that in the Watch Window (Fig. 65) the expression shows a value of True, which is confirmed by the value of 1001 shown against the lngCounter variable in the Locals Window Fig. 66

The Locals Window showing the value of variables when the procedure halted There is also a Quick Watch command that can display the value of a variable or expression for which you haven't created a watch. The information is displayed as a dialog (Fig. 67), and offers the option of saving the watch to the Watch Window.

To use this tool place the cursor on the code variable expression you want to examine and click the Quick Watch button on the Debug toolbar (Fig. 68) or press Shift+F9.