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

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.

Fig. 34 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).

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.

Fig. 36

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. Fig. 37

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

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.