18 Working with VBA

,

18.1 Getting help

There are several ways you can get help while working in the VBA editor. You can:

This will open a help window as shown in figure 18.1.

Figure 18.1   The Excel Help dialogue.

In this window you can type in the name of an object, method, property etc. in the text box in the upper left corner and then click the Search button.

The fastest method though, is to click in a word and press F1. You will then get help on how it’s used.

Intellisense

As you are writing VBA code in the editor window you’ll notice that there is something else going on. This is Intellisense in action. Intellisense is Microsoft’s implementation of autocompletion, i.e. it can complete the words or phrases you are typing before you have typed them completely. Here are a few things that Intellisense will do for you.

Create a subroutine

Intellisense will create an empty subroutine for you if you just type sub followed by a name (table 18.1).

Table 18.1 Adding End Sub

When you write this and press Enter	You get this
sub Name	Sub Name()
	End Sub

Complete variable declarations

When declaring variables you do not have to write the complete declaration. Just write enough so that Intellisense can figure out what you’re trying to achieve. A few examples are given in table 18.1.

Table 18.2 Filling in the data type

When you write this and press Enter, Tab or Space	You get this
dim s as str	Dim s As String
dim i as in	Dim i As Integer
dim si as sin	Dim si As Single
dim d as dou	Dim d As Double

Display methods and properties for an object

When you write the name of an object followed by a dot, Intellisense offers you a choice of methods and properties you can use automatically. You can now select the method or property you want to use, either by clicking on it or by using the arrow keys and Tab (figure 18.2).

Figure 18.2   Displaying a list of methods and properties.

Show you the names of arguments and enumerations

Whenever you try to use a method, Intellisense will show you a list of the arguments required (figure 18.3).

Figure 18.3   Showing a list of arguments.

It can also show you a list of constant values to choose from, as is the case for the Weight argument (second argument in method) (figure 18.4).

Figure 18.4   Displaying a list of constants.

Object Browser

The Object Browser is a handy tool that allows you to view all objects, methods, properties, events and more for the components in the type libraries you are using in your application. You open the Object Browser window shown in figure 18.4 by pressing F2 or by clicking the Object Browser button on the VBA toolbar.

Figure 18.5   The Object Browser dialogue.

First we select the type library we’re interested in from the combo box in the upper left corner. For our part it is probably the Excel and VBA libraries that are most interesting, showing objects, methods, properties and events in Excel’s object hierarchy and VBA respectively. In the left pane are shown all the relevant objects. In the right pane you’ll see a list of all the methods and properties available for the selected object. Methods are shown with a green icon, properties with a hand holding a sheet and events with a yellow lightning. If you click on one of those you will get detailed information in the bottom pane of the window, for example the type and number of arguments a method takes.

You can display a help topic by clicking an item and then pressing F1, or by clicking the Help button in the upper-right corner of the dialogue.

To search for an object, method, property or event you can type in a name in the second text box in the upper-left corner and then press Enter or click the Search button.

18.2 Errors in VBA

Even if we do our best to make error-free programs, we are rarely able to do this (especially if it is a large and complex program). Errors are very common and they are called bugs. The name originates from the early days of computing, where an insect was found in the circuitry of the computer causing it to fail. The process of removing errors from the program is called debugging.

There are three kinds of errors – syntax errors, run-time errors and logical errors. Among these the syntax errors are the easiest to find because the compiler will do it for us. To find the other errors we often have to do a thorough testing of the program. Let’s start by looking at the syntax errors.

Syntax errors

Syntax errors occur when we violate the grammar rules of the language (VBA). Typical examples are that we misspell one of the reserved words, or we forget the Then clause in an If…Then statement, or we use an operator on the wrong kind of operands. Here are a handful of samples:

Here we get an error message on the last line because we’re using a variable that is not declared (which we must do when Option Explicit is used). The error is of course that we have misspelled sum as summ.

This next line gives an error because we have written Strnig instead of String.

Dim name As String, address As Strnig, birthplace As String

VBA will also help finding syntax errors as we’re typing in our program. If we type the following program and hit the Enter key, VBA will display the error message shown in figure 18.6 because we have forgotten to add the Then clause in the last line.

Figure 18.6   Syntax error message.

If we do not want VBA to show error messages like this, we can turn off auto error checking under Tools → Options → Editor. A line with a syntax error will then be coloured red instead.

Run-time errors

