Lesson 8 : VBA UserForms and ActiveX Controls

In this Lesson, we will examine...

UserForms

The Option Button Control

The Scroll Bar Control

The Frame Control

The RefEdit Control

The MultiPage Control

The List Box and Combo Box Controls

Using worksheet as a container

You can add ActiveX controls, such as buttons, option buttons, list boxes and so on, on worksheets, chart sheets or userforms - not on a module. Placing controls on a userform creates a custom dialog box. Controls on worksheets and charts have the benefit of being closely tied to the data they use; custom dialog boxes are best when you want to use the same set of controls with a number of different worksheets – you retain generality and extend the ability of VBA programmers to collect user input.These VBA objects are invaluable for adding custom user interfaces to your applications. We have so far collect input from the user via dialog boxes using InputBox() and MsgBox() functions. Now its time to make our office application more sophisticated and user friendly by using the range of ActiveX controls provided by Excel VBA
Working with user forms, for me is the most interesting and fun part of VBA application development. When designing the form, you need to know 3 things

a) Which ActiveX controls are available.

b) How to place, resize and line up controls on a user form.

c) How to give controls properties in the Properties Window

Apart from using UserForm to interact with users, I’ll also show you how you can use a worksheet as a control panel.

1) Adding a Form to a Project

To add a form to a project, activate the VBE and select Insert/UserForm from the menu bar in the VBA IDE as shown in Figure 8.1 below…

Fig 8.1 - Click for larger image

2) Components of the UserForm Object

Just like you select a Worsheet properties, when you select a form, its properties appear in the Properties window in the VBA IDE (see Figure 8.1). Table 8.1 lists some of the properties of the UserForm object that you will commonly set at Design Time.

Selected Properties of the Userform Object

Table 8.1 - Click for larger image

Forms represent separate entities in a VBA project and have their own code window. To view the code window (module) double click on the UserForm object, or hit F7 (all with the form selected).

Just as you have guess, the structure of a form code window or module is the same as any other module window. Click on the dropdown box on the upper-left corner will display all objects contained within the form, including the UserForm object. Here you will also find a general declarations section for making module level declarations in the form module.

Whereas the upper-right corner contains a dropdown list of all event procedures associated with the various objects that may be contained in the form. An example code window and properties for a form is shown in Figure 8.2.

Fig 8.2 - Click for larger image

Generally, the behavior of variables and procedures for UserForm are identical with other Excel objects we discussed before. The declaration statements Dim, Private, and Public keywords in a form module are identical to that of an object module as discussed in Part C and D. For example, the scope of variables and procedures declared as Public in the general declarations section of a form module are global just like any other the Excel objects discussed before.

The UserForm object has several event procedures, including Click(), Activate(), and

Initialize() among others. To view the full list of event procedures of the UserForm object, select the UserForm object in the object dropdown list and then select the event procedure dropdown list from the form module (see Figure 8.2). Some of these event procedures should be familiar, as they are common to several ActiveX controls. Table 8.2 lists a few of the more commonly used event procedures of the UserForm object.

Table 8.2 - Click for larger image

3) How To Add ActiveX Controls to a Form

Like the Worksheet object, the UserForm object is a container object. It is used to hold other objects. When a form is added to a project, the Control Toolbox should automatically appear (see Figure 8.1). If the Control Toolbox does not appear, select View/Toolbox from the menu bar.

You use the tools in the Control Toolbox to add controls to your UserForm. Just click the desired control in the Toolbox and drag it into the dialog box to create the control. After you add a control, you can move and resize it by using standard techniques.

When added to a form, you access the properties of an ActiveX control via the Properties window and you access event procedures associated with ActiveX controls via the form module that contains them. Table 8.3 shows the type of ActiveX control and what they do.

Table 8.3 - Click for larger image

Let’s do a simple exercise (Example A): A Label, Command button and TextBox are used.

i) From the VBE insert a form into a new VBA project.

ii) On the Properties Window, change its Caption to “Hello” (see Fig 8.3)

iii) Add a Label and CommandButton controls to the form and change their Name properties to something meaningful like lblOutput and cmdHello (see Fig 8.4 and Fig 8.5)

