19.1 Types of procedure
Large programs can be complex and hard to understand and debug. To reduce the complexity we need to split the problem at hand into smaller subproblems, and then solve these without having to be distracted by the overall solution. For this purpose we use procedures. A procedure is defined as a named group of statements, and it can be run as a unit. To run the procedure we write the name of the procedure in the code. This is called a procedure call.
There are two types of procedure in VBA. Subroutine procedures are suited for solving a variety of tasks, while function procedures are more suitable for solving a problem that gives a value as the result.
Splitting a program into procedures has a lot of advantages:
• Complexity can be reduced.
• Collaboration becomes easier. In a team of programmers working together on a large program, each can be given responsibility for a particular set of procedures.
• It promotes reuse of code. Procedures can be written, tested, debugged and placed in their own program modules, and then reused in other programs. A library of error-free procedures is an invaluable asset for any programmer.
• It saves time. You do not need to write the same statements over and over again.
Function procedures
A function procedure, or function for short, is a procedure that will return a result back to the caller. Hence, functions are often used for calculations that will give a single result back. The result does not have to be a number. It can also be a Boolean value, a string, a date and other things. The general syntax of a simple function is:
Function Name()
' statements that will be executed by calling this function is placed
' here (also known as the function body)
End Function
As you can see, this is very similar to a subroutine (Sub has been replaced with Function).
VBA comes with a number of predefined functions. Many of these are also found in Excel, and they even have the same names. Table 19.1 shows some of the most common arithmetic functions. Please see online help for complete information.
Table 19.1 Some of the arithmetic functions available in VBA
Function |
Description |
Abs(n) |
Returns the absolute value of a number n. Same as ABS in Excel. |
Int(n) |
Returns the integer portion of a number n. Same as INT in Excel. |
Exp(n) |
Returns a number specifying e (the base of natural logarithms) raised to a power n. Same as EXP in Excel. |
Sqr(n) |
Returns the square root of a number n. Same as SQRT in Excel. |
Log(n) |
Returns the natural logarithm of a number n. Same as LN in Excel. |
The Sqr function is used for finding the square root of a number. To be able to use it we must call it somewhere in our program. This happens in the subroutine TestSqr shown below.
Sub TestSqr()
Dim root As Double
root = Sqr(229) 'calling the Sqr function
Debug .Print root
End Sub
Let’s give our attention to the second statement. By writing the name of the function (Sqr) we are saying that we want to execute the statements within the body of this function. Since the function is meant for calculating the square root it must also get a number to operate on. This is written within parentheses and placed after the name of the function. The result is stored in a variable (root).
In the second line we are making a function call. We can also say that we’re calling or invoking the Sqr function.
Writing our own functions
If VBA or Excel does not have the function you need, you can write it yourself. This is an area where VBA overshadows Excel. With your own function you can:
• Remove complicated calculations from the worksheet into your own VBA function. This gives a much cleaner worksheet, and it is easier to read the function code in the VBE instead of a worksheet cell.
• Collect often used calculations in functions.
• Enhance Excel by writing financial or other functions that Excel does not have.
As an example we’ll write a function that calculates the value of pi. This function does not exist in VBA, but it is available in Excel. Thus, strictly speaking we do not need to make our own.
' Calculates PI by multiplying arctangent of 1 by 4
'Atn is a predefined function in VBA
Pi = 4 * Atn(1)
End Function
It’s worth noting that a function will return a result. We achieve this by assigning the return value to the name of the function. The line Pi = 4* Atn(1) tells us that the function will return the value of 4 * Atn(1).
Let’s write a procedure to call the function.
Sub TestPi()
MsgBox Pi
End Sub
The line MsgBox Pi calls the Pi function and displays the result in a message box.
The syntax for declaring functions is usually a little more complex than shown in the example above. Here is a more complete syntax:
[Private|Public|Static] Function Name ([argument list]) [As type]
' statements
End Function
The reason we could write the Pi function as we did is that there are many optional parts in the definition (shown in []).
Specifying the type of the function result
If we define the function Pi as follows we are saying that the result is to be a value of type Double. That makes sense since the calculated value is indeed a decimal number.
Function Pi() As Double
' Calculates PI by multiplying arctangent of 1 by 4
' Atn is a predefined function in VBA
Pi = 4 * Atn(1)
End Function
If there is no type declaration then it is assumed that the function result is of type Variant. Why bother with adding a type declaration? It’s better to avoid the use of Variants wherever possible, as they are not as effective in use as other data types.
The reserved words Public or Private determines if the function can be called only from the module where it is defined (Private), or from every module in the program (Public). If left out Public is assumed. Static is used if every variable declared within the function is a static variable.
Functions with parameters
We have seen that a function like Sqr that is used for calculating the square root of a number needs a number to operate on. A natural question will then be: How does the function know that it will receive a number, and how is this number transferred to the function? The function must have a parameter list. A parameter list is a list of variable declarations placed in parentheses in the first line of the function declaration. Each declaration is separated with a comma. There is a small difference from a normal variable declaration in that the words Dim, Public etc. are not used in a parameter list. Instead the words ByVal and ByRef are used.
Let’s say we want to write a function that will convert from degrees Fahrenheit to degrees Celsius. Such a function will obviously need a number (degrees) to operate on. We therefore need a parameter list.
Function Convert (ByVal degrees As Double) As Double
' statements to convert here
End Function
By looking at the first line we can see that this function will need one number of type Double. The word ByVal tells us that the function is supposed to receive this number. As Double says that the function is also meant to return a result of type Double, i.e. the converted number. The complete function can be coded as:
Function Convert (ByVal degrees As Double) As Double
Convert = (degrees – 32) * 5 / 9
End Function
As you remember we must assign the resulting value to the name of the function. This is done in the only statement in the function.
OK, so now we have a ready-to-use function. How do we use it? We’ll just write a test procedure:
Sub TestConvert()
Dim f As Double
Dim c As Double
f = 98.5
c = Convert(f)
Debug.Print c
End Sub
The interesting part is in line four. We’re invoking the function by writing its name, and after the name we write a value (in parentheses) that will be transferred to the function, just like we did when calling the Sqr function. So, what have we learned here? By looking at the first line in the function definition we see that the function will receive a value of type Double. We place this value in parentheses after the function name when we’re calling the function. This is an example of call-by-value. The parameter degrees is called a value parameter, and the variable f is called an argument to the function.
It is essential to understand how parameters are passed into subroutines so we’ll repeat the steps here:
1 The first parameter (degrees) in initialized to the value of the first argument (= 98.5).
2 The statements in the function body are executed.
3 The result is transferred back to the calling procedure where it will be stored in the variable c.
It is illustrated in Figure 19.1.
Figure 19.1 Call-by-value.
Subroutines
Subroutines are a more general kind of procedure since they’re not restricted to return a resulting value back to the caller. Like functions, subroutines can also have a parameter list. The syntax is:
[Public|Private|Static] Sub ([argument list])
' statements
End Sub
The only difference from a function definition is that there is no As type clause in this definition and the word Function is replaced by Sub.
Example 19.1
As an example of a subroutine with parameters we will write a subroutine that will write a message in the status bar of the window. It is meant to be used whenever we’re doing a lengthy operation, and we want to show how far it has come. See figure 19.2.
Figure 19.2 Progress indicator in the status bar.
To be able to write a message in the status bar, we use a property of the Application object called StatusBar. This allows us to set or get the text in the status bar.
The subroutine can be coded as follows:
Sub ShowProgress(ByVal msg As String, ByVal percent As Integer)
Application.StatusBar = msg & ” (“ & percent & “%)”
End Sub
There are two parameters in this subroutine. One is of type String and the other is of type Integer. To call the procedure we must provide a string and an integer as arguments. Inside the subroutine the first parameter is concatenated with the second before the result is written in the status bar.
Here’s a subroutine written to test ShowProgress:
Sub TestShowProgress()
Dim i As Integer
Dim waitTime As Date
For i = 1 To 10
' Do something that’ll take some time here
' We’ll simulate this by just waiting a second
waitTime = TimeSerial(Hour(Now), Minute(Now), Second(Now) + 1)
Application.Wait waitTime
' Call the subroutine
ShowProgress “Please wait…”, i * 10
Next i
End Sub
The subroutine is called 10 times within a loop. For each iteration of the loop we pause the running subroutine for 1 second. This is achieved by calling the Application.Wait method with a specified time (in seconds).
Since the subroutine is expecting two parameters (msg and percent) we need to provide two arguments (“Please wait…” and i * 10). When calling a subroutine the arguments must not be enclosed in parentheses as is the case for functions. See more about this in the next section.
Calling procedures
There is a small difference in syntax when you want to call a subroutine compared to a function. A function can be called by writing its name, and if there are any arguments enclosing those in parentheses after the name. On the other hand, a subroutine can be called in two ways:
1 Write the subroutine name followed by arguments (if any). Arguments are separated by commas.
2 Write the reserved name Call followed by the name of the subroutine and the arguments enclosed in parentheses.
The following example shows how the subroutine Test can be called by other subroutines
Sub Test(ByVal a As String, ByVal b As Boolean)
…
End Sub
Method 1
Test “Roger”, True
Method 2
Call Test(“Roger”, True)
Subroutine calls are used as separate statements in the program, while function calls are used in expressions. This is due to the fact that functions always return a value, and we want to do something with this value. Here are a few examples of calling the Sqr function. You can imagine that the call is replaced by the return value in the expression where it stands.
If Sqr(123.65) > 11 Then …
Debug.Print “The square root of 82 is “ & Sqr(82)
' Calculating one of the roots for the quadratic equation ax2 + bx + c = 0
x1 = – b + Sqr (b * b – 4 * a * c) / (2 * a)
There must be exactly as many arguments as there are parameters. Calling the Test procedure with one argument missing will lead to an error.
Test “Roger” ' one argument is missing
Call-by-reference
Information can also be returned from a procedure through a parameter, or be transferred to a subroutine, changed and transferred back to the calling procedure. Both of these scenarios require the use of reference parameters. A typical example is a subroutine that swaps the values of two variables. Let’s start by looking at how we can do the swapping. Assume you want to swap the values of variables a and b. In the beginning a = 3 and b = 7. After the swap a = 7 and b = 3. Our first attempt is
Dim a As Integer, b As Integer
a = 7
b = 3
a = b
b = a
This does not work. The problem is that when we do a = b the old content of a will be overwritten and lost. It is therefore not correct to write b = a thereafter.
Figure 19.3 Erroneous attempt at swapping two variables.
To avoid this we’ll use an extra (helper) variable temp.
Dim a As Integer, b As Integer, temp As Integer a=7
a = 7
b = 3
temp = a
a = b
b = temp
Figure 19.4 illustrates the swapping.
Figure 19.4 Correct attempt at swapping two variables.
Now let’s put these statements in a procedure. The variables a and b must be transferred to the procedure. We then need two arguments. We could try with
Sub Swap (ByVal n1 As Integer, ByVal n2 As Integer)
Dim temp As Integer
temp = n1
n1 = n2
n2 = temp
End Sub
The names of the parameters do not have to be a and b. It is the values of a and b that will be transferred to the subroutine. The names of the receptors don’t matter, just the type. If this was not the case then the procedure could only be used on variables with the names a and b.
We’ll also need a test procedure:
Sub TestSwap()
Dim a As Integer, b As Integer
a = 7
b = 3
MsgBox “ a = “ & a & ” b = “ & b
Swap a, b
MsgBox “ a = ” & a & “ b = ” & b
End Sub
Running the test procedure will produce an erroneous result. The variables a and b have clearly not been swapped. What is wrong? The problem is that we’re using call-by-value, which means that the values of a and b are transferred into the subroutine but not out again. We need to transfer the new values back to a and b. We need call-by-reference. It is achieved by writing the reserved word ByRef before the name of the parameter in the parameter list (instead of ByVal). Here is the correct procedure:
Sub Swap (ByRef n1 As Integer, ByRef n2 As Integer)
Dim temp As Integer
temp = n1
n1 = n2
n2 = temp
End Sub
As a matter of fact, call-by-reference is default in VBA so that we can leave out ByRef altogether and only write
Sub Swap (n1 As Integer, n2 As Integer)
This is what happens:
1 When the procedure is called, n1 and n2 become aliases (other names) for the variables a and b respectively. Everything that is happening to n1 in the subroutine is actually happening to a, and the same for n2 and b. VBA achieves this by transferring the memory addresses of the variables a and b to the subroutine.
2 The statements in the subroutine are executed.
3 When the subroutine has finished execution the coupling between n1 and a and n2 and b will cease to exist.
Call-by-reference is visualized in figure 19.5.
Figure 19.5 Call-by-reference.
Call-by-value or call-by-reference?
Here are some rules of thumb for whether to use call-by-value or call-by-reference.
• If data is to be passed into a procedure and does not have to be returned, then you should use value parameters (declared with ByVal). A parameter used in this way is also called an input parameter.
• If data is to be passed out of a procedure then you have two options. If it is a single result you can use the return value of a function. The other option is to use reference parameters (declared with ByRef). A parameter used in this way is called an output parameter.
• If data is to be passed into a procedure, and possibly modified and then passed out of the procedure again, you must use reference parameters (declared with ByRef). A parameter used in this way is called an input/output parameter.
In VBA there is no distinction between output parameters and input/output parameters. They are both declared the same way.
When writing a procedure you must have a clear understanding of what the procedure is supposed to do. Also you must decide what kind of input data the procedure must have to do its job and what the result(s) will be. You may find it helpful to consider the following points.
• Find out which data the procedure must get from the caller that should not be modified by the procedure. Use a value parameter for each of these.
• Find out which data the caller must get from the procedure. Use a reference parameter for each of these.
• Find out which data the procedure must get from the caller that should be modified by the procedure and then returned to the caller. Use a reference parameter for each of these.
• If a procedure returns a single result, using a function can be an alternative to using a reference parameter.
• Also remember that passing an argument by value protects a variable from being (unintentionally) changed by the procedure.
Here are a couple of examples.
Example 19.2
Write a procedure that calculates the area of a triangle with sides a, b and c. The area will be calculated by using Heron’s formula.
Clearly we will need three input parameters (a, b and c) and one output parameter (area). Since this procedure will produce a single result we’ll be using a function. Hence there will be no need for the output parameter. The area will be returned as the function value.
Function Area(ByVal a As Double, ByVal b As Double, ByVal b As Double)
Dim s As Double ' Declares a helper variable
s = (a + b + c) / 2
Area = Sqr(s * (s – a) * (s – b) * (s – c))
End Function
Write a procedure that calculates the increase in debt after a certain time period given a constant interest rate.
To calculate the new debt the procedure will need the interest rate and the current debt. Of these two the interest rate will not change, but the debt will. The result will be a new value for debt. Therefore we use an input parameter for the interest rate and an input/output parameter for the debt.
Sub CalculateDebt(ByVal interestRate As Double, ByRef debt As Double)
debt = debt + debt / 100.0 * interestRate
End Sub
Optional arguments
Optional arguments enable you to omit arguments for some parameters. Optional arguments are declared with the reserved word Optional as shown in the following example.
Sub SetDate(ByVal year As Integer, Optional ByVal month As _ Integer, Optional ByVal day As Integer)
Here we see that the last two parameters are optional. This procedure can therefore be called in several ways
|
|
|
|
|
|
|
|
When optional arguments are used it is quite common to specify default values for the arguments that are missing. For example:
Sub SetDate(ByVal year As Integer, Optional ByVal month As _ Integer = 1, Optional ByVal day As Integer = 1)
Here the default values for month and day are both 1.
The following rules apply.
• If an actual argument is missing, the procedure uses the default value declared for that argument.
• If an optional argument is used in the procedure definition then every argument following that argument must also be optional.
Named arguments
When you call a subroutine or a function you pass the arguments in the order in which they appear in the definition of the subroutine or function. If you have written a subroutine AddWorker
Sub AddWorker(ByVal name As String, ByVal dateOfBirth As Date, _ ByVal department As String)
it can be called like this:
AddWorker “James Bond”, #6/17/1988#, “Sales”
It is not possible to change the order of the arguments.
' Error. Wrong order of arguments
AddWorker #6/17/1988#, “James Bond”, “Sales”
Arguments are passed by position.
But there is another way. Arguments can be passed by name. In this case you write the name of the parameter followed by a colon, an equal sign, and the argument value. In the given example you can write
AddWorker name:= “James Bond”, dateOfBirth:=#6/17/1988#, _ department:= “Sales”
When using named arguments you can supply the arguments in any order. Their position does not matter. The following call is also correct:
AddWorker dateOfBirth:=#6/17/1988#, department:= “Sales”, _ name:=“James Bond”
In a procedure with optional arguments you must use an extra comma if you want to skip an argument in the call; that is, if the arguments are passed by position. VBA has a built-in function FV that returns the future value of an annuity based on periodic, fixed payments and a fixed interest rate. The function is defined like this:
FV(rate, nper, pmt [, pv [, type]])
where rate is the interest rate per period, nper the total number of periods, pmt the payment to be made each period, pv is the present value of a series of future payments, and type is specifying when payments are due (= 0 at the end of the period and = 1 at the beginning). The parameters pv and type are optional with default values equal to 0. See online help for more information.1
It can be called like this:
FV(0.04/12, 36, –1000,,1)
Since pv is left out we need an extra comma.
By using named arguments we can write
FV(rate:=0.04/12, pmt:=-1000, nper:=12)
No extra comma is needed.
Using self-defined functions in a worksheet
A function that you define may be used in a worksheet just like the built-in functions in Excel. The only thing you must ensure is that Excel can localize the function. If it is defined in the same workbook then all you have to do is write the name of the function in a cell. You can use the function Convert in a cell like this:
=Convert(A1)
If the function is defined in another workbook we have to prefix the name of the function with the file name of the workbook where it is defined. For example, a function defined in Book 2.xlsm can be called like this:
= ' Book 2.xslm'! Convert(A1)
The quotes are optional if there are no spaces in the file name.
Exit from a procedure
A function or subroutine will run until all the statements in the procedure have been executed. Sometimes we want to exit before this has happened. This can be achieved with the statements Exit Function or Exit Sub. Program execution will then continue with the next statement after the function call or procedure call.
Tips for writing good procedures
A procedure should only solve one problem
The following function calculates VAT.
Public Const RATE As Double = 0.20
Function VAT() As Double
Dim amount As Double
amount = InputBox(“Specify amount”)
VAT = amount * RATE
End Function
This is a bad function because it does two things. A function meant to calculate VAT should not at the same time read the amount from the user. The function cannot be used for calculating VAT if the amount is not read from an InputBox. A better solution would be
Function VAT(ByVal amount As Double) As Double
VAT = amount * RATE
End Function
The amount should be read in another subroutine. A function or subroutine that does a well-defined task is said to have high cohesion.
Try to write general procedures
The following subroutine colours an area of a worksheet.
Sub ColourRange()
Dim r As Range
Set r = Range(“A1:C10”)
r.Interior.ColorIndex = 6
End Sub
The subroutine is not very flexible. It always colours the same area of the worksheet, and it always uses the same colour. Here is a far better alternative.
Sub ColourRange(range As range, color As Long)
range.Interior.ColorIndex = color
End Sub
You can choose which area and which color you want to use.
Sub TestColourRange()
ColourRange Range(“B10:D20”), 5
End Sub
Use general names for procedures
Suppose you need a function that will return the lesser of two decimal numbers. Say you need this function in a program where you need to find the minimum petrol consumption for a number of cars. Your first attempt may look like this
Function MinimumConsumption(ByVal consumption1 As Double, _ByVal consumption2 As Double)
If consumption1 < consumption2 Then
MinimumConsumption = consumption1
Else
MinimumConsumption = consumption2
End If
End Function
This is a correct function and it will serve your purpose well. But this is a general function that can be used in a number of contexts. Locking the name to a specific problem as you have done here is not good. A better solution is to use the name Minimum or Min
Function Min (ByVal n1 As Double, ByVal n2 As Double)
If n1 < n2 Then
Min = n1
Else
Min = n2
End If
End Function
As a matter of fact this function is defined in Excel.
Procedure templates
Under the Insert menu in VBE you can select Procedure. This brings up a dialogue box where you can fill in relevant information. VBE will then insert an empty procedure template in the Code window.
Figure 19.6 Add Procedure dialogue box.
Example 19.4
A European call option allows the holder to exercise the option (buy) only on the option expiration date. With an American call option, the holder is allowed to exercise at any time during the life of the option. The holder buys the option hoping that the price of the underlying instrument will rise in the future.
We will calculate the price using an n-period binomial model. For each period of time the price can move up with a factor u, or down with a factor d. The probability of an up move is calculated as 
the probability of a down move is equal to qd = 1 – qu, and r is the risk-free rate corresponding to the life of the option.
At the final step a European call option is priced as:
where
is the binomial coefficient (the number of up moves in n moves), K is the strike price and S is the spot price of the underlying asset. The binomial coefficient can be found by using the Excel function Combine(n, i).
Below is a function that calculates the price of a European call option based on the formula above.
Function BinEuroCall(price As Double, _
strike As Double, _
u As Double, _
d As Double, _
rate As Double, _
n As Integer) As Double
' Calculation of an European call option
Dim retval As Double
Dim qu As Double, qd As Double
Dim i As Integer
qu = (1 + rate – d) / (u – d)
qd = 1 – qu
For i = 0 To n
retval = retval + WorksheetFunction.Combin(n, i) _
* qu ^ i * qd ^ (n – i) _
* WorksheetFunction.Max(price * u ^ i * d ^ (n – i) – strike, 0)
Next i
BinEuroCall = retval / (1 + rate) ^ n
End Function
To calculate the price of a European put option just replace the term Suid(n–1) − K with K − Suid(n–1) in the equation. We’ll leave the implementation of a European put option as an exercise to the reader.
Example 19.5
It is reasonable to assume, based on historical data, that stock prices are lognormally distributed, i.e. the natural logarithm of stock prices is normally distributed. We can use the lognormal distribution to calculate how the price of a stock will change from time t to time t + Δt, where Δt is a short time interval. The stock price St+Δt at time t + Δt can be written as:
where
μ |
= |
expected rate of return |
α |
= |
volatility |
z |
= |
a randomly picked number (see below). |
The factor z is a random draw from a standard normal distribution (with a mean of 0, and a standard deviation of 1) representing an uncertainty that must be taken into account.
We will use this equation to calculate the lognormal stock price path for a year (approximately 250 business days). The time interval will then be equal to 1/250. For each new day the price St+Δt is calculated by using the equation above. St is the price of the stock on the day before.
The stock price, expected rate of return (μ), and volatility (α) will be written in cells C4:C6 in the worksheet. Based on these data our macro will generate a two-column table with day number and stock price as shown in figure 19.7.
Figure 19.7 Simulation of stock prices.
The factor z can be found by using the Box-Muller transform. It’s a method for generating a pair of independent, standard normally distributed random numbers (z0 and z1). The algorithm can be formulated as:
1 Generate two random numbers U1 and U2 in the closed interval [−1, +1].
2 Calculate 
3 If R2 > 1 reject the pair (U1, U2), and go back to step 1.
4 Set 
and 
To generate a random number we use the Rnd function in VBA. The Rnd function returns a value less than 1 but greater than or equal to zero.
For an alternative method to generate the number z we can use the Excel function NormSInv.
The solution is shown below.
19.2 Error handlers
If a run-time error occurs in your program, VBA will display a standard run-time error dialogue box. This may be OK if you’re making a program only for yourself, but is usually not acceptable for other users. What can you do about it? The first thing is to prevent run-time errors from happening in the first place by making a lot of checks. The next thing is to anticipate the kind of problems that can occur and then try to make your program handle them in an elegant way. For this situation VBA has the On Error statement.
An On Error statement instructs VBA what to do when a run-time error occurs. There are three forms of this statement.
On Error Goto line
On Error Resume Next
On Error Goto 0
On Error Goto line
This causes program execution to continue after a line marked with a label should there be a run-time error after the point where this statement is written. The following example will illustrate this:
The line argument is a label or a number. A line label is used to identify a single line of code. The label must start with a letter and end with a colon. Any combination of characters can be used in between. The label must begin in the first column and is not case-sensitive. Alternatively one can use a line number to identify a line. The number must be unique within the module where it is used and must begin in the first column.
In the example shown above we could easily have anticipated such a situation and checked with an If…End If statement instead, i.e.
If (a >= 0) And (b <> 0) Then
' Do the calculation
End If
Err is a predefined object that contains information about run-time errors. When a run-time error occurs the properties of the Err object will automatically be filled with information about the error. The most interesting properties are Number (contains an error number) and Description (contains a short description of the error). The Err object’s properties are reset to zero or zero-length strings (““) after an Exit Sub, Exit Function, Exit Property or Resume Next statement within an error-handling routine.
On Error Resume Next
The second form, On Error Resume Next, tells VBA to ignore the error in a statement and continue with the next statement in the code. The example below shows how we can select the cell to the left of the active cell in a worksheet. But it will fail if the active cell is in the first column. We therefore use On Error Resume Next to handle this situation:
Sub SelectCell()
On Error Resume Next
ActiveCell.Offset(0, –1).Select
If Err.Number = 1004 Then
MsgBox “Cannot select the cell to the left of the first column”
Err.Clear ' Clear the properties of the Err object
End If
' Continue here…
End Sub
On Error Goto 0
This is the third form of the On Error statement and it is the default in VBA. Any error will cause VBA to display the standard error dialogue box.
All forms of the On Error statement may be used in the same procedure. See the example subroutine OnErrorStatementDemo under VBA help (search for On Error).
19.3 Event handlers
As you already know, an event is some kind of action initiated either by the user or by VBA code. Typical examples are that the user opens a new workbook, or he clicks in a cell in the worksheet. An event handler is a procedure that is automatically called when an event occurs. Excel is responsible for calling the procedure when the event occurs. The content of the event handler, i.e. what will happen, is your responsibility.
We will begin by looking at events defined in the Workbook object. These events are fixed. You cannot add or remove events or change the declaration of event handlers. The event handlers for the Workbook object are placed in the ThisWorkbook code module. As an example we will write an event handler for the open workbook event.
1 Double-click on the ThisWorkbook item in the project window.
2 Select Workbook from the combo box in the upper left corner.
3 An empty event handler for the Open event is written into the editor. If you want an event handler for another event you will need to select the event from the combo box in the upper right corner. You may try this for the SheetActivate event. Notice that VBA places an empty event handler in the editor. Delete it if you do not intend to write an event handler for this event.
4 Add some code in the Workbook_Open procedure. In this example we will just write a welcoming message.
Private Sub Workbook_Open()
MsgBox “Welcome!”
End Sub
Save the workbook on disk. Remember to save it as an Excel Macro-Enabled Workbook. Open it again. You should then see the message box. The event handler was automatically called when the Open event happened.
The most interesting events in the Workbook object are briefly described below. Please see Excel help for more information.
Activate, Deactivate
This event occurs when the workbook is activated (for instance by clicking in it) or deactivated (by clicking in another workbook window).
BeforeSave, AfterSave, BeforeClose, BeforePrint
Occurs before the workbook is saved (BeforeSave), after it is saved (AfterSave), before it is closed (BeforeClose), and before it is printed (BeforePrint).
NewSheet, NewChart
Occurs when a new sheet (NewSheet) or new chart (NewChart) is created in the workbook.
SheetActivate, SheetDeactivate, SheetCalculate, SheetChange
Occurs when a sheet in the workbook is activated (SheetActivate), or deactivated (SheetDeactivate), or any worksheet is recalculated or after any changed data is plotted on a chart (SheetCalculate), or when cells in any worksheet are changed (SheetChange)
SheetSelectionChange
Occurs when the selection changes on any worksheet.
Here are a couple of examples of event handlers for a Workbook object.
We’ll begin by writing an event handler for the NewSheet event. Our goal is to move the newly created worksheet after the last sheet in the workbook. Follow the steps outlined above and select the NewSheet event from the upper right combo box. Add the following code.
Private Sub Workbook_NewSheet(ByVal Sh As Object)
Sh.Move After:=Sheets(Sheets.Count)
End Sub
As you can see, the Workbook_NewSheet event handler has a parameter Sh. This is an object that represents the newly created worksheet. We’re calling the Move method on this object to move it behind the sheet with the greatest index. The greatest index is found by counting the numbers of sheets in the Sheets collection.
We could also have used Count on the Worksheets collection,
Sh.Move After:=Worksheets(Worksheets.Count)
but by using the Sheets collection we’re sure to move it to the end should there be any charts in the workbook. The Sheets collection contains both worksheets and charts while the Worksheets collection only contains worksheets.
If we want to check whether the new sheet is a worksheet or a chart we can do this with the TypeName function.
If TypeName(Sh) = “Worksheet” Then …
In our next example we’ll write a handler for the SheetChange event. This event handler has two parameters. Sh refers to the worksheet that is changed and Target refers to the changed cells within this worksheet. We want our handler to check if there is an overlap between the changed area and the area A1:D5.
Private Sub Workbook_SheetChange
(ByVal Sh As Object, ByVal Target As Range)
Dim isect As Range
Set isect = Application.Intersect(Target, Sh.Range(“A1:D5”))
If isect Is Nothing Then
MsgBox “No overlap”
Else
MsgBox “Overlap”
End If
End Sub
Worksheet events
The second object that has events is the Worksheet object. To write an event handler for this object you must double-click on the worksheet name (Sheet1, Sheet2, etc.) in the project window. This opens a new code window where you select Worksheet from the upper left combo box and the event from the upper right combo box.
These are the most interesting events for this object
Activate, Deactivate
This event occurs when the worksheet is activated (for instance by clicking in it) or deactivated (by clicking in another worksheet window).
BeforeDoubleClick, BeforeRightClick
Occurs before the worksheet is double-clicked (BeforeDoubleClick) or right-clicked (BeforeRightClick), but before the default double-click or right-click action.
Calculate
Occurs when a worksheet is recalculated.
Change
Occurs when cells on the worksheet are changed.
SelectionChange
Occurs when the selection changes on a worksheet.
As an example of an event handler for a worksheet, here’s a handler that will colour all negative numbers in an area that you select in red. We’ll use the SelectionChange event.
Private Sub Worksheet_SelectionChange(ByVal Target As range)
For Each c In Target
If c.Value < 0 Then
' Change colour to red
c.Font.ColorIndex = 3
Else
' Colour is set to black
c.Font.ColorIndex = 0
End If
Next c
End Sub
Application events
The Application object can also generate events. There is no explicit code module for the Application object, so we have two options:
1 Place the event handlers for the Application object in the code module for ThisWorkbook.
2 Create a class module and place the Application event handlers in this module.
We will not be looking into class modules in this text, hence we choose the first option. This is how to do it:
1 Open the code window for ThisWorkbook by double-clicking the name in the project window.
2 Declare a variable of type Application like this
Public WithEvents App As Application
The WithEvents keyword indicates that the declared object variable App refers to an object that can raise events.
3 Create a Workbook_Open handler
Private Sub Workbook_Open()
Set App = Application
End Sub
You are now ready to start writing event handlers for the Application object:
4 Select the App object from the combo box in the upper left corner
5 Select an event from the combo box in the upper right corner.
Scheduling events with OnKey and OnTime
Maybe you want to execute a procedure when the user presses a key or you need a timer to schedule an operation at a certain time. You can achieve this by calling the OnKey or OnTime methods on the Application object.
OnKey
The OnKey event runs a procedure when the user hits a key or key combination. The syntax is:
Application.OnKey Key, Procedure
Key is a string representing the key or key combination that will trigger the event. The s key will be coded like this: “s”. For key combination we use the character % for the Alt key, ^ for the Ctrl key, and + for the Shift key. Some examples are “%k” (Alt+k), “+g” (Shift+g), “^d” (Ctrl+d), and “+^{PGDN}” (Ctrl+Shift+PgDn). Special keys have their own code, for instance “~” (ENTER), “{ESC}” (Esc), “{DEL}” (Delete), and “{F1}” (F1). See online help for more information.
Procedure is the name of the procedure that will run when the user hits the key. The name must be enclosed in quotes. To deactivate a procedure, i.e. not run it when the user hits the key, call OnKey with an empty procedure name. If a key combination has another meaning it will be reset to this use when you do this. Let’s give an example.
' Execute procedure ReadData if the user hits Ctrl+t
Application.OnKey "^t", "ReadData"
' Reset Ctrl+t to its original function again (Create table)
Application.OnKey "^t", " "
Ctrl+t is used in Excel to create a table. In the first line we redefine it to execute the procedure ReadData. In the second line we deactivate the procedure and Ctrl+t can again be used to create a table. If we want nothing to happen when the user hits Ctrl+t we can write:
Application.OnKey "^t"
OnTime
Table 19.2 Application.OnTime parameters
|
The time when the procedure will be run ( |
|
The name of the procedure that will be executed ( |
|
The absolutely latest time the procedure can run. When Excel is busy doing something else so that the procedure cannot be run on the |
|
|
The OnTime event starts execution of a procedure on a predefined time or after a certain amount of time has elapsed (table 19.2). The syntax is:
Example 19.6
Let’s create an event handler that notifies the user exactly 1 hour after he has opened a workbook. The subroutine is very simple and writes a message to the user
Sub Warning()
MsgBox "You have now worked for 1 hour. Please take a break"
End Sub
It is placed in one of the code modules.
We’ll hook up the Workbook_Open event handler and add some code to execute the subroutine after exactly 1 hour. For this purpose we’ll use the OnTime event. Here’s the code:
Private Sub Workbook_Open()
Application.OnTime Now + TimeValue("01:00:00"), "Warning"
End Sub
After 1 hour the message box pops up and advises us to take a (well-deserved) break.
Excel events and event handlers are predefined. You cannot
• change the name of a handler
• change the parameter list of a handler
• add or remove events and event handlers.2
The objects that can generate events in Excel are Application, Workbook, Worksheet and Chart. The events are organized in a hierarchy with the Application on top. The Workbook object is under the Application object and the Worksheet object is under the Workbook object. What is the significance of this? Say you have written a Worksheet_Change handler, a Workbook_SheetChange handler and an Application_SheetChange handler. They will all be called if a worksheet is changed. Because of the hierarchy the worksheet handler (Worksheet_Change) will be called first, then Workbook_SheetChange will be called and finally Application_SheetChange will be called.
It is possible to deactivate event handlers on a higher level by setting the property EnableEvents to False as shown in this handler
Private Sub Worksheet_Change(ByVal Target As Range)
' Deactivate events in the Application object
Application.EnableEvents = False
' Write code for this event handler here
End Sub
Problems
19-1. A palindrome is a word or sentence that can be read the same way in either direction, for example “racecar” and “never odd or even”. Write a function that returns True if a sentence is a palindrome, and False if it’s not. The function should take a string as parameter.
19-2. Easter in 2011 was the latest in over 50 years. Write a function that calculates the date of Easter Sunday. The year is given as a parameter. A suitable algorithm can be found at http://www.assa.org.au/edm.html
19-3. A worksheet has three columns and 10 rows with data. Column A contains stock tickers, column B contains the initial purchase price of the stocks, and column C contains the current price. Create a macro that writes “Sell” in column D if the loss is more than 8 per cent or if the profit is more than 25 per cent. Otherwise you should write “Hold” in the column.
19-4. Write a function that checks if a year is a leap year. A year is a leap year if it is
1 divisible by 4 and not by 100, or
2 divisible by 400.
The year is given as an argument to the function.
19-5. Write a function that returns the number of days in a month. The month and year are given as arguments to the function.
19-6. Create a macro that stores a monthly calendar in a worksheet. Use Input boxes for reading the year and month.
19-7. You buy a bond for £850 at the start of a year. The coupon is 8 per cent, the nominal amount is £1,000, and the maturity date is in 9 years. Payments will be made every year, starting the next year. Write a macro that calculates the duration for the bond.
19-8. You borrow $100,000 as an annuity loan over 10 years at 2 per cent interest p.a. Interest and principal are paid at the end of each year. Create a macro that calculates the interest rate, periodic payment, and balance for a given quarter. Data are read from the worksheet, and the results are written back into it.
19-9. Create a macro that calculates the expected return and standard deviation for a portfolio of stocks (Axe–Ebb) listed in the table below.
Returns per year have been observed for five different stocks over a period of 8 years (%):
19-10. Rewrite the FormatName subroutine from chapter 17 to:
Sub FormatName(name As String)
Use it to format a list of names in the range A1:A10. The formatted names are written back in the same cells.
Notes
1 Excel also has the function Fv, but with other parameter names. The parameters are called Arg1, Arg2, Arg3, Arg4, and Arg5.
2 You can create custom events for classes that you create yourself. We will not treat this topic in this text.