Run-time errors are detected by the computer during the execution of the program. They occur when the program tries to make the computer do an illegal operation such as divide a number by 0, open a file that does not exist, access an array with an index outside of the legal range. To prevent such errors from happening we can do some extra checking. For example, we can check that the denominator is not zero before we try to make a division, we can check that a file exists before we try to read data from it, and so on. We can also add error handlers to our program to make it act more elegantly should a run-time error happen (see next chapter).

The code below gives no error if the user types in an integer and press the Enter key.

Figure 18.7   Run-time error message.

If the user instead presses the Cancel key we get the run-time error shown in figure 18.7.

The reason is that the return value from the InputBox function cannot be stored in a variable of type Integer. Clicking on the End button will end the program. Clicking on Debug will highlight the line in the program code that caused the error. It will be shown in yellow. We can then correct the error and continue the execution (F5).

Logical errors

The last kind of error is the logical error. This is an error caused by a faulty algorithm, i.e. the program is not solving the problem correctly. Here’s a very simple example:

This program is supposed to multiply the numbers from 1 to 10 and write the product in cell A1, but the answer is 0. What is wrong? After investigating the matter we see that we have forgotten to initialize the sum variable. If we do not assign a value to a local variable of type Long it will automatically be initialized to 0. Therefore sum will always be 0 no matter how many repetitions we have. The solution is of course to assign 1 to sum.

Logical errors may sometimes be hard to detect. Repeated runs of the program with a variety of input data are therefore essential to make sure that we have a correct program.

18.3 Testing and debugging

As soon as you have written the program and it runs without any syntax errors you need to start testing and debugging. In this section we’ll discuss some of the debugging resources available in VBA and the Visual Basic Environment, and how you can use them to find errors. This is just enough to get you going. See online help for more information.

Immediate window

One of the simplest methods is to put output statements in your code so that it displays extra information while it is running. The output is shown in the Immediate window. It is usually found in the lower right part of the screen. If it’s not there it can be opened by pressing Ctrl+G or by selecting Immediate window from the View menu. See figure 15.2.

To display variables or other messages in the Immediate window you use Debug.Print statements in your program:

You can also enter commands in the Immediate window. The following line puts the value 1000 in cell A1:

Remember to press Enter after you have written the command.

We can show the contents of variables and other information by using the? command. To show or change the contents of variables the program must have reached a breakpoint (see below). Once the program has reached a breakpoint you can show the value of a variable age with

and change its value with

Stepping through code

Another method we can use is to step through the code line by line. We do this by pressing the F8 key. The next line that will be executed is highlighted in yellow. While doing so we can place the mouse pointer over a variable name to see which value the variable has at this point. In this way we can check that the variable updates correctly as we step through the algorithm. Besides using F8 to step to the next line there are also some other options (table 18.3).

All of these options are also available from the Debug menu. Stepping over a subroutine call is relevant when you have a program with more than one subroutine, and you are confident that a particular subroutine is OK. Before you select Run to cursor you must remember to place the cursor on the actual line.

Table 18.3 Debugging shortcut keys

Press	To
F8	Jump to the next line
Shift+F8	Step over a subroutine call. The statements in the subroutine will be executed, and the next line after the subroutine call will be highlighted.
Ctrl+F8	Run to cursor
Shift+Ctrl+F8	Jump out of the subroutine
F5	Run the rest of the program or up to the next breakpoint

It is also possible to drag the yellow arrow shown in the margin up or down to select the next line that will be executed.

Breakpoints

The next method we’ll look at is breakpoints. A breakpoint is a line in the code where execution stops. In the VBE we place a breakpoint on a line by clicking in the grey margin. A red circle will be shown. To remove the breakpoint just click the red circle again. Alternatively, you can press F9 or select Toggle Breakpoint from the Debug menu to place/remove a breakpoint on a line.

Figure 18.8   Line with a breakpoint.

Now, how does this work? You run the program by hitting the F5 key. When the execution reaches a breakpoint it stops and the line with the breakpoint is highlighted in the VBE. The line is not executed yet. At this point the program is in break mode, and you can look at or change variables in the Immediate window. To execute the line and resume program execution just hit F5 again (or maybe F8 to step into it line by line).

Instead of setting breakpoints we can write a special Stop statement directly in the code. This has the same effect as setting a breakpoint on this line.

Assertions

If you want your program execution to enter break mode only if some condition is satisfied, you can write an assertion statement in your program. The syntax is:

If condition is False then VBA breaks on that line. Here is an example on how use the statement:

The Debug toolbar

A special debug toolbar contains buttons for easy access to the commands described above. If not shown, it is available from the View → Toolbars menu.