iv) Change caption to “Click Me” for CommanButton and delete the Label caption.

v) Add a TextBox and name it to txtEnter.

vi) Adjust the size and appearance of all the control to suit your taste.

vii) Next double click on the CommandButton control to activate its Code Window

viii) Select the Click() event on the upper-right dropdown list.

ix) Finally enter the code below to the Click() event procedure. (see Fig 8.6)

Example A:

Private Sub cmdHello_Click()

lblOutput.Caption = “Welcome ” & txtEnter.Text

End Sub

With the above procedure, user will enter his/her name in the textbox. After that if the user clicks the CommandButton control name cmdHello, the click event procedure is triggered, and the Caption property of the Label control named lblOutput is changed to “Welcome” follow by the name the user entered in the text box.(see Fig 8.7)

Fig 8.3 - Click for larger image

Fig 8.4 - Click for larger image

Fig 8.5 - Click for larger image

Fig 8.6- Click for larger image

Note that (see Figure 8.6) on the upper-left of the dropdown list on the control module list all the ActiveX controls added to the UserForm.

To test the application, select the form and click on Run/Sub UserForm on the VBE standard toolbar or menu bar, or press F5 on the keyboard. The form appears as a window above the Excel application. Click the Click Me button to output the simple message to the Label control. To close the form, simply click on the X in the upper-right corner of the window.

Fig 8.7- Click for larger image

I suggest you use these naming convention for all the controls:

i) Label = lbl
ii) Textbox = txt
iii)Combobox = cbo
iv) ListBox = lb
v) CheckBox = chk
vi) OptionButton = opt
vii) RefEdit = rfe
viii) UserForm = frm

4) Showing and Hiding Forms

To load a form from the Excel application, use the Show() method of the UserForm object in a procedure that can be triggered from Excel VBA.The basic syntax follows:

UserFormName.Show [Modal]

For example, the following code displays a UserForm object in Example when the Click() event procedure of a Command Button control named cmdDisplayForm is triggered. The Command Button control can be added on a worksheet or another form.

Private Sub cmdDisplayForm_Click()

UserForm1.Show

End Sub

To hide a form from the user but retain programmatic control, usel the Hide() method of the UserForm object.

UserForm1.Hide

Note that the Hide() method does not remove the UserForm object from system memory, thus the form and its components can still be accessed programmatically. To remove a form completely from system memory, call VBA’s UnLoad() method.

UnLoad UserForm1

5) Designing Custom Dialog Boxes Using Forms

We have discussed in Example A, the ActiveX controls Label, TextBox and CommandButton to collect user input. As you can see, using the userform and the 3 controls has expand the capabilities of forms well beyond that of the InputBox() and MsgBox() functions.

There are a few more ActiveX controls that I am going to show you below.

a) The Frame Control

The function of the Frame control is to groups ActiveX controls on a form especially when the ActiveX controls grouped are related by content, or in the case of Option Button controls, be made mutually exclusive (see Fig 8.8).

The properties of the Frame control are seldom referenced in code. We usually set the Name and Caption properties along with a couple of appearance properties (Caption, Font, Height etc.) at Design Time.

You rarely access the Frame control programmatically. However, Frame control are often use to organizes or groups controls on a form for aesthetic appearance; in the case of Option Button controls, behavior.

Fig 8.8- Click for larger image

b) The Option Button Control

Option Button controls are added to the userform as a group. It is common practice that we group all the Option Buttons with a Frame control. So no matter how many Option Button controls are added to the form only one can be selected at any given time.

When the user selects an Option Button from a group of Option Buttons, Excel VBA
will automatically deselect a previously selected Option Button. We will look at an example below to clarify this concept.

c) The RefEdit Control

RefEdit control is use to enable user select a range of cells from a worksheet for some specific task. Many Excel’s dialogs and wizards contain RefEdit controls, like the Goal Seek dialog box shown in Figure 8.9

Fig 8.9 - Click for larger image

When user select a range from an existing Excel worksheet, the textual reference for the selected range is automatically entered into the edit region of the control. You can also enter the range manually by typing in the text area of the control.

