16.1 Structure of a VBA program
A program is a list of instructions to the computer. The point is to make the computer perform a task. To avoid chaos, a program needs to have a certain structure. The computer cannot execute the instructions in random order, as this will lead to an unpredictable result. In VBA the list of instructions is put within the context of a procedure, or subroutine as it is called in VBA. A procedure consists of a list of instructions. Each instruction is called a statement, and is usually written on a new line. The computer will execute the statements in sequential order, starting at the top. Later in this book we will see that the order of execution can be changed by the use of control structures. The structure of a subroutine is shown below:
Sub Name()
Statement1
Statement2
…
StatementN
End Sub
In a very small program there may be only one procedure, but usually the code is organized in more than one procedure. Procedures are gathered into larger units called modules, and each module is saved in its own file.
A large program has many modules. In Excel VBA there are four different kinds of modules, as follows.
• Most of your code is put in standard code modules. This is where you place your subroutines and functions (a special kind of procedure that will be discussed in chapter 19).
• Code that is related to a specific worksheet or workbook is placed in a different kind of module. In addition to event procedures (see “Events” below) they can contain general procedures that can be “used by” the event procedures.
• Class modules are used for creating new objects. They are not discussed in this text.
• UserForm modules contain event procedures for the UserForm and controls on the UserForm. They are discussed in chapter 21.
On top of this code hierarchy is the VBA project. It is defined as a collection of modules and other programming elements. A new project is automatically created when you create a new workbook file.
Events
An Event is an action initiated either by a user or as a result of VBA code being executed. Examples of events are:
• a mouse button is clicked
• a key is pressed or released
• a value is typed in a worksheet cell
• a workbook is opened or closed
• a menu item is selected.
Excel can detect these events and trigger a special kind of procedure when the event occurs. This procedure is called an event procedure or event handler. Event handlers are empty procedures. It is our responsibility to write the code that will be executed when the event occurs. Excel will only tell us when the event has occurred.
16.2 Comments, variables and constants
Comments
Comments are added to a program to help others understand your code. They make the source code easier to read and maintain. Here are some of the reasons why you want to use comments in your code:
• to explain the functional characteristics of a program module or procedure, i.e. explain what it does
• to explain the purpose of variables, constants etc.
• to describe arguments passed to a procedure, and certain preconditions that must be met
• to describe or justify an algorithm used to solve a problem
• as an explanation of code that is complex or difficult to understand (perhaps rewriting the code would be better)
• to temporarily comment out source code for testing and debugging purposes.
A comment begins with a single apostrophe character (′). Comments can occupy the entire line or follow a statement on the same line. Every word after the apostrophe is ignored by VBA. When you have typed in a comment, and pressed the Enter key, the line will turn green, making it clearly stand out as a comment. If you want to type in a comment that will span several lines, you need to start each line with an apostrophe.
Example
' This function will calculate the body mass index (BMI)
' Input parameters:
' weight (in kilograms)
' height (in centimeters)
'
' Returns:
' the BMI
'
' The following formula is used to calculate the BMI:
' BMI = (height × height) / weight
Comments are simply text that is ignored by the compiler when the program is run.
Variables
Every computer program, except for the most trivial ones, needs to have variables. Variables are used for temporarily storing data while the program is executing. The data stored in the variable is called its value.
Before you start using a variable in your program it should be declared. When a variable is declared it is given a name and a data type. The name is used to refer to the value stored at the variable’s location in memory, and the data type tells VBA what kind of data the variable can hold. The syntax1 for declaring a variable is:
Dim name As Type
A variable declaration starts with the reserved name Dim, which is an abbreviation of Dimension, followed by a variable name, the reserved word As, and a data type. The available data types you can use are listed later in this chapter. Here are some examples:
Dim price As Single
Dim quantity As Integer
Dim product Name As String
In the first declaration we declare a variable that has the Single data type. This means we can store decimal numbers in this variable. The name of the variable is price. The second line declares a variable of data type Integer. Integer is used for storing positive and negative integers. The last line declares a variable of type String. This means it can be used for storing text strings, i.e. plain text.
If you need to declare many variables of the same type you can declare each one on a separate line:
Dim a As Integer
Dim b As Integer
Dim c As Integer
or you can declare them on the same line separated with commas:
Dim a As Integer, b As Integer, c As Integer
Beware of the following declaration:
Dim a, b, c As Integer
VBA will not complain about this, but the result is not what you may think it is. Only the variable c has the data type Integer. The variables a and b are declared without a data type. In such cases VBA will assume the data type is Variant.
Variable names must comply with certain rules:
• The name must start with a letter and not a digit.
• The characters !, @, $, #, period or space cannot be used.
• A variable name can be no longer than 255 characters
• A variable name cannot be the same as any one of VBA’s reserved words (If, For etc.).
Variable names should reflect the purpose of the variable in the program. Sometimes you may want to declare variables with names consisting of more than one word. In this case you have two options since spaces are not allowed in the name:
1 Separate each pair of words with the underscore character, as in product_name, or…
2 Start each word (except the first) with a capital letter, for example dateOfBirth
Assignment statements
We have now talked about how to declare a variable, but have said nothing about how to store a value in its location in memory. This is the purpose of the assignment statement. The syntax of an assignment statement is:
variablename = value
We say that we’re assigning a value to the variable. With the help of assignment statements we can assign different values at different times during program execution. The operator = is called the assignment operator.
Examples
price = 1290.5
quantity = 100
productName = "Sleepydog Adjustable Sleep System"
A text string must be enclosed in double quotes. The quotes themselves do not belong to the string, they just mark the beginning and end.
A special kind of assignment statement is:
quantity = quantity + 1
The purpose of this sentence is to increment quantity by 1. Here the variable name is used on both sides of the operator. To understand what’s happening the expression on the right side of the operator is evaluated first. Since quantity has the value 100, the expression has the value 101. This value is stored in the variable written on the left side of the operator, i.e. 101 is written into quantity. The net effect is that quantity has increased by 1.
Some more examples:
|
|
|
|
|
|
By default, VBA does not require that you declare every variable you use in your program. All you have to do is start using it. But this may sometimes lead to errors that are difficult to spot. Suppose you did not declare the quantity variable from the last section. If you then write (notice the misspelling of quantity);
quantity = quanity + 1
VBA will not flag this as an error, but instead create a new variable called quanity of type Variant.2 So instead of incrementing quantity you have created a new variable, added 1 to that variable and stored the result in quantity. Probably not what you intended.
To prevent errors like this you should start every program module with the directive
Options Explicit
This directive can automatically be added to every new code module by choosing Options from the Tools menu. On the Editor tab check the Require Variable Declaration option.
Figure 16.1 The Options dialogue.
Constants
A variable declaration can be modified by using the Const keyword. The Const modifier tells VBA that the name represents a constant.
Constants are data items whose values cannot change while the program is running. If you define something as Const and then try to modify it, VBA will generate an error
PI = 3.14159 ' Error. PI is defined as Const
Many programmers write symbolic constants in all uppercase, with underscores to separate words. This makes them stand out from variables. Other naming conventions are also in use. Whatever convention you use, it does not matter to VBA.
You must always initialize a constant when you create it. It cannot be declared and later assigned a value in an assignment statement.
Instead of using literal values directly into your program you should define symbolic constants. This has a number of benefits:
• It makes the program more readable. It is easier to understand what INTEREST_RATE means in an expression such as: amount = balance * INTEREST_RATE, than a mere value.
• It is easier to maintain the program code. Suppose INTEREST_RATE should be raised to 0.0375. By using a symbolic constant the only thing you’d have to do was to change its value. If not, you would have to search through the source code for every occurrence of the literal constant 0.035, and then change it. You could also risk changing 0.035 in places where it does not represent the interest rate.
• It helps to prevent you from unintentionally changing the value of a constant. If you do this VBA will flag this as an error.
The syntax for defining a constant is:
[Public | Private] Const constantname [As type] = expression
Expressions in square brackets are optional. The character “|” means OR, i.e. we can choose Public or Private. [As type] can also be left out.
Public Const CITY_NAME As String = "London"
Private Const SEC_PER_DAY As Long =60 * 60 * 24
Here is a complete example that calculates the weekly salary for a person. The number of hours worked and the tax rate are read from two cells in a worksheet. The salary is written back into another cell.
Sub CalculateSalary()
Const HOURLY_RATE As Single = 10
Dim hours As Single
Dim taxrate As Single
Dim salary As Single
' Read the tax rate from the cell B1
taxrate = Cells(1, 2).Value
' Calculate the salary
salary = hours * HOURLY_RATE * (1 – taxrate)
' Write the salary back into the spreadsheet
Cells(1, 3).Value = salary
End Sub
Figure 16.2 Worksheet after running the CalculateSalary subroutine.
Variable scope
The scope of a variable defines in which parts of the code it can be used. A variable can be declared inside a procedure or in a module outside of any procedure. Procedures will be explained in chapter 19. When declaring variables we are not limited to using the Dim keyword. Instead of Dim we may use the keywords Public, Private and Static. The difference lies in where the variable may be used. The following rules apply.
• Variables declared within a procedure (subroutine or function) may only be used in the subroutine where they are declared. They are called local variables.
• Variables declared outside of a procedure with the keyword Private or Dim can only be used in the module where they are declared (also in any procedures in that module). If they are declared with the Public keyword they may be used in every code module in the project. They are called global variables.
The same rules apply to constants. Table 16.1 summarizes the rules.
Table 16.1 Scope of variables
A variable declared with |
Declared where |
Has scope |
|
Module |
The module where it is declared |
|
Procedure |
The procedure where it is declared |
|
Module |
Every module in the project |
|
Procedure |
The procedure where it is declared |
If a program has more than one code module it may happen that a public variable declared in one module has the same name as a public variable declared in the other module. In such cases we can distinguish between the variables by qualifying them with the module name. Qualifying means that we prefix the variable name with the module name separated by a dot, as in
Module1. varname
Module2. varname
Static variables
Local variables declared with the keyword Dim exist only while the subroutine in which they are declared is executing. If we want the local variable to preserve its value after the subroutine has finished executing we must declare it with the Static keyword:
Static count As Long
Suppose we want to count the number of times a certain subroutine is executed. The problem can easily be solved by declaring a static variable within the procedure. Each time the subroutine is called we increment the value by 1. Numeric local variables are always initialized to 0.
Public Sub CountCalls()
Static count As Long
' Increment count each time the subroutine is called
count = count + 1
' Other statements are written here
Debug.Print "The subroutine has been called " & count & " times "
End Sub
Debug.Print is a statement that you can use to display messages or variable values in the Immediate Window. The Immediate window is usually found below the code window.
Debug.Print "This will appear in the Immediate window"
If the Immediate window is hidden it can be made visible by pressing Ctrl+G. Debug.Print is mainly used for debugging purposes.
If all variables declared within a subroutine are Static it is easier to declare the whole subroutine as Static, like this:
Static Sub Routine()
' All variables declared in this subroutine will be made static,
' no matter whether they are declared with the Dim, Private,
' or Static keywords.
End Sub
In addition to scope, variables also have a lifetime. This is the period of time within which they can be accessed. We can determine the lifetime of variables by looking at where they are declared. The following rules apply.
• Non-static variables and constants declared within a procedure have a lifetime as long as the procedure is executing. They are created when the procedure is called, and are no longer accessible when the procedure has finished executing.
• Static variables declared within a procedure are created the first time the procedure is called and are accessible until the application exits. This means that they keep their values between each time the procedure is called. Variables and constants declared at the top of a module are preserved for the lifetime of the application.
Good programming habits
An important aspect of writing good code is to make your programs understandable and easy to maintain by other people. Therefore you must strive for more than just writing programs that work correctly. In this chapter we’ll look at some of the things you can do to achieve this goal. If you ever happen to maintain somebody else’s code, you will appreciate it if these steps have been followed.
• Insert blank lines within and between procedures to make your code more readable. Compact code without any space is difficult to read.
• Use comments in your code. A comment is text that is written after a single quote, and is meant to explain certain things about the code.
• Do not write statements that are too long. A very long statement can be split into more than one line. Each line, except the last, must end with a space and an underscore character (_).
• Indent your code. Do not let all your statements begin at the left margin. Typically we indent statements within a procedure, a loop or other control structure. The point is to indicate clearly where the block of code starts and ends. You will see many examples of this later in this text. The Tab key can be used to indent.
16.3 Data types
A variable declaration always has a data type associated with it. The data type refers to:
• the amount of memory that is used to hold the data values
• the type of values (i.e. numbers, strings, etc.) the variable can hold
• the kind of operations that are allowed on these values.
Before we declare a variable in our program we must know what the variable will be used for. If it is a string of characters, for example a name, then the decision is simple. It must have a String data type. On the other hand, if we want to store a number then we have several options. To make the best choice we must know whether it is an integer or a decimal, whether we can have negative values, and how large the values can be. There are also data types for storing booleans and dates.
|
' |
|
' |
|
' |
The data types supported by VBA are as follows.
The Byte data type
This is VBA’s smallest numeric data type and holds a numeric value from 0 to 255. Uses 1 byte of memory.
The Integer data type
Contains an integer in the range of −32,768 to 32,767. Uses 2 bytes of memory. This is the most common type for storing integers.
The Long data type
If you need a data type with a larger range than Integer you can use the Long data type. It can be used to store numbers in the range of −2,147,483,648 to 2,147,483,647. Uses 4 bytes of memory.
The Single data type
Used to store numbers with decimal places or fractional numbers. Use this data type to store negative numbers from −3.402823 · 1038 to −1.401298 · 1045 or positive numbers from 1.401298 · 10–45 to 3.402823 · 1038. This data type takes 4 bytes of memory and has a precision of seven significant digits.
The Double data type
Used to store double-precision floating-point numbers in the range of –1.79769313486231570 · 10308 through −4.94065645841246544 · 10–324 for negative numbers and 4.94065645841246544 · 10–324 to 1.79769313486231570 · 10308 for positive numbers. This data type takes 8 bytes of memory and has a precision of 15 significant digits.
The Boolean data type
A data type with only two possible values (False or True). Use this data type if you need a variable to remember if something is true or false, on or off, yes or no, etc. The actual values stored in memory are integers (–1 for False and 0 for True) and the data type uses 2 bytes of memory.
The Date data type
This data type can be used to store either date or time, or both. The value is saved as a decimal number using 8 bytes of memory. The value to the left of the decimal represents a date, and the fraction represents a time. The range is from 00:00:00 (midnight) on 1 January 0001 through 11:59:59 pm on 31 December 9999.
The String data type
Used for storing a sequence of characters such as letters, numbers, punctuation, and special characters (including spaces). The string can contain from 0 to approximately 2 billion Unicode characters. Each character in the string is stored as a number in memory (character code).
There is also a fixed-length string type. To declare a fixed-length string, use the Dim statement in the form
Dim varname As String * string length
Maximum string length is 65,400 characters for a fixed string.
The Currency data type
Used for defining monetary values from –922, 337, 203, 685, 477.5808 to 922, 337, 203, 685, 477.5807. Uses 8 bytes of memory and can have 19 digits with a fraction of maximum four decimals.
The Decimal data type
Decimal is a subtype of the variant data type and not a truly separate data type. It can be used for storing numeric values in the range –79,228,162,514,264,337,593, 543,950,335 to 79,228,162,514,264,337,593,543,950,335 with no decimal point. With decimals the precision is up to 28 digits with values ranging from –7.9228162514264337593543950335 upto 7.9228162514264337593543950335, and the smallest non-zero number is ±0.0000000000000000000000000001 (± 10−28).
The Object data type
The Object data type is used for declaring a variable that can hold a reference to an object. It uses 4 bytes of memory. Internally the reference is stored as an address to the location of the object in memory. See the section on objects for more information.
The Variant data type
The Variant data type is the most flexible of all data types in VBA. It behaves like a chameleon in that it can hold almost any kind of data (not at the same time). If we do not explicitly give the data type when we declare a variable, VBA interprets it as a Variant.
|
' |
|
' |
Given that VBA has such a flexible data type one might ask why there are other types at all. The reason is that this flexibility comes at a price. Variants use at least 16 bytes of memory to store the data value. They also slow performance as VBA has to determine what type of data the variant holds.
Variants can also make it difficult to find errors in the source code. We can write
|
' |
|
|
|
' |
Sentences like those above can make it difficult to know what kind of data the variable holds at any time and might cause unexpected errors. If we instead write
Dim age As Integer
age = "Fifteen"
VBA will flag this as an error. It would go undetected if age was a variant. For reasons such as these it is not recommended to use variant variables.
Having said that, there are situations where variants are very well suited, for example as parameters in procedures, and for capturing entry in input boxes where the data type isn’t always known in advance.
Type declaration characters
Sometimes you will see variable declarations like these:
Dim name$ ' same as Dim name As String
Dim age% ' same as Dim age As Integer
The dollar sign and per cent sign characters at the end are examples of type declaration characters. This is an archaic form that is used in older programs. They exist in VBA today primarily for backward compatibility. You should avoid such statements. The most common type declaration characters are: Integer (%), Long (&), Currency (@), Single (!), Double (#), and String ($).
16.4 Strings
String is an important data type in VBA. Variables of the data type string are used for holding all sorts of text. We declare a variable of type String as
Dim name As String
and then assign a value (string) to it with an assignment statement
name = "Captain William H. Loudspeak"
You must enclose a string literal (value) within quotation marks (" "). They are not part of the value, they just mark the beginning and end of the string.
After working with Excel for some time you probably know that there are many functions available to manipulate numbers and strings. For example, to find the square root of a number we can use the built-in function SQRT in a cell.
The syntax for the SQRT function is:
= SQRT(Number)
Number is the number for which you want to find the square root. It can be a number or a cell reference.
As an example of a text function we have the UPPER function. It is used to convert text to all upper case or capital letters.
= UPPER(Text)
Text is the text you want to change. It can be a cell reference.
We can make most of the built-in worksheet functions in Excel accessible in our VBA application. To find the median3 in a set of values we can use the MEDIAN function:
Dim med As Integer
med = WorksheetFunction.Median(12, 45, 211, 56, 149, 68, 200) ' med=68
Just remember to prefix the function name with WorksheetFunction and a period. We’ll explain this syntax in more detail after we have read the section about objects.
Neither the SQRT nor the UPPER function is available in VBA, but it doesn’t really matter. VBA has its own uppercase function (called UCase), so it is not necessary to ask Excel for help in this case. To convert a text we can write:
Dim nameInUppercase As String
nameInUppercase = UCase(name) ' CAPTAIN WILLIAM H. LOUDSPEAK
The converted name is saved in the nameInUppercase variable.
VBA also has a function to find the square root of a number (Sqr). Some functions are available both in Excel and in VBA (and some even have the same name).
In the rest of this section we will take a look at some of the built-in string functions that are available in VBA. To count the number of characters in a string (i.e. string length) we use the Len function:
Dim length As Integer
Dim name As String
name = "Harold W. Stephenson"
' Find the number of characters in the name
' by using the Len function
length = Len(name)
' Print the number in the Immediate window
Debug.Print length ' writes 20
Some of the more commonly used VBA functions are listed in the table below. Example 16.1 shows how to use them.
Table 16.2 VBA string functions
Function |
Action |
|
Convert |
|
Convert |
|
Used for advanced string conversions. If |
|
Returns the length of |
|
Creates a string with |
|
Creates a string by repeating the |
|
Extracts |
|
Extracts |
|
Extracts |
|
Returns a copy of |
|
Returns a copy of |
|
Returns a copy of |
|
Finds the position of the first occurrence of |
|
Replace all instances of |
|
Used for comparing two strings |
String operators
VBA also has some operators that can be used on strings. We have already used the assignment operator for assigning string values to string variables. The main string operator is the string concatenate operator (&). By using this operator we can concatenate strings into a longer string. If both operands are of the String data type then the resulting string will also have this type. If one of the operands is not a String, it will be converted to a Variant (containing a string) and then concatenated with the other operand. The result will be a Variant containing a string. Here are some examples:
Debug.Print "I" & " love"& " " & "you" ' I love you
Debug.Print "10" & 5 ' 105
Strings can also be concatenated with the + operator but the result may not always be as expected. If one of the operands is a number and the other a string that can be interpreted as a number then the string operand will be converted to a number and an arithmetic addition will be the result. Because of this it is usually more safe to use the & operator instead.
Comparison operators compare two numbers or strings and return a logical (True or False) result. We will show how these can be used in chapter 17.
Sub TestStringFunctions()
Dim s As String, t As String, u As String, v As String
Dim pos As Integer
s = "the yorkshire"
t = "dale"
'StrComp is called with StrComp(string1, string2[, compare])
'compare is optional and specifies the type of string comparison.
' compare = –1 (using the setting of the Option Compare statement)
' compare = 0 (performs a binary comparison)
' compare = 1 (performs a textual comparison)
'The function returns a number
' Return value = 0 (string1 is equal to string2)
' Return value = –1 (string1 is less than string2)
' Return value = 1 (string1 is greater than string2)
' Return value = Null (string1 or string2 is Null)
Debug.Print StrComp(s, t) ' 1
'Converting s to uppercase
s = UCase(s)
Debug.Print s ' THE YORKSHIRE
'Creates a string with 10 space characters
u = Space(10)
'Debug.Print ">"&u&"<" '> <
'Creates a string with 10 a’s
u = String(10, "a")
Debug.Print u 'aaaaaaaaaa
'Left justifies a string within a string variable
LSet u = "GRR"
'Writes GRR to the left in u and
'adds 7 spaces to it
Debug.Print u & " < " 'GRR <
'Concatenates strings (+) and convert the result to proper case
v = StrConv(" " & s & " " & t & "s ", vbProperCase)
Debug.Print ">" & v & "<" ' > The Yorkshire Dales <
'Removes leading and trailing spaces from the string
v = Trim(v)
Debug.Print v 'The Yorkshire Dales
' Searching for the word 'Dale’ in v
' Uses the function InStr([start], string1, string2[, compare])
' start is the starting position for the search. If omitted,
' search begins at the first character position
' string1 is the string being searched
' string2 is the string sought
' compare has the same meaning as for StrComp
' The function returns a Variant (Long) specifying the position
' of the first occurrence of one string2 within string1
' First position is 1. Returns 0 if string2 not found
pos = InStr(1, v, "Dale", 1)
Debug.Print "Found Dale in position " & pos ' Found Dale in position 15
' Returns a Variant (String) containing a specified number of
' characters from a string
' Uses one of the functions Left, Mid, and Right
' Left(string, [length])
' Mid(string, start, [length])
' Right(string, [length])
' string is the string from which characters are returned
' start is the character position in string at which the part to be
taken begins
' length is the number of characters to be copied (if omitted,
' the left of the string is assumed)
' The function returns a string
Debug.Print Left(v, 3) 'The
Debug.Print Mid(v, 5, 9) 'Yorkshire
Debug.Print Right(v, 5) 'Dales
End Sub
16.5 Dates
The Date data type is used for storing dates and times. A variable of the Date data type can hold values that represent dates ranging from 1 January of the year 100 through 1 January of the year 9999. Internally, variables of type Date are stored as 8-byte floating point numbers. The date is stored in the integer position, and the time is stored in the fraction. Midnight is 0 and 0.5 is midday. Negative numbers represent dates before 30 December 1899.
Three predefined functions (Now, Date and Time) are used for getting the current date and time, as shown in the example code below
Dim aDate As Date
Dim aTime As Date
' Get the current date and time
aDate = Now
Debug.Print aDate ' for example 10/15/2012 21:16:27
' Get the current date only
aDate = Date
Debug.Print aDate ' for example 10/15/2012
' Get the current time only
aTime = Time
Debug.Print aTime ' for example 21:16:27
' Date literals must be enclosed within number signs, for example
aDate = #3/4/2010# ' month/day/year
aTime = #10:05:23 PM# ' time:min:sec
'Using another function CDate (meaning convert to date) we can
'write the literal date as a string:
aDate = CDate("February 25, 2 011")
aDate = CDate("9/30/1976")
The DateSerial function generates a date from a year, month and day passed as arguments to the function:
aDate = DateSerial(1988, 11, 3)
Extracting parts of a date
VBAhas several functions for extracting parts of a date and time. Just use one of the functions Year, Month, Day, Hour, Minute and Second. For example, to use the Minute function to obtain the minute of the hour from a specified time we write:
Dim minutes As Integer
aTime = #10:05:23 PM# ' time:min:sec
minutes = Minute(aTime) ' minutes = 5
Similarly, the Year function returns the year from a date
Dim aYear As Integer
aDate = CDate("3/4/1970")
aYear = year(aDate) ' aYear = 1970
Doing calculations on dates
DateAdd is a very handy function if we want to add a time interval to a date. It has the following syntax:
DateAdd(interval, number, date)
Table 16.3 DateAdd function parameters
The following example will add 45 days to the current date. The new date is saved in the variable futureDate
Dim currentDate As Date
Dim futureDate As Date
|
|
|
|
|
|
The number of time intervals between two dates can be found with the function DateDiff. The syntax is:
DateDiff(interval, date1, date2, [firstdayofweek [, firstweekofyear]])
Table 16.4 DateDiff function parameters
Part |
Description |
|
Has the same meaning as for |
|
The first date or time |
|
The second date or time |
|
Optional. An integer specifying the first day of the week. If omitted then Sunday (1) is assumed to be the first day of the week. Specify 2 for Monday. See Excel help for other options |
|
Also optional. An integer specifying the first week of the year. If omitted then the first week is assumed to be the one containing 1 January (1). Other options are the week that has at least four days in the new year (2) or the first full week in the new year (3). |
The next example calculates the age of a person in days. The date of birth is typed into cell A1 in the worksheet.
Sub AgeInDays()
Dim dateOfBirth As Date
Dim numDays As Long
Try to change this example to find your age in years.
Formatting dates
To determine how the date/time is going to be displayed, we use either the FormatDateTime function or the Format function. FormatDateTime is the simplest and lets us choose from a number of predefined formats (named numeric constants). It has the following syntax:
Date is the date to be displayed and NamedFormat (optional) is a numeric value that indicates the date/time format used. If it is omitted, vbGeneralDate is used. Below are shown some examples. See Excel help for more information.
Dim dat As Date
dat = Now
|
' |
|
' |
|
' |
|
' |
|
' |
The Format function accepts a parameter called a format string that allows us to determine how the date is going to be displayed. It can also be used to format other data types. The syntax is:
Format(expression[, format[, firstdayofweek[, firstweekofyear]]])
Table 16.5 Format function parameters
Part |
Description |
|
This is an expression that we want to format. It can be a number, a string, or a date. |
|
Optional. Used to define the format. Either select a predefined format such as General Number, Scientific, Medium Date, True/False or many others, or define your own format using special characters. |
|
Optional. An integer specifying the first day of the week. If omitted then Sunday (1) is assumed to be the first day of the week. Specify 2 for Monday and so on. See Excel help for other options. |
|
Also optional. An integer specifying the first week of the year. If omitted then the first week is assumed to be the one containing 1 January (1). Other options are the week that has at least four days in the new year (2) or the first full week in the new year (3). |
The # character used on numbers means that a digit should be displayed in this position only if there is a digit there. It not, then nothing should be displayed. To display a 0 in a position where there is no digit one can swap the # character with a 0. The < and > characters will convert strings to lowercase or uppercase. The % character will multiply numbers with 100 before they are displayed.
For further information see Excel help.
16.6 Type conversion functions
Sometimes we need to convert a value from one data type to another, for example from number to text or from one numeric data type to another. VBA comes with a variety of type conversion functions where we can coerce an expression to a specific data type. The most common ones are shown in table 16.6. The name of the function determines the data type of the value it returns.
Expr is usually a string or numeric expression. If the expression cannot be converted the function will fail. The function IsNumeric(expr) can be used to check if an expression can be evaluated as a number. It returns a value of the data type Boolean (True if it is a numeric expression).
Table 16.6 Type conversion functions
Function |
What it does |
|
Convert |
|
Convert |
|
Convert |
|
Convert any valid date or time |
|
Convert |
|
Convert |
|
Convert |
|
Convert |
|
Convert |
|
Convert |
|
Convert |
Here are a few examples of type conversions using these functions.
16.7 VBA operators
Arithmetic operators
You’ve already seen some VBA operators. Among these is the assignment operator (=), which is used to assign the value of an expression to a variable. Also you have seen how we can concatenate string operands into a longer string with the & operator. In this section we will look at some other operators that are available in VBA. Since this is a book about financial modelling it is natural to start off with the arithmetic operators. See Table 16.7.
Table 16.7 Arithmetic operators
Operator |
Name |
+ |
Addition |
- |
Subtraction |
* |
Multiplication |
/ |
Division |
Integer Division |
|
Mod |
Modulus |
^ |
Exponentiation |
Most of these operators are self-explanatory. There are two operators for division. They differ in how they treat the fractional part. The / operator saves the fraction, but the integer division operator () saves only the integer result. The Mod operator gives the remainder of a division (an integer) when it is used on integer operands. If it is used on numbers with fractions the numbers are rounded upwards to the nearest integer before the division takes place. A few examples are shown below:
All of these arithmetic expressions give a numeric value when they are evaluated.
Operator precedence
When an arithmetic expression is evaluated, VBA uses built-in rules. An expression that calculates the volume of a sphere with radius r is given by 4/3 * 3.14159 * r^3. To get the correct answer VBA must know which operation it should do first (division, multiplication or exponentiation). Arithmetic operators are evaluated in the following order of precedence.
• Exponentiation has the highest order of precedence and is done first.
• Multiplication and division have the same order of precedence, and they have higher order of precedence than addition and subtraction.
• Operators with the same order of precedence are evaluated from left to right.
The order of precedence can be overridden by the use of parentheses. Expressions within parentheses are always evaluated first. If there are parentheses within parentheses, then the innermost expressions are evaluated first.
Table 16.8 summarizes the order of precedence used by VBA. Operators on the same row have the same order of precedence.
Table 16.8 Operator precedence
Symbol |
Operation |
^ |
Exponentiation |
-, + |
Unary minus and plus (for example in the expression (–12 * 2) |
*, / |
Multiplication and division |
Integer division |
|
Mod |
Modulus |
+, - |
Addition (and string concatenation) and subtraction |
& |
String concatenation |
<<,>> |
Arithmetic bit shift |
<, <=,>, >=, =, <>, Is, Like |
Comparison operators |
Not |
Logical NOT |
And |
Logical AND |
Or |
Logical OR |
Xor |
Logical EXCLUSIVE OR |
Eqv |
Logical EQUIVALENCE |
Imp |
Logical IMPLICATION |
What will happen if an operand has operators both to the left and right of it, and these operators have the same order of precedence? The order of evaluation will be from left to right. Let’s look at an example:
Dim a As Integer
a = 16 / 4 * 2 ' a = 8
The result will be 8 because the division 16/4 (resulting in 4) is done before the multiplication 4*2. For this reason, operands in VBA are said to be left-associative (an operand is taken by the operator to the left of it).
The use of parentheses can override both the order of precedence and the left associativity. The following expression evaluates to 2.
a = 16 / (4 * 2) 'a=2
In addition to the arithmetic operators we also have concatenation operators, logical operators and comparison operators. We’ll be looking at the logical operators and comparison operators in the next chapter.
16.8 Objects in VBA
Objects, properties and methods
Objects are a fundamental construct in VBA. Performing a task in VBA involves manipulating various types of objects. It’s easiest to illustrate objects by looking at some examples from our daily life. An object is a thing like a person, a car, a valve or a wallet. It can also be an abstract concept such as a bank account, an insurance policy, a marriage or a schedule.
Objects have attributes. In VBA they’re called properties. For example, a person has attributes such as name, sex, salary and position. A car object has attributes like colour, weight, registration number, make and model, to mention a few. Each of these properties holds a value. A person can have the name “Jenny Williams”, sex “female”, salary £25 000, and position “dentist”. The set of values held by the attributes of an object represents the state of the object.
Objects also exercise behaviour, i.e. actions that can be performed on the object. In VBA these actions are called methods. Car objects have methods for driving, stopping, starting, turning headlights on or off. Bank objects can have methods for deposits and withdrawals. Some methods also need arguments. For example, the drive method needs to know the velocity.
To find the objects, properties and methods in a problem domain you may want to look for nouns, adjectives and verbs in the text. Objects correspond to nouns, properties correspond to adjectives, and methods correspond to verbs. Abstraction is an important element here. We concentrate on the things that are of relevance, and leave out the rest.
Excel object model
To use VBA in Excel we need to get to know Excel’s object model. All the available objects in Excel form an object hierarchy. Some objects are collection objects; others are not. Collections are objects that contain a group of other objects, all of the same type. Many collections have a name that is the plural form of the name of the objects they contain. There are collections of Worksheet objects (called Worksheets), Workbook objects (Workbooks), Border objects (Borders), and so on. Each object, whether it is a collection object or not, has methods and properties. An object can also be a property of another object. Exactly which objects are available is dependent on the version of Excel that you’re using. The number has increased with new releases of Excel. To find the model your version of Excel has, you can search for Excel Object Model Reference under Help.
Figure 16.3 shows a small excerpt of Excel’s object hierarchy. At the top of the hierarchy is the Application object. This object represents the application we are using; in this case, Excel itself. The Application object contains a Workbooks object. From the figure we can see that this is a collection of Workbook objects. Each Workbook object represents an opened workbook in Excel. Workbook objects contain a Charts object and a Worksheets object. These are collection objects that contain Chart objects and Worksheet objects respectively. A Chart object represents a diagram and a Worksheet object represents a worksheet in a workbook. Among the objects found in Worksheet is Range which represents a cell range in the worksheet. Workbook also has a collection object called Sheets. This object contains a collection of both Chart and Worksheet objects.
Using objects in Excel
As you know by now, objects have methods and properties. When we are working with Excel objects we usually want to execute a method or get or set the value of a property. To do this we write the name of the object followed by a dot operator and then the name of the method or the property.
Figure 16.3 A small part of Excel’s object hierarchy (shaded boxes are collection objects).
Table 16.9 The basic hierarchy of the Excel object hierarchy
Object |
What it represents |
|
The application, i.e. Excel. Contains methods and properties that affect the entire application, for example a property that can be used to find the active workbook (the window on top) or a method to display a dialogue box for user input. |
|
A collection of workbooks that are opened in Excel. |
|
A workbook. |
|
An object containing collections of all the worksheets and the chart sheets in the workbook. This does not include charts embedded on worksheets or dialogue sheets. |
|
A collection of Worksheet objects. Each Worksheet object represents an opened worksheet in a workbook. |
|
A collection of all the chart sheets in the workbook. |
|
A chart in a workbook. The chart can be either a separate chart sheet or an embedded chart. |
|
A worksheet in a workbook. |
|
A collection of columns in a worksheet. |
|
A cell, a row, a column, a selection of cells containing one or more contiguous blocks of cells, or a 3-D range. |
|
A collection of rows in a worksheet. |
To place the number 60 in the active cell in the active worksheet (the selected cell in the topmost window) we can write
ActiveCell.Value = 60
To multiply the value by 2 we write
ActiveCell.Value = ActiveCell.Value * 2
To place another number in the cell below the active cell we write
ActiveCell.Offset(1,0) = 70
Value and Offset are properties in the ActiveCell object.
The Range object represents a cell, a row, a column, a selection of cells containing one or more contiguous blocks of cells, or a 3-D range. To use the Range object we need to provide an argument that names the range. This argument is written within parentheses after the word Range. The following example places the value 1000 in cell A1 (regardless of the active cell):
' Place the value 1000 in cell A1
Range ("A1").Value = 1000
More examples:
' Fill the range A1:C10 with zeros
Range ("A1:C10").Value = 0
' Cut the value in cell A1 and paste it in cell B4
Range ("A1").Cut Range ("B4")
' Change the background colour in the range B1:B4 to red
Range ("B1:B4").Interior.Color = RGB(255,0,0)
In the last example we have to use two dots because Interior is an object in itself, in addition to being a property of the Range object. Color is a property of the Interior object.
Referring to an object in a collection
How can we refer to an object in a collection? There are several options. First we can use an index starting with 1 for the first object in the collection and increasing with 1 for each successive object. To refer to the first Worksheet object in the Worksheets collection we write
Worksheets(1)
The index is written in parentheses after the name of the collection object.
Another option is to use the name of the object, for example “Sheet3”. This is a string and must therefore be enclosed within double quotation marks.
Worksheets ("Sheet3")
Be aware of the fact that the worksheet with the name “Sheet3” is not necessarily the same as the worksheet with index 3. A benefit of using the name is that we do not need to worry about where in the collection the worksheet is stored.
When Range is used in the examples above it is implied that we are referring to a range in the active worksheet. The active worksheet is the one in the topmost window. To refer to another worksheet in code we must qualify Range with the name of this worksheet:
' Place the value 1000 in cell A1 in the worksheet named Sheet3 Worksheets ("Sheet3").Range("A1").Value = 1000
Qualification means that we write the name of the parent object followed by the name of the object we are referring to separated by a dot. To get a fully qualified name we start at the top of the object hierarchy and write the names of all objects we’re passing through on our way to the one we’re referring to. Remember to put a dot between names.
' Fully qualified name
Application.Workbooks ("Book1").Worksheets("Sheet1").Range("A1"). _ Value=1000
Application is always assumed and can be left out. More objects can also be dropped. If "Book1" is the active workbook we can skip Workbooks ("Book1") and write
Worksheets("Sheet1").Range("A1").Value = 1000
If “Sheet1” is the active worksheet we can also leave out Worksheets("Sheet1")
Range("A1").Value = 1000
Beware that this will break the code if the user changes to another workbook or worksheet before the lines are executed. To avoid this we can set the active workbook and worksheet from code like this:
' Activate Book1
Workbooks ("Book1").Activate
' Select sheet 3
Worksheets(3).Select
This can also be combined into
Workbooks ("Book1").Worksheets(3).Select
Object variables
An object variable is a variable that can refer to an object. Object variables are used because there are several advantages to this. We assign an object to a variable for the following reasons.
• A variable name is often shorter and easier to remember than the full path. We can therefore shorten the code we have to write.
• It leads to more effective code because VBA does not repeatedly need to resolve the full path of methods and properties.
• Different objects can be assigned to the variable while the program is running.
Before we can use the object variable it must be declared (using one of the Dim, Public, Private, or Static keywords).
Dim r As Range
Having done that we can assign a Range object to the variable.
Set r = Application.Worksheets ("Sheet2").Range("B4:B8")
r can now be used to refer to this object.
' The next two lines have the same effect
' ClearContents deletes the formulas from the specified cell range
' Which one do you prefer?
Application.Worksheets("Sheet2").Range("B4:B8").ClearContents r.ClearContents
One important change from an ordinary variable assignment is that when we have an object variable, it has to be assigned with the Set keyword. If you forget to do this you will get a perhaps non-intuitive error message like the one shown in the message box in figure 16.4.
Figure 16.4 Run-time error message.
The syntax is:
Set objectvariable = Object
The Range object is one of the most used objects in VBA. It represents a cell, a row, a column, a selection of cells containing one or more contiguous blocks of cells, or a 3-D range. It also has many important properties.
We can specify cell ranges in many different ways. One of the most common is to use a string:
Another option is to use a variable of the object type Range:
Dim prices As Range
Set prices = Range("B1:D3")
As you know by now, you can create Excel names that refer to cells, a range of cells, a constant value, or a formula. These names can be used in VBA to replace values or cell references. If we have created the name “price” representing a range in Excel, we can write:
Range("price")
The name is a string, hence the double quotes.
Let’s now look at some of the methods and properties that can be used on Range objects. For a complete list you must search for Range Object Members under Excel help.
Value
We’ll start off with Value which is used for getting or setting a cell value. The value in cell A1 can be read with:
Dim val As Double
val = Range("A1").Value
and set with:
Range("A1").Value = 67
To put the same value in a cell area that consists of more than one cell we can write:
Range("A1:A10").Value = 67
The number 67 will then be stored in each cell in the range.
Value is the default property for the Range object, which means it can be omitted.
Range("A1") = 67 'same as Range("A1").Value = 67
Range objects have a Cells property that returns a reference to a Range object. Yes, Cells is both a property of Range and a Range object. It is commonly used to refer to cells within a range.
' Refering to the 4th cell in the range C10:C20, i.e. cell C13 Range(″C10:C20″).Cells(4)
' Refering to the cell in row 5, column 6, i.e. cell H14 Range("C10:H20").Cells(5,6)
Cells can also be used for specifying the first and last cell in a range
' Set the font style for cells A1:C5 to italic
Range(Cells(1, 1), Cells(5, 3)).Font.Italic = True
The following example can be used to verify if the numbers in cells A1:A100 are in ascending order4
To refer to the cells B1:B5 we can write Range(″B1:B5″) or Range(Cells(1, 2), Cells(5,2)). It is often more convenient to use the last method, as we then can use variables to refer to each cell within the area as shown in the example above. It is possible to achieve the same effect without using Cells, and instead use the string concatenation operator:
' Fill the area C1:C10 with numbers from 1 to 10
For n=1 To 10
Range(″C″ & n) = n
Next n
Offset
Offset returns a Range object that represents a range that’s offset from a specified range. It is used for moving around the worksheet. The syntax is:
Offset(RowOffset, ColumnOffset)
where the parameters are as given in Table 16.10.
Table 16.10 Offset parameters
|
The number of rows to move. Can be positive, 0 and negative. Positive numbers will move downwards, while negative numbers move upwards. |
|
The number of columns to move. Can be positive, 0 and negative. Positive numbers will move right, while negative numbers move left. |
Examples
' Move one cell down from D5 (D6)
Range(″D5″).Offset(1, 0).Select
' Move three cells to the left from D5 (A5)
Range(″D5″).Offset(0, –3).Select
' Move four cells down, and two cells right from the selected cell
ActiveCell.Offset(4, 2).Select
' Copy the active cells and the two cells below it one column right
Range(ActiveCell, ActiveCell.Offset(2, 0)).Copy _
Range(ActiveCell.Offset(0, 1), ActiveCell.Offset(2, 1))
Rows, Columns, Row, Column, EntireRow, EntireColumn
The Rows and Columns properties return a Range object that represents the rows or columns in a specified range. Some examples are given in Table 16.11.
Table 16.11 Rows and Columns examples
Expression |
Represents |
|
all the rows on the active worksheet |
|
all the columns on the active worksheet |
|
row 1 on the active worksheet |
|
column 2 on the active worksheet |
|
column 3 on the active worksheet |
|
all the rows on Sheet2 |
|
column 1 on Sheet1 |
|
column 2 in the specified range, i.e. column C |
When applied to a Range object that’s a multiple selection, the Row and Column properties return the number of the first row or column in the first area in the specified range.
Worksheets(″Sheet1″).Range(″C5:D9, G2:H16, B1:D18″).Rows.Count ' prints 5 Debug.Print Range(″C5:F10″).Row 'prints 5
The EntireRow and EntireColumn properties return a Range object that represents the entire row(s) (or column(s)) that contain the specified range.
' Put a continuous border around the rows 5 to 10 Range(″A5:B10″).EntireRow.BorderAround xlContinuous
The following example will set the font style to italic in the first cell of the column where the active cell is:
ActiveCell.EntireColumn.Cells(1,1).Font.Italic = True
To select the entire row or column where the active cell is we can write
ActiveCell.EntireRow.Select
ActiveCell.EntireColumn.Select
Sometimes we want to select all consecutive cells in a row or column that has some content. This can be achieved with:
' Select cells in a row starting with the active cell and going
' right until an empty cell is found Range(ActiveCell, ActiveCell.End(xlToRight)).Select
' Select cells in a column starting with the active cell and going
' down until an empty cell is found Range(ActiveCell, ActiveCell.End(xlDown)).Select
End represents the cell at the end of the region that contains the source range. The direction can be specified with xlToRight, xlToLeft, xlUp, and xlDown.
After a selection has been made we can iterate over each cell with a For Each…Next loop.5
Dim r As Range
For Each r In Selection
' Do something with r
Next
The properties Rows, Columns, Row, Column, EntireRow and EntireColumn are read only.
SpecialCells, CurrentRegion and UsedRange
A selection can consist of cells with different cell type such as numbers, text, formulas. Some cells may also be empty. The SpecialCells method returns a Range object that represents all the cells that match the specified type and value. The next example shows how we can use SpecialCells to select only cells that contain numbers.
Dim r As Range
Dim numCells As Range
' Let numCells only contain cells with numbers
' xlCellTypeConstants means we’ll be looking for cells containing
' Constants, and xlNumbers means that the value must be a number.
Set numCells = Selection.SpecialCells(xlCellTypeConstants, _ xlNumbers)
For Each r In numCells
' Do something with r here
Next
On Error GoTo 0
The SpecialCells method will fail if there are no cells to meet the criteria. We use On Error Resume Next to guard against that situation.
Two other properties used to select cells are CurrentRegion and UsedRange. CurrentRegion returns a Range object that represents the current region. The current region is a range bounded by any combination of blank rows and blank columns. It is illustrated in figure 16.5. UsedRange returns a Range object that represents the used range on the specified worksheet. See figure 16.5.
Figure 16.5 CurrentRange (shaded) and UsedRange (framed).
If the active cell is E7, CurrentRegion is the Range C5:F7. If the active cell is G12, the CurrentRegion is G11:G12.
The properties CurrentRegion and UsedRange are read only.
Union, Intersect
The Union and Intersect methods let us find the union or rectangular intersection of two or more ranges (max. 30). The union of two or more ranges is a range containing all of the cells in those ranges. The intersection of two or more ranges is the range containing only the cells contained in every range. For example, the union of the ranges A1:C3 and B1:D3 is the range A1:D3. The intersection of those two ranges is the range B1:C3.
Ranges must be separated with commas:
Union(Range(″A1:A10″), Range(″B15″), Range(″C1:C6″))
Intersect(Range(″A1:C3″), Range(″B2:B10″))
This example fills rows 1, 2 and 3 with random numbers:
Dim r As Range
Worksheets("Sheet1").Activate
Set r = Union(Rows(1), Rows(2), Rows(3))
r.Formula = ″=RAND()″
Referring to cells relative to other cells
By combining Range and Cells we can use Cells to refer to cells relative to a range. The following example is referring to a cell relative to the range A2:G10, i.e. cell F6.
Range(″A2:G10″).Cells(5, 6).Value = 700 ' writes 700 in cell F6
In the next example we’re referring to cell C3.
Range(″A2:G10″).Cells(10).Value = 800 ' writes 800 in cell C3
Start counting 10 cells to the right of cell A2. Continue on the next row when the last column in the range (G) is reached.
The Offset property can also be used to refer to a range that’s offset from specified range. Here’s how:
|
' |
' |
This statement returns a Range that is offset three rows down, and one column to the left of C2:G10, i.e. B5:F13.
The With statement
The With statement allows you to perform a series of statements on a specified object without requalifying the name of the object. The general syntax is:
With object
' statements
End With
Suppose you want to call many methods and properties on an object. One way to do it is to write:
For every method and property called we must qualify the name with the name of the object. Using the With statement saves us from all this typing. Instead we can write
With anObject
.Method1
.Method2
.Method3
If .Property1 = 100 Then …
End With
A method or property written within the With statement is automatically qualified with the name of the object written after the reserved With. That is, if there is a dot in front of the method or property name. A few more examples will clarify this.
Code written like this:
Dim area As Range
Set area = Application.Worksheets("Sheet1").Range(″E5″)
With area
area.Cells(1, 2).Value = "Expenses"
area.Cells(1, 2).Font.Name = "Bookman"
area.Cells(1, 2).Font.Size = 14
area.Cells(1, 2).Interior.Pattern = xlPatternCrissCross
area.Cells(1, 2).Interior.ColorIndex = 8
can be written more compactly using the With statement:
With area
.Cells(1, 2).Value = "Expenses"
.Cells(1, 2).Font.Name = "Bookman"
.Cells(1, 2).Font.Size = 14
.Cells(1, 2).Interior.Pattern = xlPatternCrissCross
.Cells(1, 2).Interior.ColorIndex = 8
End With
or as
With area.Cells(1, 2)
.Value = "Expenses"
.Font.Name ="Bookman"
.Font.Size = 14
.Interior.Pattern = xlPatternCrissCross
.Interior.Colorlndex = 8
End With
Nested With statements are also possible. However, the members of outer With blocks are masked within the inner With blocks. You must therefore provide a fully qualified object reference in an inner With block to any member of an object in an outer With block.
We’re indenting each new With statement to clearly see where each one begins and ends.
The spreadsheet in figure 16.6 summarizes sales by sales representative and quarter. We will create a pivot table from the data. The table is set up with the following fields:
• SalesRep – a row field
• Quarter – a column field
• Sales – a data field.
Figure 16.6 Sales worksheet.
The code to create the table is shown below, and the resulting table is shown in figure 16.7.
Sub TotalSales()
' Creates a pivot table from the data in figure 16.6
' For a detailed description of the objects
' PivotCache, PivotTable, and PivotFields,
' please refer to the description found under Excel help
Dim pivCache As PivotCache
Dim pivTable As PivotTable
Set pivCache = ActiveWorkbook.PivotCaches.Add(xlDatabase, _ Range(″A1:C17″))
Set pivTable = pivCache.CreatePivotTable(″″, _ ″SalesPivotTable″)
With pivTable
.PivotFields("SalesRep").Orientation = xlRowField
.PivotFields("Quarter").Orientation = xlColumnField
.PivotFields("Sales").Orientation = xlDataField
End With
End Sub
Figure 16.7 Pivot table example worksheet.
Problems
16-1. Write a formatting procedure that changes the content in the range A1:B5 like this:
• uses the “Microsoft Sans Serif” font
• sets the colour to blue
• makes the content bold.
16-2. Create a macro that puts a frame around the current region.
16-3. Create a macro that inserts a new worksheet as the first worksheet in the workbook. Set the title to “Content".
16-4. Create a macro that calculates the number of days between two dates read from the worksheet cells A1 and B1. The result is written in cell C1.
16-5. We wish to calculate the present value of an investment. The present value is the sum of what a series of periodic payments is worth today. For example, if you borrow the money, the amount borrowed is the present value for the lender. Data is read from cells B1, B2 and B3 in the worksheet. When the user clicks on the button the present value is calculated and shown in cell B4. Use the following formula:
PV is the present value, C is the periodic payment, r is the annual interest rate, and n is the number of payments.
Notes
1 Syntax is the set of rules that define the combinations of symbols that are considered to be correctly structured programs in that language.
2 VBA will use the Variant data type for variables that are not declared. You may change this with the DefType statement. See VBA help for more information.
3 The median is the number in the middle of a set of numbers.
4 To fully understand this example you must read about the For … Next loop in chapter 17.
5 The For Each … Next loop is explained in chapter 17.