Hold the cursor over a button to show tooltips. This requires that the Show ToolTips option in the General tab of the Options dialogue is checked. The Options dialogue is found under the Tools menu.

Figure 18.9   The Debug toolbar.

Comment out code

A simple way to test and debug your program is to comment out sections of your code that are not relevant to what you’re trying to figure out at the moment. Commenting out large sections of code can be done by selecting the text and then clicking the Comment Block button. It’s found on the Debug toolbar. To uncomment the code, just click the Uncomment Block button. If no text is selected, then the comment character is added or removed in the line where the pointer is located.

18.4 Dialogue boxes

Dialogue boxes are used in almost every Windows-based application. They’re used for such things as:

In VBA we have access to a number of predefined dialogue boxes that we may use in our programs. If they’re not what we need, VBA also lets us define our own dialogue boxes (called UserForms). UserForms are described in chapter 21.

Message boxes

The message box is a simple dialogue box used to convey a message to the user. A certain interactivity is also built in as the dialogue has one or more command buttons. Figure 18.10 shows a simple message box. Message boxes are created and displayed with the function MsgBox in VBA. The syntax is:

A description of the parameters is given in Table 18.4.

Table 18.4 MsgBox parameters

Parameter	Meaning
prompt	The message displayed in the dialogue box. Required.
buttons	The number and type of buttons to display, and the icon style to use. It also sets the modality of the dialogue box and the identity of the default button.Optional. The default value is 0.
title	Optional. The text in the title bar of the dialogue.
helpfile	A string identifying the help file used to provide context-sensitive information to the user. Optional. If it is specified then context must also be specified and vice versa.
context	A context number specifying where in the help file the relevant information is found. Optional.

All parameters except prompt are optional. If they’re omitted we get a message box with an OK button and the title “Microsoft Excel”.

Here are some examples (see figures 18.10 and 18.11).

Figure 18.10   VBA message box.

Let’s remove the OK button and instead add Yes and No buttons. An icon and a title are also added.

Figure 18.11   VBA message box with Icon.

The predefined constant vbQuestion will display an icon in the box (a question mark). Other options are vbInformation (the letter i), vbExclamation (an exclamation mark), and vbCritical (white cross in a red circle). Use whatever you find most appropriate. vbYesNo will add the Yes and No button to the box. If more than one constant is used they must be Or’ed together with the Or operator. It can only be one constant for an icon and one constant for the buttons. For buttons the most important choices are vbOK (an OK button), vbOKCancel (OK and Cancel buttons), vbYesNo (Yes and No buttons), vbYesNoCancel (Yes, No and Cancel buttons), and vbRetryCancel (Retry and Cancel buttons).

If we take a closer look at the message box in figure 18.11 we see that the Yes button is outlined, meaning that it is the default button. Pressing the Enter key on the keyboard will have the same effect as clicking the button. In this case this seems quite OK, but what if you want the No button to be the default button? All you have to do is Or another constant to vbYesNo. Pick one of vbDefaultButton1, vbDefaultButton2, vbDefaultButton3, or vbDefaultButton4. For a dialogue box with two buttons only, vbDefaultButton1 and vbDefaultButton2 are applicable. If you want the No button (the second button from the left) to be the default button you write:

Figure 18.12   VBA message box with second button as the default button.

In a message box where there is more than one button the program may want to know which one the user has clicked. The MsgBox function will return a value indicating which button was clicked. We can use an If…Else…Then statement to test for this value. If the user has clicked the Yes button the returned value will be vbYes. Other possibilities are vbNo, vbCancel and vbRetry.

If the prompt shown in the message box is very long and needs to be shown on several lines, we may use a string variable to format the text before we display it in the dialogue.

The maximum length of the prompt is approximately 1024 characters.

Input boxes

Input boxes are used to get input from the user. A typical example is shown in figure 18.13.

Figure 18.13   VBA input box.

The dialogue box has a label used to display a message to the user, a text box where the user can type in a value (string), and two command buttons.

To display an input box in VBA you call the InputBox function, which has the general syntax

The meaning of the parameters is given in table 18.5.

Table 18.5 InputBox parameters

Parameter	Meaning
prompt	The message displayed in the dialogue box. Required.
title	Optional. The text in the title bar of the dialogue.
default	Optional. A value shown in the text box. Used as default value if no input is provided.
xpos	Horizontal distance from the left edge of the screen. Measured in twips (1 cm = approx. 567 twips). Optional. If omitted the dialogue is horizontally centred.
ypos	Vertical distance from the top of the screen. Measured in twips (1 cm = approx. 567  twips). Optional. If omitted the dialogue is placed about 1/3 of the distance down the screen.
helpfile	A string identifying the help file used to provide context-sensitive information to the user. Optional. If it is specified then context must also be specified and vice versa.
context	A context number specifying where in the help file the relevant information is found. Optional.