Let’s look at an example to illustrate the combination of using the RefEdit control, OptionButton, Label, Frame, CommandButton, TextBoxt controls.

Example B:

i. Insert a UserForm (see Figure 8.10)

Fig 8.10 - Click for larger image

ii. This dialog box has nine controls. Set the properties according to Table 8.4.

Table 8.4 - Click for larger image

The next set of steps makes executing this procedure an easy task: Double-click the Calculate button and enter the following Code:

Private Sub cmdCalc_Click()

Dim myRange As Range

Set myRange = Range(rfeInput.Text)

If optAverage.Value = True Then

lblResult.Caption = "Average ="

txtResult.Value = Round(Application.Average(myRange), 2)

ElseIf optMax.Value = True Then

lblResult.Caption = "Maximum ="

txtResult.Value = Application.Max(myRange)

ElseIf optMin.Value = True Then

lblResult.Caption = "Minimum ="

txtResult.Value = Application.Min(myRange)

End If

End Sub

Double-click the Close button and enter the following Code…

Private Sub CloseButton_Click()

Unload UserForm3

End Sub

iii. Activate a Excel worksheet and name it “Example_C”

iv. Fill a Range on the Worksheet with random numbers

v. Go back to VBE and select UserForm3

Double click on UserForm3, select the Initialize and enter the following code:

Private Sub UserForm_Initialize()

Worksheets("Example C").Activate

rfeInput.SetFocus

End Sub

The above code is to ensure the Worksheets("Example C") is active and the focus is on the RefEdit control.

vi. Run the program.(Run/Sub UserForm on the VBE)

First you select a range on the worksheet. The textual reference for the selected range is automatically entered into the edit region of the control. You can also enter the range manually by typing in the text area of the control.

After that you select which option you want to calculate by clicking on one of the option buttons. Finally you click on the Calculate button to start calculating. The label caption will change to “Average” if you’ve selected the Average option button and the result is stored in the textbox. (see Fig 8.11)

Close the program by clicking the close button.

Fig 8.11 - Click for larger image

d) The List Box and Combo Box Controls

The List Box control shows a certain number of values in a form where the user may select one or more items.

You can add the List Box control on the form with varying height and width such that it displays one or more items in the list. If there are more items in the list that can be displayed in the area provided, the scroll bars will automatically appear.

It is a good practice to draw the List Box control with its Height property set to a value large enough for several values to be displayed, because it is difficult to see the scroll bar when the control is at a minimum height.

If space on the form is an issue, use a Combo Box control instead and set the Style property to dropdown list. The Combo Box control combines the features of a List Box control with a Text Box control, allowing the user to enter a new value if desired and it also save you some space.

The difference between a combo boxes and the list boxes is that the combo box is a drop-down list and the user can submit a single value either one of the values from the drop-down list or any other value. The list box shows a certain number of values with or without a scroll bar and the user can select one or more values but not a value that is not in the list.

Properties of the List Box and Combo Box controls usually set at Design Time and Run Time are listed in Table 8. 5). Data is added to the List Box and Combo Box controls at run time using their AddItem() method.

ControlName.Additem (item)

Table 8.5 - Click for larger image

The AddItem() method must be called for every row of data added to the list. A looping code structure will often work well to complete this task (This will be demonstrated with Example below). Other methods belonging to both the List Box and Combo Box controls include, Clear() and RemoveItem() which remove all or one item from the control’s list, respectively.

Example C:

Add a UserForm and a ListBox named ListBox1 (shown below in Fig 8.12)

Fig 8.12 - Click for larger image

i. Select your UserForm and press F7 to activate the userform code window.

ii. Select the Initilize event procedure from the drop-down list at the top of the Code window.

iii. Add the initialization code for the form:

Private Sub UserForm_Initialize()

With ListBox1

.AddItem "Boston"

.AddItem "Chicago"

.AddItem "Detroit"

.AddItem "Los Angeles"

.AddItem "Miami"

.AddItem "New york"

.AddItem "Omaha"

.AddItem "Pasadena"

.AddItem "San Diego"

.AddItem "San Francisco"

.AddItem "Denver"

.AddItem "Florida"

End With

' Select the first list item

ListBox1.ListIndex = 0

End Sub

iv. To test the form, on the main menu of Visual Basic, click Run -> Run Sub/UserForm

To determine the selected item, add the following to the CommandButton1_Click procedure: The result is display in Figure 8.13

Private Sub CommandButton1_Click()

Dim Msg As String

Msg = "You selected item # "

Msg = Msg & ListBox1.ListIndex

Msg = Msg & vbNewLine

Msg = Msg & ListBox1.Value

MsgBox Msg

End Sub

Fig 8.13- Click for larger image

Note : the first item in a ListBox has a ListIndex of 0, not 1

Determining multiple selections

To enable the user select more than one item, you need to set the ListBox MultiSelect property to 1. To determine all selected items, you need to use the Selected property, which contains an array.

To allow multiple selections in a ListBox, set the MultiSelect property to

either 1 or 2. You can do so at design time by using the Properties window or

at run time by using adding VBA statement below to the UserForm_Initialize() event.

UserForm2.ListBox1.MultiSelect = 1.

On the VBE window, double click on CommandButton2 and enter the code below to the CommandButton2_Click event procedure:

Private Sub CommandButton2_Click ()

Dim Msg As String

Dim i As Integer

UserForm2.ListBox1.MultiSelect = 2

Msg = “You selected” & vbNewLine

For i = 0 To ListBox1.ListCount – 1

If ListBox1.Selected(i) Then

Msg = Msg & ListBox1.List(i) & vbNewLine

End If

Next i

MsgBox Msg

End Sub

The message box below show the result.

To remove an item from the ListBox, you need to use the RemoveItem() statement. Add a CommandButton3 and enter the code below.

Private Sub CommandButton3_Click()

i = ListBox1.ListIndex

ListBox1.RemoveItem (i)

End Sub

The above code will enable the selected item on the ListBox to be deleted or remove.

The Combo Box control also includes a DropDown() method that, when invoked, displays the control’s list.

The most useful event procedure of the List Box and Combo Box controls is the Change() event. Although you may find the DropButtonClick() event procedure of the Combo Box control quite useful as well. The Change() event is triggered when the Value property of the control changes. (The Value property of the List Box and Combo Box control is the selected item from the list.) The DropButtonClick() event of the Combo Box control is triggered when the controls dropdown button is clicked signaling that the user is viewing the list of items in the control.

Be sure to check the Object Browser for a complete list of properties, methods, and events associated with the ActiveX controls discussed in this Part.

e) The MultiPage Control

The MultiPage control is another example of a container control that groups or organizes ActiveX controls. An example of the MultiPage control in the Excel application is the Options dialog box shown in Figure 8.14 which is quite familiar to you. As you can see from this example that the MultiPage control allows you to cram a lot of options onto a single form.

Fig 8.14 - Click for larger image

You usuallly set their appearance at Design Time and they are rarely reference in a procedure code. By default, when you add a MultiPage control to a form, two pages are included. To add more pages, right click on a page tab while in Design Mode and select New Page from the shortcut menu. Figure 8. shows a form in Design Mode containing a MultiPage control.

Fig 8.15 - Click for larger image

The SelectedItem property returns the currently selected Page object. It is useful for identifying what page on the MultiPage control is active. For example:

If MultiPage1.SelectedItem.Caption = “Page 1” Then

MsgBox “Page 1 is active.”

End If

Just like the other controls you can set and manipulate Multipage in your VBA code and also in Design Mode. Take note that there is no Activate() or Select() method of the MultiPage or Page objects.

Therefore, you need to set the Value property of the MultiPage control to an index value representing a specific Page object in the Pages collection object. The following statement selects the second page (index numbers start at zero) of a MultiPage control.

MultiPage1.Value = 1

f)The Scroll Bar Control

You have undoubtedly seen and used scroll bars in numerous applications for scrolling through lengthy documents or large figures. Scroll bars sometimes automatically appear on the sides and/or the bottom of VBA controls so the user can view the entire content displayed in a control. Situations such as these require nothing extra from you, or your program—the scroll bars are simply there to provide the user with a method of seeing the complete content of the control; however,

I believe you have used scroll bars many times as a method to see the complete contents of page or form. VBA also provides a Scroll Bar control that you can add to forms in your project to enhance an interface, so that the user can scroll through content on a form. Apart from this the scroll bar control also enable you to sets a value from a large range of choices. For example in the Excel Format Cells dialog box (see Figure 8.16), a scroll is related to a textbox for user to choose the number of decimal points.

Fig 8.16 - Click for larger image

To do this, you need to set certain properties of the scroll during Design time (Set the Min value and Max value in the properties window) and enter this code in the ScrollBar_Change event.

TextBox.Value = ScrollBar.Value

The Change() event procedure is triggered when the value of the Scroll Bar control is changed by the user i.e clicked.

Table 8.6 summarizes the major properties of the Scroll Bar control.

Table 8.6 - Click for larger image

6) Using Worksheet as the Container or Control Panel

As promised earlier, I’ll now show you how to use worksheet as a container or control panel. Select a worksheet and activate the Control Toolbox by select View – Toolbars - Control Toolbox on the Excel menu. Then just drag the control object from the Control Toolbox into the worksheet. (You’ve done this on a UserForm…remember.

Example D: Controls use are Option Button, Frame, Label and TextBox. This time we insert all these control on a worksheet.

i) Select a worksheet and rename it to Compounding

ii) Insert 5 text boxes, 4 option buttons and a command button.

iii) Format the worksheet shown in Figure 8.17

iv) Rename all controls properties according to Table 8.7.

v) To do so, right click on the controls to activate their properties window.(see Fig 8. )

vi) Double click on the command button to activate the module window. Select the Click() event procedure and enter this code:

Private Sub cmdCalc_Click()

Dim Principal As Currency

Dim IntRate As Double

Dim IntEarned As Currency

Dim FutureValue As Currency

Dim RatePerPeriod As Double

Dim Periods As Integer

Dim CompoundType As Integer

Dim i As Double

Dim n As Intege

Principal = CCur(txtPrincipal.Text)

IntRate = CDbl(txtIntRate.Text) / 100

If optMonth.Value = True Then

CompoundType = 12

ElseIf optQuarter.Value = True Then

CompoundType = 4

ElseIf optSemi.Value = True Then

CompoundType = 2

Else

CompoundType = 1

End If

Periods = CInt(txtPeriods.Text)

i = IntRate / CompoundType

n = CompoundType * Periods

RatePerPeriod = IntRate / Periods

FutureValue = Principal * ((1 + i) ^ n)

IntEarned = FutureValue - Principal

txtIntEarned.Text = FormatCurrency(IntEarned)

txtAmtEarned.Text = FormatCurrency(FutureValue)

End Sub

Table 8.7 - Click for larger image

Fig 8.17 - Click for larger image

Fig 8.18- Click for larger image

First user has to enter the principal, interest rate and no. of period that need to be calculated. Next user can choose which compound frequency that apply by selecting one of the option buttons. Finally, click on the Calculate will start the procedure and the results will be displayed on the interest earned and amount earned text box. (see Figure 8.18)

(Download example file here)

Example E: The Controls used and embedded on the worksheet are a ComboBox and a SpinButton. This example will also show you, how you integrate controls and Excel built in function i.e =PMT() used to calculate the payment for a loan based on constant payments and a constant interest rate.

i) Select a worksheet, name it “Example E” and format it according to Figure 8.19.

ii) Insert a ComboBox and SpinButton.

iii) On Range(“C15”) insert this function: =PMT(C7/C11,C9*C11,-C5)

iv) On Range(“C17”) insert this formula: =SUM(C15*(C9*C11))

v) On Range(“C20”) insert this formula: =C17-C5

vi) Activate VBE by pressing ALT-F11, and double on the Worksheet(“Example E”) and select the Activate event on the right-hand dropdown list.

vii) Enter this code:

Private Sub Worksheet_Activate()

Me.ComboBox1.Clear

Me.ComboBox1.AddItem ("None")

Me.ComboBox1.AddItem ("Toyota Vios")

Me.ComboBox1.AddItem ("Honda City")

Me.ComboBox1.AddItem ("Toyota Camry")

Me.ComboBox1.AddItem ("Honda Accord")

End Sub

viii) Rightclick on SpinButton1 and select View Code.

Select the Change Event and enter this Code:

Range("C9").Value = Me.SpinButton1.Value

ix) Rightclick on SpinButton1 and select Properties

Enter the following:

Max = 10

Min = 1

SmallChange = 1

ix) Double click on ComboBox1 to bring up the Code window. Select the Change event and enter this code:

Private Sub ComboBox1_Change()

Dim loan As Single

Dim interest As Double

If Me.ComboBox1.Value = "Toyota Vios" Then

Range("C5").Value = 77000 'loan amount

Range("C7").Value = 0.045 'interest rate

ElseIf Me.ComboBox1.Value = "Honda City" Then

Range("C5").Value = 77500 'loan amount

Range("C7").Value = 0.055 'interest rate

ElseIf Me.ComboBox1.Value = "Toyota Camry" Then

Range("C5").Value = 150000 'loan amount

Range("C7").Value = 0.045 'interest rate

ElseIf Me.ComboBox1.Value = "Honda Accord" Then

Range("C5").Value = 145000 'loan amount

Range("C7").Value = 0.045 'interest rate

End If

End Sub

Fig 8.19 - Click for larger image

When Worksheet(“Example E”) is activated, the Activate event is triggered. This will load up the ComboBox1 with the type of cars.

When user select one of the car in the drop down list of the ComboBox1, the Change event is triggered. The code in this procedure will execute and calculation is made. You can choose the Length of Loan(years) by clicking the SpinButton and Range(“C9”) will be updated follow by the calculation as well.

(Download example file here)

7) Summary:

We have learn how to use VBA UserForms and worksheets as containers for our applications. We also discussed adding ActiveX controls to a form, including the Label, Command Button, Text Box, Frame, Scroll Bar, Option Button, RefEdit, MultiPage, Combo Box, and List Box controls and how to use the code window of a form.

Lesson 7 : Debugging And Error-Handling Techniques

In This Lesson, we will learn

Understand the different type of errors

Trapping and handling errors

Using the VBA on Error and Resume statements

Finding how you can use an error to your advantage

1) Types of Errors

No matter how well you are at writing VBA code, sooner or later you will encounter an error when running your program . For example, you may have type a keyword incorrectly or misspell the syntax. If such an error occurred, you won’t even be able to execute the procedure until you correct it.

When working with VBA, you should be aware of these classes of errors: compile errors, run-time errors, design and logical errors.


a) Design errors

This error consist of syntax errors that occur when you mistype a statement. The statement MsgBox (“Simple Error” , produces a syntax error because you have omitted the closing parenthesis.


b) Compile errors

Compiling is the process of converting or translating the VBA code into a format that the computer can understand. This compiling process happened very fast and you are typically unaware of it happening.

If any errors occur during the compile process, an error message pops up, and the VBE will highlights the location of the error.


c) Runtime errors

This error occurs when your code executes. VBE will displays a message box informing you of the error. A common example will be the TYPE MISMATCH error when you pass the wrong data type value to a variable. If you pass a string to an expression that expects a numeric value, a runtime error occurs.

Generally to avoid your program stop due to runtime error you need to use the On Error Resume Next statement at the beginning of your procedure. Try not to do this as the program still run although there are errors. The bottom line is you still need to handle the errors.

I will discuss more on this error later.


d) Logical errors

A logical error do not produce any type of error message. Simple logical errors include mistyping a value, placing a decimal point in the wrong place.



2) Trapping and handling errors

The primary goal of error handling is to write code that avoids displaying Excel’s error messages as much as possible. You need to anticipate potential errors and deal with them.

So, how do we handle and debug the errors. Let me show you a few methods.


a) Insert break points in a subroutine

You can insert break points in a procedure to stop execution at the specified line of code. This way, you can determine whether a procedure run correctly up to a specified location. To do this:

- activate the VBE and double click on a module to bring up the Code Window

- click on the right side of the Code Window. The break points will be highlight red (Figure 7.1) with a red circle on the right.

- You can clear a break point by clicking it again.

- Remember to clear all break points after you complete debugging your code.

After the break points are set, the procedure executes until it reaches the specified break point, highlight the line and stop the execution. All the variables will be updated. (see Figure 7.2) When you place your cursor on top of the variable, a pop up statement will tell the value. ( a yellow box )

You can continue running your code until it encounter another break point or end the procedure. You can change the code as needed to correct the error.

Fig 7.1 - Click for larger image

Fig 7.2 - Click for larger image

b) Step through a procedure

You can debug your procedure by stepping through the execution of the code one line at a time. Unlike break points, you trace the codeline by line by clicking the Step Into button. How do you do this?

- First click inside the procedure that you want to execute

- Then click on the Step Into button once. ( see Figure 7.3 )

- The line of code that was highlighted is the active line. ( see Figure 7.4 )

- Click on the Step Out option if you want to exit the procedure

Fig 7.3 - Click for larger image



Fig 7.4 - Click for larger image

As you step into the current procedure, VBE will selects and highlight the first line of code in the procedure i.e. the Sub or Function statement. Clicking on the Step Into option will execute the next line. The Local Window below (see Figure 7.4) updates the values of the local variables each time there is a value change. ( this is also the same scenario if you use the break points method described above).

Thus you can change what is necessary to fix the error. This error trapping method is very useful if you want to amend any logical error.

3) A further discussion on runtime errors

To get things started, I developed a short VBA macro. Activate the VBE, insert a module, and enter the following code:

Sub ErrorExample_1()

Dim Num As Integer

‘ Prompt for a value

Num = InputBox(“Enter a value”)

‘ Insert the square root

ActiveCell.Value = Sqr(Num)

End Sub

Fig 7.4a - Click for larger image

This procedure asks the user for a value. It then enters the square root of that value into the active cell.

You have enter “1q” which is wrong. The message Run-Time ‘13’ will be display (Fig 7.4b). If you click on the “Debug” button, VBA suspends the macro so you can use the debugging tools. VBA will show you the line of code where the error occur, the highlighted line of code shown in Figure 7.5.

Fig 7.4b - Click for larger image

Fig 7.5 - Click for larger image

To improve the procedure, you have to anticipate this error and handle it more gracefully.

Here’s a modified version of ErrorExample_1()

Sub ErrorExample_1()

Dim Num As Integer

‘ Prompt for a value

Num = InputBox(“Enter a value”)

‘ Make sure the number is nonnegative

If Num <>

MsgBox “You must enter a positive number.”

Exit Sub

End If

‘ Insert the square root

ActiveCell.Value = Sqr(Num)

End Sub

I use an If-Then structure checks the value contained in the Num variable. If the user enter a value other than an integer, the procedure displays a message box containing information that you can actually understand. The procedure ends with the Exit Sub statement, so the error never has a chance to occur.

4) Using the On Error statement

a) On Error Resume Next

The procedure below will demonstrate how this On Error Resume Next work. Insert a module and enter the following code.

Sub ErrorExample_2()

On Error Resume Next

Dim total As Integer

Dim intA As Integer

Dim intB As Integer

Dim intC As Integer

intA = Range("A1").Value

intB = Range("A2").Value

intC = Range("A3").Value

total = intA + intB + intC

MsgBox ("Total is " & total)

End Sub

Fig 7.6 - Click for larger image

When you run the procedure it will produce…

As you can see in Figure 7.6, the value in Range(“A3”) is not an integer but the procedure continue to execute because the On Error Resume Next is entered at the top of the program. If there is no such statement there a runtime error Type Mismatch error will occurs as Range(“A3”) is not an integer and nonnumeric.

With this statement, VBA skip any runtime error that occur during the execution of the procedure and an error message does not inadvertently display on the screen. However, an error still exists in the code and therefore the procedure does not produce the appropriate results.

This statement is very useful if you intend to develop program to be distributed to another user. It ensure that the program that you code does not stop abruptly due to an error encounter in the code.

There are error trapping methods to handle any potential errors where you can inform the user anytime that an error has occur. If you want to execute specific code when an error is encountered, you can modify the On Error statement to be On Error GoTo label

b) On Error GoTo label

With this statement the procedure will jump to the label section of the code whenever an error condition is encountered. Typically this label code is placed at the end of the procedure. You need to enter an Exit Sub statement prior to the labeled section to keep the procedure from executing the code within the label if an error is not encountered.

Let’s look at an example. Modify the example above to this code:

Sub ErrorExample_3()

On Error GoTo errorHandler

Dim total As Integer

Dim intA As Integer

Dim intB As Integer

Dim intC As Integer

intA = Range("A1").Value

intB = Range("A2").Value

intC = Range("A3").Value

total = intA + intB + intC

MsgBox ("Total is " & total)

Exit Sub

errorHandler:

MsgBox ("VBA skip the line of code containing a runtime error")

Resume Next

End Sub

In this example, the program jump to the errorHandler part of the code (see Figure 7.7) as error occurs and display the message box in Figure 7.8. Click the Ok button and the procedure will finish executing the code. Note: don’t forget to put a colon after the errorHandler label.

Fig 7.7 - Click for larger image

Fig 7.8 - Click for larger image

As promised above, I will further explain to you how to handle a runtime error. As you can see in procedure ErrorExample_1, a runtime error 13 “Type Mismatch” has occurred.

This error is capture by Excel VBA in the Err object. You can use this information to process the error and often correct the error situation. The number property in this Err object i.e. Err.Number enable us to design an appropriate error processing code to react differently depending upon the specific runtime error encountered.

We have the Run-time error 13 in the procedure i.e. the value passed to the variable intC is not the correct data type. A string was passed instead of an integer.

Enter the code below in a module:

Sub ErrorExample_4()

On Error GoTo errorHandler

Dim total As Integer

Dim intA As Integer

Dim intB As Integer

Dim intC As Integer

intA = Range("A1").Value

intB = Range("A2").Value

intC = Range("A3").Value

total = intA + intB + intC

MsgBox ("Total is " & total)

Exit Sub

errorHandler:

Select Case Err.Number

Case 13

MsgBox ("Make sure an integer is entered")

Case Else

Resume Next

End Select

End Sub

If runtime error 13 is encounter in this procedure, then the message box in Figure 7.9 will be displayed to the user and when user click the OK button, the procedure will end.

Fig 7.9- Click for larger image

This is only one run time error example. There are many others but the most common one is shown in Fig 7.9a below;

Fig 7.9a- Click for larger image

When you use an On Error statement in your VBA code, Excel will bypass its built-in error handling and use your own error-handling code. In the previous example, a run-time error causes macro execution to jump to the statement labeled errorHandler. As a result, you prevent Excel’s unfriendly error messages and you can display your own friendlier message to the user like using the Err.Number statement in the procedure ErrorExample_4.

Generally, you can use the On Error statement in three ways, (Fig 7.9b)

Fig 7.9b- Click for larger image

Sometimes its alright to ignore errors. In fact we treat them as “friendly error”. In the example below, we need to divide the value in the Range(“A1:A6”) with another value

Sub ErrorExample_5()

On Error Resume Next

Dim totalSales As Integer

Dim branchSales As Single

Dim sales As Range

Set sales = Range("A1:A6")

totalSales = 100

For Each cell In sales

branchSales = totalSales / cell.Value

Next cell

End Sub

Fig 7.10- Click for larger image

If the On Error Resume Next is not used, then there will be a runtime error 11, “Division By Zero” as you can see Range(“A5”) has zero sales in Figure 7.10. But the procedure continue to run ignoring this run time error as the On Error Resume Next statement already entered.

5) Summary

An error-handling routine has the following characteristics:

• It starts immediately after the label specified in the On Error statement.
• You must use a statement such as Exit Sub or Exit Function immediately before the label because this error handling code will only be execute if an error occurs.
• A Resume statement is use if you need to continue with the main code even though an error has occurred.

Error handling can be a tricky proposition, after all we are only human that we can’t anticipate them all.