Only the prompt parameter is mandatory. The InputBox function returns a string representing the value typed into the text box. The following example lets us type a name into the input box.

Figure 18.14   VBA input box.

Excel also has an input box that can be used from VBA (see table 18.6 and figure 18.15). This method has several advantages compared to the VBA InputBox:

The syntax for calling the Excel input box is:

Table 18.6 Excel InputBox parameters

Parameter	Meaning
Prompt	The message displayed in the dialogue box. Required. It can be a number, date, string or Boolean. It is converted to a string before it is shown in the dialogue.
Title	Optional. The text in the title bar of the dialogue. If omitted the title will be “Input”.
Default	Optional. A value shown in the text box. Used as default value if no input is provided. This may be a cell range.
Left	Horizontal distance from the left edge of the screen. Measured in points (1 point = 1/72 inch = approx. 0.035 cm). Optional.
Top	Vertical distance from the top of the screen. Measured in points (1 point = 1/72 inch = approx. 0.035 cm). Optional.
HelpFile	The name of the help file used to provide context-sensitive information to the user. Optional. If it is specified then context must also be specified and vice versa.
HelpContextID	A context number specifying where in the help file the relevant information is found. Optional.
Type	Optional. Specifies the datatype of the value. If omitted, the dialogue returns a string. Possible values are 0 (a formula), 1 (a number), 2 (a string), 4 (a logical value, True or False), 8 (a cell reference, as a Range object), 16 (an error value, such as #N/A), and 64 (an array of values).

Only Prompt is required. The return type is Variant. For more information, please see Excel help. A couple of examples will show you how to use this function.

Figure 18.15   Excel input box.

If we click on the OK key we’ll get an error message, since “skinny” is not a valid number.

Type in a number and try again.

The function can also be called by using named arguments, like this:

Named arguments are explained in chapter 19.

Figure 18.17 shows how we can select a range in a worksheet and add the reference into an input box.

Figure 18.16   Validation error.

Figure 18.17   Using Excel’s input box to select a range.

Opening and saving files

Among the built-in dialogue boxes are the standard Open and Save As dialogue boxes that you can find in all Microsoft Office applications (figure 18.18). They can be used if you want to open or save a workbook from VBA code. Let’s see how we can allow the user to select one or more files and to open these files. The slightly modified example is taken from Excel help:

Figure 18.18   The File Open dialogue.

Application.FileDialog will create the dialogue box shown above and return a FileDialog object. The property takes a single argument, and this argument will determine the type of dialogue box displayed. There are four options (table 18.7).

Table 18.7 Constants used as argument to Application.FileDialog.

Constant	Dialogue
msoFileDialogOpen	An Open dialogue box – lets users select one or more files that you can then open in the host application using the Execute method.
msoFileDialogSaveAs	A SaveAs dialogue box – lets users select a single file that you can then save the current file as using the Execute method.
msoFileDialogFolderPicker	A Folder Picker dialogue box – lets users select a path. The path that the user selects is captured in the FileDialogSelectedItems collection.
msoFileDialogFilePicker	A File Picker dialogue box – lets users select one or more files. The file paths that the user selects are captured in the FileDialogSelectedItems collection.

Application.FileDialog does not show the dialogue. To do that you must use the Show method. Once it is displayed, no code executes until the user dismisses the box. If the user clicks the Open button Show will return –1, if the Cancel button is clicked 0 is returned.

The For Each loop will step through each selected file and perform the Execute method on the file. The Execute method opens the file.

The Save As dialogue box lets users save the active workbook with a selected filename. The filename is read from the dialogue and it is saved using the Execute method.

The only thing that is changed from the OpenFiles example is that we’ve replaced the constant msoFileDialogOpen with msoFileDialogSaveAs. Also the For Each loop is removed since we’ll only be saving a single file. The displayed dialogue is shown in figure 18.19.

Figure 18.19   The File Save dialogue.

Problems

18-1.  The following erroneous program should write the numbers 1 to 10 in cells A1:A10, and the product of all the numbers in cell A11. Change the program so that it will work as intended.

18-2. A prime number can be divided evenly only by 1 or itself. The ListPrimes subroutine prints all prime numbers in the range 0 to N in the Immediate window. Unfortunately, the program has some logical errors, so the output is not correct.

Using the techniques described in this chapter, do the following: