VBA Training

QAEX10VBAA v1.
3 Microsoft VBA 2010 – Excel Advanced
Copyright Statement
QA Limited owns all copyright and other Intellectual Property Rights and asserts
its moral right to be regarded as the author of this material. All rights reserved. No
part of this publication may be reproduced, stored in a retrieval system, or
transmitted in any form or by any means electronic, mechanical, photocopying,
recording or otherwise, without prior written permission of the copyright owner.
Contents may be subject to change at the discretion of QA Limited and without
notice.
QA Limited shall not be liable in any way in respect of any loss, damages, costs,
liabilities or expenses suffered by the user, whether directly or indirectly as the
result of the content, format, presentation or any other use or aspect of the
Materials. The user shall not make, give or supply any guarantee, warranty or
other undertaking as to the appropriateness or other attributes of Materials or the
Course which binds or purports to bind QA limited or its Affiliates.
The operating systems and applications used for the purpose of fulfilling QA
training events or referred to in this courseware are used for demonstration
purposes only.
Copyright © 2013 QA Limited
All Rights Reserved

Microsoft VBA 2010 – Excel Advanced QAEX10VBAA v1.3
Contents
Module 1 - Excel 2010 Object Hierarchies and the Object Browser...................... 1
Topic A - The Excel Component Object Model .................................................. 1
Topic B - Using Methods, Events and Constants .............................................. 4
Topic C - Using the Object Browser................................................................... 5
Topic D - Object Variables and Binding ............................................................. 7
Topic E - Creating User Defined Types and Classes ...................................... 11
Topic F - Creating a Class: What is a Class? .................................................. 14
Module 2 - Working with the Range Object ......................................................... 19
Topic A - Range Objects Defined .................................................................... 19
Topic B - Referencing Ranges using VBA ....................................................... 20
Topic C - Dynamic Range Handling................................................................. 22
Topic D - Copying or Moving Range Data ....................................................... 27
Topic E - Creating Array Variables .................................................................. 32
Module 3 - Working with Data in Excel ............................................................... 45
Topic A - Importing Data from a Delimited File or Web Page .......................... 47
Topic B - Excel and ADO ................................................................................. 49
Topic C - Data Types or Formatting ................................................................ 54
Topic D - Validating Data ................................................................................. 57
Topic E - Filtering and Sorting Data with VBA ................................................. 60
Topic F - Using Worksheet Database Functions ............................................. 66
Topic G - Excel Tables .................................................................................... 68
Module 4 - Presenting Data in Excel ................................................................... 75
Topic A - Apply Conditional Formatting with VBA ............................................ 75
Topic B - Working with Charts ......................................................................... 82
Topic C - Pivot Tables ..................................................................................... 87
Module 5 - Working with the Excel Interface ....................................................... 94
Topic A - Working with Page and Print Options ............................................... 94
Page i
QAEX10VBAA v1.3 Microsoft VBA 2010 – Excel Advanced
Topic B - Controlling Visual Elements of a Workbook ...................................... 98

Topic C - Workbook Sharing and Track Changes .......................................... 102
Topic D - Workbook Security Using VBA ....................................................... 106
Module 6 - Managing Files and Folders Using VBA .......................................... 113
Topic A - FileSystemObject............................................................................ 113
Topic B - Managing Folders from Excel ......................................................... 115
Topic C - File Management Tasks from Excel ............................................... 118
Topic D - Working with File Properties and Attributes Using VBA.................. 122
Topic E - Reading a Text File Using VBA ...................................................... 125
Lesson Labs ...................................................................................................... 128
Lab 1-1: Finding a Worksheet ........................................................................ 129
Lab 1-2: Create an Event Driven Macro ......................................................... 130
Lab 1-3: Create a New Workbook Using an Object Variable ......................... 131
Lab 1-4: Creating a User Defined Type ......................................................... 133
Lab 1-5: Creating a Class .............................................................................. 135
Lab 2-1: Creating a Range Object Variable ................................................... 137
Lab 2-2: Counting Records in a Range .......................................................... 138
Lab 2-3: Dynamically Rename a Range ........................................................ 139
Lab 2-4: Copying Data to a New Workbook ................................................... 141
Lab 2-5: Create Fixed and Multidimensional Arrays ...................................... 142
Lab 2-6: Create and ReDim a Dynamic Array ................................................ 144
Lab 2-7: Create, Populate and Use an Array ................................................. 145
Lab 3-1: Create a Query to Open a CSV file as an Imported Item ................. 146
Lab 3-2: Import Microsoft Access Data into Excel .......................................... 148
Lab 3-3: Add Data Validation to a Worksheet ................................................ 150
Lab 3-4: Combine, Filter and Sort Data ......................................................... 152
Lab 3-5: Using a Database Function in VBA .................................................. 155
Lab 3-6: Create an Excel Table Out of a Range ............................................ 157
Lab 4-1: Create and Apply Conditional Formatting ........................................ 159
Page ii
Lab 4-2: Make a Chart and Copy it to PowerPoint ......................................... 161

Lab 4-3: Pivot the Sales Data ........................................................................ 163
Lab 5-1: Work with Print and Page Settings .................................................. 166
Lab 5-2: Hide and Unhide Sheets .................................................................. 168
Lab 5-3: Share a Workbook and Copy Change History to a New Workbook . 170
Lab 6-1: Add the Scripting Library and Create a Folder................................. 172
Lab 6-2: Open a Folder and Write a File List to an Array Variable ................ 173
Lab 6-3: Copy a File to a Folder and Rename It ............................................ 174
Lab 6-4: Use a File’s Attributes and Properties ............................................. 175
Lab 6-5: Read the Contents of a Text File ..................................................... 176
Appendices ....................................................................................................... 177
Sample Answers to Review Questions ............................................................. 183
Module 1: Review Questions .................................................................... 183
Page iii
Table of Tables
Table 1 – UDT Scope .......................................................................................... 14

Table 2 - Class Statements ................................................................................. 16
Table 3 - Special Cell Types ............................................................................... 26
Table 4 - Special Cells Constants ....................................................................... 27
Table 5 - PasteSpecial Types ............................................................................. 30
Table 6 - PasteSpecial Operations ...................................................................... 31
Table 7 - Array ReDim Statement ....................................................................... 37
Table 8 - Common Data Terminologies............................................................... 47
Table 9 - Data Types ........................................................................................... 56
Table 10 - AutoFilter Enumerators ...................................................................... 63
Table 11 - Excel Database Functions.................................................................. 67
Table 12 - Excel Tables ListObject Parameters. ................................................. 69
Table 13 - Excel Tables ListObject Source Options ............................................ 70
Table 14 - Excel Table Total Row Functions ....................................................... 72
Table 15 - FormatConditions Type Expressions ................................................. 78
Table 16 - Pivot Table Data Formats................................................................... 89
Table 17 - Pivot Table Fields ............................................................................... 90
Table 18 - VBA Header and Footer Codes.......................................................... 97
Table 19 - Worksheet Protect Method Arguments ............................................ 110
Table 20 - File Attributes ................................................................................... 123
Table 21 - Range Object Methods .................................................................... 179
Table 22 - Excel Comparison Operators ........................................................... 180
Table 23 - Conditional Format Icon Sets ........................................................... 181
Table 24 - Excel Condition Value Types ........................................................... 182
Page iv
Table of Figures
Figure 1 - Object Browser ..................................................................................... 6

Figure 2 - New Class .......................................................................................... 12
Figure 3 - GUI Paste Special Options ................................................................. 29
Figure 4 - Array in Locals Window ...................................................................... 34
Figure 5 - ADO Hierarchy ................................................................................... 49
Figure 6 - Data Mismatch Error Message ........................................................... 55
Figure 7 - Track Changes Options .................................................................... 103
Figure 8 - Scripting Runtime Library ................................................................. 113
Figure 10 - File Properties and Attributes ......................................................... 123
Page v
How to use this workbook

ACTIVITY USEFUL TOOL
Alongside this icon you will This icon indicates a
find details of the group / technique that will help you
individual activity or a point put what you learn into
for everyone to discuss. practice.
IMPORTANT IDEA OR HELPFUL HINT

CONCEPT
This icon guides you to tips
Generally this icon is used or hints that will help you
to draw your attention to avoid the standard pitfalls
ideas that you need to that await the unwary
understand by this point in practitioner or to show you
the course. Let your trainer how you might increase
know if you do not your effectiveness or
understand or see the efficiency in practising what
relevance of this idea or you have learned.
concept.
KEY POINT REFERENCE MATERIAL
This icon is used to indicate When we have only touched
something that practitioners briefly on a topic this icon
in this field should know. It is highlights where to look for
likely to be one of the major additional information on the
things to remember from the subject. It may also be used
course, so check you do to draw your attention to
understand these key International or National
points. Standards or Web
addresses that have
interesting collections of
information.
DEFINITION WARNING
Where a word with a very This icon is used to point out
specific definition (or one important information that
that could be described as may affect you and your use
jargon) is introduced this will of the product or service in
highlight that a definition is question.
provided. (These words will
also be found in the
Glossary at the back of the
workbook.)
Page vi
Use of Shortcut Keys

Shortcut keys are provided using the following structure:
{SHIFT} Use of Braces (Squiggly brackets) specifies keys that should be

held down.
[INSERT] Use of Parameter (Square Brackets) specifies a key that should be

pressed once.
Such combinations of these are shown as follows:
{SHIFT} + {CTRL} + [DELETE] Specifies that you hold down the SHIFT and
CTRL keys together, press the DELETE key once, than release the SHIFT and
CTRL keys.
Use of Code
All code is written in Courier New font. When a single line of code written in
the Visual Basic Editor (VBE) has to be written on two or more lines of text in this
manual, VBA line breaks (space plus underscore) are added to the code. For
example the following code can be written on one line in the VBE.
Application.Workbooks("Q3_Sales.xlsx") _
.Worksheets("Sales").Activate
Page vii
Module 1 - Excel 2010 Object Hierarchies and the

Object Browser
This module is about:
 The Excel Component Object Model.
 Using Methods, Events and Constants.
 Object Variables and Binding.
 Creating User Defined Types and Classes.
Topic A - The Excel Component Object Model

At the very core of Visual Basic for Applications, or VBA, sit Objects. Objects
exist in all manner of forms, some of which are generically referred to across
Microsoft Office 2010, and some are application specific.
Our purpose here is to work with Objects across Microsoft Excel 2010. Excel has
a very large collection of Objects built in. These combined with the common
Office 2010 Objects, references to external Objects, and any user defined
Objects, make Microsoft Excel a very powerful and customisable tool.
Understanding where Objects are stored or referenced, the hierarchical structure

of Excel 2010, together with what you can or cannot do with the Objects, is the
key to successful programming in Excel 2010. We will now review Objects.
Objects
Put simply, an Object is an element or part of an application that can be
accessed and manipulated, either programmatically or directly from the GUI.
What you are able to do with the Object using VBA, will largely be determined by:
 The type of Object.
 How the Object is referenced by your code.
 Whether the Object is visible to your code.
Page 1
Take a very large object such as a workbook. Consider what actions you can
carry out on this type of object: Save it, Open it, Close it. Now consider a smaller
Object such as a range or chart in an Excel worksheet. What can you do to those
Objects?
The Type of Object

Some Objects such as workbooks or Office documents will have generic Methods
or Events right across Office 2010, such as Save, Open or Close and are coded
in the same way. Think again about the smaller Objects referred to in the
previous paragraph. The properties of those examples are quite different, so
what you can do with them will also differ.
How the Object is to be Referenced by Your Code

Consider a document file. If the file is opened using code, directly into an
application, then all the actions you might carry out within that application are
available to you. If the file is referenced without being opened, you could for
example, move it with code to another folder. You cannot do that if the document
is opened in the application without the correct Object reference.
Whether the Object is Visible to Your Code

You can only work directly with an Object if the code can ‘see’ it. It needs to be
made available to the code for this to happen. Being able to work with an
application specific Object is a default behaviour of VBA, if you have the
application open. Consider that you have a table of Excel data you need to use
in a Word report. Excel cannot directly recognise Word Objects such as a Word
document without it being referenced. Later in this chapter we will look at the
Reference Libraries which can be loaded into applications.
Collections
All the Objects that you might reference with your code are held in Collections or
represent a Collection. It is a simple concept, and knowing which Collection
contains the Object you need to manipulate, is a good start to identifying what
you will be able to do with the Object.
For examples, imagine your application is a supermarket. A supermarket will

have different departments and each department will contain its own specific
products. Some products will have different properties and may allow or require
you to carry out different actions on them, using pre-set methods. Some need
cooking, some opening first, some can be eaten straightaway.
Page 2
Excel itself can be seen as a part of a Collection called Office 2010. It is an

application Object. Every time an Excel workbook Object is opened, it is found in
the workbooks Collection. Each workbook may have from 1 to many worksheets.
These are contained in the sheets Collection. Every sheet has rows, columns,
cells which a part of the range Collection.
You can reference an individual Object by referring to the Collection. You can
also reference the contents of a Collection by referring to the Collection itself.
This example might cycle through all the sheets in the active workbook using the
sheets Collection and prints the name of each sheet into the Immediate window.
For Each S In ActiveWorkbook.Sheets
SName = Sheets(counter).Name
Debug.Print SName
counter = counter + 1
If counter <= Sheets.Count Then
Sheets(counter).Activate
Else
Exit Sub
End If
Next S
For further information on the members of a Collection, use a combination of the

Visual Basic Help and the Object Browser. We will discuss the Object Browser
later.
Hierarchies
Objects are ultimately referenced using a hierarchical system within the VBA
environment. Take the following line of code as an example:
Application.Workbooks("Q3_Sales.xlsx").Worksheets("Sales").Activate
The hierarchy is seen as you read it from left to right. It is absolute in its
referencing and older versions of the Basic language required this level of detail
to function correctly. As VBA has been developed over the years, the referencing
methods have altered. However, to correctly make the “Sales” sheet active, you
still need to know the hierarchy to get to it. This line may do the same if the
correct workbook is active:
ActiveWorkbook.Worksheets("Sales").Activate
As will this:
Sheets("Sales").Activate
Page 3
The name of the Workbook might have been stored as a variable, and this is also
a valid code solution to reference a member of a Collection. For example, the
variable Q3 has been assigned the name of the “Q3_Sales.xlsx” file. As long as
the file is open, this code will activate the worksheet.
Sub GetSalesChartSheet()
Dim Q3 As Workbook
Set Q3 = Workbooks("Q3_Sales.xlsx")
Q3.Sheets("Sales").Activate
End Sub
In the above examples we are seeing references to Parent and Child Objects.
The “Sales” worksheet is the Child and the “Q3_Sales.xlsx” is the Parent.
Collections may also have a similar relationship with Objects above and below it
in the hierarchy.
Module 1 Lab 1: Finding a Worksheet. Turn to page 129 to complete the Lab.
Topic B - Using Methods, Events and Constants

Often, the purpose of a line of code is to have an effect on the properties of an
Object. Consider these lines of code:
Sub AddCustomerName()
Sheets("Sales").Activate
Range("A3").Select
Activecell.Value = "ABC Ltd"
ActiveWorkbook.Save
ActiveWorkbook.Close
End Sub
At first glance, it is apparent that the ultimate aim of the procedure is to enter
‘ABC Ltd’ into cell A3. Is there any evidence of working with a hierarchy here?
We can see the references to Objects in Collections. Writing the value into the
cell alters its property. The property in question is Value.
How has the property been changed? In order to get to the point where the code
can alter the property, it needs to carry out a set of actions on the appropriate
Objects. These actions are called Methods.
Page 4
A Method is an action that code may invoke on an Object. The effect of a

Method is dependent on the recipient Object. In other words the Object type
determines the effect of the Method.
In the example above, Select and Save are both Methods.
Closing the workbook and activating the worksheet are Events. The differences
between Methods and Events can be minimal. We could have used the Select
command on the Sheet and it would still be referred to as a Method in VBA. In
essence we see Events applied to actions on Objects, which in the application
GUI are replicated with a Windows action, e.g. closing a file or clicking a
worksheet tab to give it focus.
Module 1 Lab 2: Create an Event Driven Macro. Turn to page 130 to complete the
Lab.
Topic C - Using the Object Browser

Whether you work solely in one application to write the VBA code, or across
different applications, the Object Browser is a tool to help you navigate
Collections and their Members. It is also an essential reference point to identify
what Methods, Events and properties are available for each type of Object and
Collection. To view the Object Browser, click the icon on the Standard toolbar or
press [F2].
Page 5
Figure 1 - Object Browser
The top left hand corner identifies the Library to display the Objects from.
Libraries are discussed later in this chapter.
Enter the name of the Object you need information on, whether that be what it is
related to, or what actions can be carried out upon the Object. The icons
displayed show what an entry in the Object Browser signifies.
Icon Item
procedure
property
constant
event
Page 6
Topic D - Object Variables and Binding

Most of the variables you create in VBA will be Data variables to hold transient
information, such as the name of a document, or the contents of a cell. A
separate class of variables exist in VBA and these are Object variables. These
hold references to an actual class of Object, whether existing, or as newly
created Objects in VBA.
Object variables are declared using the same keywords as Data variables and
their scope follows the same principles.
Information: Object Data Type

Object variables are stored as 32-bit (4-byte) addresses that refer to objects. Using
the Set statement, a variable declared as an Object can have any object reference
assigned to it.
Referencing an Existing Object

To use Object variables to reference an existing Object, the Object must be
‘visible’ to the code. It does not need to be active in the application’s GUI. The
variable can also reference different Objects at run time.
An important difference between Object variables and Data variables is the use
of the VBA keyword Set. The Object variables are declared using standard
keywords such as Dim or Public, but they must be given a value before they can
be used in the code. The Set keyword is used to give a value to the variable and
binds the object to the variable.
1st example references the active Excel document to print it.
Dim MyWbk as Workbook
Set MyWbk = ActiveWorkbook
MyWbk.PrintOut
The use of the variable here also allows IntelliSense to work. Typing MyWbk
followed by the Dot will display a list of actions available for the ActiveWorkbook.
2nd example populates a variable with the ActiveCell in an Excel worksheet and
then allows us to access the relative Address property for that cell.
Dim MyCell as Range
Set MyCell = Activecell
MsgBox MyCell.Address (False, False)
Page 7
Creating an Object (Instantiating)

To create a new instance of an Object, you use syntax or a construct, which is
similar in principal to that used when referencing an existing Object.
The Object variable is declared as an application specific object and the Set
keyword is used to call the Add Method to create the new Object. When you
create an Object using a variable, the declaration of the variable tells VBA which
Collection the Object will become a member of. The Set statement adds the
instantiated Object to the Collection.
'creates a new workbook
Dim MyTempBk As Excel.Workbook
Set MyTempBk = Excel.Workbooks.Add
You can see from the code above, that Object type is explicitly declared. The
Add Method is then used for the new Excel workbook.
Do not assume that when adding a new Object to its Collection that it will become
the active object. We can use the Set statement to make an Object active, so
that VBA can reference it if required.
Dim MyTemBk As Excel.Workbook
Set MyTemBk = Excel.Workbooks.Add
Set MyTemBk = ActiveWorkbook
Here we have used the Workbooks Collection Add Method, to create a new Excel
document. The second Set statement updates the workbook property to make it
active.
Whilst not required, it is good practice to formally remove an Object variable from
memory, once it is no longer required by your procedure.
Use the following code to remove an Object variable from memory:
Set MyTemBk = Nothing
Instantiating Objects using Object variables is not limited to creating application

centric Objects from within that application. As long as the appropriate
Reference Library is loaded into your Project, you can reference and create
Objects in another application.
Binding
Once you have instantiated an Object, your Project is ready to use the
functionality which the Object supports. The initial consideration when
instantiating the Object should be when to bind it. Binding is the method used by
Page 8
the Common Object Model or COM architecture to create a connection to the

Object from the Reference Library that contains the Object type. The Object
does not exist in memory until it is bound.
Late Binding v Early Binding

Whilst the code is in a design mode, the Object variable exists only as a generic
type if you do not set a specific Object type reference to it.
Dim MyWbk as Object
 The variable in the above code will only allow late binding. In other words, the
variable will only be bound to an Object type at run time or when the code
executes.
 Explicitly declaring the Object variable as a type at design time, is known as

early binding. This associates the variable with a defined Reference Library.
Note this means that the variable will only be usable if the Library is available
at run time.
Dim MyWbk as New Excel.Workbook
Set MyWbk = Workbooks.Add
This example of early binding forces VBA to associate the variable with the
correct Reference Library. Therefore it will enable IntelliSense to work as well,
only allowing the methods and properties that can be associated with the Object
type.
In addition to the code design enhancements, another advantage offered by early

binding is speed. The VBA code will run faster because fewer calls to resolve the
Object type are required. Late binding requires VBA to firstly handle a String
name for the variable, before it can then associate it at run time with the correct
type.
Why use late binding? Late binding allows you to instantiate an object against a
Reference Library that is not explicitly referenced in the code. Early binding ties
a variable to a Library which is version specific, e.g. Microsoft Excel 12.0 Object
Library. Late binding in the code removes the Library version information,
thereby allowing the object to use whichever version of Excel is loaded at run
time. In other words, different versions of Excel could run the same macro.
Ensure that your declaration statement references the application, if your code
works across other applications. For instance, Excel and Word both have Range
objects.
Page 9
Dim MyRange As Excel.Range
Scenario
Your company’s annual sales are tracked on an order-by-order basis in an Excel
workbook, with each quarter stored on a separate worksheet within the
workbook. You have a requirement to extract the data from each worksheet and
create a new workbook with all the sales data. This is to enable the new
workbook to be available for future forecasting.
Using objects and any required variables, what steps need to be included in the
procedure? These might be:
1. Open the existing workbook
2. Assign it to an Object variable for easier referencing using VBA
3. Create the new workbook which will be used as the destination for the copied
data
4. Reference the first sheet of data in the existing workbook
5. Capture the data area and copy it
6. Activate the new workbook, find the point at which you will append the data
and paste it in
7. Repeat the actions until all sheets are processed
8. Close the existing workbook
9. Save and close the new workbook
Code for steps 1 to 5 might look similar to this:

Dim wkbSales As Workbook
Set wkbSales = Workbooks.Open("C:\2010 Annual Sales.xlsx")
Dim wkbAllSales As New Workbook
Set wkbAllSales = Workbooks.Add
wkbSales.Activate
Sheets(1).Activate
Range("A1").Select
ActiveCell.CurrentRegion.Select
Selection.Copy
Page 10
Code for step 6 might look like this:

wkbAllSales.Activate
Sheets(1).Activate
Range("A1").Select
If ActiveCell.Value <> "" Then

Do Until ActiveCell.Value = ""
ActiveCell.Offset(1, 0).Select
Loop
End If
ActiveSheet.Paste
Possible code for steps 8 and 9:

wkbAllSales.SaveAs "C:\2010 Sales Summary.xlsx"
wkbAllSales.Close
wkbSales.Close
Finally, the code removes the objects from memory:
Set wkbAllSales = Nothing
Set wkbSales = Nothing
Module 1 Lab 3: Create a New Workbook Using an Object Variable. Turn to page
131 to complete the Lab.
Topic E - Creating User Defined Types and Classes
User Defined Types (UDTs)

You have declared variables to hold data, or to reference an object type in your
code. Unless declared as a Variant, the data variable can only reference a single
data type, even if the value changes during run time.
Likewise, once an Object variable has been declared as a type and then
assigned a value, using the Set keyword means it cannot be used for any other
purpose.
Page 11
Creating your own User Defined Type (UDT) for data, is a useful tool when you
need to create a single variable that can hold related pieces of information.
Creating a UDT in VBA creates a new item, which is visible in the Object Browser
along with its Members. A UDT can also be used to create a variable, which
holds references to different types of Objects.
You can pass UDTs to individual procedures and also declare new variables
based upon your defined UDT, thereby allowing the new variable to directly
reference the Members. The UDT can also contain Arrays.
Figure 2 - New Class
The actual creation process is straightforward, but must follow rules similar to the
declaration of public variables.
Creating a User Defined Type

User Defined Types are created using the Type statement. The statement can
only be placed in the Declarations section of a module, regardless of the module
type or location. The UDT can either be declared as Public, or Private, by using
the appropriate keyword.
The actual syntax used is very similar to a block IF:
Public Type MyData
'set members here
'one line per member!
End Type
You could create a UDT that might reference a complete data record, made up of
several fields, with each holding different types of information. The record is the
UDT and the fields are the Members. The record might be a row in an Excel
worksheet or table.
Public Type Customer
Page 12
ID As Long
CustName As String
CustContact As String
CustCity As String
CustLastOrder As Date
End Type
Using UDTs when Declaring Variables

Having created a new UDT called Customer, you can now declare variables as
its type and those variables will inherit the members so that values can be
assigned.
Sub AddNewCustomer()
Dim NewCust As Customer
NewCust.ID = 345
NewCust.CustName = "UDT Unlimited"
End Sub
Creating UDTs that refer to Objects follows the same process:
Private Type SalesReport
SalesSheet As Worksheet
SalesBook As Workbook
SaleRep As Workbook
End Type
Variables declared as the new UDT, can now be used with all the attributes as
those classically declared, with values assigned as required.
Sub MakeSales()
Dim DoSales As SalesReport
DoSales.SalesBook = Workbooks.Open("Monthly Sales.xlsx")

Range("Sales").Select
Selection.Copy
DoSales.SaleRep.Workbooks.Add
The location of the code that creates UDTs, and subsequently declares variables
based on them, has an impact on scope. The following table identifies the
possible effects.
Page 13
Module/Procedure Create a UDT as Declare variable based on the UDT as
Standard Module Public or Private Public or Private
Class Module Public or Private Public or Private
Form Module Private only Private only
Procedure Cannot be created Local scope only
Table 1 – UDT Scope
It is also possible to pass procedure arguments using your UDTs.

Sub AddReturningCustomer(RetCust as Customer)
RetCust.ID = 678
RetCust.CustName = "Back Again Corp"
End Sub
Module 1 Lab 4: Creating a User Defined Type. Turn to page 132 to complete the
Lab.
Topic F - Creating a Class: What is a Class?

A Class is always defined in a Class Module held within your Project. Each Class
module can contain one class only. A Class is a template for an Object. An
Object can be defined to represent anything that VBA can process. It might be a
reference to a physical object in your application, data or a function. Defining a
Class allows a flexible approach, by combining several properties, methods or
procedures into a single template.
When you declare a variable based on a Class type you instantiate, it becomes
an object which can carry out whatever actions have been attributed to the Class.
You must instantiate a class into an object in order to do anything with it. There is
nothing you can do with a class module beyond creating an object from it. If the
Class has been created as an Object, any variable that uses the Class template
Page 14
inherits the properties, methods and events associated with the Object type. It is
declared in the same way.
Dim PartyBalloon As cBalloon
Set PartyBalloon = New cBalloon
Why Use a Class?

Class Modules were introduced into VBA some years ago to provide a possible
solution to the limitations inherent in User Defined Types.
If you have created a User Defined Type and search for it in the Object Browser,
it will be labelled as a Class. This is not strictly true. The UDT can only
reference an item type but it is not itself an Object and therefore cannot actually
do anything. Creating a Class offers an alternative to UDTs, by providing a single
separate location to store the code which references the Object.
This makes for a simpler structure with which to manage your code. Complex
code can be removed from the Standard modules and the Class becomes a
reusable Object.
Create and Add the Class to the Project

The first step is to insert a Class module into your Project. It will be named as
Class1. The name assigned to your Class by VBA, is the name of the module it
is created in, so rename the module. Naming conventions suggest that the letter
c is placed in front of the actual module name, e.g. cBalloon.
In the module Declarations area, declare the primary components that will make
up the Class item. These are normally declared on a Private basis as they should
only be visible to the module.
Private MoveMe As Shape
Private Colour As String
Private Rise As Double
Private Width As Double
The variables are then referenced by the Public Property statements which will
be required to define the Class members. Remember that properties will provide
a description of an object, which may represent actual data values, or be used to
attribute methods to the object.
The Property keyword is used in three contexts:
Page 15
Property Get Statement This returns the value of a property.
Used for object properties in the same way as object

Property Set Statement
variables, use the Set keyword following declaration.
Property Let Statement Sets a new value of a property.
Table 2 - Class Statements
A typical property will consist of two of the statements outlined above. The
Property Get statement is generally used to retrieve a value first, followed by the
Let or Set statements. There may be some overlap between the statements, so
the basic rule to follow is: Let for data and Set for an object.
The properties are typically read-write, so that the calling procedure will use the
appropriate values for the Class. You start with the Get statement for the private
variable defined for the Class. Note the syntax used here: ‘Up’ is a public
property for the Class.
The property names together with any arguments, must match for each pair of
Property statements. In the following example, the Get statement will assign any
value held by the variable ‘MoveMe’ Should the value not exist, or if it needs to
be overwritten, then the Let statement uses an argument called ‘MyVal’ to
populate the property. The value for the argument will be assigned in the
procedure or function that calls the Class.
Public Property Get Up() As Double
Up = MoveMe
End Property
Public Property Let Up(ByVal Myval As Double)

MoveMe = Myval
End Property
A pair of statements is required for each Private variable declared for the Class
object.
Tip: To help write the statements use Insert, Procedure and choose Property as the
Type.
Page 16
Using the Class Object

Having created the Class Object, you can now use it in your Standard and Form
modules. Within any procedure or function, declare a variable based upon the
new Class and then instantiate a copy of the object.
Dim PartyBalloon As cBalloon
Set PartyBalloon = New cBalloon
The variable will now have all the defined Public properties of the Class available
for use within the procedure.
PartyBalloon.Up = MoveUp(Count:=2)
Module 1 Lab 5: Creating a Class. Turn to page 134 to complete the Lab.
Module 1 Review
This module was about:
 The Excel Component Object Model
 Using Method, Events and Constants
 Object Variables and Binding
 Creating User Defined Types and Classes
Review Questions:
1. Name an Event for an Excel workbook
2. How can you display the Object Browser?
3. What is a Reference Library?
4. Describe a benefit of Early Binding
5. Where must a User Defined Type be declared?
6. How do you create and name a Class?
Page 17
Notes
www.qa.com
Page 18
Module 2 - Working with the Range Object

 Range Objects Defined
 Referencing Ranges using VBA
 Dynamic Range Handling
 Copying or Moving Range Data
 Creating Array Variables
Introduction
We have seen in the previous module, that the Excel application is constructed
from a series of components or objects. Each of the objects can be referenced
and manipulated in order to work with its properties. In this module, we will build
on the first module and concentrate on using VBA with the object most often
referred to in VBA. This is the range object.
This module will examine how VBA can reference static or dynamic ranges, in
order to work with the contents of a range. The module is concluded with an
introduction to using Array variables in your VBA code.
Topic A - Range Objects Defined

Let us start by giving a definition of a range. We typically associate the word
range with a group of cells. This may be true or it may not. In VBA terms, a
range is any region on an active worksheet that contains cells.
The reference to worksheet is important here. A range object is often coded as:
Range(″B3″).Select
The actual code is a shortcut to:
ActiveSheet.Range(″B3″).Select
References in ranges are only absolute, if there is an active sheet that can be
accessed by the code. For instance, if cell C2 was already selected or active on
a worksheet, and the following code was used, the result might be a little
unexpected!
Page 19
Selection.Range(″B1″).Select
The code would select cell D2. Here the code is relative to the selection and
therefore moves the Active Cell 1 column and 1 cell to the right of C2.
It is therefore extremely important to ensure that your code can ‘see’ an active
worksheet. This allows you to successfully work with a range object, whether the
range is defined in your code using cell references, or is already named within
the workbook. The workbook or worksheet does not need to be visible on the
screen, as the code can open a virtual copy, or refer to an open workbook and
not require the screen to be refreshed. This speeds up your code.
A range may consist of a single cell, a group of contiguous cells or a 3D

reference. The range object has many methods and properties, all of which are
exposed to VBA. Do not confuse the VBA keywords Cells and Range. Range is
used to refer to specific cells on an active sheet, whereas Cells is typically used
with index numbers representing row and column references often populated
using variables. This means that Cells is generally used where a single cell
reference is needed, and where the actual cell reference is not known.
Topic B - Referencing Ranges using VBA

As stated in the previous section, the code needs to be able to ‘see’ the range to
successfully apply a method or work with its properties. Consider this code and
its syntax.
expression.Range(Cell1, Cell2)
Expression is a variable. It is optional, but must refer to a worksheet if used. If it

is not used, then the active sheet is used by the code. Incorrect referencing can
lead to coding errors.
Cell1 is required and is a variant data type. It must either be a name or an A1
style reference which can include the following operators:
 Colon. This is the range operator to signify the inclusive start and finish of the
range.
 Comma. This is the union operator to reference non-contiguous cells.
You can include dollar signs. However, these are ignored by the code.
If a name is used this equates to a string or cell references, even if the local
range name is referred to using a variable in the VBA.
Page 20
Cell2 is optional. If it is included, it follows the same guidelines listed for Cell1.
The following 5 examples are valid to work with the same range on Sheet1.
Range(″A1:C5″).Select
ActiveSheet.Range(″A1:C5″).Select
Worksheets("Sheet1").Range("A1:C5").Select
Range(Cells(1, 1), Cells(5, 3)).Select
Dim myRange as Range

Set myRange = Range(″A1:C5″)
Range(myRange).Select
Which of the examples above does not require the active sheet to be Sheet1?
To refer to non-contiguous cells use the following syntax, if the cells have not
already been defined using a local name.
Range(″A1″,″C5″).Select
Methods for Working with a Range

A Method is fundamentally an action which can be carried out on an object to
expose a property of that object. When you work with ranges in the Excel GUI,
you will use commands from a variety of sources, such as the Ribbon or from a
right-click menu. These commands are Methods. See Table 21 - Range Object
Methods in the Appendix, which lists a selection of the Methods available to use
on a range from within VBA. Whilst the list is not exhaustive, it is immediately
apparent that it matches commands found in the Excel GUI.
We will use some of the Methods as we work through topics in this workbook.
The Methods work in a similar way to Functions. They are all easily accessed
using IntelliSense and most then prompt for the arguments needed to ensure the
Method works.
Whilst we often use Methods to access properties, we can often refer directly to
the property of the range object to set a value. This example code uses the
property to ensure all the columns in a range are sized for the data the cells
contain:
Range(myData).Columns.AutoFit
This example changes the Boolean state to temporarily remove a lock on editing
the cells in a range:
Range(myData).AllowEdit = True
Page 21
The range references we have discussed so far are fairly typical, where the range
is predefined in terms of the rows and columns it encompasses. In the next
section, we will concentrate on dynamic ranges.
Module 2 Lab 1: Creating a Range Object Variable. Turn to page 137 to complete
the Lab.
Topic C - Dynamic Range Handling

It is often the case that we work with data which will vary in its dimensions. The
number of columns is often predefined, if they represent fields in a table of
records. The number of records, or indeed the actual contents of the records,
may vary every time we need to process the data using code. This means that
our code needs to incorporate commands that will react to say: a different row
count, every time the macro is run. Writing code that is flexible enough to be
applied to different data sets can be one of the most challenging tasks
undertaken in VBA.
Consider a scenario where you have a workbook that contains a list of all your
customers, who have placed orders in an individual month. Assume that each
month has its own worksheet in a workbook, and that the records are all stored
with the same field names.
This presents at least two challenges, if we need to create a macro that will even
carry out a simple task of counting the orders placed on a month-by-month basis.
The first challenge is to ensure the required month is identified by the code and
that the appropriate worksheet is activated. The second is to ensure the
complete range of data is referenced by the code, even if the number of records
changes from one month to the next.
If the sheet tabs are labelled with the months in order from left to right, then
activating the correct sheet could be achieved, regardless of which sheet was
active when the workbook is opened.
You could store the current month in a variable, if you need to use the information
later in your code.
Dim intCurMonth As Integer
Page 22
intCurMonth = Month(Date)
Sheets(intCurMonth).Activate
Now consider selecting the records, or the range they are stored in, so we can
count the sales records. Our scenario assumes that the field or column headings
remain the same across all the worksheets.
Working in the Excel GUI, we would very often place our cursor into the top left
cell, making it active. To select the data, we could then click and drag across the
cells, or perhaps use Select All to ensure all the records were included in the
selection. We could then count the rows to see how many records are in the
data, perhaps using a Subtotal function.
How can we translate these actions into an equivalent in a macro? Having

activated the required sheet, we can then select the range start point. Here we
assume it is in cell A1.
Range(″A1″).Select
At this point, we can use a property of a range that is quite possibly the most
important in VBA, when working with blocks of cells containing data. The
property is CurrentRegion. It is a property that is widely used in Excel both in the
GUI and in the VBE. As soon as a cell is referenced either in code or by simply
clicking into it, Excel works out how many other cells are connected to the active
cell on a contiguous basis. We can use that property for our code. The syntax
often used, following the Select line above, is as follows:
ActiveCell.CurrentRegion.Select
We can in fact shorten the code and replace both lines of code above with:
Range("A1").CurrentRegion.Select
We do not need to select twice. If all we need is a record count, then we can
change the code again to generate the record count and store that information in
a variable.
The -1 is included below, because the CurrentRegion property will include the top
row where our field names are stored.
Dim intRecCount As Integer
intRecCount = Range("A1").CurrentRegion.Rows.Count -1
Module 2 Lab 2: Counting Records in a Range. Turn to page 138 to complete the
Lab.
Page 23
If you need to select the records, without selecting the header row, then the
following code will meet that requirement. It assumes you have selected a cell in
the records. Note the use of the Resize property. This will also be referred to
later in this module, when we consider working with array variables.
Dim SalesRecs As Range
Set SalesRecs = ActiveCell.CurrentRegion
SalesRecs.Offset(1, 0).Resize(SalesRecs.Rows.Count - 1, _
SalesRecs.Columns.Count).Select
The use of CurrentRegion is invaluable when ranges are dynamic. It has its own
virtual subset of Methods and properties based upon those available to the
Range Class.
For example, you can name and rename a range using ‘CurrentRegion.Name’ in
your code, or perhaps use the Offset Method, to automatically make the active
cell the next available blank row to add records.
There will be times when you need to move the active cell to the end of a data
range, but remain in the same row or column. Using the End Method offers some
options. For instance, you need to append data to the bottom of an existing
range in a worksheet. Selecting the first cell in the range and then applying the
End Method, combined with an Offset, will achieve this.
For the purposes of an example, you have data starting in cell A1 but do not
know how many rows there are in the existing data. The requirement is that you
activate the first blank cell at the bottom of the range. The code might look
something like this:
Range("A1").End(xlDown).Offset(1, 0).Activate
The arguments available to the End Method are xlDown, xlUp, xlLeft and xlRight.
End, in a similar way to CurrentRegion, only uses cells that have a value in them.
The arguments can be used to move to the top or bottom of a column, or the first
or last cell in a row.
The use of CurrentRegion and End are all valid. However, let us reconsider the
example we have just referred to, relating to the use of End. You need to append
data below the current range, but the current data contains blank cells or rows.
End(xlDown) will stop at the first blank cell, so that Method would fail in this
scenario. CurrentRegion will not be active beyond a complete blank row in the
data, so that will also fail. Fortunately, VBA provides a possible solution.
Page 24
Excel automatically maintains a record of all the cells in a workbook that have a
value or data in them. This property is accessible at a worksheet level. So
despite this module being about ranges, we need to take a step up the hierarchy,
to the worksheet that contains the range. To work with a range that
encompasses all data in a worksheet, we can invoke the UsedRange worksheet
property. This returns a range object and may provide part of a solution to
ranges that have blanks.
This sample code activates the last cell in the range that contains data:
ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Activate
See the VB Help or Object Browser for a list of available Methods and properties
for working with the range object.
Special Cells
Another Method available for working with a range is SpecialCells. This Method
uses the following syntax:
expression.SpecialCells(Type, Value)
‘Expression’ represents the reference to a range.
The ‘Type’ is used to determine which cells in a range are to be affected by the
VBA. This is a required argument for SpecialCells.
‘Value’ is optional and is used to determine whether to include cells which are
either a Constant, such as text or numbers. Alternatively, cells which contain
formulas can be included. The default is to include both types of cell constants.
For cells to only include a text Constant, the code might look similar to this:
expression.SpecialCells(xlCellTypeConstants, xlTextValues)
For example, you have a range of data on a worksheet and you need to ascertain
the address of the last cell in the range, together with its value. The code to
ascertain the address would be:
Range("A1").SpecialCells(xlCellTypeLastCell).Address
The value can be obtained using:
Range("A1").SpecialCells(xlCellTypeLastCell).Value
Any range Method can be applied to SpecialCells.
SpecialCells are often used in conjunction with filtered data when you need to
work with the results of the filter and not the entire range. The xlCellTypeVisible
Page 25
Type is required. An example of using the visible cells only can be found in the
Filtering Data section of Module 3 - Working with Data in Excel.
See the tables below for a list of the Types and Constant Values available to the
SpecialCells Method.
Name Value Description
xlCellTypeAllFormatConditions -4172 Cells of any format
xlCellTypeAllValidation -4174 Cells having validation criteria
xlCellTypeBlanks 4 Empty cells
xlCellTypeComments -4144 Cells containing notes
xlCellTypeConstants 2 Cells containing constants
xlCellTypeFormulas -4123 Cells containing formulas
xlCellTypeLastCell 11 The last cell in the used range
xlCellTypeSameFormatConditions -4173 Cells having the same format
xlCellTypeSameValidation -4175 Cells having the same validation criteria
xlCellTypeVisible 12 All visible cells
Table 3 - Special Cell Types
Page 26
XlSpecialCellsValue constants Value
xlErrors 16
xlLogical 4
xlNumbers 1
xlTextValues 2
Table 4 - Special Cells Constants
Module 2 Lab 3: Dynamically Rename a Range. Turn to page 139 to complete the
Lab.
Topic D - Copying or Moving Range Data

Data often needs to be utilised elsewhere and this usually involves either cutting
or copying from one range to another. Cut and copy, together with the default
paste, have been available for some time in both the Excel GUI as well as in
earlier versions of VBA. They are not necessarily intuitive Methods in the VBE as
they do not always utilise IntelliSense.
In this section, we will look at the Methods and will also include references to
using Paste Special which is a frequently used feature in the GUI. The
references to copy actions in this section also apply to moving or cutting data.
One of the most common questions raised when discussing copy and paste, is
how to turn off the indicators which show which area has been copied or cut:
answer to follow.
The first principle to coding the copy or cut process, is to ensure that the range is
accessible to the code. This may involve a single cell or a contiguous range of
cells.
Note that whilst you can select non-contiguous ranges in VBA, you cannot copy
them elsewhere.
Page 27
Traditionally, we select the range and then copy or cut it. This is not necessary in
VBA. Look at the code below for a simple copy.
Range("A1:D25").Copy
As long as the code contains the range information (which might include the
sheet reference), the code can simply be told to copy or cut the contents of the
range.
There is also the option to use the CopyPicture Method. This takes the range as
a single image and may be useful when pasting the data as a static item into
another application such as Microsoft Word.
A point to remember here relates to the clipboard. When you cut or copy using
VBA, it will only hold one item at a time, so if multiple ranges need to be copied,
the process will need repeating.
Once the range has been cut or copied, the code must explicitly state where the
data is to be pasted in. This will involve some selection process and it is at this
point that the process may not be intuitive. These two lines might follow the line
above:
Sheets(2).Activate
ActiveSheet.Paste
Note that the paste Method is applied to a sheet. Using ActiveCell will fail with a
run time error. Additionally, ActiveSheet does not use IntelliSense, so you need
to know the commands to use. On the flip side, the line that pastes the code,
does not require you to select the target cells to paste the range into. Being too
explicit in identifying the destination for the paste might lead to errors if the
destination is a different size.
A good rule of thumb is to ensure that prior to pasting in the contents of the
clipboard the code identifies the top left hand corner of the destination range.
To answer the question mentioned above regarding the copy or cut indicators,
and how to switch them off, use this code in your procedure:
Application.CutCopyMode = False
When typing the ‘=’ in the code, VBA will suggest two options. Ignore them and
simply type False.
Page 28
Paste Special
Out of all the Microsoft Office applications, Excel is probably the leading
contender for most frequent place to use Paste Special. It is an important tool
when working in the GUI. The figure below contains the options available when
Figure 3 - GUI Paste Special Options
The command has three separate sections. What to paste, any special action to
carry out mathematically and whether to ignore blanks or transpose the data at
the time of pasting.
The VBA PasteSpecial Method has this syntax:

expression.PasteSpecial(Paste, Operation, SkipBlanks,
Transpose)
‘Expression references the range object to carry out the PasteSpecial upon. All
the arguments are optional.
‘Paste’ is the type of action to carry out using the Method. This can be any of the
following options.
Page 29
Name Description
xlPasteAll Everything will be pasted.
xlPasteAllExceptBorders Everything except borders will be pasted.
xlPasteAllUsingSourceTheme Everything will be pasted using the source theme.
xlPasteColumnWidths Copied column width is pasted.
xlPasteComments Comments are pasted.
xlPasteFormats Copied source format is pasted.
xlPasteFormulas Formulas are pasted.
xlPasteFormulasAndNumberFormats Formulas and Number formats are pasted.
xlPasteValidation Validations are pasted.
xlPasteValues Values are pasted.
xlPasteValuesAndNumberFormats Values and Number formats are pasted.
Table 5 - PasteSpecial Types
‘Operation’ is the mathematical option and can be any of the following:
Notes
www.qa.com
Page 30
Name Description
xlPasteSpecialOperationAdd Copied data will be added with the value in the

destination cell.
xlPasteSpecialOperationDivide Copied data will be divided with the value in the

destination cell.
xlPasteSpecialOperationMultiply Copied data will be multiplied with the value in the

destination cell.
xlPasteSpecialOperationNone No calculation will be done in the paste operation.
xlPasteSpecialOperationSubtract Copied data will be subtracted with the value in

the destination cell
Table 6 - PasteSpecial Operations
‘Skip Blanks’ and ‘Transpose’ are Boolean. Setting either to True will force that
option to be actioned. The default for both is False and therefore if they are not
required, they can simply be omitted.
One of the most common reasons for using Paste Special in Excel is to meet a
requirement where data, which is a result of a formula, needs to be copied
elsewhere, but with only the formula result being required. This sample code will
carry out that action on a range:
Range("D1:D5").PasteSpecial (xlPasteValues)
After the PasteSpecial Method is added to the code, type an open bracket and
VBA will display a list of available types.
This code uses the Sum operation, to add the copied data, to the values in the
destination range being pasted into:
Range("D1:D5").PasteSpecial _
Operation:=xlPasteSpecialOperationAdd
This should be used carefully. If the cells being copied contain formulas, then the
result of the formula, together with the formula itself, will be pasted into the
destination cells and also, in this example, added to the destination cell values
and formula.
Page 31
PasteSpecial is also used when copying data from one application to another.
For instance, data from Excel into a static picture in a Word document.
Alternatively, PasteSpecial would be used to create a Paste Link back to the
source data.
Module 2 Lab 4 Copying Data to a New Workbook. Turn to page 141 to complete
the Lab.
Topic E - Creating Array Variables
Arrays Defined
An array is primarily a data variable with an extra feature. In simple terms, using
an array allows you to refer to a series of variables with a single name. Each
entry in the array has a unique index number, to distinguish it from the other
members of the array. Each indexed entry in an array is called an element.
Imagine a table consisting of the same data. The table will be made up of rows
and columns. An array variable is a representation in VBA of the table, and the
number of rows and columns are referred to as dimensions. It is this property
that is the key difference between an array variable and a scalar, or simple
variable, which can only hold a single value at any one time.
An array has an upper and lower boundary which defines how many entries or
individual data items it contains. The data in an array is always stored
contiguously. The elements in the array are then accessible by index number. It
is not possible to load a portion of an array into your code, but you can work with
the data elements either through looping code or by reference to the index
number.
Information: All the elements in an array will have the same data type. However if
the Variant data type is used, the data types can be mixed.
Declaring Arrays
Arrays are declared using the same statements as any other data variable, e.g.
Dim, Public etc., and should always be declared with an As clause. To declare
the variable as an array, include parentheses after the name of the variable.
Page 32
These behave in a similar way to arguments after a procedure name. This code
declares an array called ‘Sales’. The parentheses indicate to VBA it is an array,
and the number within the parentheses sets the upper boundary on the number
of elements the array can initially hold.
Dim Sales(5) As Double
Fixed Arrays
If you know how many elements will be needed in the array, then it is good
practice to explicitly define that number in the declaration statement. The
example code above declares an array called ‘Sales’ with a single dimension
which is represented by the number 5. This is potentially misleading however, as
the declaration statement does not lead to VBA initialising an array at run time
called ‘Sales’ containing 5 elements. The number 5 in the example, sets the
upper boundary for the number of elements the array may hold.
Let’s say that you need to create an array to hold the total sales figures for a
month. Each day will have its own individual total. This would be a good
opportunity to create a single variable with enough elements to potentially hold all
the daily sales figures in a single VBA variable and then each could be
referenced by its unique number for interrogation.
The code may look similar to this:
On the basis that whilst not all months have 31 days, you know that no month will
have more than that number. One array variable could be used as you process
each month’s data.
Option Base
We have seen that we can declare the array to hold our possible 31 sales
elements. However this is not strictly accurate, assuming a VBA default option
exists.
Display the Locals window and then place the declaration statement above into a
procedure. If you then step into the procedure to initialise the array, you may be
surprised by the result! A default setting in VBA is for arrays to start indexing the
elements from zero (0).
Page 33
Figure 4 - Array in Locals Window
Setting the upper boundary at 31 in the example shown actually creates an array
with 32 elements numbered from 0 to 31. The correct code would actually look
like this:
In order to establish the lower boundary that VBA is using, add this line to your
procedure after the declaration statement:
MsgBox LBound(Sales)
Use the LBound function to identify what is currently set as the lower boundary.
Declaring fixed arrays may be simpler, if the element index number start point is
set to 1. To force VBA to number the elements, add the following code into the
declaration area of the module that contains the array:
Option Base 1
The Option Base statement requires either a 0 or 1 and must appear in the
module before the array is declared. It is only effective in the module where the
statement has been added. If it is not included, the default lower boundary is set
to 0, even in the same Project. If the above statement is added to the module for
our Sales procedure, we can use the original array declaration.
Page 34
An alternative to Option Base is to explicitly declare the number of elements in

the array. In a similar way to how we might set the number of operations on a
For...Next loop in VBA, we can provide the parameters necessary to force the
indexing to start and finish.
Dim Sales(1 to 31) As Double
This creates a 31 element single dimension array. It is possible to start the

indexing at any number up to and including the upper limit.
Multidimensional Arrays
The analogy of an array as being similar to a table, holds good even in a single
dimension array as shown in the example above (assuming an Option Base of 1)
The table described in the array has a single column with 31 rows. If you needed
to create an array that tracks 12 months’ worth of sales, an extra dimension is
required.
Dim Sales(12,31) As Double
Here the extra dimension has been added to the declaration. We now have 2
dimensions and a virtual table of possible elements stored in 31 rows across 12
columns. Remember the elements are arranged sequentially and contiguously,
even if they do not contain any data at run time. To work with an individual
element and the data it contains you can reference it directly by index number in
the VBA. This code will print the contents of the first column and the fourth row:
MsgBox Sales(1, 4)
If the array stores the sales figures from January 1st to December 31st, you could
theoretically drill into the array to establish what the figure was for a date. The
example above could represent the 4th January, dependent on how your data is
written into the array. It might be the first of April of course!
Arrays will support up to 60 dimensions through VBA, although a maximum of 3

is normally considered sufficient. If you need to work with information regarding a
three dimensional object, then it is straightforward to visualise the dimensions.
Height, length and depth for example, would represent 3 dimensions. Beyond 3
dimensions and we might struggle to ‘see’ the data. If 2 dimensions represent a
table, then 3 dimensions would represent more than one table, with each of the
tables having the same number of rows and columns.
Page 35
Consider our sales figures scenario again. You need to track the daily sales
figures across 12 months for up to 31 days per month. An additional requirement
is that for forecast purposes you need to track over say 3 years. This introduces
a new dimension to the array. Here we could use the first dimension to represent
the year, the second the month and so on.
Dim Sales(3, 12, 31) As Double
This next line might represent the element in the second year or table for the 4 th
of January.
MsgBox Sales(2, 1, 4)
Module 2 Lab 5: Create Fixed and Multidimensional Arrays. Turn to page 142 to
complete the Lab.
Dynamic Arrays
The declaration of arrays discussed so far, use a defined upper boundary for the
possible number of elements an array may hold at run time. Consider that the
array may be required to hold a fluctuating range of data, every time the calling
procedure is run.
This presents an issue, as we do not wish to edit the code every time we run the
procedure referencing the array. The answer is to declare the array as dynamic.
Dynamic arrays can be resized at any point during run time. This resizing may
be automatically driven by the data thrown at the code, or it may be handled
directly by the code itself using variables.
Declaring a dynamic array follows the same rules and processes as for a fixed
array. The only difference being that you do not specify the element upper
boundary in the declaration statement. Imagine a set of sales records. Every
order might have its own unique reference number but the number of orders in
our data is unknown. We could declare a dynamic array to capture each order
number, regardless of how many there were at run time.
Dim OrderID() As Integer
This means that the initial declaration creates an array with a single dimension.
Whilst this may appear to be a limitation, we can use the ReDim statement to
control the array even if it has already been populated with data.
Page 36
Using the ReDim Statement and Preserve

A dynamic array can be declared using any standard statement, e.g. Public, Dim
etc, and can also be declared at module or procedure level. Once declared and
populated, its upper boundary is set by the number of elements it holds at run
time. If the number of elements needs to be set or changed, or the dimensions
require modifying, the ReDim statement is used to either size or resize the array.
ReDim can only be used at procedure level and only on an array that is within
scope and has been initially declared to be a dynamic array.
ReDim has the following syntax.
ReDim [Preserve] varname(subscripts) [Astype]
Preserve An optional keyword used to keep data in the array (discussed

below).
varname Required and is the name of the array that is being redeclared by ReDim
subscripts Required. Used to specify the elements and dimensions to apply to the
array. These can be either numeric or a variable.
type The data type. If the original declaration for the array specified a data
type, ReDim cannot be used to change the data type unless the original
type was set as Variant. See Preserve for further information.
Table 7 - Array ReDim Statement
ReDim may be used as often as required within a procedure. If the procedure

uses the same array across different data during run time use ReDim to resize it
as required. The code example here sets the element number for the previously
declared array.
ReDim OrderID(25) As Integer
This example uses a variable to store a count of the rows in an Excel worksheet
and the number is then passed to the array via the ReDim statement.
Dim intOrders As Integer
intOrders = ActiveCell.CurrentRegion.Rows.Count - 1
ReDim OrderID(intOrders)
Page 37
Information: ReDim cannot be used to return an array to its initial dynamic state.
The subscripts argument requires a value.
It is important to note that every time the ReDim statement is executed, all the
values held in the array are lost. VBA resets numeric arrays to zero, string arrays
to a zero length string and Variant arrays to an Empty value.
Removing the contents of the array may be required, if you are reusing it, or
perhaps you need to resize it during run time. If however, you need to keep the
data and resize the array, you will need to include the Preserve keyword in the
ReDim statement.
For example a dynamic array has been declared as follows:
Dim Payments()
A ReDim statement is then executed to specify the elements and dimensions

required:
ReDim Payments(10, 5)
During the same procedure, the array requires resizing to accept more elements,
with the proviso that the current data is retained. To keep the data, we could add
the following line to the procedure:
ReDim Preserve Payments(10, 10)
To reduce the size of the array, whilst still maintaining data in the remaining
elements, use the Preserve keyword with smaller element numbers. Data held in
the removed elements will be lost.
The Preserve keyword is a useful option on the ReDim statement, but care is
needed when using it. It has limitations which may impact on how you can use it.
Preserve can only be used to resize the last dimension of the array, at the time
the statement is executed. This line of code generates a ‘Subscript out of range’
error.
Of course if there is only one dimension, this limitation is not effective.
Preserve cannot be used to change the number of dimensions in an array.
Page 38
Module 2 Lab 6: Create and ReDim a Dynamic Array. Turn to page 143 to complete
the Lab.
Using Arrays in Code

So far, we have examined the concepts of the array variable. Let us now look at
some examples of where arrays may be used within Excel.
Arrays are generally used when working with ‘tables’ of data.
Populating the Array

We have seen how to declare arrays, and when required, how to resize dynamic
arrays. The next step is populating the array with data. First we will look at an
example where the array element contents are hard coded into the VBA. This
sample code declares a dynamic array and then uses the VBA Array function to
populate it.
Dim DataArray() As Variant
DataArray = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10)
Traditional methods of working with data often involve moving through each cell
or field in the data and then processing the data within some form of repeated
code. For small sets of data this approach is still valid. This example iterates
through the cells in a given range and populates the array element by element. A
For...Each...Next loop is used to move through both the cell and the target
elements.
Dim DataArray() As Integer
Dim cell As Object
Dim counter As Integer
counter = 1
' Start the loop on the range.

For Each cell In Range("A1:A10")
'Resize the array preserving the previous

'elements using the counter variable.
ReDim Preserve DataArray(counter)
' Populate the next element.
DataArray(counter) = cell.Value
Page 39
counter = counter + 1
' Loop
Next cell
For small data sets, the approach above is valid but it is not efficient. The VBA
has to make 10 requests to the application to get, and then subsequently
process, the information. The following approach is more efficient, requiring a
single pass across the source data and then populating the appropriate number
of elements.
DataArray = Range(“A1:A10").Value
The same approach can be used if the range of cells containing the data consists
of several rows and columns, or even if it is non-contiguous data.
DataArray = Range("A1:C10").Value
DataArray = Range("A1:A10, A15").Value
Using the Array Data

Before your VBA attempts to process the array, it might be prudent to check it
actually contains data. Many data arrays will contain a mixture of data types and
are therefore declared as Variant. Adding the Block If code, as per the example,
will tell us whether (in this case the first cell) contained data. Empty is the state a
Variant variable has if there is no data.
DataArray = Range("A1:A10").Value
If IsEmpty(DataArray(1, 1)) Then
MsgBox "No data"
Exit Sub
End If
Processing requirements for the contents of an array will differ widely and it is not
possible to cover all eventualities here. We will however look at a couple of
examples. Consider this scenario with two requirements:
You have a list of customers contained in a worksheet within an Excel workbook

that you need to extract data from. The two requirements are:
 We need to extract the complete list of customers from a single sheet and
write that data to a new workbook to create a backup copy.
Page 40
 We also have a request from the UK office to extract a list of the UK

customers only from the data and to export into a CSV file for their CRM
system.
These tasks require automating as they will be repeated on a regular basis and
the amounts of data to extract may vary over time.
You have decided that using arrays will increase the speed with which you can
handle the data, and that they will allow for flexibility, bearing in mind the amount
of data to process is likely to change. We will now break the coding process
down into steps.
The first requirement will involve three workbooks, controlling the application to
turn off prompts to the user and somewhere to hold a temporary copy of the data
extract. This will be the array.
We will declare the variables required and open the workbook holding the data
we need to work with.
Dim CustList() As Variant 'array for temp data
Dim CustRows As Integer 'needed for resize
Dim CustCols As Integer 'need for resize
Dim Backup As Object 'will become new workbook
Workbooks.Open "C:\Customers.xlsx"
Sheets("CustomerList").Activate
Range("A1").Select
Now, we populate the row and column variables needed for the Resize method
and write a temporary range name to the current data ready for the array.
CustRows = ActiveCell.CurrentRegion.Rows.Count
CustCols = ActiveCell.CurrentRegion.Columns.Count
ActiveCell.CurrentRegion.Name = "Customers"
We can now populate the array with a simple and efficient assignment statement.
CustList = Range("Customers")
The Customers workbook is no longer required, so can be closed and removed

from memory. It will be closed, without saving changes to it.
Workbooks("Customers.xlsx").Close False
This next line creates the file which will become the backup copy of the data:
Set Backup = Excel.Workbooks.Add
Page 41
Now we use the array to write the data into the new workbook. Note the use of
Resize to ensure that the destination range has the required number of rows and
columns to match the array dimensions.
Range("A1").Resize(CustRows, CustCols).Value = CustList
We will now empty the array in readiness for the second requirement (UK
customers). This is achieved using the Erase statement.
Erase CustList
At this point, we will save the backup file and remove the sheets which are not
needed. This will be without user intervention, so the application prompts are
turned off.
Application.DisplayAlerts = False
Sheets(1).Name = "Master List"
Sheets(2).Delete
Sheets(2).Delete
Backup.SaveAs Filename:="C:\Backup.xlsx"
Having created the customer list, we can use this to filter for the UK customers
and save those into a CSV format.
We will need two extra variables within the procedure. One will represent the UK
customers as a range of cells and the other will become the new CSV file.
Dim rUK As Range
Dim UKCSV As Object
The filter is then applied to the data.
Range("A1").Select
ActiveCell.CurrentRegion.AutoFilter 6, "UK"
We now face an old Excel problem with hidden cells. We cannot easily assign
the visible data to our array. The assignment statement used for all the data will
include hidden rows. The simplest workaround is to take the data onto a
temporary sheet and then use the copied data to populate the array. The code
here is not the only solution.
Set rUK = ActiveSheet.AutoFilter.Range
rUK.Copy
Sheets.Add
ActiveSheet.Paste
ActiveCell.CurrentRegion.Name = "UK_Customers"
Page 42
CustList = Range("UK_Customers")
The backup file can now be closed and the array data written to a new file. The
new file is then saved in a CSV format and closed.
Backup.Close savechanges:=False
Set UKCSV = Excel.Workbooks.Add

ActiveWorkbook.SaveAs "C:\UK Customers.csv", xlCSV
UKCSV.Close
Finally, we would turn on the application prompts and remove the variables from
memory.
Module 2 Lab 7: Create, Populate and Use an Array. Turn to page 145 to complete
the Lab.
Module 2 Review
 Range Objects Defined
 Referencing Ranges using VBA
 Dynamic Range Handling
 Special Cells
 Copying or Moving Range Data
 Creating Array Variables
Review Questions:
1. What is Option Base?
2. Give an example where you might use Special Cells
Page 43
3. How do you declare a two dimensional Array?

4. What does the Preserve statement do?
5. Describe the CurrentRegion property
6. What does the following code do?
Notes
www.qa.com
Page 44
Module 3 - Working with Data in Excel

 Importing Data
 Data Types v Formatting
 Validating Data
 Filtering and Sorting Data with VBA
 Using Database Functions
 Excel Tables
Introduction
Microsoft Office 2010 provides two methods of connecting to ‘remote’ data i.e. it
is not stored in the application you wish to use the data in. The Excel 2010
interface provides data specific tools, such as Connections or Microsoft Query, to
get to the data stored elsewhere. This is the primary route for most end users.
The interface typically takes you through a ‘wizard’ style process where the steps
are broken down into a simple process along these lines:
 Identify the type of data source to connect to
 Locate the source
 Navigate to the data or recordset to import
 Apply any options to select the actual data required
 Confirm where the data should be imported to in Excel
 Carry out the import
Once the data has been imported into Excel, the interface provides options to
refresh the data or break the Connection. In the previous module, the refresh
process was introduced when discussing Tables.
The Connection is the single most important element when you need to import
data from outside of Excel in such a way that the data can be refreshed. This is
in effect a ‘two way’ process even though the data is pulled into Excel.
Page 45
Of course, not all data is imported in such a way that it needs to be refreshed.
You may need to import data ‘one way’. That is to say that the data is imported
once and not refreshed from the same source. This might be achieved by a
File…Open process. However, if we need the data to be imported into an
existing workbook, the Import process will be needed.
In this module, we will look at some of the techniques you can use to import and
work with data.
ADO ActiveX Data Objects. The default method used in Office 2010 to
provide a standard set of objects to communicate with different data
sources. Consists of 3 main objects: Connections, Commands and
Recordsets.
Connection Part of ADO. This is the actual link to an external data source.
Expressed as a string in VBA.
Data Source Location of the data to interact with in VBA. Specified as a string or
string variable and is used by a Connection to locate the data store.
Data Type A field property which determines the nature and behaviour of the
data stored in the field. Normally referenced using Data variables.
Database A term to define the interactive methods of working with database

Access Layer objects in a standard way. An example would be how a Connection
is written.
Field An individual piece of data forming part of a record. Fields are often
accessed as a header row. Fields define the type of data held within
a record. Fields are referenced either by Index number or the string
name.
Filter Method or property applied to a recordset to display or hide records

according to criteria set.
Query The question asked to create a recordset. A query may exist as an

object in an application or be created in VBA. Queries do not hold
permanent data. They are a tool to create the Recordset or a link
(Connection) to external data.
Range Any group of related cells in either a worksheet or table. Usually a

contiguous set of rows often including headers or field names.
Page 46
Record Typically a row of data comprising a related set of fields.
Recordset Any single group of records. Normally associated with tables and
queries in Access. These may be actual database objects or virtual
objects created in VBA.
Sort Method or property applied to a recordset to alter the view of

records. May apply to data types or by cell formatting and icons in
Excel.
SQL Structured Query Language. Standard language for querying

Recordsets. May also refer to a database type.
Table One Recordset either as a defined part of a Word document, Excel

Table or reference to a database Recordset. Referenced either by
physical location or string name.
Table 8 - Common Data Terminologies
Topic A - Importing Data from a Delimited File or Web Page

A requirement to work with data in Excel, which is in another format, will need a
process to open another file which is not a standard XLSX file, in order to add the
data to an existing workbook.
The most common file type is CSV followed by TXT. Both of these formats will
usually store data using a delimiter. Identifying the delimiter can make all the
difference to the end result. Importing from a web page uses a similar process.
Module 6 discusses a technique to read the contents of a text file which could be
used to identify the delimiter character.
Excel can be quite flexible with the text file types it will attempt to open. It may
not matter what extension a file has as long as Excel can read the contents in
such a way that it recognises the characters. This line of code creates a
Connection to a CSV file.
ActiveSheet.QueryTables.Add(Connection:="TEXT;C:\Cust.csv", _
Destination:=Range("$A$1"))
As the data is external, QueryTables is the collection added to. The Connection
identifies the type of external file, sets the path as a string and then references
the cell which will hold the top left corner of any data and headers imported.
Page 47
Once the source and destination have been identified, the code then needs to
specify the options for the import.
The range holding the imported data is assigned a name based upon the name of
the external file.
.Name = "Cust"
The code needs to specify whether the first row contains column headings or field
names. It can be omitted if the code is simply pulling in the data and appending it
to existing data.
.FieldNames = True
The important component of the import requires that the correct delimiting
character is identified.
.TextFileCommaDelimiter = True
The code above will apply to a TXT file. If the file was tab delimited, then the
above line of code is replaced with:
.TextFileTabDelimiter = True
Opening and importing from a web page is similar to the CSV example. The
main differences are around the Connection and how to select the data to import.
It must be remembered that importing from a web page may not bring in all of the
information on the page. Excel has limitations, and data to import has to be in a
recognisable tabular format, to be able to place the result into a worksheet.
Consider a scenario where you need to provide information to colleagues from a
web source with product prices. The information is available from a web page,
which can either be accessed from the web or from your intranet.
The connection would look similar to this example. Web page data is referenced
in the same way as delimited CSV and text files. QueryTables collection is
added to, using the connection information, with the name representing the name
of the web query.
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://www.awebpage.co.uk/finance#", _
Destination:=Range("$A$1")) _
.Name = "finance#"
The query must specify the tables of data on the web page to query. The tables
are usually indexed on the page, using a simple count from the top left hand
corner. However, the process for identifying the tables that can be imported is
often best achieved by attempting to carry out the import from the Excel GUI.
Page 48
Each table that can be imported will be represented by a yellow box containing
an arrow.
.WebTables = "2,3" ‘2nd and 3rd table in web page
You can also set the refresh properties as required. In the example code below,
the query is refreshed using the Open event for the workbook. The query is also
refreshed automatically every 15 minutes in the background, whilst the workbook
remains open.
With Selection.QueryTable
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshPeriod = 15
End With
Module 3 Lab 1: Create a Query to Open a CSV File as an Imported Item. Turn to
page 146 to complete the Lab.
Topic B - Excel and ADO

Office 2010 uses a combination of the legacy DAO or ODBC to maintain a
backwards compatibility, together with ADO or ActiveX Data Objects. Whilst you
will have local copies of files or databases in some instances, in most cases you
will connect to a file stored remotely. To work with remote files such as
databases, Office uses Connections. The ADO schema contains elements from
the DAO schema together with ADO specific entities.
Figure 5 - ADO Hierarchy
Page 49
This model is based on a single object variable called Connection, which

establishes a link to any compatible database.
The following example shows the creation of a connection to a database and a

recordset containing pointers to the Customers table. The recordset is opened as
a dynaset with optimistic locking. It assumes the Access Reference Library is
loaded into the VBE.
Dim strConn as String
Dim strDB as String
Dim rs as Recordset
strDB = "C:\Customers.accdb"
strConn = "Provider=Microsoft.ACE.OLEDB.14.0; _
Data Source=" & strDB
Set rs = New ADODB.Recordset
rs.LockType = adLockOptimistic
Once the Connection has been established all of the database Objects together
with their associated Events, Methods and properties are exposed to VBA as
long as the Connection is maintained.
Once the Connection is established, you use Recordset objects to manipulate
data from the data source. When you use ADO you manipulate data almost
entirely using Recordset objects. At any time the Recordset object refers to a
single record within and treats it as the current record.
ADODB.Recordset is the code that should be used to create a Recordset object

within Access. Use ADODB when creating a Recordset in Excel by connecting to
an Access database.
There are four different cursor types defined in ADO: Dynamic, Keyset, Static and
Forward Only.
Set the type of Cursor property before opening the Recordset or pass a
CursorType with the Open method. If you don't specify a cursor type, ADO
opens a forward-only cursor by default.
You can create as many Recordset objects as needed.
Recordset objects are able to support two types of data updating. These are
immediate and batched. With immediate updating, changes to data are written
straightaway to the underlying data source once you call the Update method on
the Recordset. You can also pass arrays of values as parameters with the
Update methods, and simultaneously update several fields in a record.
Page 50
If the database supports batch updating you can have the changes to more than
one record applied in a single command issued to the database with the
UpdateBatch method. This applies to changes made with the AddNew, Update,
and Delete methods.
The information given here is not Microsoft Access specific and may also be
relevant when connecting to other data sources such as a SQL database.
Example
Scenario: your customers’ information is held in an Access database and you
need to copy all the customers’ data into an Excel workbook for Sales.
Subsequently you need to extract the UK customers into a separate workbook for
further analysis by Sales. These tasks will be repeated on a regular basis and
are therefore suitable for a programmatic solution.
Let us firstly determine the steps involved:
 We need to import the data into Excel from Access into a workbook and this
needs saving with a date and time stamp.
 The data needs to be filtered to show the UK customers, and then the filtered
data is to be stored in a new workbook.
 File management will need to be handled as required.
 The whole process should have one ‘trigger’ to run it from start to finish
without user intervention.
The VBA will need to be written and subsequently run, from the Personal.xlsb file
stored on each user’s PC
We will now look to identify what procedures are needed to achieve the end
result. The requirements listed above provide a good starting point, as they
identify the applications involved, together with a running order that the code
must replicate. Here is a possible summary of what we need the code to
achieve.
 The code must establish the connection to the database. The code would be
similar to the example above.
 The recordset containing the required data needs to be opened. This is a

virtual operation carried out in memory. The recordset does not need to be
physically opened and displayed.
Page 51
 The contents of the recordset need to be gathered. This process can be

achieved by employing different approaches including the following:
 Use the CopyFromRecordset method
 Write the records into an array
 Have the code instruct Access to export the file
 The new workbook needs saving into an accessible location, overwriting any
existing file.
 The data requires a filter to be applied, with the subsequent results of the filter
copied into another workbook. The techniques for filtering and copying the
filtered results are discussed later in this module. This further workbook
needs to be saved as required – again overwriting any existing file.
 All variables require setting to Nothing, following the code running.
 The code should run ‘silently’ when triggered, with updates to the screen and
taskbar temporarily turned off.
We will now look at the code representing a possible solution to the requirement.
First, we will create the procedure and set the application level requirements to
allow the ‘silent’ running of the code.
Sub ImportCustomers()
Application.ScreenUpdating = False
Application.ShowWindowsInTaskbar = False
Now the variables are declared to handle the connection, database, new
workbooks, fields and data.
Dim strConn As String
Dim strDB As String
Dim rs As Recordset
Dim wsSheet As Worksheet
Dim lnFieldCount As Long, lnCount As Integer
Dim wbAll As Workbook
dim wbUK as Workbook
The destination workbook is created next and saved. The location specified
would be the shared network location. Alternatively, the file could simply be
opened if it already exists.
Page 52
Workbooks.Add
Set wbAll = ActiveWorkbook
wbAll.SaveAs ("C:\AllCustomers.xlsx")
The connection and database details are now supplied to the code, by populating
the appropriate variables. Note the reference to the cursor locking. The data in
the recordset will be locked record by record, to allow the code to query the
information, whilst at the same time allowing others to use the data in the table. If
the ‘Provider’ is a SQL database, then substitute the Microsoft.ACE.OLEDB
reference for SQLOLEDB. Note: In the following code14 refers to a 2010 access
database, use 12 to refer 2007 and 11 to refer to 2003.
strDB = "C:\Customers.accdb"
strConn = "Provider=Microsoft.ACE.OLEDB.14.0;data Source=" & _
strDB
Set rs = New ADODB.Recordset
rs.LockType = adLockOptimistic
The destination worksheet is set against the wsSheet variable. The worksheet
name will probably require changing, if referencing an existing workbook. If an
existing workbook is being opened by the code, then the Clear method for the
CurrentRegion, will ensure that no legacy data will remain ahead of the new data
being copied in.
Set wsSheet = wbAll.Worksheets("Sheet1")
wsSheet.Range("A1").CurrentRegion.Clear
We now use a simple SQL statement to ensure that all of the records in our
Customers table are included.
'Select all data from the recordset
rs.Open "SELECT * FROM Customers", strConn
The CopyFromRecordset method will only take the data contained in the table
into the destination worksheet. In order that subsequent filtering, sorting or
analysis of the data is capable of being handled by Excel efficiently, we will need
to have the code write the fields into the destination worksheet.
'count the fields in order to replicate them as column
'headings
lnFieldCount = rs.Fields.Count
'Write field names to worksheet
Notice the For…Next loop uses the number of fields minus 1 to identify when the
processing and writing of the field names to the worksheet. The code that
created the new workbook does not specify which cell is active and therefore cell
Page 53
A1 on the worksheet will be selected. The loop ensures that the first field name
is written into column A, by adding 1 to the value of the lnCount variable. The
initial value is set to 0 in this example.
For lnCount = 0 To lnFieldCount - 1
wsSheet.Cells(1, lnCount + 1).Value = _
rs.Fields(lnCount).Name
Next lnCount
The actual records are now copied into the destination worksheet, with a couple
of lines of code added to ensure the data is visible. The workbook is then saved.
'copy in records below field headings
wsSheet.Cells(2, 1).CopyFromRecordset rs
'tidy data
wsSheet.Range("A1").CurrentRegion.Cells.WrapText = False
wsSheet.Range("A1").CurrentRegion.Columns.AutoFit
wbAll.Save
At this point, the code would apply a filter for the UK customers and then copy the
results to another workbook.
Finally, we clear the object variables from memory.
Set rs = Nothing
Set wbAll = Nothing
Set wbUK = Nothing
End Sub
Module 3 Lab 2: Import Microsoft Access Data into Excel. Turn to page 147 to
complete the Lab.
Topic C - Data Types or Formatting

What exactly is data? Strictly speaking data is a single piece of information. The
term of course is now much broader in its definition. A typical definition would
describe data as a collection of numbers or characters which can be processed
electronically.
To work with data and manipulate its properties in Excel 2010, VBA allows for the
use of a combination of generic constructs, together with some application
specific methods. The purpose of this module is to examine how these are
combined to work with data, both locally to an application and globally across
Page 54
Office 2010 applications. In other words, you either have to import data, or work
with existing data in an Excel workbook, and wish to perhaps filter, sort or
validate it leaving the data in the workbook.
A common challenge faced by anyone working with data, whether manually

inputted or imported, is to ensure that the data is fit for purpose. This may
involve processing the data prior to it reaching the workbook, although more often
than not, the data is already in Excel. Getting your data to the point that it is
usable, requires that the correct data types are present, together with appropriate
formatting for presentational purposes.
We may need to apply flexibility in our data processing, and for this we can use
VBA, in combination very often with the interface tools, to achieve the desired
results. To help understand the processes for data manipulation we first need to
define some terminologies.
Data Types
Data is not just ‘information’ and regardless of how or where it is referenced, the
actual information will always have a data type. This information may be known
or assumed before the VBA processes it.
When working with data, it may be essential that the type of data is established,
ahead of code being applied to it. This will eliminate errors during run time. The
most common error with data is Type mismatch or Run-time error 13.
Figure 6 - Data Mismatch Error Message
Correct references to data types should either eliminate associated errors or at

least present an opportunity to trap them in your code. The table below lists
common data types.
Page 55
Type Decimals Value Range Prefix
Boolean Na True/False bln
Byte 0 0 to 255 byt
Integer 0 -32,768 to 32,767 int
Long 0 -2.1*109 to 2.1*109 lng
Single 7 -3.4*1038 to 3.4*1038 sng
Double 15 -1.8*10308 to 1.8*10308 dbl
Currency 22 -9*1014 to 9*1014 cur
Date Na 1/1/100 to 31/12/9999 dat
String Na 0 to ~63,000 str
Variant Na Na Var
Object Na Na obj
Table 9 - Data Types
The table lists data types which are also valid for variable declaration statements.
Note the Object entry. This is not an actual application Object type, as discussed
earlier in this workbook. It is the name of an Object as string.
If you are storing and processing data using Data variables, declaring the
variable with the appropriate data type using the As statement, before it receives
the data, is recommended. This improves processing speed and also allows for
data validation or error handling, should there be a mismatch between the
declared data type and the actual data processed.
What if you are unsure of the data type until run time? Many VBA procedures will
use the Variant data type. This has a benefit of allowing any data type to be
passed to a variable without error. The downside of using too many variant
declarations, or of not declaring a variable as any data type, is a reduction in
Page 56
processing speed. The VBA will check the data type every time the variable
receives a value, and then assign the correct data type and properties to it.
Working with data types may also involve formatting the data. Data that has a
format in the GUI will often correspond to the actual data type underlying the
value in a cell. Mismatches between the format and the data type can cause
errors during run time, and also in the end result displayed in the GUI.
Dates are a common area where formats and data types can clash. For
example, you are processing data which involves a time element, in say an
employee timesheet, in order to total hours for payroll calculations.
Excel prefers to reference numeric data against a whole unit. The whole unit for
date information is a day. Time is a proportion of a day in the GUI and once the
number of hours exceeds 24, Excel starts to convert the data to a decimalised
version of the hours. This then leads to failures in the GUI to calculate properly.
You might therefore use VBA to control both the format and the actual values,
and may even carry out any calculations to ensure the data is usable, prior to
writing the results into a workbook.
Topic D - Validating Data

In this section we will look at checking the validity of data from two perspectives.
The first entails that the data type is appropriate for your code in order to avoid
run time errors. You can either convert the actual data, or alternatively, handle
the error.
We will also look at the formal Data Validation command, as found and used in
the GUI on the Data tab in the Ribbon, and its VBA equivalent.
Identifying the Data Type Using VBA

Perhaps the most common method for explicitly controlling the data type, is to
pass the data to a variable that has the type declared. Declaring a variable so
that it expects a data type can lead to errors. Data mismatch errors, are of
course trappable. However, there will times when the code has variables
declared as Variant, not declared at all, or alternatively, the data is known to
contain more than one data type.
VBA has many intrinsic functions available for you to use; many of which are
specifically designed to work with data and variables. These include amongst
Page 57
others, numeric and string functions, as well as conversion functions. The

workbook for the QA course: Introduction to VBA in Office 2010, contains
references to many of these functions together with examples.
The practical uses of the intrinsic functions are wide ranging and there is
insufficient scope in this workbook to detail them all. For the purposes of this
section, we will look at TypeName and an example of a conversion function.
To put these into some context, look at the table below detailing a section of a
worksheet. The data in the ‘Born’ column visibly indicates an anomaly. The
chances are that the third record has the date stored as text.
You can check the type of data by testing its value.
Use the TypeName function to establish what VBA identifies as the data type.
We can write a procedure that can cycle through each cell, and write the cell
value into a variable. The code can then test the content of the variable, using
the TypeName function, to determine that actual data type. It is important to note
that the function ignores the format when examining the data.
x = ActiveCell.Value
If TypeName(x) <> "Date" Then 'case sensitive!
If the result of the function indicates that the value is not a Date, in this case we
can then code an appropriate response. This might include converting the data
in the cell into the required data type. This line uses the Date Conversion
function to update the cell value:
x = CDate(x)
For consistency in presentation, the code could then format the cell containing
the data.
ActiveCell.Value = FormatDateTime(x, vbShortDate)
The quick example detailed above, is equally applicable to data that is being
written to a worksheet using VBA on a cell-by-cell basis, or processing existing
cell values.
Page 58
Coding the Excel Data Validation Command

The use of the Data Validation command in the Excel GUI is an excellent tool for
ratifying both data type and value at the point of entry. The actions associated
with the command control up to three arguments for the function. The function
firstly dictates data type and value. It may then provide an input message to the
end user, and finally has multiple options to control how Excel reacts if the data
validation rule is broken. This uses a simple message box option.
Equivalents for all three arguments can be coded using VBA, if we are
processing the data in the code itself. If your code is creating workbooks, or
components such as sheets or forms, which will be used by an end user to enter
data, and that data needs validating, you will need to add the appropriate
validation. In other words, the code emulates the Data Validation command and
applies the validation rules directly to the range of cells that the data will be
manually entered into.
Scenario
Consider a scenario where the code creates a new worksheet for each week in
order to record sales. The sheet is set up with a blank table, consisting of various
columns or fields. The sales records are entered manually and one of the fields
requires the date of the sale. A sales record cannot be recorded as having
occurred in the future. The decision is made that only an entry that satisfies the
validation rule is acceptable against the sales record.
The VBA returns the Validation object for a range and uses the following syntax:
Expression.Validation
‘Expression’ can be any valid reference to a range. The sample code that follows
uses the Add Method to apply the validation with associated options. The options
equate to the three possible arguments the command may have in the GUI. Here
a named range is selected by the code and a With statement is used to apply the
options.
Range("transact_dates").Select
With Selection.Validation
The Add Method is called and the validation parameters are set. Note the use of
the comparison operator and the string value for the formula.
.Add Type:=xlValidateDate, AlertStyle:=xlValidAlertStop, _
Operator:= xlLessEqual, Formula1:="=TODAY()"
Page 59
This line allows a validation to be set and saved without the cells needing to
contain a value.
.IgnoreBlank = True
Once the data part of the validation has been set, the input message method
arguments are applied followed by the error message options. These exactly
mirror the process and options available in the GUI.
.InputTitle = "Transaction Date Required"
.ErrorTitle = "Invalid Date Entered"
.InputMessage = "Enter the date of the transaction.” _
& “This must not be in the future."
.ErrorMessage = "The date entered is either in the” _
& “wrong format or is in the future. Try again."
.ShowInput = True
.ShowError = True
End With
To remove data validation from a range, use the Delete Method.
Range("transact_dates").Validation.Delete
Module 3 Lab 3: Add Data Validation to a Worksheet. Turn to page 149 to complete
the Lab.
Topic E - Filtering and Sorting Data with VBA

How data is stored in an application often dictates what we can do with it. Excel’s
tabular layout provides us with familiar tools. Filtering, sorting, applying formulas,
Pivots and charts are all examples of data activities. In many cases the Excel
GUI provides all the functionality we require, but using VBA and Macros can give
us a speed advantage over network connections, or when handling very large
data sets. Repeated activities are often prime examples of where a coded
solution is preferable every time.
When working with data in workbooks, the Range and ActiveCell Objects are
extremely useful components to reference in the code. Remember that there can
only ever be one active cell in Excel at a time, so knowing which cell ActiveCell
refers to, is a great start point.
The ‘Go To’ Special option Current Region is another key tool to use. Here are
some of the Methods and properties which can be referenced.
Page 60
With ActiveCell.CurrentRegion
.Select
.Name
.Rows.Count
Warning: Regardless of how a data validation is set on a range, data that is written
to the range using VBA, or a direct import from an external source, ignores the
validation rules.
.EntireRow.Delete
.Columns.AutoFit
.Font.Bold = True
.AllowEdit = False
.ClearContents
.Locked = True
As long as the ActiveCell is identified, its CurrentRegion Methods and properties

are dynamic. For instance, you can dynamically name a range or count rows
(records) each time code runs without having to specify an absolute target.
A Range refers to any contiguous or connected group of cells. Ranges can be

created dynamically in VBA and also handled directly using the code. Ranges,
like cells, are normally referred to by their string name, but can also be
referenced by index number. A combination of Range and
ActiveCell.CurrentRegion example might be:
Range("A1").Select
ActiveCell.CurrentRegion.Name = "Current Sales"
Range("Current Sales").Rows.Count
We select a known start point for our data, but do not know how many rows and
columns make up the contiguous region. The second line of VBA dynamically
creates and names the Range, regardless of its size.
Filters
So how do we apply a filter to a range?
Range("Customers").AutoFilter 6, "UK"
The AutoFilter acts like a switch, in that it is a Boolean Method. It toggles

between an ‘on or off’ state dependent upon the Range it is applied to. If the
Range is not filtered, the code switches it on and vice versa. The code needs at
least two arguments to apply an actual filter. The example above applies the
“UK” as Criteria1 to column 6 in the Range. The columns in the range are
Page 61
referred to by their position starting at ‘1’and working left to right. Your code may
need to establish the position of a column to filter on, achieved by counting the
columns across whilst checking for a predetermined value.
We can apply Autofilters to more than one column in the range of data. This
example modifies the code example above, to filter the data for our customers in
the UK, and only show those in London.
With .Range("Customers").AutoFilter
.AutoFilter Field:=6, Criteria1:="UK"
.AutoFilter Field:=7, Criteria1:="London"
End With
Multiple criteria can be used with xlAnd or xlOr Enumerators. The VBA also
allows for the use of other filters in the same way as the Excel GUI. This code
could find records that match 2 criteria from the same column.
Range("Customers").AutoFilter Field:=6, Criteria1:="UK", _
Operator:=xlOr, Criteria2:=”France”
The following table shows the options for the XlAutoFilterOperator Enumerators.
Notes
www.qa.com
Page 62
Name Description
xlAnd Logical AND of Criteria1 and Criteria2
xlBottom10Items Lowest-valued items displayed (number of items specified in

Criteria1)
xlBottom10Percent Lowest-valued items displayed (percentage specified in Criteria1)
xlFilterCellColor Colour of the cell
xlFilterDynamic Dynamic filter
xlFilterFontColor Colour of the font
xlFilterIcon Filter icon
xlFilterValues Filter values
xlOr Logical OR of Criteria1 or Criteria2
xlTop10Items Highest-valued items displayed (number of items specified in

Criteria1)
xlTop10Percent Highest-valued items displayed (percentage specified in Criteria1)
Table 10 - AutoFilter Enumerators
The AutoFilter Method only allows you to specify two criteria. If you need to filter
for more than two possible entries in a column, use an array in the criteria.
Range("Customers").AutoFilter Field:=6,_
Criteria1:=Array("UK", "France", "Germany", "USA")
To find blank entries in a column, set the criteria on the appropriate column to be:
Criteria1:="="
To hide blanks, the criteria would be:
Criteria1:="<>"
Page 63
You can also use wildcards in your code. Wildcards are especially useful when
you need to either search for entries that have a common value in the data, or
where you are looking for different values for the same data.
This example would find all the entries where you need to find entries in a
postcode field, where the first part of data is common across the records.
Criteria1:="SL*"
The arguments can be made to be interactive, with a simple use of the InputBox
function to prompt the user for the required information, or they might be chosen
by the code.
Advanced Filters
When you work with filtering data in the GUI, there will be occasions when an
AutoFilter is insufficient. Perhaps you need to apply several filters and then copy
the results into another location on the worksheet. To meet this requirement, you
could use an Advanced Filter. This option is also available in VBA. The
arguments are the same in both environments.
You need to: identify the range to filter, the location of the criteria, whether to
display the results in the original range, or to copy to another area, and finally you
can specify whether to show all results, or just the unique records.
This example references the existing customer data, returns the filtered result in
the same location using the criteria specified. Only unique records are displayed.
Range("Customers").AdvancedFilter _
Action:=xlFilterInPlace, _
CriteriaRange:=Range("European Customers"), _
Unique:=True
Filtered data is really a combination of hidden and visible rows. If you need to
filter data in order to process the visible rows, e.g. copy that data, only use the
Special Cells property. This copies the visible rows only.
Set UKCust=ActiveCell.CurrentRegion
UKCust.Select
Selection.Copy
Sorting
Using VBA to sort data is easily achieved and is often used in conjunction with
other actions that need to be performed. The VBA to apply a sort is
straightforward.
Page 64
expression.Sort(Key1, Order1, Key2, Type, Order2, Key3, Order3,

Header, OrderCustom, MatchCase, Orientation, SortMethod,
DataOption1, DataOption2, DataOption3)
Here, expression is the selection of data to be sorted. Keys refer to the columns
to sort, applying a priority to the columns. Key 1 is sorted before Key2. Order 1,
Order2, etc., determine whether the outcome is xlAscending or xlDescending.
Header is xlYes, xlNo or xlGuess. This is used to determine whether the first row
should be excluded from the records or not. The default is ‘No’ so always specify
xlYes.
This line sorts the Country column in an A to Z order top to bottom.
Range("Customers").Sort Key:="Country", Order1:=xlAscending, _
Header:=xlYes
Sorting is frequently a pre-cursor to another action. Subtotal
expression.Subtotal(GroupBy, Function, TotalList, Replace,
PageBreaks, SummaryBelowData)
Here, ‘expression’ is the selection of data to be subtotalled. Groupby refers to

the fields to group by. Function is the subtotal function ie xlcount or xlsum.
TotalList is used if you require the subtotal to appear in a column other than the
last column. Replace will replace existing subtotals if set to true. Page Breaks if
set to true adds page breaks after each group. SummaryBelowData places the
summary data relative to the subtotal
Scenario
As a scenario, you have a worksheet with your customers listed by ID number.
You have a requirement to temporarily arrange the data, so that a count of
customers can be carried out by country.
Assume the code to Sort has been entered into the VBA as listed above. We can
now subtotal the data to count the customers, and then remove the subtotalling
with a Sort applied to the customer reference, thus returning the data to its former
state.
'This line applies a subtotal to column 6 which is our
‘Country column in the data, using a count and summary
Selection.Subtotal Groupby:=6, Function:=xlCount, _
SummaryBelowData:=xlSummaryBelow
'This line collapses the outline to just display the
'Country totals
ActiveSheet.Outline.ShowLevels RowLevels:=2
Page 65
'This line removes subtotals from a selection

Selection.RemoveSubtotal
'Now resort the data by the ID column.
Range("Customers").Sort Key:="ID", Order1:=xlAscending, _
Header:=xlYes
Module 3 Lab 4: Combine, Filter and Sort Data. Turn to page 152 to complete the
Lab.
Topic F - Using Worksheet Database Functions

Working with data using VBA is not just rearranging or finding raw information. It
may also include some work to analyse the data, in order to use the results of the
analysis elsewhere.
Many of the familiar functions you would use in the GUI can be made available to
your code, without writing them explicitly into a cell as a formula.
The functions can be used to generate return values to populate variables, or to

write into a workbook. If you are familiar with the Database or the D functions in
the Excel environment, then you can also use them in the code you write. Note
that not all functions are available. Any function which has a VBA equivalent or
similar name cannot be used. These include many of the Excel Date functions.
The following table lists the available Excel database functions.
Function Description
DAVERAGE Returns the average of selected database entries
DCOUNT Counts the cells that contain numbers in a database
DCOUNTA Counts nonblank cells in a database
DGET Extracts from a database a single record that matches the specified
criteria
DMAX Returns the maximum value from selected database entries
DMIN Returns the minimum value from selected database entries
Page 66
Function Description
DPRODUCT Multiplies the values in a particular field of records that match the criteria
in a database
DSTDEV Estimates the standard deviation based on a sample of selected database

entries
DSTDEVP Calculates the standard deviation based on the entire population of

selected database entries
DSUM Adds the numbers in the field column of records in the database that
match the criteria
DVAR Estimates variance based on a sample from selected database entries
DVARP Calculates variance based on the entire population of selected database

entries
Table 11 - Excel Database Functions
For a full list of the Excel worksheet functions available to use in VBA, enter
‘Worksheet Functions’ in the VBA Help.
In this example, a variable has been declared which is populated using the
DCountA worksheet function. The function requires all the expected arguments
to be available.
Dim UKCust
UKCust =Application.WorksheetFunction _
.DCountA(Range("Customers"), _
"Country",Range("UKCriteria"))
The result of the function is held by the variable.
Information: VBA does not directly recognise the Excel functions used in
workbooks even from within the Excel VBE. Virtually all of the functions can be
made available by using the Excel hierarchy.
Application.WorksheetFunction.function name here(arguments)
The DCountA example above, uses a predefined range as the Criteria argument.
The argument can be populated with any valid range reference. This could also
Page 67
be coded so that it is interactive, prompting the end user to specify either the
criteria to use, or even the range for the criteria cells.
This example line of code replaces the defined criteria with the results of
information from the end user. The prompt only requires a single cell reference
from the range where the criteria is held in the worksheet. CurrentRegion is used
to reference all adjacent cells.
Dim GetCriteria
GetCriteria = InputBox("Enter the reference of a cell which” _
& “holds the criteria to use")
UKCust = Application.WorksheetFunction _
.DCountA(Range("Customers"),"Country",_
Range(GetCriteria).CurrentRegion)
Module 3 Lab 5: Using a Database Function in VBA. Turn to page 154 to complete
the Lab.
Topic G - Excel Tables

Data in Excel is often held in a simple range. Excel 2010 provides the capability
to convert a range into an Excel Table. This provides some formatting and
design options but more importantly, the Table has some built-in data
manipulation features. The Table is dynamic and regardless of the size or source
of the data, can be created, named and manipulated using VBA. An Excel Table
is part of the ListObjects collection. The syntax to create a Table on a worksheet
is as follows:
expression.Add(SourceType, Source, LinkSource, HasHeaders,
Destination)
‘Expression’ is a variable that refers to an instance of a ListObject. Each
ListObject represents a Table in the worksheet. The Add Method is called to
create the Table.
Page 68
Name Required/Optional Description
SourceType Optional Indicates the kind of source for the query.
Source Optional When SourceType = xlSrcRange. A Range object

represents the data source. If omitted, the Source
will default to the range returned by list range
detection code. When SourceType =
xlSrcExternal. An array of String values
specifying a connection to the source is required.
LinkSource Optional Boolean. Indicates whether an external data

source is to be linked to the ListObject object.
Invalid if SourceType is xlSrcRange, and will
return an error if not omitted.
HasHeaders Optional An XlYesNoGuess constant that indicates whether

the data being imported has column labels. If the
Source does not contain headers, Excel will
automatically generate headers.
Destination Optional A Range object specifying a single-cell reference

as the destination for the top-left corner of the new
list object. If the Range object refers to more than
one cell, an error is generated. The Destination
argument is ignored if SourceType is set to
xlSrcRange. The destination range must be on
the worksheet that contains the ListObjects
collection specified by expression.
Table 12 - Excel Tables ListObject Parameters.
When the list has headers, the first row of cells will be converted to Text, if not
already set to text. The conversion will be based on the visible text for the cell.
Moreover, if there are two cells in the header row that have the same visible text,
an incremental integer will be appended to make each column header unique.
The source of the data to use for the Table will not necessarily be an existing
Excel range of data. The Source parameter listed above can be any one of four
options. Whilst the most common is likely to be a range, the data for the table
can be imported into Excel in largely the same way as you can link to an external
source. As long as the external source can be connected to using a method that
Page 69
Excel supports, and is in a format that is appropriate, the Table can be populated.
The following table lists the four Source options available in VBA.
xlSrcExternal 0 External data source (e.g. Microsoft Windows SharePoint Services

site).
xlSrcQuery 3 Query
xlSrcRange 1 Range
xlSrcXml 2 XML
Table 13 - Excel Tables ListObject Source Options
This code sample shows the syntax for connecting to a SharePoint List.
Variables are used for the parameters. strServerName holds the url for the
SharePoint server, strListName represents the actual name of the list to import
and strListGuid is the reference to the web part holding the list.
Set objListObject = ActiveWorkbook.Worksheets(1) _
.ListObjects.Add(SourceType:=xlSrcExternal, _
LinkSource:=True, _
TableStyleName:=xlGuess, Destination:=Range("A10"))
Tip: If you have the Access Reference Library loaded, you can make use of the
DLookup function in your code.
This example code converts an existing Excel range into an Excel Table.
Firstly, we select the worksheet holding the range to convert.
Sheets("Customers_Table").Select
As the source is an existing range, the code uses the existing range name to
select the data to convert. The code assumes the top row contains the headers
and then names the new Table. This line of code actually creates the ListObject.
ActiveSheet.ListObjects.Add(xlSrcRange,Range("Cust_Range"), _
Page 70
, xlYes).Name ="Cust_Table"
Once the Table exists, the code then selects the table and applies one of the
available styles for presentational purposes. Note the syntax to include all fields.
Range("Cust_Table[#All]").Select
ActiveSheet.ListObjects("Cust_Table").TableStyle = _
"TableStyleLight4"
Removing a Table using code can be accomplished using the Delete Method.
ActiveSheet.ListObjects("Cust_Table").Delete
Once the Table has been created, you can then work with the data by filtering
and sorting as required. Use the same techniques described earlier in this
module.
One particular feature of Excel Tables is the Total row, which can be appended
and controlled using VBA. Adding a simple line of code to the routine above will
display the row. The visibility of the row is controlled with a Boolean property.
ActiveSheet.ListObjects("Cust_Table").ShowTotals = True
The default setting for the Total Row is to use one of the standard aggregate
functions and apply the function in the cell below the last column in the Table. If
a numerical value is stored in the column, excluding date / time, the function will
be the Subtotal Sum. Text and other formats will have the Subtotal Count
function applied.
Subject to the data format, the function can be controlled in your code. You can
also place the function required in the column required. Reference the column
either by a string field name, or by its index number. This line of code uses
Count under the column headed “Country”.
ActiveSheet.ListObjects("Cust_Table").ListColumns("Country") _
.TotalsCalculation =xlTotalsCalculationCount
To remove a function from a Table, reference the column as above and set the
TotalsCalculation to be equal to xlTotalsCalculationNone.
The following table identifies the functions available in a Total Row.
Page 71
XlTotalsCalculation can be one of these XlTotalsCalculation constants
xlTotalsCalculationNone
xlTotalsCalculationSum
xlTotalsCalculationAverage
xlTotalsCalculationCount
xlTotalsCalculationCountNums
Warning: Deleting the Table also deletes all the data held in the Table!
xlTotalsCalculationMin
xlTotalsCalculationStdDev
xlTotalsCalculationVar
xlTotalsCalculationMax
Table 14 - Excel Table Total Row Functions
To add new records using VBA to a Table created from a converted range, is not
complicated, but may require some work ahead of the actual records being
included in the Table. By default, any rows of data appended to the Table are
automatically included and handled as additional records.
If you do not have a Total Row, make sure that the active cell that has been
selected is the first blank cell in the left column of the Table and then simply
paste the new records in. Excel will alter the dimensions of the Table to include
the new rows.
Range("A1").Select
Selection.End(xlDown).Select
Page 72
Activecell.Offset(1,0).Select
ActiveSheet.Paste
If the Table has a Total Row, the append process is simpler, if you temporarily
switch off the Totals by toggling the status to False. Reverse the process after
appending the new records, by setting the status back to True.
ActiveSheet.ListObjects("Cust_Table").ShowTotals = False
Using External Data

If the Table is populated with data from an external source, you can use the
Refresh Method to force the Excel copy of the data to match the source.
Note the slight change in syntax in the following line of code. The code refers to
a query table indicating that refreshing the data should use the Connection stored
in the workbook for the Table. There is no need to manage any Total Row if
present. The code assumes the Table has been selected in some way.
Selection.ListObject.QueryTable.Refresh
To update all connected data in a workbook, use this code:
ActiveWorkbook.RefreshAll
If you need to delete records from the Table, simply delete the rows as you would
delete any Excel row. Note that if the data is externally stored, refreshing the
data will add any records back into the Table, if they still exist in the source.
Module 3 Lab 6: Create an Excel Table out of a Range. Turn to page 156 to
complete the Lab.
Module 3 Review
 Importing Data
 Data Types and Formatting
 Validating Data
Page 73
 Filtering and Sorting Data with VBA

 Using Database Functions
 Excel Tables
Review Questions:
1. What is the purpose of the TypeName function?
2. What is a Connection?
3. What criteria could you use to find blank records when applying a filter?
4. What code needs to be used to enable the use of an Excel function in VBA?
5. An Excel Table is a member of which collection?
6. What does the run time error “Type mismatch” mean?
7. What data type might the number 32768.37 be stored as, in a variable?
Notes
www.qa.com
Page 74
Module 4 - Presenting Data in Excel

 Applying Conditional Formatting with VBA
 Working with Charts
 Pivot Tables
Introduction
In the previous module we examined some of the VBA methods available to
automate working with raw data in workbooks. We also looked at Excel Tables.
That topic also involved some ‘presentation’ of your data. This module extends
the presentational aspects and will take a more specific look at some of the
options for presenting or working with data visually.
Topics will include applying and customising conditional formatting, across a

range of cells which contain data, even if the range is dynamic. We will examine
the chart Object from two perspectives. Charts created as a separate worksheet,
or as an Object on a standard worksheet, as both present different challenges in
VBA. This topic will also introduce using VBA to control another Office
application as a destination for a chart.
Finally, we will introduce some concepts around working with PivotTables using
VBA.
Topic A - Apply Conditional Formatting with VBA

We know we can manually set and apply conditional formats using icons, data
bars or colours to a specified range of cells from the Excel GUI. The same
options are available when automating the processes involved. These processes
are discussed below. We will also consider how you might apply conditional
formatting if the range containing the data is dynamic in some way. (Refer to
Module 2 – Working with the Range Object for further information).
Page 75
Scenario
Data is added to a new sheet in a workbook on a regular basis. Every time the
data is added into the new sheet it needs to have conditional formatting applied.
The conditional formatting can be applied manually each time the new data and
sheet are added, or perhaps the formatting can be applied using the format
painter.
There may not be a built-in conditional format that matches the requirements for
either the presentation or complexity of the data. Take the icons you can apply.
These are usually divided into equal parts e.g. 3 icons with each icon
representing a third of the data values.
Consider the processes below which might be required to achieve the formatting
of the data on a new sheet in a workbook, each time data is added to the file. We
will assume that the method of adding the data is a copy and paste, although it
could be manually entered, or an imported data set.
1. Open destination workbook
2. Add a new sheet in a suitable location and name the sheet
3. Get data and write to new sheet
4. Select data regardless of size of range
5. Apply conditional formatting
6. Close and save workbook
Processes 1, 2 and 6 are easily coded and are discussed in module 2; processes
3 and 4 can be handled by VBA.
We will now consider the actions required to apply the conditional formatting
using VBA.
Conditional formatting relies on one or more tests to be applied to the data to be

formatted. Any test is fundamentally Boolean in its construction and therefore will
rely on the comparison operators. The comparison could be hard coded into the
VBA, use variable measures, or perhaps reference other data elsewhere in the
workbook. Look at this sample code:
With Worksheets(1).Range("e1:e10").FormatConditions _
.Add(xlCellValue, xlGreater, "=$a$1")
With .Borders
.LineStyle = xlContinuous
.Weight = xlThin
Page 76
.ColorIndex = 6
End With
With .Font
.Bold = True
.ColorIndex = 3
End With
End With
The code will apply a format, based upon a test of the values in a range of cells
being compared to the value in another cell. The comparison operator used is
‘xlGreater’ which is a member of the xlFormatConditionOperator child class.
To summarise what the code above achieves: the range is identified and then the
data in the range is subject to a comparison against another value. If the
comparison results in a true statement, then the formats are applied.
At this point you might be thinking that this is not what we mean, when we talk of
conditional formatting. It is conditional formatting in a simple form and introduces
a reference to the important FormatConditions Object.
FormatConditions Object
The FormatConditions Object represents the conditional formats that can be
applied to a range of cells. There may be one or multiple formats applied to the
range. All of which are referenced in the code using FormatConditions. New
formats and the conditions required can be applied using the Add Method. The
Modify Method is used to amend existing formats. The syntax for the Add
Method is as follows:
expression.Add(Type, Operator, Formula1, Formula2)
‘Expression’ represents the Object to apply the FormatConditions to.
‘Type’ is used to specify whether the format is based on a cell value, as in the
example above, or whether an expression is used. An example expression could
be AddDataBar. The expressions are specific Methods for the conditional
formatting we see on the Excel Home tab. These are listed in the following table.
This argument is required.
‘Operator’ is an optional argument and is generally used in conjunction with the

optional ‘Formula1, Formula2’ arguments. Examples of the ‘Operator’ could
include xlEqual or xlGreater. In the example code above, the xlGreater is the
‘Operator’ and the cell reference is the ‘Formula1’ argument. The ‘Formula’
argument can be a cell reference, a constant, a string or a formula.
Page 77
xlGreater, "=$a$1"
FormatConditions Expression Result/Purpose
AddAboveAverage For all values above average for the range
AddColorScale Adds colour scales conditional formatting
AddDatabar Adds data bar conditional formatting
AddIconSetCondition Uses one of the built-in icon sets
AddTop10 Format for the top 10 values only
AddUniqueValues Only formats the unique values in a range
Table 15 - FormatConditions Type Expressions
The same principles applied in the code sample above, also apply to using the
pre-set graphical formats found using conditional formatting on the Ribbon. The
difference between the example above and the pre-set options in the conditional
formatting command (we are ignoring custom rules here), might be best
described by comparing an IF function and a Nested IF.
An IF has a true or false outcome. The code above results in either the format
being applied, or not. The code above is essentially an IF function.
Take a pre-set conditional format on the Ribbon such as a traffic light icon set.
There are three icons so the data comparison must comprise more than one test.
If the data value being tested is in the top 33% compared to the whole range of
values, apply the red traffic light. If, however, the data value lies in the middle
33% of the whole range of values, apply the orange traffic light. Otherwise, apply
the green traffic light.
The FormatConditions property being referred to here is known as the

IconCriteria and it determines in what order the values are tested, what they are
tested against and therefore which icon is applied to the cell. This will be
discussed with an example shortly in this topic.
Regardless of the type of formatting or graphic applied to a cell, the code must be
able to instruct Excel in which order to evaluate the comparison and then what
Page 78
formatting to apply. This example applies a colour scale to a range. The colour
scale could be added using the Add Method as in the previous example, but VBA
provides an explicit command of AddColorScale.
Sub RedBlueColourScale()
Dim RedtoBlue As ColorScale
'range has various numbers already set
Range("A1:A10").Select
ColorScale can contain up to three base colours representing maximum, mid-

point and minimum values in the selected range of cells. The argument for
AddColorScale is ColorScaleType. An integer is required of either 2 or 3
inclusive to set the argument. Using 2 sets a top and bottom measurement for
the colour range. Setting the argument criterion to 3 includes the mid-point.
'Create a two-colour ColorScale format for the data range
Set RedtoBlue = _
Selection.FormatConditions.AddColorScale(ColorScaleType:=2)
Once the parameters for the scale have been set, the actual colours to use are
identified in the code. Note that the colours are set using the RGB codes. If you
are unsure of the codes, record a macro that sets the colours required and either
choose from one of the pre-set formats, or set your own rule in the macro.
The code works on an ascending basis, using the ColorScaleCriteria index

number to apply the colours required. Excel then applies shades of the colour
until the cut-off point is reached. In this example, shades of red are applied up to
the halfway point in terms of the range of values.
'Set the minimum threshold to red, maximum threshold to blue
RedtoBlue.ColorScaleCriteria(1).FormatColor. _
Color = RGB(255, 0, 0)
RedtoBlue.ColorScaleCriteria(2).FormatColor. _
Color = RGB(0, 0, 255)
End Sub
Conditional formatting is not just the colour scales. It also includes icons and
data bars. These also use FormatConditions to establish the type of format to
apply and then the order in which to evaluate the cell values. This example code
sets a traffic light icon set on a range of cells which has already been named.
The third line of the code instructs VBA to use an icon set from the icon set
collection in Excel. The fourth line counts how many conditional formats have
been set on the range and then uses the SetFirstPriority Method. The Method is
Page 79
an instruction to apply the conditional formatting contained in the procedure to the

range first. Note that this is applied on a worksheet basis only.
Sub UseIcons()
Application.Goto Reference:="Dog_Cost"
Selection.FormatConditions.AddIconSetCondition
Selection.FormatConditions(Selection.FormatConditions. _
Count).SetFirstPriority
Having set the priority, the code uses an index to use a specific icon set.
With Selection.FormatConditions(1)
.ReverseOrder = False
.ShowIconOnly = False
.IconSet = ActiveWorkbook.IconSets(xl3TrafficLights2)
End With
The next section in the code, specifies that the values in the range are to be
evaluated proportionately by percentile using the Type statement. As the icon set
being used has three possible outcomes (the traffic lights in this case), the code
just needs to specify the lower and upper value limits to use. Any other value
which falls between the two value ranges will automatically use the middle range
icon.
The limits in this example are a straightforward split into thirds. The values can
be hard coded, a variable, expression or other value. The operator here is using
a pre-set value for the comparison operator. See the Excel Comparison
Operators table in the Appendix, for the constants and values that are available.
Here 8 is less than or equal to and 7 is greater than or equal to.
With Selection.FormatConditions(1).IconCriteria(2)
.Type = xlConditionValuePercent
.Value = 33
.Operator = xlLessEqual
End With
.Value = 67
.Operator = xlGreaterEqual
End With
End Sub
The following code could be substituted to set the evaluation against a specified
value:
.Type = xlConditionValueNumber
Page 80
.Value = 60
The other common conditional format uses data bars. The code for these is very
similar in its construction to other formats. The range needs to be identified and
selected in the code and then you use the AddDataBar Method.
Sub CustDataBars()
Range(“Price_List”).Select
Selection.FormatConditions.AddDatabar
Selection.FormatConditions(Selection.FormatConditions.Count) _
.ShowValue = True
The order of application of the conditional format is then set in the same way, as
it was for the icon set.
Selection.FormatConditions(Selection.FormatConditions.Count) _
.SetFirstPriority
Having set the priority, the code then identifies the condition to be evaluated and
applies the colour. This example sets a gradient fill. Solid fill data bars use a
single RGB number to apply the ‘.color’ property. It is recommended that you
record a macro to identify the correct colour code if you are unsure of the RGB or
gradient value to use.
.MinPoint.Modify newtype:=xlConditionValueLowestValue
.MaxPoint.Modify newtype:=xlConditionValueHighestValue
End With
With Selection.FormatConditions(1).BarColor
.Color = 5920255
.TintAndShade = 0
End With
End Sub
See the Appendix and the following tables for further information:
 Table 22 - Excel Comparison Operators
 Table 23 - Conditional Format Icon Sets
 Table 24 - Excel Condition Value Types
Module 4 Lab 1: Create and apply Conditional Formatting. Turn to page 158 to
complete the Lab.
Page 81
Topic B - Working with Charts

Excel is still the primary application providing charting capabilities. If the charting
process is to be controlled through the use of VBA, then different approaches
need to be employed dependent on where the code runs from. Sample syntax to
add a chart in excel.
Workbooks.Charts.Add
The following Reference Libraries all contain objects that are accessed when
working with charts.
 Microsoft Office 14.0 Object Library
 Microsoft Graph 14.0 Object Library
 Microsoft Excel 14.0 Object Library
The Charts.Add Method in Excel takes the selected data and creates a chart on a
new sheet in the workbook. In Excel, a chart is a member of the ChartObjects
Class if added as a new worksheet, or a Shape if added to the current worksheet.
The chart is subsequently referenced as a member of the ChartObjects Class if
manipulated by VBA.
Adding Charts in Excel Using VBA

Using VBA to create charts in workbooks is fast, efficient and easy, but needs to
be planned with a little care. Look at the code below. A defined range of data
is selected in code and then the Add Method is invoked.
Information: The Add Method will only create a chart on a new worksheet in the
workbook. To create a chart in the active sheet, use the AddChart Method..
Warning: There is a known error with the Type argument used in both the Add and
AddChart method. Avoid using it when adding the chart and instead use the
ChartType property to manipulate the Type of chart.
This code shows the optional arguments that may be used to provide some
control at run time.
Charts.Add(Before, After, Count, Type)
If you do not use any of the arguments, a default chart (normally a 2D Column) is
created on a new sheet in the workbook. The sheet is positioned to the left of the
Page 82
current sheet. To control the initial positioning of the new sheet, explicitly specify
the location using Before or After.
Charts.Add After:=Sheets("Sales")
To create several new chart sheets each with their own chart, use the Count
argument. If the argument is omitted, a single new sheet is added. Should more
than one chart be created, they will all be of the same chart type. If different
chart types are required, then each chart will need to be activated and changed
separately. This can be actioned from VBA.
Charts.Add After:=Sheets("Sales"), Count:=2
Where a different chart type is required, the Type property should be used.
Charts.Add After:=Sheets("Sales")
ActiveChart.ChartType=xl3DPie
To add a chart to the active sheet, the AddChart Method is required. Note the
additional reference to the Shapes Class.
ActiveSheet.Shapes.AddChart(Type, Left, Top, Width, Height)
The arguments are optional and need not be used, if the default chart type is
required, and the size or position is not important. If a degree of control is
needed, then the arguments fall into three categories.
Type argument should be avoided as discussed above.
Left and Top are used to control the position of the chart on the active sheet.
Both are measured in points. Left is the distance from column A and Top is
relative to row 1.
ActiveSheet.Shapes.AddChart Left:=260, Top:=180
Width and Height control the actual size of the chart. These are also measured
in points.
ActiveSheet.Shapes.AddChart Width:=500, Height:=275
If the arguments for position and size are used, some degree of trial and error will
most probably be required to achieve the desired result. It is recommended that
some care be taken with these arguments. Different screen resolutions will give
different results!
Regardless of the Method used to create the chart and therefore where it is
located, ActiveChart is subsequently used to manipulate the properties of the
Object.
Page 83
Naming, Activating and Updating Charts Using VBA

Once the chart has been created, it will have been assigned a name by Excel at
the time of its creation. Knowing or setting the name of the chart makes
activation and subsequent updates much easier.
The name of an embedded chart will consist of two parts, only one of which is
visible in the GUI. Selecting a chart manually in Excel, and then going to the
Layout tab in the Chart Tools section of the Ribbon, will display the second part
of the name, e.g. Chart 13. This can of course be updated manually, by entering
the name required directly into the Ribbon.
When you need to refer to the chart in VBA, the Name property is available.
ActiveChart.Name
Unfortunately, this property includes both parts of the chart object’s name. The
Name Property of the chart includes the name of the worksheet, if the chart is an
embedded object on the sheet. This means that subsequent attempts to activate
the chart may fail, unless you reference the sheet name as well. The sample
code here creates a chart in the ‘Sales Data’ worksheet. We will not know the
name of the chart, so the Name property which is a string, is assigned to the
variable ‘ChNameOld’.
Dim ChNameOld, ChNameNew, ChPos
Sheets("Sales Data").Activate
ActiveSheet.Shapes.AddChart.Select
ActiveChart.SetSourceData Source:=Range("Sales")
ActiveChart.ChartType = xl3DColumn
'name property includes the sheet name as well as the chartname!!
This section of code then uses Instr to search the Name string, to generate a
point from which the Right function is used to rewrite the value in the ChNameOld
variable. This allows us to reference the chart directly and then rename it
programmatically as required.
ChNameOld = ActiveChart.Name
'find position of 'Chart' in name-remember instr finds starting
‘postition!
ChPos = InStr(1, ChNameOld, "Ch") - 1
ChNameOld = Right(ChNameOld, Len(ChNameOld) - ChPos)
ChNameNew = "Sales Chart"
ActiveSheet.ChartObjects(ChNameOld).Name = ChNameNew
‘Subsequent code can then activate and work with the chart as
‘required.
Page 84
ActiveSheet.ChartObjects("Sales Chart").Activate
If the chart is on a chart worksheet, you can activate it by referring to the name of
the sheet. The sheet name is used as the chart name by Excel. Both of these
lines of code are valid if the chart is on its own worksheet.
Sheets("Sales Chart").Activate
Charts("Sales Chart").Activate
Assuming the chart is still linked to its source data, then it will of course update
automatically if that data changes. However, what if the source data changes i.e.
it is an increasing range of cells that needs to be incorporated into the chart
automatically.
On the face of it this poses a problem because changing the data range is usually
carried out in the GUI as a manual process and you want it to happen when the
file is opened or the sheet is selected.
Planning ahead is key here. Using a named range to act as the source data for
the chart and naming the chart, simplifies subsequent processing of the chart. As
long as you can get to both the data and the chart, the update is easily achieved.
As an example, let’s say we have a chart called “Sold” based on a named range
of cells called “Sales”. Both the data and the chart are on the same sheet. The
code example here selects the existing named range and then dynamically
renames all contiguous cells with the same name.
'Select the existing data range
'rename the range using VBA
ActiveCell.CurrentRegion.Name = "Sales"
The chart is then activated by name and the source data is reset.
'Activate the named chart
ActiveSheet.ChartObjects("Sold").Activate
'Reset the chart’s source data
ActiveChart.SetSourceData Source:=Sheets("Sales Data") _
.Range("Sales")
We can trigger the code manually to run the update on an ad-hoc basis if
required. How to run the procedure without needing to manually run the code?
Best practice would indicate that the code be attached to an Event.
Putting your code into an Auto_Open Macro which will run when the workbook is
opened is one possibility. Alternatively, embed the code into a Class Module
Page 85
attached to either the workbook or the sheet that contains the chart. Placing the
code against the Activate or Open Event for the Object will also work.
Private Sub Worksheet_Activate()
'code in here
End Sub
Private Sub Workbook_Open()

'Code in here
End Sub
You also have the choice of setting any chart options such as type, size etc. This
example sets the title on the chart.
salesChart.HasTitle = True
With salesChart.ChartTitle
.Characters.Font.Italic = True
.Characters.Font.Size = 18
.Characters.Font.Color = RGB(0, 0, 100)
.Text = "2008 Sales"
End With
Copying Charts From Within Excel Using VBA

We are all familiar with Copy and Paste and most probably Paste Special, when
used in the GUI. It is easy to copy an Excel chart and then go to your
presentation or document and either paste in directly, or use the Paste Special
options to create links, or convert the chart into a picture.
In the GUI there is only one choice for copying a chart. It does not matter what is
selected in the chart, or how you copy (right-click, shortcut key etc.). VBA has
some characteristics when using the Copy Method that mean it behaves
differently with charts.
You firstly need to activate the chart whether it is on a separate sheet, or an

Object on a sheet. To make this process easier to code, it is good practice to
give each chart a name.
ActiveSheet.ChartObjects("2008_Sales").Activate
Tip: If a chart is on a chart worksheet, the name of the sheet is the name of the
chart. Activating the sheet activates the chart.
Page 86
Once the chart is active, the temptation would be to use this code:
Charts.Copy
This is valid, but be aware that this Method automatically creates a new
workbook with the copied chart duly pasted into it. If this is what is required, then
it is a valid Method, and the copied chart retains its links back to the source data.
If you wish to copy the chart and then control where and how it will be pasted,
use this code once the chart has been activated:
ActiveChart.ChartArea.Copy
Do not worry about the reference to the ChartArea. The whole chart is copied.
You can then activate the insertion point ready for the paste. It is recommended
that Paste Special is used when copying from one application to another. This
sample code inserts the copied chart into a Word document and maintains a link
back to the source.
Selection.PasteSpecial Link:=True, _
DataType:=wdPasteOLEObject, Placement:= _
wdInLine, DisplayAsIcon:=False
An alternative Method is available which allows the chart to be copied into a
picture format. Whilst this breaks any links to the source data, it does mean that
the copied item is highly portable and its format will be retained. Having activated
the chart or the chart sheet, use this code:
ActiveChart.CopyPicture
Module 4 Lab 2: Make a Chart and Copy it to PowerPoint. Turn to page 163 to
complete the Lab.
Topic C - Pivot Tables

Pivots and VBA have been the subject of considerable debate. Creating a
PivotTable or PivotChart from Excel is a simple process, using drag and drop
followed by customisation of the results. Writing the code to create a Pivot object
from scratch is not a short process. It is recommended that if you are going to
automate any or all of the process, your starting point should be to record a
macro which creates the required Pivot Cache and Pivot Table or Chart.
Page 87
Creating a Pivot Cache and Table Using VBA

This code creates a Pivot Cache and the PivotTable based on an existing Excel
file.
Workbooks.Open Filename:="C:\Shipping.xlsx"
Sheets.Add
ActiveWorkbook.PivotCaches.Create _
(SourceType:=xlDatabase, SourceData:= _
"Invoices!R1C1:R2156C11",Version:=xlPivotTableVersion12) _
.CreatePivotTable _
TableDestination:="Sheet1!R3C1", _
TableName:="PivotTable1", _
DefaultVersion:=xlPivotTableVersion12
Sheets("Sheet1").Select
Cells(3, 1).Select
Let’s take a closer look at the code to work out what is happening via the VBA.
The first line sets the parent source of the data which will be pivoted. The source
of the data can be external to Excel and accessed using the ADO Connect
method, as detailed in Module 3 of this workbook.
A worksheet is added to provide a destination for the Pivot Object. The sheet
might already be in existence or created using VBA at run time. If either of these
is the case, then the worksheet must be referenced using:
TableDestination:="Name of sheet here!R3C1"
The Pivot Cache is required at the time the Pivot Object is created. It acts as a
temporary store of information detailing the parameters the VBA must use to
create the pivoted outcome. Fundamentally it states where to get the data, what
Fields are available and where to place the actual Pivot Object.
The SourceType indicates what format the data to be pivoted is stored in. The
most common is xlDatabase. The following table identifies the formats.
Page 88
Name Description
xlConsolidation Multiple consolidation ranges
xlDatabase Microsoft Excel list or database
xlExternal Data from another application
xlPivotTable Same source as another PivotTable report
xlScenario Data is based on scenarios created using the Scenario Manager
Table 16 - Pivot Table Data Formats
The Insert, PivotTable function on the Ribbon does not support Multiple
Consolidation Ranges so you cannot record a Macro to combine data into a single
Pivot.
Tip: To use the legacy PivotTable or Pivot Chart Wizard, add the command by
customising the Quick Access Toolbar. Choose All Commands and scroll to
PivotTableWizard.
The ‘SourceData:=’ code can be an absolute cell range, or a named range in

Excel. It must include the location of the workbook or worksheet.
Version information identifies the Pivot engine being used. The number 12 is
Office 2010.
Once the Cache has been established, the PivotTable is created in the location
specified. The table name is automatically generated at run time, if the string is
left as a null value. If you want to specify the name then it must be a string. The
previously added or referenced worksheet is activated and the explicit cell
reference is used as the start point for the PivotTable.
If a PivotChart is to be created, the code above is included together with this

sample code which is appended:
ActiveSheet.Shapes.AddChart.Select
ActiveChart.SetSourceData _
Source:=Range("'Sheet2'!$A$1:$J$2126")
Page 89
ActiveWorkbook.ShowPivotChartActiveFields = True
ActiveChart.ChartType = xlColumnClustered
Adding Pivot Table Fields

The code discussed so far creates the placeholder for the source data to be
pivoted. The Fields need to be added individually by the VBA, thus emulating the
‘drag and drop’ approach you would use from the Excel GUI.
There are four main Fields to include in the code.
xlPageField This is the Office 2010 term for a Report Filter and is seen
as the main driver of your PivotTable. It will appear in the
top-left hand corner of the table.
xlRowField Creates a list of all the available data entries for whichever
data field is chosen. Appears sorted A-Z down the left
hand side of the table.
xlColumnField Creates a list of all the available data entries for whichever
data field is chosen. Appears sorted A-Z across the top of
the table.
DataFields The data contained in these fields represent the

summarised information from the cross tabulation of the
row and column fields.
Table 17 - Pivot Table Fields
There is an additional field called the CubeField. This represents data used in
the Pivot Object as a result of an OLAP query.
This code adds the Country Field to be the Page Field. The hierarchy is
straightforward: which worksheet, which table, which available Field is identified
initially. The ‘.Orientation’ is a property of the PivotFields Object group and is
used to determine the location of the Field on the PivotTable. Excel will allow
more than one Field per Orientation, so the ‘.Position’ property is used to identify
the order which the Field appears in the table.
With ActiveSheet.PivotTables("PivotTable1") _
.PivotFields("Country")
.Orientation = xlPageField
.Position = 1
Page 90
End With
This code adds Company Name to the Row Field in the table:
With ActiveSheet.PivotTables("PivotTable1") _
.PivotFields("Company Name")
.Orientation = xlRowField
.Position = 1
End With
Datafields require a little more instruction to be created in the table. The same
hierarchy is utilised. However, the AddDataField Method is called to identify the
location for the summarised data field. After the Field to base the summary on is
identified in the code ‘.PivotFields(“Extended Price”)’, the caption or label for the
table entry is set, followed by the aggregate function to be used to calculate the
summary.
With
ActiveSheet.PivotTables("PivotTable1").AddDataField _
ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("Extended Price"), _
"Total Value", xlSum
Once the PivotTable is in existence, whether created or simply reference by the

VBA, the following Method should be used to ensure the data is current.
PivotTables("PivotTable1").RefreshTable
Extracting Data from the Pivot Table

We have seen that we can create a PivotTable using code and then
subsequently refresh the data. When working within the GUI, the GetPivotData
function can be used to extract an individual item of data from the table, for use
elsewhere in a workbook. The function is also available in VBA as a Method of
the PivotTable Object.
The syntax used is very similar to the Excel function. A range is returned by the
results of the Method, which can contain up to 14 items of data from a field in the
table.
expression.GetPivotData(DataField, Field1, Item1, Field2,
Item2, Field3, Item3,…)
Here ‘expression’ refers to the name of the PivotTable to be referenced by the
Method.
‘DataField’ is used to identify the actual field in the source data to query.
Page 91
‘Field1’ refers to the name of the column or row in the PivotTable and ‘Item1’ is
the entry in ‘Field1’ to return to the range.
The sample code below returns the value of the UK entry in the Country row, or
column from the Widgets data field, in a PivotTable called Annual Sales. The
value is stored in a variable, which could then be subsequently processed as
required.
Sub WidgetSalesByCountry()
Dim rngCountryResults As Range
Set rngCountryResults = ActiveCell. _
PivotTable("AnnualSales").GetPivotData _
("Widgets", "Country", "UK")
MsgBox "The total Widget Sales in the UK is " & _
rngCountryResults.Value
End Sub
Module 4 Lab 3: Pivot the Sales Data. Turn to page 163 to complete the Lab.
Module 4 Review
 Applying Conditional Formatting with VBA
 Working with Charts
 Pivot Tables
Review Questions:
1. What is a Pivot Cache?
2. How can you reference a chart in VBA if it is on a separate sheet?
3. Give three examples of FormatConditions
4. Where can the data used to create a Pivot Table be stored?
5. What does this line of code do?
Page 92
Notes
www.qa.com
Page 93
Module 5 - Working with the Excel Interface

 Working with Page and Print Options
 Controlling Visual Elements of a Workbook
 Workbook Sharing and Track Changes
 Workbook Security using VBA
Introduction
In this module, we will consider how you may use VBA to work with elements of
Excel, which are not necessarily directly linked to the data in a workbook. The
purpose here is to transfer the focus of your code from a range, or data specific
viewpoint, to one where we are looking at working with some of the Excel
components, which affect how a workbook is presented to the end user as a tool.
We will examine how you might print with the required options, such as headers,
footers and page layout being set. This module also discusses how you can use
VBA to control which parts of a workbook are visible, by changing Window
options or applying properties to worksheets, including hiding, un-hiding or
manipulating document and sheet protection.
Reviewing changes to a shared workbook are also considered.
Topic A - Working with Page and Print Options

Many applications such as Word or PowerPoint provide an obvious page layout
when you work with a document. You create a new document and the page
orientation and margins are often clearly defined. This makes working within the
confines of what is visible a simple process and will often control how a document
will print. Headers and footers are also usually displayed. Excel does not
provide those visible guides by default, unless you are in the new Page Layout
view.
Page 94
How often have you printed an Excel worksheet, only to discover the data does
not flow correctly, due to the page orientation being inappropriate or more often
than not, your data fits nicely onto several pages apart from one column or row?
Setting the page orientation, margins and more importantly the Print Area, are
relatively easily achieved through the Page Layout tab on the Ribbon.
What happens though, if the area to print changes each time, because the data is
constantly changing dimensions? Setting the options for layout and printing
could be automated, so that consistent results are achieved each time. We
discussed working with dynamic ranges in Module 2. Applying techniques for
working with ranges is useful when the data in your workbooks alters.
Scenario
You have a workbook in which sales records are recorded. The workbook will
have up to four worksheets, with each sheet representing a quarter of the current
year. Each quarter sheet is added as required and will probably contain a
different number of records, although the top row of each sheet will have the
same column headings. The average number of records is 2000 per quarter, but
this is no guarantee of how large the range to print will be.
A printout of the current quarter’s sales is required on a monthly basis. Printing is

carried out in the first week of the following month. Each print must repeat the
column headings at the top of each page and needs to be landscape on A3 size
paper. The data will fit widthways on that paper size. Sales records for the
months included on the current quarter are appended to each sheet by date and
need to be included in the printed material. The printout requires a header
identifying the name of the sheet and the date of printing. The footer should
contain page numbering.
You want to create a procedure that will make sure paper size, page orientation
and the print area is set every time. The correct sheet for the quarter must be
selected and column headings repeated at the top of each piece of paper in the
printout. Headers and footers to be set as required. Of course, your code will
also need to specify which printer to use.
The requirements in the example above mean the code will need to reference
various elements in the Excel model, from the application down to range level.
Assuming that the workbook is opened in the code and its location is known, the
first requirement is to ensure that the correct sheet is selected. We know that the
workbook has a maximum of 4 worksheets. These will be labelled Q1, Q2, etc.,
with Q1 containing the sales records for January to March inclusive.
Page 95
A simple Select…Case statement would be sufficient to activate the correct

sheet.
Dim dtmCurDate
dtmCurDate = Month(Date)
Select Case dtmCurDate
Case 1 To 3
Sheets("Q1").Activate
Case 4 To 6
Sheets("Q2").Activate
Having selected the sheet, the code will need to identify the records to include in
the printout in order to set the print area. Techniques for selecting a range were
discussed in Module 2, but the following simple code will suffice here. We know
the data starts in cell A1. All we need to ensure is that A1 has been selected.
Once the cell has been selected, the following code will set the Print Area.
With ActiveSheet.PageSetup
.PrintArea = ActiveCell.CurrentRegion.Address
End With
Note the use of ActiveSheet and the PageSetup property. This property is key to
manipulating its members to work with properties, such as orientation or headers
and footers. ActiveSheet does not directly support the dot generated autolist
members, so use the Object Browser to find the property you need to work with,
or record a macro.
Now we will use VBA to ensure that the page orientation is as required. Adding
the following code within the With Statement above, will instruct Excel to set the
property we want.
.Orientation = xlLandscape
Before the code sets the page size, it is recommended that you set the printer to
use first. If you attempt to set a paper size which is not supported by the printer
marked as current or default, the code will generate an error.
Setting the printer to use is carried out at application level. Excel will try to print
to the default printer for whoever is logged onto the PC, or whichever printer they
last printed to, in the current instance of Excel. Use this code to set the printer.
'The string for the printer will need to specify the print
'server path
Application.ActivePrinter = "HP LaserJet 2100 PCL6 on TS003"
Page 96
We can now set the paper size for the printing requirement. This brings the focus
of the code back to the sheet containing the records.
ActiveSheet.PageSetup.PaperSize = xlPaperA3
Setting the Print Titles and Header / Footer options remain to be included in the
VBA, ahead of the actual Print command being issued.
.PrintTitleRows = "$1:$1"
.LeftHeader = "&D"
.RightHeader = "&A"
.CenterFooter = "&P of &N"
End With
The code is not complicated but the syntax must be correct. All the options are
referenced as String, including the field codes to set the options required. The
Header / Footer codes are detailed in the following table.
VBA String Field Code
“&P” Page Number
“&N” Number of Pages
“&D” Date at time of printing
“&T” Time when printed
“&Z&F” Path and Filename
“&F” Filename
“&A” Sheet Name
Table 18 - VBA Header and Footer Codes
If you want to preview before printing, then use this line of code:
ActiveWindow.SelectedSheets.PrintPreview
To print the required information without previewing, use:
ActiveSheet.PrintOut
Page 97
After you have either viewed the sheet in Print Preview, or have issued the Print
command, the page breaks are automatically displayed. To remove them, use
this line of code:
ActiveSheet.DisplayPageBreaks = False
All the code listed above can be combined into a single macro or function, which
can be triggered manually, or attached to an automatic workbook event.
Module 5 Lab 1: Work with Print and Page Settings. Turn to page 166 to complete
the Lab.
Topic B - Controlling Visual Elements of a Workbook

This topic discusses working with the physical parts of a workbook and the Excel
GUI. Techniques covered will include freezing and splitting a worksheet window,
hiding or displaying worksheets and columns. We will also look at how you can
use VBA to change the properties of parts of the Excel window, in order to control
what is visible to an end user.
Freezing or Splitting a Worksheet Using VBA

One of the most useful tools when working with large data ranges is the ability to
freeze or lock rows and columns, so that they remain visible when scrolling the
worksheet.
Whilst the code is not complicated, the VBA must ensure that the worksheet to
apply a freeze or split to has been selected. In other words, the sheet must be
active. As we saw in the previous topic, pointing the code at the correct Excel
Object is the key. When you need to freeze or split, the code needs to work with
the ActiveWindow, hence the requirement to make the sheet active.
The VBA is almost identical whether freezing or splitting or, for that matter, if
removing the freeze and split. There are limitations in the GUI that are replicated
in VBA. For instance, you cannot freeze and split a sheet at the same time. You
also need to remember to adopt an R1C1 approach to referencing rows and
columns.
First we will apply a split to a sheet. The code uses the ActiveWindow member of
the Application Class and then applies the appropriate ‘split’ row or column
property.
Page 98
With ActiveWindow
.SplitColumn = 6
.SplitRow = 9
End With
The code applies the split to the right of the specified column (F in this case) and
below the row. So the example places the midpoint of the split at the bottom-right
hand corner of cell F9. Note the R1C1 references. The column and row
references must be integers. If you used the following code, VBA will generate a
run time error indicating a Type Mismatch.
.SplitColumn = "F"
To remove the split, replace the column and row references with 0 (zero). To
move an existing split, use the same code and replace the references with the
new position for the split e.g.
With ActiveWindow
.SplitColumn = 2
.SplitRow = 13
End With
This will move the split so that the cross is at the bottom-right hand corner of cell
B13.
Freezing a sheet uses almost identical VBA to the split. Excel treats freezing and
splitting as variants of each other and the code behaves in the same way. The
VBA required to freeze a sheet has two elements. You first specify the row and /
or column to specify where to apply the freeze. This is the first element and in
this example we are specifying that the top row is to be frozen.
With ActiveWindow
.SplitColumn = 0
.SplitRow = 1
End With
The second element of the VBA is the instruction to Excel to treat the code as a
freeze and not a split. The code toggles the Boolean property to True, to apply
the freeze at the location specified in the code above.
ActiveWindow.FreezePanes = True
To remove the freeze, it is tempting to simply run the following line of code in
order to set the FreezePanes property back to False.
ActiveWindow.FreezePanes = False
Page 99
Unfortunately, running the code on its own simply removes the freeze and leaves
a split in its place. If you step through the code to apply the freeze and look at
the GUI, you will notice that the Split command on the View tab indicates it has
been clicked. This is because VBA uses SplitRow and SplitColumn to set the
location to freeze. To remove the freeze, the VBA needs to repeat the split with
the row and column references set to zero.
With ActiveWindow
.SplitColumn = 0
.SplitRow = 0
End With
ActiveWindow.FreezePanes = False
Hiding Columns, Rows or Sheets

There will be occasions when it is prudent to prevent information being displayed
to the end user. For example, you have a sheet containing lookup formulas
which reference other data in the workbook which must not be altered. A simple
technique is to hide the data. This is often combined with sheet or workbook
protection. We shall discuss protection later in this module.
Hiding a column or row uses similar VBA and is usually selection specific. The
process to follow in the GUI would usually involve selecting the column or row to
hide, followed by applying the appropriate format. Hiding, or displaying rows and
columns is a formatting property which toggles the visible state. VBA works in
the same way. This code hides column B by setting the Hidden property.
Columns("B:B").Select
Selection.EntireColumn.Hidden = True
To hide a row you can use the same code, changing EntireColumn for
EntireRow. Note that the row is referenced as a String.
Rows("4:4").Select
Selection.EntireRow.Hidden = True
The code to display the column again is the same, with the exception of the
Hidden property. The code can still ‘select’ the column, even though it is not
visible.
Columns("B:B").Select
Selection.EntireColumn.Hidden = False
Unhiding the rows or columns in the example above, assumes you know which
row or column to select. If the precise references are not known or, alternatively,
Page 100
there are several rows and columns hidden, you can use the CurrentRegion
property of the range using this code.
Selection.CurrentRegion.EntireColumn.Hidden = False
This will work even if the first or last columns or rows are hidden, as they are still
part of the CurrentRegion and therefore selectable by the VBA.
The same principles can be applied to entire sheets in a workbook, whether they
are being hidden or displayed. Again, the key is making a sheet that you need to
hide active. Once the sheet is active, then this code will hide it.
ActiveWindow.SelectedSheets.Visible = False
If you need to hide more than one sheet, either select the sheets first and then
use the same line of code or loop through the sheets, setting the Visible property
as required. An alternative method explicitly refers to the sheet by name or
index.
To unhide a sheet, apply the same principles to unhide rows or columns.

Remember that the Visible property is part of the format for the sheet and
therefore the object is still present in the workbook. You can either reference the
sheet to unhide by its String name, or index number. This sample code displays
a sheet called Q3 which was previously hidden. Using the name of the sheet,
means the VBA does not need to know the exact position of the sheet in the
workbook.
Sheets("Q3").Visible = True
If you have a hidden sheet (or sheets) in a workbook and they need to be
displayed, but you are unsure as to the name or position, use a loop to cycle
through all the sheets. The sample below checks each sheet’s Visible property
and changes it to be true if found to be false.
Dim x
For x = 1 To ActiveWorkbook.Sheets.Count
If Sheets(x).Visible = False Then
Sheets(x).Visible = True
End If
Next x
Working with rows, columns or whole sheets, to either hide or unhide the objects,
can all be triggered automatically if required. For instance, there might be a
requirement to hide a sheet to prevent changes to its contents whenever the
workbook is opened. This can be achieved by adding the code to the Open
event of the workbook or alternatively by using a Special Macro.
Page 101
Module 5 Lab 2: Hide and Unhide Worksheets. Turn to page 168 to complete the
Lab.
Topic C - Workbook Sharing and Track Changes

Workbook sharing, so that many people can open and edit the same file, is
frequently used in organisations. This of course allows the change history to be
‘silently’ recorded in the document. In this topic, we will demonstrate how to
share a workbook and set the tracking options. The process for capturing the
changes using the History sheet will be discussed. We will then look at how a
workbook can be opened in exclusive mode by removing the sharing.
There are limitations to what can be achieved or actioned using VBA, particularly
when reviewing any changes to the workbook. Sharing is frequently combined
with workbook or sheet protection. The subject of protection is discussed in the
next topic.
Sharing a Workbook and Setting Tracking Options

Sharing a workbook requires editing by more than one user to be set, even if the
change history is not tracked. The sharing process also requires the ‘shared’
property to be saved to the workbook file, regardless of whether changes are
stored for future review.
The process for sharing the workbook in the GUI involves up to three separate
actions. These are, in the order to be applied: checking the box to allow more
than one user to open or edit the file, setting tracking options and finally saving
the file.
In our first example code, the defaults are accepted prior to the file being saved.
With ActiveWorkbook
.KeepChangeHistory = True
End With
The reference to the change history is a Boolean property of the workbook. If it is

left as True, the changes to the workbook are automatically tracked for the
default duration of 30 days. If tracked changes are not required, set the property
to False.
To change the duration, add this code to the With… End With statement:
Page 102
.ChangeHistoryDuration = 100
You can also set, or alter, the other track changes options using VBA. Look at
the following screenshot showing the options available. The frequency of any
changes to the workbook can be set, together with which changes ‘win’ if there is
a conflict. Finally, personal settings can be set for filters and printing.
The code examples that follow will set the options to match the screenshot.
Figure 7 - Track Changes Options
To set the frequency for updating the local copy of the file making it automatic,
use this code. The code forces updates to occur every 30 minutes.
With ActiveWorkbook
.AutoUpdateFrequency = 15
.AutoUpdateSaveChanges = True
End With
Conflicts will be resolved by asking the local user which change to accept, using
this code.
Page 103
.ConflictResolution = xlUserResolution
Finally, the print and filter settings are not preserved, with the VBA removing the
entries in the two checkboxes.
.PersonalViewListSettings = False
.PersonalViewPrintSettings = False
Once the options have been set, the final step is to save the workbook to
complete the process.
ActiveWorkbook.SaveAs Filename:= _
"C:\Documents\Sales.xlsx", _
FileFormat:=xlOpenXMLWorkbook, AccessMode:=xlShared
The key code when saving the file determines the ‘AccessMode’. Note the
argument ‘xlShared’. This argument can be set to an exclusive mode to remove
sharing.
A shared workbook is referred to in VBA as a shared list. The actual property is

referred to in VBA as MultiUserEditing. The property is invoked by the workbook
SaveAs method.
This second example shares a workbook with all the tracking options accepted,
regardless of whether they have previously been changed from the defaults. The
code checks the status of the MultiUserEditing property.
If Not ActiveWorkbook.MultiUserEditing Then
ActiveWorkbook.SaveAs Filename:=ActiveWorkbook.FullName, _
AccessMode:=xlShared
End If
If the changing of options is not required, the code is a simple way of applying the
shared property. It removes the requirement to include a String reference to the
path and filename, unless the workbook has not previously been saved.
Both the examples above will prompt the user to save the changes at run time.
To turn off the prompt, set Application.DisplayAlerts = False. Do not forget to set
the property back to true at the end of the procedure!
Once a workbook has been shared with the change history recorded, you may
wish to review the changes. In the GUI, this requires you to specify which
changes to include in any review and whether the changes are highlighted on
screen and/or written to a temporary sheet called ‘History’. Those options are
also present in VBA and are illustrated in this code sample.
With ActiveWorkbook
Page 104
.HighlightChangesOptions When:=xlAllChanges, _
Who:="Everyone"
.ListChangesOnNewSheet = True
.HighlightChangesOnScreen = True
End With
To only see changes made by others, substitute “Everyone” with “Everyone but
Me” in the Who argument. Individual names can also be used, if they have been
editing the workbook.
It is not possible to accept or reject individual changes using VBA. You can,
however, accept all changes using this code.
ActiveWorkbook.AcceptAllChanges
Substitute the word Accept for Reject to reject all changes. This behaviour is
default by design.
To remove the sharing property from a workbook, the AccessMode should be

changed. This is achieved by using the ExclusiveAccess workbook method.
Note that this removes all the tracked history from the file.
ActiveWorkbook.ExclusiveAccess
If you require a list of all changes to a workbook, either when reviewing or

removing the shared status, it is recommended that you copy the changes to the
‘History’ sheet and then copy that sheet to another workbook. First list the
changes on a new sheet.
With ActiveWorkbook
.HighlightChangesOptions When:=xlAllChanges
.HighlightChangesOnScreen = True
End With
Then select the ‘History’ sheet. You can then simply apply the Copy method, as
this will automatically create a replica of the sheet in a new workbook.
Sheets("History").Select
Sheets("History").Copy
Save the newly created workbook. If the process is to be repeated, perhaps to

create an audit trail of changes over a period of time, you could copy the ‘History’
sheet to an existing workbook.
Page 105
Module 5 Lab 3: Share a Workbook and Copy Change History to a New Workbook.
Turn to page 170 to complete the Lab.
Topic D - Workbook Security Using VBA

Securing or protecting workbooks, sheets and contents is important to prevent
unwanted access, or to preserve the integrity of your data. In this topic, we will
examine options available for workbook security, including the setting and
removal of protection, in so far as Excel allows.
We will look at the options for setting security at the highest level e.g. workbook,
and then turn our attention to worksheets and cells. The subjects discussed here
could be used with other topics in this module.
Securing a Workbook
There are three SaveAs options that you can apply, when saving a workbook
directly from Excel. These are read-only recommended, password to modify and
password to open. All of these options are available in VBA and are arguments
of the SaveAs method.
Look at the following code, which saves a workbook with the read-only argument
set. The argument is Boolean and is set to False if it is omitted, unless it has
previously been applied to the workbook.
"C:\Documents and Settings\XPMUser\My Documents\2008 " _
& "Annual Sales.xlsx", _
FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False, _
ReadOnlyRecommended:=True
This code saves a workbook with a password to modify set. The argument is the
WriteResPassword. The password is stored as a String directly in the code and
is not encrypted.
Warning: Storing the password for a file directly in the code is not recommended,
unless the Project itself is password protected. Alternatively, reference the
password using a variable declared elsewhere in the project.
Page 106
"C:\Documents and Settings\XPMUser\My Documents\2008 " _

& "Annual Sales.xlsx", _
FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False, _
WriteResPassword:="Fred123"
To save a file with a password, which must be supplied in order to open the file,
use the ‘Password’ argument in the SaveAs method. Again, the password is
stored as a String.
Password:="TopSecretFile"
Protecting a workbook’s structure is also available through VBA. This is typically

used in conjunction with sheet protection. The Protect workbook method has the
following arguments:
expression.Protect(Password, Structure, Windows)
All of the arguments are optional. ‘Expression’ represents a workbook.
‘Password’ is an unencrypted String and consideration should be given to storing

the password directly in the code.
Tip: If you record a macro setting protection to a file, either through SaveAs, or
applying the Protect method and apply a password, the macro recorder omits the
password arguments from the VBA. The password is applied but not visible in the
code.
Both ‘Structure’ and ‘Windows’ are Boolean, defaulting to False if omitted.

Setting ‘Structure’ to True prevents the sheets in the workbook from being
altered, either by position or name. In effect, the Sheets Collection for the
workbook is locked. It cannot be added to, nor have any components removed.
If ‘Windows’ is set to True, the view of the current workbook will be locked, in
order to prevent new windows being opened or the sheets being tiled. The
freezing and splitting of sheets is also disallowed. This code sample invokes the
Protect method without a password being set.
ActiveWorkbook.Protect Structure:=True, Windows:=True
To remove the workbook protection, use the Unprotect method.

ActiveWorkbook.Unprotect
The code above must include the password required. If the password is not
included in the code, a run time error occurs and the VBA fails.
Page 107
Securing Ranges and Sheets

So far we have discussed protection at workbook level. We will now look at
protecting ranges and sheets.
Range or cell protection covers simple cell locking and hiding of the contents.
The protection has no effect unless combined with sheet protection when working
in the GUI. The same rules also apply in VBA. In practice, we unlock or unhide
the cells to allow editing and then protect the sheet. The same approach should
also be adopted when using VBA.
When working with ranges they need to be selected first. You then set the locked
and hidden properties as required. The properties are Boolean. This code will
allow editing of a selected range.
Selection.Locked = False
This code will prevent the contents of a cell being displayed in the formula bar,
regardless of the type of content.
Selection.FormulaHidden = True
Once the range protection has been set as required, the code must then protect
the sheet. VBA uses the Protect method for the sheet.
The first line of the following code sets the default sheet protection level. The
second line allows any cell to be selected in the GUI. It does not overwrite any
range protection.
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, _
Scenarios:=True
ActiveSheet.EnableSelection = xlNoRestrictions
To apply sheet protection, which does not use the defaults, requires each
argument to have its Boolean state explicitly set in the code. The following table
defines the arguments available for the worksheet Protect method.
Page 108
Name Required Description

/ Optional
Password Optional A string that specifies a case-sensitive password for

the worksheet or workbook. If this argument is
omitted, you can unprotect the worksheet or
workbook without using a password. Otherwise, you
must specify the password to unprotect the
worksheet or workbook. If you forget the password,
you cannot unprotect the worksheet or workbook.
DrawingObjects Optional True to protect shapes. The default value is True.
Contents Optional True to protect contents. For a chart, this protects

the entire chart. For a worksheet, this protects the
locked cells. The default value is True.
Scenarios Optional True to protect scenarios. This argument is valid only

for worksheets. The default value is True.
UserInterfaceOnly Optional True to protect the user interface, but not macros. If
this argument is omitted, protection applies both to
macros and to the user interface.
AllowFormattingCells Optional True allows the user to format any cell on a

protected worksheet. The default value is False.
AllowFormattingColumns Optional True allows the user to format any column on a

AllowFormattingRows Optional True allows the user to format any row on a

AllowInsertingColumns Optional True allows the user to insert columns on the

AllowInsertingRows Optional True allows the user to insert rows on the protected
worksheet. The default value is False.
AllowInsertingHyperlinks Optional True allows the user to insert hyperlinks on the worksheet.
The default value is False.
Page 109
AllowDeletingColumns Optional True allows the user to delete columns on the

protected worksheet, where every cell in the column
to be deleted is unlocked. The default value is
False.
AllowDeletingRows Optional True allows the user to delete rows on the protected
worksheet, where every cell in the row to be deleted
is unlocked.
AllowSorting Optional True allows the user to sort on the protected

worksheet. Every cell in the sort range must be
unlocked or unprotected.
AllowFiltering Optional True allows the user to set filters on the protected
worksheet. Users can change filter criteria, but
cannot enable or disable an auto filter. Users can set
filters on an existing auto filter.
AllowUsingPivotTables Optional True allows the user to use pivot table reports on the
Table 19 - Worksheet Protect Method Arguments
If perhaps you do not want users to select locked cells and can format unlocked
cells, the code could look similar to this:
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, _
Scenarios:=True, AllowFormattingCells:=True
ActiveSheet.EnableSelection = xlUnlockedCells
Excel provides a further method for allowing exceptions to sheet protection in
order to edit ranges, even if the ranges have been locked. AllowEditRanges is a
sub-property of the ActiveSheet Protection property. It has three main
parameters and relies on named ranges being available.
The first parameter is the Title, or name of the range, to allow editing. The
second parameter is called Range. This specifies the actual address of the
range and then finally, you would include Password. This is a String required to
be entered, in order to allow editing of the range.
This sample code refers to a range called Range1. It specifies the cells the
range covers, and then provides the password required to allow editing.
Page 110
ActiveSheet.Protection.AllowEditRanges.Add Title:="Range1", _
Range:=Range("A2:L45"), Password:="TopSecret"
All of the protection applied to a sheet can be removed with the following code.
This will of course prompt for any password which was set at the time of applying
the protection.
ActiveSheet.Unprotect
All of the protection options described in this topic can be coded separately and
many can be applied separately. Most however, will be used together and also
combined with other options referred to in this module, e.g. hide a column,
protect the remaining cells and then protect the sheet and workbook. Perhaps
you might hide a sheet to prevent changes, hide the formulas that refer to the
sheet and protect the workbook.
Module 5 Review
 Working with Page and Print Options
 Controlling Visual Elements of a Workbook
 Workbook Sharing and Track Changes
 Workbook Security Using VBA
Review Questions:
1. Give an example of the code to use to hide a worksheet
2. How do you set the print area using VBA?
3. What does this code do?
4. If you store passwords to unprotect workbooks or ranges in the code, how do

you protect the code from being accessed by another user?
5. What is the resultant effect of this code?
Page 111
With ActiveWindow
.SplitColumn = 0
.SplitRow = 1
End With
Notes
www.qa.com
Page 112
Module 6 - Managing Files and Folders Using VBA

 FileSystemObject
 Managing Folders from Excel
 File Management Tasks Using Excel
 Working with File Properties and Attributes from Excel
 Reading a Text File from Excel
Topic A - FileSystemObject
VBA is seen as a language specific to Office applications and this is a good basis
upon which to view its role. However, as we have already seen with the
References Libraries, virtually any other software installed on a PC can in theory
be accessed from VBA. This software does of course include the Microsoft
Windows operating system.
The References made available to date have all been Office Reference Libraries.
To allow VBA to directly handle Objects in a Windows File System, we need to
add another Library. The VBA Project needs the Reference Microsoft Scripting
Runtime to be made available. This library contains FileSystemObject.
Figure 8 - Scripting Runtime Library
Page 113
FileSystemObject Members
Once the Library is available, its Classes and Members can be viewed by looking
in Scripting. Selecting the FileSystemObject Class will display the Methods which
should appear familiar to anyone who uses Windows Explorer. These Methods
together with some file and folder specific commands are now available for use.
Figure 9 - File System Objects
The use of the FileSystemObject Class requires the declaration and setting of
object variables, together with the CreateObject function to allow virtually any file
or folder to be referenced.
An example to identify attributes of a file may look something like this:
Sub GetFileDates()
Dim File, Fso
Set the Fso variable to handle a file type:
Set Fso = CreateObject("Scripting.FileSystemObject")
Page 114
Set File = Fso.OpenTextFile("C:\Audit.txt")

Now the attributes can be accessed, e.g.:
File.DateCreated
File.DateLastModified
End Sub
Topic B - Managing Folders from Excel

The example above relates to a file, however, the same principles can also be
applied to folders. As we can work with Collections and their Objects, the same
types of processes are applied to folders. After all, a folder can be a Collection or
an Object in a Collection.
Think about common tasks carried out with folders: check to see if the folder
exists before you save files to it; create a new folder if it does not exist; move or
delete folders so that you are always referencing the current location for your
files. All of these actions are possible from directly within the Office application
using VBA.
Create a Folder
Scenario
A file is being created or updated which will be stored in a folder that does not yet
exist. Whether the file is saved manually or by using VBA, a procedure can be
written to create the folder on behalf of the end user, either triggered manually or
attached to an automated event.
The FileSystemObject is the key to the procedure. Set within a procedure, the
code would be similar to this:
Dim Fso 'declare a variable to reference the new folder
Create the folder specify the path\name as String:
Fso.CreateFolder("C:\Audit Files")
Tip: The CreateFolder Method will generate a trappable error if the folder already
exists.
Page 115
Module 6 Lab 1: Add the Scripting Library and Create a Folder. Turn to page 172 to
complete the Lab.
Check If a Folder Exists

You are going to create a new folder using VBA. Running the procedure might
generate an error, if the folder already exists. You do not want the user to see an
error in the code.
If your procedure to create the folder does not include a suitable error handler,
you can include a logical or Boolean test to discover whether the folder already
exists, before the code to create the folder runs. The code may look something
like this:
Dim FsoCheck 'declare the variable to hold the FSO
Set FsoCheck =CreateObject("Scripting.FileSystemObject")
Test for the folder:
If FsoCheck.FolderExists("C:\Audit Files") Then
MsgBox FsoCheck & " folder already exists"
End If
 This code could be nested in the folder creation procedure, to determine

whether you need to create the folder.
Move a Folder
Scenario
You have an automated routine that always needs to store a file in a folder which
represents the current month. As the months change, you need the routine to
check the current date and if the destination folder does not match the month,
you should move the old folder to an archive.
We would need code to identify the current date and extract the month from it.
Then check to see if the current destination folder contains the month
information. If it does, move the folder to a specified location. We could then
replace it using the CreateFolder Method.
Variables are declared as per the examples above. The syntax to move a folder
is:
Fso.MoveFolder("C:\Audit Files", "C:\Audit Archive\")
Page 116
 The first string argument identifies the folder to move and the second string is
the destination. Note the use of the trailing “\”. This tells the code to place the
folder inside another creating a subfolder.
Delete a Folder
We have seen how a folder can be checked for, created or moved. We can also
delete a folder using VBA. There may be instances where this action is required,
e.g. where a folder is created every time a routine is run, or to remove redundant
items.
Assuming that we have checked that the folder exists, the code to carry out the
delete method is similar to examples shown above.
Fso.DeleteFolder("C:\Audit Files")
This Method must be used carefully as it cannot be undone in many cases.
Tip: If you need to get to subfolders inside the folder being examined, use this code:
For Each MyFolder in MyFolder.SubFolders
'strTmp = variable
strTmp = strTmp & MyFolder & ","
Next
Warning: Deleting a folder also deletes the files and subfolders it contains. If the
folder contains files which are read-only or shared, an error occurs at runtime.
Viewing Files in a Folder

VBA is capable of working directly with folders as objects. There will often be a
requirement to ‘look’ inside a folder to carry out a search for a file, or perhaps to
check whether it is empty before it is deleted. This code sample uses a For...
Next loop to cycle through all the files in a folder, displaying the name of each file
found and then finally displaying the number of files.
The code could be adapted to check if the Count is 0, or to search for a particular
file to work with using the .Name property of each file found.
Sub CheckFolderContents()
Dim Fso, MyFolder, MyFile
Page 117
Set MyFolder = Fso.GetFolder("C:\Audit Files")
For Each MyFile In MyFolder.Files

MsgBox MyFile
Next
MsgBox MyFolder.Files.Count
End Sub
Module 6 Lab 2: Open a Folder and Write a File List to an Array Variable. Turn to
page 173 to complete the Lab.
Topic C - File Management Tasks from Excel

This module has concentrated on Methods available to work with folders. The
same processes can also be applied to file management from within the VBA
environment. As with folders, working with files requires the FileSystemObject.
For a complete list of Methods or actions available, use the Object Browser and
change the Library to Scripting. Here we will consider some common actions
carried out on files, which we would normally use Windows Explorer to
accomplish.
Moving a File
Consider the examples detailed above using folders, and apply the same
techniques to individual files. Keep in mind what you might do to a file in
Windows Explorer and then transfer those actions to VBA.
You need to move a file from one location to another. There are of course
different ways we can accomplish this with a mouse, or keyboard shortcuts.
Essentially, Move as a method is Cut and Paste. As we have no mouse in code,
we look for a Method to apply to an Object.
A file is the Object and moving the file is the Method. We will need to tell the
code a couple of Properties. Namely, filename and path for its current location
and the new location to move it to. These are normally entered in the routine as
String, but can also be referenced using a variable, if the Properties are capable
of changing each time the routine is run.
Sub MoveFileIntoFolder()
Page 118
'moves a file from one location to another

Dim Fso As FileSystemObject
Fso.MoveFile "C:\Q3_Sales.xlsx", "C:\Audit Files\"
End Sub
Examine the code sample. Note it uses a “\” at the end of the destination String.
This forces the VBA to refer to the second String as a folder. This code ‘Cuts’ the
file and ‘Pastes’ it into the new folder location.
Warning: If you omit the “\” from the end of the destination string, VBA will rename
the file and leave it in the same location. This may cause an error or an unstable
file, as there will be no extension to tell Windows how to handle the file.
Renaming a File
It could be argued that moving a file from one folder to another, is in effect
renaming it, as the full FileName Property is updated. From both a practical as
well as a VBA sense, this is not a formal action. Renaming a file implies that it is
retained in the current location, although we can combine a rename, with either
moving or copying the File Object elsewhere.
Look at the sample code below:
Sub RenameFile()
'Renames a file
Fso.MoveFile "C:\Q3_Sales.xls", "C:\Last_Quarter_Sales.xlsx"
End Sub
Compare the code with the Move Method sample. The important difference is
the lack of “\” at the end of the second String. This forces VBA to physically
rename the file and in this case, leave it in the same folder.
Tip: To combine moving and renaming a file, alter the destination String to include
the folder path.
Page 119
Copying a File
The CopyFile Method is a ‘copy’ and ‘paste’ process from directly within the code
environment. Its syntax and process is very similar to the MoveFile Method
shown above.
We can achieve the same result using ActiveDocument.SaveAs “New file name
and path” using VBA. However, that requires the document file to be opened
first. CopyFile allows us to carry out the same action, without the file being
opened into memory. Look at the code below and note the “\” at the end of the
second String. This leaves the name of the file unchanged.
Sub CopyFileIntoFolder()
'copies a file from one location into another
Fso.CopyFile "C:\Q3_Sales.xlsx", "C:\Audit Files\"
End Sub
If the file copy is to replace an existing file in the destination folder, e.g. overwrite
the file, then use this code:
Fso.CopyFile "C:\Q3_Sales.xlsx", "C:\Audit Files\" , True
 This will force an overwrite action, replacing the existing file with a new copy.
 If you need to copy the file to another location, and rename it at the same
time, use this code:
Fso.CopyFile "C:\Q3_Sales.xlsx", _
"C:\Audit Files\Old_Sales.xlsx"
 To create a copy of the file in the same folder as the file being copied, use this
code:
Fso.CopyFile "C:\Q3_Sales.xlsx", "C:\Old_Sales.xlsx"
Module 6 Lab 3: Copy a File to a Folder and Rename It. Turn to page 174 to
complete the Lab.
Deleting a File
All of the Methods detailed so far, use a very similar construction. The
expression required to force the deletion of a file also follows the same basic
Page 120
construct. Some careful consideration should be given to how the DeleteFile

Method is used, as its results are final and therefore not reversible.
Warning: The DeleteFile Method is not reversible once actioned on a file. Even
files that are local to the PC are not placed in the Recycle Bin. It is recommended
that some form of ‘check’ or prompt is placed in the code, which asks the user to
confirm the deletion. Consideration should also be given to creating a copy of the
file prior to deletion.
The code follows a familiar pattern. Note the word ‘True’ at the end of the
DeleteFile line of code. This forces the deletion of the named Object.
Sub DeleteOldFile()
'deletes a file from a specified location without prompts!

Fso.DeleteFile "C:\Old_Sales.xlsx", True
End Sub
Checking If a File Exists

All the Methods discussed above, work on the assumption that the file Object
exists before the code is run. We can check for the existence of a file prior to
carrying out any of the actions listed, or perhaps before we try and open the file,
using VBA.
Sub CheckForFile()
'Checks to see if a file existsin a specified location
If Fso.FileExists("C:\Q3_Sales.xlsx") then
<add statement block here>
End If
End Sub
In the example shown the path and name of the file are specified. The FileExists
Method is a search tool from within the code and could easily be modified to
become a flexible part of your procedure. For instance, the String containing the
Page 121
file details could be replaced with a variable that prompts the user to provide the
file details to search for.
The If block could be nested inside a looping structure, to cycle through a folder
location looking for a file which requires action. Combined with the folder Method
GetFolder as detailed earlier in the Module, the tool could search for multiple
files.
Topic D - Working with File Properties and Attributes Using VBA

All files and folders have properties which are automatically set by either the
application they are opened or created in, together with any custom properties
set by a creator or editor of a file.
Additionally, a file or folder may have attributes that are normally set by
accessing the Object’s Properties in Windows Explorer. These attributes and
properties can be exposed by VBA and in some cases can be changed using
code.
Notes
www.qa.com
Page 122
File Attributes
Constant Value Description
Normal 33 Normal file. No attributes are set
ReadOnly 1 Read-only file. Attribute is read/write
Hidden 2 Hidden file. Attribute is read/write
System 4 System file. Attribute is read/write
Volume 8 Disk drive volume label. Attribute is read-only
Directory 16 Folder or directory. Attribute is read-only
Archive 32 File has changed since last backup. Attribute is read/write
Alias 64 Link or shortcut. Attribute is read-only
Compressed 128 Compressed file. Attribute is read-only
Table 20 - File Attributes
The table lists the attributes a file may have. As always VBA has a set of integer
Values representing a property together with its associated Constant. Both can
be revealed by code. These are normally set in the following dialog boxes.
Figure 10 - File Properties and Attributes
Page 123
File Attributes may become significant when attempting to carry out an action
upon the file. For instance, your code might attempt to open a document and
then update its contents automatically. If the Attribute is set to File.Attributes = 1
then it is set to Read-only, thus preventing any updates, and in all probability
prompting the user on screen when the document is open. Setting the Attribute
in the VBA appropriately can work around this limitation. You could then
automatically reset the attribute when saving the file.
The code sample demonstrates some of the Attributes being tested for. It also
contains a sample line to turn on the Read-only Attribute. Note the syntax used
here. The Value is held as part of a bit pattern. The bit pattern is an array of
values.
The value is written to a variable called Attrib and then tested using the ‘And’
operator to compare against the possible results in the code. This means that
the Attribute Value is often a combination of Values E.g. if the code below returns
a value to Attrib of 33, then the file is Archive or 32 (found in Advanced
Properties) And Read-only or 1.
Sub TestAttributes()
Dim Fso, MyFile, Attrib
Set MyFile = Fso.GetFile("C:\Q3_Sales.xlsx")
Attrib = MyFile.Attributes
If Attrib = 0 Then MsgBox "Normal"

If Attrib And 1 Then MsgBox "Read Only"
If Attrib And 2 Then MsgBox "Hidden"
If Attrib And 128 Then MsgBox "Compressed"
End Sub
To change the Attribute, simply write the bit pattern required e.g.:
MyFile.Attributes = MyFile.Attributes Or 1
 This would make the file read-only. Using a negative value turns off the
Attribute. You could therefore temporarily set the bit pattern to -1 to turn off
Read-only, carry out an action on the file, and then set it back to 1.
Page 124
File Properties
Most file Properties are set automatically when a file is created and then
subsequently edited. Examples could be DateCreated or DateLastModified.
Some but not all of these Properties can be exposed to the VBA code.
The sample code below reveals three properties. All of these are read-only.
Sub TestProperties()
Dim Fso, MyFile, CreateProp, ModifiedProp, SizeProp

Set MyFile = Fso.GetFile("C:\Q3_Sales.xlsx")
These include date and time which can be formatted as required.
CreateProp = MyFile.DateCreated
ModifiedProp = MyFile.DateLastModified
'Size is measured in Bytes
SizeProp = MyFile.Size
End Sub
Module 6 Lab 4: Use a File’s Attributes and Properties. Turn to page 175 to
complete the Lab
Topic E - Reading a Text File Using VBA

We have seen that we can work with files and folders directly using VBA. The
properties can be exposed and altered, as can the physical location and name.
We will now look at using VBA to read the contents of a text file. In the module
‘Working with Data in Excel’, various methods for working with other file formats
were considered.
Scenario
Consider a scenario where the data is contained in a text file. You may need
some of the data only. You could import the whole file into Excel and then ‘clean’
it once imported. Alternatively, you could create a procedure using VBA, which
examines the data in the text file line by line, and only writes the data needed into
Excel.
Page 125
We can use a FileSystemObject to represent the text file. The file can be opened
virtually and the contents written line by line into a variable. Once the variable
has been populated with data, it can be validated as required and then written to
a workbook.
Here is a sample procedure to read a text file.
A Constant is set with a value of 1. The explanation for this is in the code below.
The other variables are declared as Objects to use the FileSystemObject and to
refer to the actual file which will be opened by the code. The last string variable
will be populated by each line of the text file as the loop runs through the
contents.
Sub ReadTextFile()
Const ForReading = 1
Dim objfso As Object, objtextfile As Object
Dim strTextline As String
Set objfso = CreateObject("Scripting.FileSystemObject")
The code now invokes the OpenTextFile Method. The Method has two
arguments. The first sets the path and the name of the file to be opened. The
second argument uses the ForReading Constant. The value has been set to 1.
This means the file is opened on a read-only basis.
Set objtextfile = objfso.OpenTextFile _
("C:\Testtextfile.txt", ForReading)
The loop uses the AtEndOfStream as the condition to halt the code. This is a
property of the TextStream child class and refers to the last character contained
in the text file.
The ReadLine Method in effect copies the content of each line into the string
variable that has been declared. The action is line by line and populates the
variable each time the code loops. In our example code, the content of the
variable is displayed on a Message Box. The variable content could be written to
a cell.
Do Until objtextfile.AtEndOfStream
strTextline = objtextfile.ReadLine
MsgBox strTextline
Loop
End Sub
Page 126
Module 6 Lab 5: Read the Contents of a Text File. Turn to page 176 to complete
the Lab.
Module 6 Review
 FileSystemObject
 Managing Folders from Excel
 File Management Tasks Using Excel
 Working with File Properties and Attributes from Excel
 Reading a Text File from Excel
Review Questions:
1. Which Reference Library needs to be loaded to work with files and folders?
2. What would this code do to the Q3_Sales.xlsx file?
3. Fso.CopyFile "C:\Q3_Sales.xlsx", "C:\Audit\Old_Sales.xlsx"
4. Name 3 file Attributes
5. Give an example where you might need to work with an Attribute in VBA
6. Name 3 actions you can carry out on a folder with VBA
Page 127
Lesson Labs
The following Lesson Labs will reflect on the topics covered in this book:
 Labs 1-1 to 1-5: Module 1: Excel 2010 Object Model
 Labs 2-1 to 2-7: Module 2: Working with the Range Object
 Labs 3-1 to 3-6: Module 3: Working with Data in Excel
 Labs 4-1 to 4-3: Module 4: Presenting Data in Excel
 Labs 5-1 to 5-3: Module 5: Working with the Excel Interface
 Labs 6-1 to 6-5: Module 6: File and Folder Management Using VBA
Notes
www.qa.com
Page 128
Lab 1-1: Finding a Worksheet
Scenario
This Lesson Lab will take you through the process referring to items in a
Collection.
Refer to page 2 (Collections) for topic details.
Exercise
1. Open a new blank Excel workbook.
2. Add the Developer tab to the Ribbon and set the Macro Security to ‘Enable all
macros’ from the tab.
3. Rename Sheet3 in the workbook as ‘Data’.
4. Save the file as a macro enabled workbook with your own name as the
filename.
5. Insert a Standard module into the workbook and name it modObjects.
6. Create a procedure which will iterate through the sheets in the workbook. Call
the procedure FindDataSheet. The code should display the name of each
sheet on a message box.
7. Amend the code so that it halts the procedure when the ‘Data’ sheet has been
found.
8. Save and close the workbook.
Page 129
Lab 1-2: Create an Event Driven Macro
Scenario
Understanding the use of events to work with an object’s properties provides an
opportunity to automate tasks with VBA. You want to prompt an end user to
enter information into a worksheet when they select the worksheet.
In this exercise you will use the Activate event of a worksheet to display a prompt
for information to be written to the worksheet in the next blank row in column A.
Refer to page 4 (Methods, Events and Constants) for topic details.
Exercise
1. Open your workbook and display the Visual Basic Editor. Ensure the Project
Explorer is displayed.
2. Select the Data worksheet and add ‘Customer ID’ to cell A1.
3. Double click the Data worksheet to display its code window.
4. Using the drop-down selectors at the top of the module window, change
General to be Worksheet and select Activate from the events list.
5. Add the following code to the Activate event procedure to create the prompt.
Dim CustomerID
CustomerID = InputBox("Enter the new customer's ID")
6. This code will find the next blank cell in column A and write the value of the
CustomerID variable into that cell.
Range("A1").Activate
If ActiveCell.Offset(1, 0).Value = "" Then
ActiveCell.Offset(1, 0).Value = CustomerID
Else
Activecell.Value = CustomerID
End If
7. Close the VBE and test the code by clicking on the Data worksheet tab.
8. Save and close your workbook.
Page 130
Lab 1-3: Create a New Workbook Using an Object Variable
Scenario
Your company’s annual sales are recorded in a single workbook with each
quarter on a separate worksheet. You have received a request to create a macro
that, when run, will create a new workbook with 5 worksheets. The worksheets
will be named Q1 through to Q4 with a summary sheet at the end of the
workbook.
The workbook will need saving as ‘Annual Sales’ preceded by the current year.
To help cut down on the amount of code, you decide to use an object variable to
reference the new workbook. The code will need distributing to colleagues, so
must be in its own module.
Refer to page 5 (Using the Browser) for topic details.
Exercise
1. Open the workbook previously saved with your name as the filename.
2. Open the Visual Basic Editor and display the insert a new module. Name the
module modSalesBook.
3. Create a procedure called MakeSalesBook.
4. Now declare two variables. One for the new workbook and one to hold the
current year information.
Dim SB As Workbook
Dim ThisYear
5. Populate the ThisYear variable using the Year and Date functions.
ThisYear = Year(Date)
6. Create a new workbook and use the Set statement to bind the SB variable to
the new workbook with this code.
Workbooks.Add
Set SB = ActiveWorkbook
7. Save the new workbook.
SB.SaveAs "C:\" & ThisYear & " Annual Sales.xlsx"
8. Add the two extra worksheets.
Page 131
SB.Sheets.Add
SB.Sheets.Add
9. Rename the worksheets.
SB.Sheets(1).Name = "Q1"
SB.Sheets(5).Name = "Summary"
10. Now close and save the new workbook.

SB.Close True
11. Close and save the changes to your workbook.
Notes
www.qa.com
Page 132
Lab 1-4: Creating a User Defined Type
Scenario
This Lesson Lab will take you through the process of creating a User Defined
Type called Customer_Record. The UDT will then be used in a procedure to
open a workbook and then create a new record.
Refer to page 11 for topic details.
Before you start, close all open files and open the workbook previously saved
with your name as the filename.
Exercise
1. Insert a Standard module and rename it modUDT
2. In the declaration area of the module add the following code to create the
UDT:
Public Type CustRec
CompName As String
CompCountry As String
SalesPerson As String
End Type
3. Add another module to your workbook and name it as modNewCust.
4. Create a procedure in the new module called AddNewCust and declare a

variable called NewCust as the new UDT. Declare a second variable to
reference the destination workbook.
Dim NewCust As CustRec
Dim NC As Workbook
5. Add the following code to use the UDT variable to hold the details for the new
customer to add.
NewCust.CompName = "Chips With Everything"
NewCust.CompCountry = "UK"
NewCust.SalesPerson = "Irma McCain"
6. Set the NC variable to be the New Customers workbook and activate it.
Set NC = Workbooks.Open("C:\EXVBA\New Customers.xlsx")
NC.Activate
Page 133
7. Move to the first blank row and write the UDT variable values in as a new
customer record. Then add your own code to save and close the New
Customers file.
ActiveCell.Value = NewCust.CompName
ActiveCell.Offset(0, 1).Value = NewCust.CompCountry
ActiveCell.Offset(0, 2).Value = NewCust.SalesPerson
8. Save the Changes to your workbook and leave it open for the next lab.
Notes
www.qa.com
Page 134
Lab 1-5: Creating a Class
Scenario
This Lab will take you through the process of creating a Class and then
instantiating a new instance of the Class in a procedure.
Exercise
1. Open your workbook and insert a Class module. Name the module as
cCustomer.
2. In the declaration area of the Class module declare the following variables:
Private cCoName As String
Private cCountry As String
Private cSalesp As String
3. Now add the following code to set the Property Get and Let statements:
Public Property Get CoName() As String
CoName = cCoName
End Property
Public Property Let CoName(ByVal myCoName As String)

cCoName = myCoName
End Property
Public Property Get Country() As String

Country = cCountry
End Property
Public Property Let Country(ByVal myCountry As String)

cCountry = myCountry
End Property
Public Property Get SalesPerson() As String

SalesPerson = cSalesp
End Property
Public Property Let SalesPerson (ByVal mySales As String)
Page 135
cSalesp = mySales
End Property
4. Open the module modNewCust and create a procedure called

CreateCustomer.
5. Declare and set two variables. One as a new instance of the cCustomer class
and one to represent the New Customers workbook.
Dim NC As Workbook
Dim AddCust As cCustomer
Set AddCust = New cCustomer
6. Populate the class based variables with the following code.

AddCust.CoName = "More Fries Ltd"
AddCust.Country = "USA"
AddCust.SalesPerson = "BK Jones"
7. Now write that information into the New Customers workbook. Copy and
paste the code from the previous lab’s procedure and amend as appropriate.
Set NC = Workbooks.Open("C:\EXVBA\New Customers.xlsx")
NC.Activate
ActiveCell.Value = AddCust.CoName
ActiveCell.Offset(0, 1).Value = AddCust.Country
ActiveCell.Offset(0, 2).Value = AddCust.SalesPerson
NC.Close True
8. Close and save the New Customers workbook.
9. Close and save your workbook.
Page 136
Lab 2-1: Creating a Range Object Variable
Scenario
This Lesson Lab will take you through the process of creating an object variable
representing a range. Using the variable, you will update two properties of the
range.
Refer to page 21 (Methods for Working with a Range) for topic details.
Exercise
1. Open your workbook and insert a module. Name the module modRanges.
2. Open the workbook Annual Sales.xlsx from the folder C:\EXVBA and view the
Quarter 4 Sales worksheet. You will notice that the data is not displayed
properly. Close the workbook.
3. In the new module, create a procedure called TidyRecords.
4. Declare two variables to represent a workbook and a range.
Dim Sales As Workbook
Dim Q1 As Range
5. Add the code to set the Sales variable to be the Annuals Sales workbook and
activate it.
6. Add the following code to populate the Q1 variable with the contents of the
Quarter 1 Sales worksheet.
Set Q1 = Sales.Sheets("Quarter 4 Sales") _
.Range("A1").CurrentRegion
7. We will now turn off any text wrapping and autofit the columns to the data.
With Q1
.Cells.WrapText = False
.Columns.AutoFit
End With
8. Check that the code opens the Annual Sales workbook and formats the
correct range by stepping through the code.
9. Close and save the Annual Sales workbook.
Page 137
Lab 2-2: Counting Records in a Range
Scenario
This Exercise Lab will work with the CurrentRegion property of a range to carry
out a count of the rows within the range in order to determine the number of
records. The exercise will need to take account of the top row in the range as
this will be a header row and should not be included in the record count. The
code will need to process all the worksheets in the workbook to write a total value
to another variable.
Refer to page 22 (Dynamic Range Handling) for topic details.
Exercise
1. Open your Excel workbook and navigate to the modRanges module.
2. Create a procedure called TotalSales
3. Declare the following variables:
Dim Sales As Workbook
Dim Q1 As Integer, Q2 As Integer, Q3 As Integer, Q4 As Integer
Dim Total As Integer
4. Assign a value to the Sales variable and activate the workbook.
Set Sales = Workbooks.Open("C:\EXVBA\Annual Sales.xlsx")
Sales.Activate
5. Add the following line of code to populate the Q1 variable.
Q1 = Sales.Sheets("Quarter 1 Sales") _
.Range("A1").CurrentRegion.Rows.Count - 1
6. Repeat the code for the other 3 worksheets in the workbook in order to
populate the Q2 to Q4 variables.
7. Add the code to make the Total variable value the sum of the Q1 to Q4
variables and display the result on a message box. (Answer should be 2155).
8. Close the Annual Sales workbook, without saving changes if prompted.
Page 138
Lab 2-3: Dynamically Rename a Range
Scenario
Often when working with data you will find that the amount of data varies each
time it is processed. A good way of referring to data in the GUI is to name the
range. However, should the amount of data change, the name must be manually
updated. In this exercise you will use VBA to name a range and then rename it
dynamically to reflect any changes to the number records.
Refer to page 22 (Dynamic Range Handling) for topic details.
Exercise
1. Open your Excel workbook and navigate to the modRanges module.
2. Add a procedure called CountEmployees and declare the following variables.
Dim EmpCount As Integer, wbEmp As Workbook
3. Set the value of the wbEmp variable and activate the Employees worksheet.
Set wbEmp = Workbooks.Open("C:\EXVBA\Employees.xlsx")
wbEmp.Sheets("Employees").Activate
4. Name the range of contiguous cells with this code.
Range("A1").CurrentRegion.Name = "Employee_List"
5. Populate the EmpCount variable taking into account the header row.
EmpCount = Range("Employee_List").Rows.Count – 1
6. Activate the Employee Count worksheet and find the next blank row.
wbEmp.Sheets("Employee Count").Activate
Range("A1").Select
Loop
7. Write the date and employee count into the worksheet ensuring the columns
are the correct width.
ActiveCell.Value = Now
ActiveCell.Offset(0, 1).Value = EmpCount
ActiveCell.CurrentRegion.Columns.AutoFit
8. Close and save the Employees workbook.
Page 139
wbEmp.Close True
9. Open the Employees workbook. Check the named range and then add
yourself as an extra employee. Close the file and save the changes.
10. Step through and note the code renames the range and revises the employee
count.
11. Save your workbook and leave it open for the next lab.
Notes
www.qa.com
Page 140
Lab 2-4: Copying Data to a New Workbook
Scenario
This Lab will use variables to create and reference a new workbook and an
existing workbook. Data will be copied from the existing workbook to the new
workbook.
Refer to page 27 (Copying or Moving Range Data) for topic details.
Before we start, ensure your workbook is open, the VBE is displayed and
modRanges is the open module.
Exercise
1. Create a procedure called CustomerCopy and declare the following variables:
Dim CustBook As Workbook, CustList As Worksheet
Dim CustCopy As New Workbook
2. Bind the CustBook and CustList variables:

Set CustBook =_
Workbooks.Open("C:\EXVBA\Customers.xlsx")
Set CustList = CustBook.Sheets("Master List")
3. Activate the sheet and copy the contents:
CustList.Activate
Range("A1").CurrentRegion.Copy
4. Create the new workbook and paste in the data:
Set CustCopy = Workbooks.Add
ActiveSheet.Paste
5. Save and close the files, removing the variables from memory:
CustCopy.SaveAs "C:\EXVBA\Copy of Customers.xlsx"
CustCopy.Close False
CustBook.Close False
Set CustBook = Nothing
Set CustCopy = Nothing
Set CustList = Nothing
Page 141
Lab 2-5: Create Fixed and Multidimensional Arrays
Scenario
This Lesson Lab will take you through the process of creating and populating
Fixed arrays and Multidimensional arrays.
Refer to page 33 (Fixed Arrays) and to page 35 (Multidimensional Arrays
for topic details.
Exercise
1. Open your workbook and insert a Standard module. Name the module
modArrays.
2. Create a new procedure called FixedNewCustomer.
3. Declare a variable called FNewCust based on a new instance of the

cCustomer and assign the following values:
FNewCust.SalesPerson = "Bob Smith"
FNewCust.CoName = "Great Food Unlimited"
FNewCust.Country = "Chipville"
4. Declare a fixed array variable:
Dim CustDetails(2) As Variant
5. Assign values to the elements of the array:
CustDetails(0) = FNewCust.SalesPerson
CustDetails(1) = FNewCust.CoName
CustDetails(2) = FNewCust.Country
6. Step through the procedure using the Locals window to check the values
assigned to each element of the array.
7. Create a new procedure called MultiNewCustomer.
8. Declare and assign values to 2 variables:
Dim DNewCust As New cCustomer
Dim CustDetails(1, 2) As Variant
DNewCust.SalesPerson = "Bob Smith"
DNewCust.CoName = "Great Food Unlimited"
DNewCust.Country = "Chipville"
Page 142
9. Populate the first dimension of the array.

CustDetails(0, 0) = DNewCust. SalesPerson
CustDetails(0, 1) = DNewCust.CoName
CustDetails(0, 2) = DNewCust.Country
(Continues on next page)
10. Reassign values to the DNewCustVariables:

DNewCust.SalesPerson = "Jane Roe"
DNewCust.CoName = "Chips Forever"
DNewCust.Country = "Friestown"
11. Populate the next dimension in the array.

CustDetails(1, 0) = DNewCust. SalesPerson
CustDetails(1, 1) = DNewCust.CoName
CustDetails(1, 2) = DNewCust.Country
12. Set a breakpoint on the End Sub line and run the procedure to check the
values assigned to the 6 elements.
Notes
www.qa.com
Page 143
Lab 2-6: Create and ReDim a Dynamic Array
Scenario
This Lesson Lab will take you through the process of creating a structure to cycle
through data in a worksheet cell by cell, working with a dynamic array. Each
cell’s value will be written to a new element in the array maintaining existing data.
Refer to page 37 (Using the ReDim Statement and Preserve) for topic details.
Exercise
1. Open your workbook and create a new procedure called ListCities in the
modArrays module.
2. Declare the variables required.
Dim DataArray()
Dim xCell As Object
Dim Counter As Integer
3. Set the Counter and open the Workbook containing the data to work with.
Counter = 0
Workbooks.Open ("C:\EXVBA\Customers.xlsx")
4. Add the following code to dynamically name the range containing the cities.
Range("E2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Name = "Cities"
5. Add the following code to cycle through the data.
For Each xCell In Range("Cities")
ReDim Preserve DataArray(Counter)

DataArray(Counter) = xCell.Value
Counter = Counter + 1
Next xCell
6. Step through the routine and examine the contents of the variable.
7. Add a city of your choice and step through again to see the dynamic array at
work.
8. Close Customers.xls without saving the changes.
9. Save your workbook and leave it open for the next lab.
Page 144
Lab 2-7: Create, Populate and Use an Array
Scenario
This Lesson Lab will use a dynamic array to hold data to be used in a copy and
paste process. Open your workbook and display the module modArrays.
Refer to pages 40 (Using the Array Data) for topic details.
Exercise
1. Create a procedure called CustomerList and declare the following variables:
Dim CustList() As Variant
Dim CustRows As Integer, CustCols As Integer
2. Add this code to open the Customers.xlsx workbook and activate the cell
representing the start point of the data to copy.
Workbooks.Open "C:\EXVBA\Customers.xlsx"
Sheets("Master List").Activate
Range("A1").Select
3. Now populate the 2 integer variables which will be used on the Resize method
when pasting the array data.
4. Type this code to populate the array using a temporary name for the range
holding the data.
CustList = Range("Customers")
5. Close the Customers.xlsx file without saving changes.
Workbooks("Customers.xlsx").Close False
6. Create a destination sheet in your workbook and paste in the data from the
array.
Sheets.Add
7. Check that the copied data is correct and close your workbook and save the
changes.
Page 145
Lab 3-1: Create a Query to Open a CSV file as an Imported Item
Scenario
You use an in-house application that Excel cannot connect to. However, the in-
house application can export data to a CSV file. You need to import the file on a
regular basis into Excel using a macro to help automate the process. This
exercise will create a procedure to open a CSV file into Excel.
Before you start, ensure your workbook is open.
Exercise
1. Insert a new Standard Module and name it as modOtherApps
2. Create a procedure called ImportCSV.
3. Add the following code to create a new sheet to hold the import and then to
create the connection to the CSV file.
Sheets.Add
With ActiveSheet.QueryTables _
.Add(Connection:="TEXT;C:\EXVBA\Customers.csv", _
Destination:=Range(″$A$1″))
4. This code instructs Excel how to read and import the data from the CSV into
the new worksheet.
.Name = "Customers"
.FieldNames = True
.PreserveFormatting = True
.SaveData = True
.AdjustColumnWidth = True
.TextFileParseType = xlDelimited
.TextFileCommaDelimiter = True
5. Now we turn off the refresh so that Excel treats the process as a ‘copy’
process.
.Refresh BackgroundQuery:=False
6. Finally, complete the With…End With structure.

End With
Page 146
7. Run the procedure to confirm the data has been imported into your workbook.
Notes
www.qa.com
Page 147
Lab 3-2: Import Microsoft Access Data into Excel
Scenario
This Lesson Lab will take you through the process of importing data from an
Access Recordset directly into your workbook. It will require the libraries to be
available. The exercise creates a connection to an Access database, opens a
table (Recordset) virtually and copies the contents of the table to a new
worksheet.
Before you start ensure your workbook is open and navigate to the
modOtherApps module.
Exercise
1. Click onto Tools, References and load the Microsoft Access 14.0 library
together with the Microsoft ActiveX Data Objects 2.8 library.
2. In the module, create a procedure called ImportCustomerData and declare

the following variables.
Dim rs As New ADODB.Recordset
Dim MyWS As Worksheet, MyFld As Integer
Dim strConn As String, strDB As String
3. Add a worksheet and set the target cell for the data.
Sheets.Add
Set MyWS = ActiveSheet
Range("A1").Select
4. Now populate the variables which will be used to represent the connection to
the source database which is a 2007 Access Database
strDB = "C:\EXVBA\Customers.accdb"
strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" &
strDB
5. Open the Access recordset identifying the connection to use and the source
table.
With rs
.ActiveConnection = strConn
.Source = "tblCustomers"
.Open
Page 148
6. The code will use the CopyFromRecordSet method. This only copies the data
and does not include fields, so these are added with this code.
For MyFld = 1 To .Fields.Count
MyWS.Cells(1, MyFld).Value = .Fields(MyFld - 1).Name
Next MyFld
7. Add this code to copy the data into the sheet starting from the top-left corner
of the new worksheet.
Range("A1").Offset(1, 0).CopyFromRecordset rs
.Close
End With
8. Check the worksheet to confirm records have been imported.

9. Empty the rs variable from memory and then tidy the data in Excel.
Set rs = Nothing
ActiveCell.CurrentRegion.Rows.WrapText = False
Notes
www.qa.com
Page 149
Lab 3-3: Add Data Validation to a Worksheet
Scenario
In order to control data entry in a worksheet you are to use data validation. This
will be applied automatically whenever the worksheet in question is activated. In
this exercise you will attach code to the Activate event of a worksheet which
applies the data validation.
Before you start, ensure your own workbook is open.
Exercise
1. Add a new worksheet to your workbook and name it Paint. Then add the
following information: type Date of Sale in cell A1 and Colour in cell A2.
2. Now create a list of 3 or 4 colours in column J and then select the list. Name
the range as Colours.
3. Double-click the Paint worksheet in the VBE Project Explorer and display the
Activate Event Private Sub.
4. Add this code to select cell B1 and start the Validation code. Note the use of
.Delete. This is required to clear any validation, but does not clear any
existing data.
Range("B1").Select
.Delete
5. Now add the line of code that creates the validation together with its
arguments.
.Add Type:=xlValidateDate, AlertStyle:=xlValidAlertStop, _
Operator:=xlLessEqual, Formula1:="=TODAY()"
6. This code sets the properties, such as whether to display an Input or Error
message.
.IgnoreBlank = True
.InputTitle = "Transaction Date Required"
.ErrorTitle = "Invalid Date Entered"
.InputMessage = "Enter the date of the transaction.” _
& “This must not be in the future."
Page 150
.ErrorMessage = "The date entered is either in" _

& "the wrong format or is in the future. Try again."
.ShowInput = True .ShowError = True
End With
7. Now we repeat the coding process for the cell B2. This will use the list
created in the named range.
Range("B2").Select
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop,_
Operator:=xlBetween, Formula1:="=colours"
8. Now add this code to set the properties.

.IgnoreBlank = True
.InCellDropdown = True
.ShowInput = True
.ShowError = True
End With
9. Return to your worksheet and activate another worksheet. Click the Paint
worksheet and test the validation.
Notes
www.qa.com
Page 151
Lab 3-4: Combine, Filter and Sort Data
Scenario
This Lesson Lab will demonstrate working with several record sources which will
be combined and then filtered and sorted. We will then apply subtotals to the
resulting combined and sorted data set.
Before you start, close any open files and open your workbook. Insert a
Standard module and name it modDataProcs
Exercise
1. Create a procedure called CombineSales. Add the following code to declare
variables and open the workbook we will work with.
Dim QSheet As Integer
Dim NumSheets As Integer
NumSheets = 4
Workbooks.Open "C:\EXVBA\2011 Annual Sales.xlsx"
2. Add a new sheet to hold the summarised data.

Sheets.Add After:=Sheets(4)
ActiveSheet.Name = "Summary"
3. Set up the loop to iterate through each sheet containing the sales data.
For QSheet = 1 To 4
Sheets(QSheet).Activate
4. The first sheet contains the headers to use, so an If is introduced to the For
Next loop to write the complete contents of that sheet into the Summary
sheet.
If QSheet = 1 Then
Sheets(5).Activate
ActiveSheet.Paste
(Continues on the next page)
Page 152
5. If it is not the first sheet, we still want the data but do not need the header row.
This is a simple method using the ActiveCell object to delete header
information.
Else
Sheets(5).Activate
Range("A1").Select
Loop
ActiveSheet.Paste
ActiveCell.EntireRow.Delete
End If
Next
6. Clean the data and name the range for later use.
7. Now we will apply a filter to show just the UK data. The number in the code
represents the column that is to be filtered by.
Range("Customers").AutoFilter 2, "UK"
8. Step through the code to this point and view the result.
9. Remove the filter with this line of code.
Range("Customers").AutoFilter
10. Now we will apply the Sort Method to our data. This sorts by country in this
particular example.
Range("Customers").Sort "Country", xlAscending, _
Header:=xlYes
11. Run the code to this point in your procedure to check the data has been
sorted.
12. Having sorted the data, we will add the code to apply the Subtotal function at
each change in country. Type this code:
Range("Customers").Select
Selection.Subtotal GroupBy:=2, Function:=xlCount, _
SummaryBelowData:=True
Page 153
13. Now we will collapse the view of the data to give a higher level summary.
ActiveSheet.Outline.ShowLevels RowLevels:=2
14. Close the 2011 Annual Sales File if open without saving, and run your
procedure.
15. Save the changes to your workbook and leave it open for the next lab.
Notes
www.qa.com
Page 154
Lab 3-5: Using a Database Function in VBA
Scenario
In this exercise you will use a worksheet database function in your code. You
have imported the customers in a previous exercise (Lab 3-1) from the CSV file
and have been asked to create a macro that prompts the end user to specify a
country and then display how many customers are in that country. The code will
also report if there are no records that match the user input.
Refer to page 66 (Using Worksheet Database Functions) for topic details.
Before you start, ensure your workbook is open and you have navigated to the
worksheet containing the imported customer CSV data - this contains columns for
Company Name, Country and Salesperson only.
Exercise
1. Navigate to the modDataProcs Module and create a procedure called
CountCustomers. Declare the following variables.
Dim strCountry As String
Dim CustCount As Integer
2. Add the user input prompt and then write the results to the worksheet to
create the criteria for the function.
strCountry = InputBox("Enter the country to count")
3. Now add the code to setup the criteria.
Range("H1").Select
ActiveCell.Value = "Country"
ActiveCell.Offset(1).Value = strCountry
ActiveCell.CurrentRegion.Name = "Criteria"
4. Now add the code to name the data range dynamically.
Range("A1").Select
ActiveCell.CurrentRegion.Name = "Custs"
5. Add this code to invoke the DCountA worksheet function. The function will
use the two named ranges and counts the records in the first column that
match the criteria. Note the code is one line!
CustCount =
Application.WorksheetFunction.DCountA(Range("Custs"), 1, _
Page 155
Range("Criteria"))
6. Create the test to check whether any records match the criteria or not, and
then display the result to the end user.
If CustCount = 0 Then
MsgBox "Country invalid", vbCritical
Else
MsgBox "There are " & CustCount & " customers in " & _
strCountry, vbInformation
End If
7. Finally, remove the criteria from the worksheet.

Range("Criteria").ClearContents
Notes
www.qa.com
Page 156
Lab 3-6: Create an Excel Table Out of a Range
Scenario
This Lab will take you through the process of importing a simple range of data
into an Excel Table. The range will be converted and then a calculated row will
be appended to the data. You will convert the data held in the Copy Of
Customers.xlsx workbook created in lab 2-4.
Before you start, ensure your workbook is open and you have navigated to the
modDataProcs module.
Exercise
1. Create a procedure called CustTable and declare and set the following
variable.
Dim wb As Workbook
Set wb = Workbooks.Open("C:\EXVBA\Copy of Customers.xlsx")
2. Now add the code to ensure the correct worksheet is active and then
subsequently name the range of data to use as the source for the Table.
wb.Activate
wb.Sheets(1).Activate
Range("A1").CurrentRegion.Name = "Cust_List"
3. This next line of code will convert the range of existing data into an Excel
Table.
ActiveSheet.ListObjects _
.Add(xlSrcRange, Range("Cust_List"), , xlYes).Name = _
"Cust_Table"
4. The code needs to now select the entire Table to apply a style.
Range("Cust_Table[#All]").Select
5. The style is now applied.

ActiveSheet.ListObjects("Cust_Table").TableStyle = _
"TableStyleLight3"
6. Add this code to create the Totals Row and count how many records you
have. Note that the second line below is one line of VBA!
Page 157
ActiveSheet.ListObjects("Cust_Table").ShowTotals = True
ActiveSheet.ListObjects("Cust_Table") _
.ListColumns("Country").TotalsCalculation = _
xlTotalsCalculationCount
7. To preserve the original workbook save a copy with this code.

ActiveWorkbook.SaveCopyAs "C:\Customer Table.xlsx"
8. Check the Table and then close the Copy of Customers workbook without
saving changes.
Notes
www.qa.com
Page 158
Lab 4-1: Create and Apply Conditional Formatting
Scenario
You use a workbook to record the sales of certain food products and you have
been asked to improve the presentation of the actual numbers. You decide to
use conditional formatting to accomplish this. As this is a task you will repeat,
you will create a macro that will apply the formatting. This exercise will take you
through the process of applying conditional formatting to a range of cells.
Exercise
1. Insert a new Standard Module and name it as modPresentation.
2. Create a procedure called Conditions and add the following code to open the
workbook, activate the sheet and select the previously named range that
contains the sales figures.
Workbooks.Open "C:\EXVBA\Food Sales.xlsx"
Sheets("Food").Activate
Application.Goto Reference:="Numbers"
3. This code sets a conditional format on the range and identifies the order in
which any formatting should be applied with ‘priority’.
Selection.FormatConditions.AddIconSetCondition
Selection.FormatConditions(Selection.FormatConditions.Count). _
SetFirstPriority
4. Add this code to specify that the traffic light icons will be added to the range.
.ReverseOrder = False
.ShowIconOnly = False
.IconSet = ActiveWorkbook.IconSets(xl3TrafficLights2)
End With
Page 159
5. Now the code is added to adapt the existing 3 icons to only display 2.
.Value = 0
End With
.Value = 50
End With
6. Check the effect on the range.
7. Alter your code to use different values or operators to see the effect.
8. Close and save your workbook and the Food workbook.
Notes
www.qa.com
Page 160
Lab 4-2: Make a Chart and Copy it to PowerPoint
Scenario
You need to present the sales information from the Food Sales workbook in
PowerPoint. A line chart is required based on the sales data and this does not
need saving in the workbook. This Lab takes sample data from an Excel
workbook and creates a chart from the data. The chart is then copied into a new
PowerPoint presentation which is saved and closed.
Refer to page 82 before you start, close all files and open your workbook.
Navigate to the modPresentations module. Ensure the PowerPoint 14.0 Object
Library is loaded.
Exercise
1. Create a procedure called SalesChartToPowerPoint.
2. Declare the following variables:
Dim MyPPT As New PowerPoint.Application
Dim PPSales As PowerPoint.Presentation
Dim PPSlide As PowerPoint.Slide
Dim myEMF As PowerPoint.Shape
3. Add the following code to set variables and create the text on the title slide.
MyPPT.Visible = msoTrue
Set PPSales = MyPPT.Presentations.Add
Set PPSlide = PPSales.Slides.Add(1, ppLayoutTitle)
With PPSlide.Shapes.Placeholders(1)
.TextFrame.TextRange.Text = "Monthly Sales Update"
End With
Set PPSlide = PPSales.Slides.Add(2, ppLayoutBlank)
4. Now open the Sales.xlxs file and select the data for the chart.
Workbooks.Open ("C:\EXVBA\Food Sales.xlsx")
ActiveWorkbook.Sheets("Food").Activate
Page 161
5. This code creates the chart and copies it.

ActiveSheet.Shapes.AddChart
ActiveSheet.ChartObjects(1).Activate
ActiveChart.ChartType = xlLine
ActiveSheet.ChartObjects(1).Copy
6. Now we move focus to the PowerPoint file.

MyPPT.Activate
Set PPSales = MyPPT.ActivePresentation
PPSales.Slides(2).Select
PPSales.Slides(2).Shapes.PasteSpecial _
DataType:=ppPasteMetafilePicture
7. The pasted chart needs repositioning, so type this code.
Set myEMF = PPSales.Slides(2).Shapes(1)
myEMF.Select
'these are measurements from the top left of the slide in
'points
myEMF.Left = 175
myEMF.Top = 175
8. Add the code to save and close the presentation, clearing variables from
memory.
PPSales.SaveAs "C:\VBADataFiles\Sales.pptx"
PPSales.Close
Set PPSlide = Nothing
Set PPSales = Nothing
Set myEMF = Nothing
MyPPT.Quit
Set MyPPT = Nothing
9. Save and close your workbook.
10. Close the Food Sales file without saving changes.
11. Open the PowerPoint file to see the results.
Page 162
Lab 4-3: Pivot the Sales Data
Scenario
The Sales Figures for 2010 are held in a single sheet in an Excel workbook. In
order to do analysis on the figures for each product by country, a Pivot Table will
be required. The table will need to include two figures for each entry. The first
field will sum the values and the second will provide an average value. The Pivot
Table will need to be placed in a new workbook separate from the source data.
Exercise
1. Open your workbook and navigate to the modPresentation module.
2. Create a procedure call SalesPivot2010 and declare the following variable

and turn off the display alerts.
Dim SPBook As Workbook
3. Add the following code to open the workbook containing the sales data and
activate the worksheet holding the sales data.
Workbooks.Open Filename:="C:\EXVBA\2010 Sales.xlsx"
Application.Goto Reference:="Total_Sales"
4. We will now create and save the workbook which will hold the Pivot Table.
We will also set the variable value to be the new workbook.
Workbooks.Add
ActiveWorkbook.SaveAs "C:\EXVBA\Sales Analysis.xlsx"
Set SPBook = ActiveWorkbook
5. Name the sheet to hold the Pivot and delete the other two sheets that are not
required.
Sheets(1).Name = "SalesPivot"
Sheets(2).Delete
Sheets(2).Delete
Page 163
6. Add this code to create the Pivot Cache to hold the data and then create the
Pivot Table. Note that the text here represents a single line of VBA code.
ActiveWorkbook.PivotCaches.Create _
(SourceType:=xlDatabase, SourceData:= _
"C:\EXVBA\2010 Sales.xlsx!Total_Sales", _
Version:=xlPivotTableVersion12) _
.CreatePivotTable TableDestination:= _
"SalesPivot!R1C1", TableName:="Sales_Pivot", _
DefaultVersion:=xlPivotTableVersion12
7. Now add the following code to place the required fields in the Pivot and set
their properties as required.
Sheets("SalesPivot").Select
Cells(1, 1).Select
With ActiveSheet.PivotTables("Sales_Pivot") _
.PivotFields("Country")
.Orientation = xlPageField
.Position = 1
End With
.PivotFields("Company Name")
.Orientation = xlColumnField
.Position = 1
End With
.PivotFields("Salesperson")
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables("Sales_Pivot") _
.AddDataField ActiveSheet.PivotTables _
("Sales_Pivot").PivotFields("Extended Price"), _
"Sum of Extended Price", xlSum
With ActiveSheet.PivotTables("Sales_Pivot"). _
PivotFields("Sum of Extended Price")
.Caption = "Total Sales"
.Function = xlCount
End With
Page 164
ActiveSheet.PivotTables("Sales_Pivot") _
.AddDataField ActiveSheet.PivotTables _
("Sales_Pivot").PivotFields("Extended Price"), _
"Sum of Extended Price", xlSum
.PivotFields("Sum of Extended Price")
.Caption = "Average Sale Value"
.Function = xlAverage
.NumberFormat = "£#,##0.00"
End With
8. Close the workbooks, saving changes and remove the variable.

Workbooks("2010 Sales.xlsx").Close False
Set SPBook = Nothing
Application.DisplayAlerts = True
9. Save the changes to your workbook.

10. Run the procedure to check it works.
11. Close your workbook.
Notes
www.qa.com
Page 165
Lab 5-1: Work with Print and Page Settings
Scenario
You need to print the contents of the 2010 Sales workbook on a regular basis, so
the print area and page setup options will need resetting each time the workbook
is printed. A macro will be created that will set the print area, create headers and
footers as required, and ensure that the heading row is repeated at the top of
each page. This exercise will take you through the process of setting the print
requirements.
Exercise
1. Insert a new Standard Module and name it as modFileWork.
2. Create a procedure called PrintSalesReport.
3. Add the code to open the workbook and name the range of data.
Workbooks.Open "C:\EXVBA\2010 Sales.xlsx"
Range("A1").Select
ActiveCell.CurrentRegion.Name = "Sales"
4. Set the rows to repeat at the top of each printed page.
.PrintTitleRows = "$1:$1"
End with
5. Now above the End With Set the print area to be the named range.
.PrintArea = "Sales"
6. The header, footer and page setup options can now be added to the code
after the PrintArea. (Ensure all code is above End With)
.LeftHeader = "&D"
.RightHeader = "&A"
.CenterFooter = "&P of &N"
.PaperSize = xlPaperA4
.Orientation = xlLandscape
.FitToPagesWide = 1
.Zoom = 70
.Order = xlDownThenOver
Page 166
7. Set the output to be a print preview.

ActiveWindow.SelectedSheets.PrintPreview
8. Or alternatively, the output could be sent directly to a specified printer. Note
this will not work in this lab.
Application.ActivePrinter = "HP LJ4000"
ActiveSheet.PrintOut , , , True
9. Run the procedure and check the print preview.
10. Close the 2010 Sales file without saving changes.
11. Save the changes to your workbook and leave open for the next lab.
Notes
www.qa.com
Page 167
Lab 5-2: Hide and Unhide Sheets
Scenario
You have a workbook that contains information on a worksheet that should be
hidden whenever the workbook is opened. You will need to update information
on the hidden worksheet from time to time. Hiding the worksheet will be triggered
by the Open event of the workbook. This exercise will create the procedure to
hide and then subsequently unhide worksheets.
Exercise
1. Open the Employees.xlsm workbook and display the module for This
Workbook.
2. In the Open event procedure, add this line of code.
Sheets("Employee Count").Visible = False
3. Close the workbook and save changes. Reopen the workbook to check the
sheet has been hidden. Close the workbook again saving changes.
4. Navigate to the modFileWork module in your workbook.
5. Create a procedure called ShowHiddenSheets and add the following variable:
Dim x
6. Open the Employees workbook and display a message showing the number
of worksheets.
Workbooks.Open "C:\EXVBA\Employees.xslm"
Workbooks("Employees.xlsm").Activate
MsgBox ActiveWorkbook.Sheets.Count
7. Now add a For… Next loop to unhide any hidden worksheets.
For x = 1 To ActiveWorkbook.Sheets.Count
If Sheets(x).Visible = False Then
Sheets(x).Visible = True
End If
Next x
8. Run the procedure to display the hidden worksheet.
Page 168
9. Close both workbooks saving the changes.
Notes
www.qa.com
Page 169
Lab 5-3: Share a Workbook and Copy Change History to a New

Workbook
Scenario
This Lab will demonstrate the process to share a workbook using VBA. It will
also use VBA to copy the change history from a shared workbook to another
workbook.
Exercise
1. Navigate to the modFileWork module and create a new procedure called
ShareSales.
2. Turn off application alerts and open the Shared Sales workbook.
Workbooks.Open "C:\EXVBA\Shared Sales.xlsx"
Workbooks("Shared Sales.xlsx").Activate
3. Here we use an IF to check whether the workbook is already shared. If it is
not then Sharing with default settings is applied. The workbook is then saved
by the code.
If Not ActiveWorkbook.MultiUserEditing Then
ActiveWorkbook.SaveAs Filename:=ActiveWorkbook _
.FullName, AccessMode:=xlShared
End If
4. Finally, turn on the alerts.
5. Run the procedure and check the workbook is shared. Make some changes
to the data and save changes. Leave the workbook open.
6. Now create a second procedure called Audit in your workbook.
7. Add this code to turn off alerts and to copy any changes to the History
worksheet.
With ActiveWorkbook
.HighlightChangesOptions When:=xlAllChanges
Page 170
(Continues on next page)

.HighlightChangesOnScreen = False
End With
8. Select the History worksheet and copy it to a new workbook.

Sheets("History").Select
Sheets("History").Copy
9. Save and close the new book, overwriting any previous copy.
ActiveWorkbook.SaveAs "C:EXVBA\History.xlsx"
ActiveWorkbook.Close
10. Close the Shared Sales workbook without saving changes.
Workbooks("Shared Sales.xlsx").Close False
11. Run the second procedure and then open the new History workbook to check
the contents.
12. Close open workbooks saving changes.
Notes
www.qa.com
Page 171
Lab 6-1: Add the Scripting Library and Create a Folder
Scenario
This Lesson Lab will take you through the process of adding the Microsoft
Scripting Runtime Library to use FileSystemObject to create a folder using VBA.
Refer to page 115 (Create a Folder) for topic details.
Exercise
1. Open your workbook and add the Microsoft Scripting Runtime library to
References.
2. Insert a Standard module in your workbook and name it modSystemWork.
3. Create a procedure called MakeAuditFolder.
4. Declare and assign a variable to refer to the FileSystemObject.
Dim Fso As Object
Set Fso = CreateObject("scripting.filesystemobject")
5. Create the folder.
Fso.CreateFolder ("C:\EXVBA\Audit Files")
6. Run the procedure and check the folder has been created.
7. Leave the workbook open and save the changes.
Notes
www.qa.com
Page 172
Lab 6-2: Open a Folder and Write a File List to an Array Variable
Scenario
This Lesson Lab will use a loop in code to populate an array with the files
contained in a folder. The resulting array will then be written to a sheet in your
workbook.
Refer to page 118 (Viewing Files in a Folder) for topic details.
Using Windows Explorer, copy the Shared Sales.xlsx, 2010 Sales.xlsx and
Annual Sales files from the VBADataFiles folder into the new Audit Files folder.
Open your workbook.
Exercise
1. Create a procedure in the modSystemWork module called GetFileList and
declare the following variables.
Dim Fso, MyFolder, MyFile, Counter
Dim FileList() As Variant
2. Set the FileSystemObject and Folder variables.
Set MyFolder = Fso.GetFolder("C:\EXVBA\Audit Files")
Counter = 0
For Each MyFile In MyFolder.Files
MsgBox MyFile
ReDim Preserve FileList(Counter)
FileList(Counter) = MyFile
Counter = Counter + 1
Next
MsgBox MyFolder.Files.Count
3. Run the procedure.
4. Save changes to your workbook and leave it open for the next lab.
Page 173
Lab 6-3: Copy a File to a Folder and Rename It
Scenario
This Lesson Lab will use the folder created in lab 6-1. A file will be copied from
its current location to the second folder. It will then be renamed in the second
folder with today’s date appended to the filename. Open your workbook.
Refer to page 118 (Copying a File) for topic details.
Exercise
1. Create a procedure in the modSystemWork module called BackupRename
2. Declare and set a File System Object.
3. Use the CopyFile Method to copy the Customers.xlsx file from the EXVBA
folder to the Audit Files folder.
4. Run the procedure and check the file has been copied.
5. Add the MoveFile Method to the procedure to rename the file in the Audit
Files folder. The new filename will consist of “Customers” & today’s date &
“xlsx”.
6. Save the changes to your workbook.
Notes
www.qa.com
Page 174
Lab 6-4: Use a File’s Attributes and Properties
Scenario
This Lesson Lab will use two procedures. The first will temporarily remove a
Read Only attribute from a file to allow editing. The attribute then be reset in the
second procedure.
Exercise
1. Create a procedure in your workbook called NotReadOnly in the
modSystemWork module.
2. Set and declare 3 variables as follows:
Dim Fso, MyFile, Attrib
Set MyFile = Fso.GetFile("C:\EXVBA\Customers Read Only.xlsx")
3. Assign a value to the Attrib variable.
Attrib = MyFile.Attributes
4. Now run the procedure and check the value returned to Attrib.
5. Set the value of Attrib to 0 in the code.
6. Add this line to turn off the Read Only attribute.
MyFile.Attributes = Attrib
7. Create another procedure called SetReadOnly in the same module.

8. Copy and paste the code from the NotReadOnly procedure and amend the
code so the Read Only attribute is reset.
9. Open the read-only file and change some data. Click Save and see the
result. Close the file without saving any changes.
10. Run the first procedure and then reopen the workbook. Add yourself as a
customer and click Save.
11. Close the workbook and run the second procedure. Check the file properties
in Windows Explorer.
Page 175
Lab 6-5: Read the Contents of a Text File
Scenario
This Lesson Lab will use a text file and display each line of its contents on a
message box. The information from the text file could be written to variables or
an array for testing.
Exercise
1. Create a procedure in your workbook called ReadTextFile in the
modSystemWork module.
2. Specify that the code will action on a Read Only basis using Const and then
set and declare 3 variables as follows:
Const ForReading = 1
Dim objfso As Object, objtextfile As Object
Dim strTextline As String
3. Specify values for the first two variables identifying which library to use and
which text file will be opened virtually.
Set objfso = CreateObject("Scripting.FileSystemObject")
Set objtextfile = objfso.OpenTextFile _
("C:\EXVBA\Customers.txt", ForReading)
4. Now add the code which will loop through the file and display its contents line
by line.
Do Until objtextfile.AtEndOfStream
strTextline = objtextfile.ReadLine
MsgBox strTextline
Loop
5. Run the procedure to display the resulting message boxes. Open the text file
manually and add yourself in as a new line under the existing data. Close and
save the file.
6. Rerun the procedure.
7. Close your workbook and save changes.
Page 176
Appendices
Method Name Method Description
Activate Activates a single cell, which must be inside the current selection. To
select a range of cells, use the Select method.
AddComment Adds a comment to the range.
ApplyNames Applies names to the cells in the specified range.
AutoFill Performs an autofill on the cells in the specified range.
AutoFilter Filters a list using the AutoFilter.
AutoFit Changes the width of the columns in the range or the height of the rows in
the range to achieve the best fit.
AutoOutline Automatically creates an outline for the specified range. If the range is a
single cell, Microsoft Excel creates an outline for the entire sheet. The new
outline replaces any existing outline.
BorderAround Adds a border to a range and sets the Color, LineStyle, and Weight
properties for the new border. Variant.
CheckSpelling Checks the spelling of an object.
Clear Clears the entire object.
ClearComments Clears all cell comments from the specified range.
ClearContents Clears the formulas from the range.
ClearFormats Clears the formatting of the object.
ClearHyperlinks Removes all hyperlinks from the specified range.
ClearNotes Clears notes and sound notes from all the cells in the specified range.
Consolidate Consolidates data from multiple ranges on multiple worksheets into a

single range on a single worksheet. Variant.
Copy Copies the range to the specified range or to the Clipboard.
Page 177
CopyFromRecordset Copies the contents of an ADO or DAO Recordset object onto a

worksheet, beginning at the upper-left corner of the specified range.
If the Recordset object contains fields with OLE objects in them,
this method fails.
CopyPicture Copies the selected object to the Clipboard as a picture. Variant.
CreateNames Creates names in the specified range, based on text labels in the sheet.
Cut Cuts the object to the Clipboard or pastes it into a specified destination.
Delete Deletes the object.
FillDown Fills down from the top cell or cells in the specified range to the bottom of
the range. The contents and formatting of the cell or cells in the top row of
a range are copied into the rest of the rows in the range.
FillLeft Fills left from the rightmost cell or cells in the specified range. The
contents and formatting of the cell or cells in the rightmost column of a
range are copied into the rest of the columns in the range.
FillRight Fills right from the leftmost cell or cells in the specified range. The
contents and formatting of the cell or cells in the leftmost column of a
range are copied into the rest of the columns in the range.
FillUp Fills up from the bottom cell or cells in the specified range to the top of the
range. The contents and formatting of the cell or cells in the bottom row of
a range are copied into the rest of the rows in the range.
Find Finds specific information in a range.
FindNext Continues a search that was begun with the Find method. Finds the next
cell that matches those same conditions and returns a Range object that
represents that cell. Doesn't affect the selection or the active cell.
FindPrevious Continues a search that was begun with the Find method. Finds the
previous cell that matches those same conditions and returns a Range
object that represents that cell. Doesn't affect the selection or the active
cell.
Insert Inserts a cell or a range of cells into the worksheet or macro sheet and
shifts other cells away to make space.
Justify Rearranges the text in a range so that it fills the range evenly.
Merge Creates a merged cell from the specified Range object.
Page 178
PasteSpecial Pastes a Range from the Clipboard into the specified range.
PrintOut Prints the object.
PrintPreview Shows a preview of the object as it would look when printed.
RemoveDuplicates Removes duplicate values from a range of values.
RowDifferences Returns a Range object that represents all the cells whose contents are
different from those of the comparison cell in each row.
Select Selects the object.
Sort Sorts a range of values.
SpecialCells Returns a Range object that represents all the cells that match the
specified type and value.
Table Creates a data table based on input values and formulas that you define
on a worksheet.
TextToColumns Parses a column of cells that contain text into several columns.
UnMerge Separates a merged area into individual cells.
Table 21 - Range Object Methods
Page 179
xlBetween 1 Between. Can be used only if two formulas are provided.
xlNotBetween 2 Not between. Can be used only if two formulas are provided.
xlEqual 3 Equal.
xlNotEqual 4 Not equal.
xlGreater 5 Greater than.
xlLess 6 Less than.
xlGreaterEqual 7 Greater than or equal to.
xlLessEqual 8 Less than or equal to.
Table 22 - Excel Comparison Operators
Page 180
xl3Arrows 1 3 Arrows
xl3ArrowsGray 2 3 Arrows Grey
xl3Flags 3 3 Flags
xl3TrafficLights1 4 3 Traffic Lights 1
xl3TrafficLights2 5 3 Traffic Lights 2
xl3Signs 6 3 Signs
xl3Symbols 7 3 Symbols
xl4RedToBlack 10 4 Red To Black
xl4CRV 11 4 CRV ( )
xl4TrafficLights 12 4 Traffic Lights
xl5CRV 15 5 CRV ( )
xl5Quarters 16 5 Quarters
Table 23 - Conditional Format Icon Sets
Page 181
xlConditionValueNone -1 No conditional value.
xlConditionValueNumber 0 Number is used.
xlConditionValueLowestValue 1 Lowest value from the list of values.
xlConditionValueHighestValue 2 Highest value from the list of values.
xlConditionValuePercent 3 Percentage is used.
xlConditionValueFormula 4 Formula is used.
xlConditionValuePercentile 5 Percentile is used.
Table 24 - Excel Condition Value Types
Notes
www.qa.com
Page 182
Sample Answers to Review Questions
Module 1: Review Questions

Review Questions:
1. Name an Event for an Excel workbook
Answers may vary but can include Open, Activate, Close, Print.
2. How can you display the Object Browser?

Click the icon on the toolbar or go to View, Object Browser or press F2.
3. What is a Reference Library?

A set of objects and associate methods etc, which are usually application
specific. Loading a library exposes the Objects to the VBE.
4. Describe a benefit of Early Binding?

Answers may vary, but can include faster coding through the availability of
IntelliSense and Methods or Properties.
5. Where must a User Defined Type be declared?

In the declaration of a Standard Module.
6. How do you create and name a Class?

Insert a Class Module. The Class takes the name you give the Module.
Notes
www.qa.com
Page 183

Review Questions:
1. What is Option Base?
Sets the start point for Array variables in a Module.
2. Give an example where you might use Special Cells

Answers can vary but might include working with visible cells, cells with
formulas, formatting or blanks in a range.
3. How do you declare a two dimensional Array?

Example answer:
Dim Sales(12,30) As Double
4. What does the Preserve statement do?

Retains data held in Array dimensions when combined with ReDim.
5. Describe the CurrentRegion property.

Answers can vary, but should include references to contiguous cells relevant
to the current active cell.
Declares a dynamic Array variable which may contain mixed data types and
then populates the variable with the contents of cells A1 through to A10.
Notes
www.qa.com
Page 184

Review Questions:
1. What is the purpose of the TypeName function?
To identify the actual data type in a value.
2. What is a Connection?
(Example Answer) A defined link to a data source which can be utilised by an
application to import data. This may be coded into the VBA or called by the
code.
3. What criteria could you use to find blank records when applying a filter?
“=”
4. What code needs to be used to enable the use of an Excel function in VBA?
Application.WorksheetFunction
5. An Excel Table is a member of which collection?

ListObjects
6. What does the run time error “Type mismatch” mean?
A variable is expecting to contain a particular data type, but the value supplied
is a different data type.
7. What data type might the number 32768.37 be stored as in a variable?

Single, Double, Decimal, Variant.
Notes
www.qa.com
Page 185

Review Questions:
1. What is a Pivot Cache?
The store of information used to create the actual table or chart. It can
contain the source of the data, connection and where to place the resulting
object.
2. How can you reference a chart in VBA if it is on a separate sheet?

By its string name which is by default the name of the worksheet.
3. Give three examples of FormatConditions

Answers can vary, but may include data bars, icon sets, colour scales,
top/bottom rules, unique values or custom rules.
4. Where can the data used to create a Pivot Table be stored?

In the same workbook, a different workbook, external data source.
5. What does this line of code do?
Adds a default chart type to the active sheet with the dimensions specified.
Notes
www.qa.com
Page 186

Review Questions:
1. Give an example of the code to use to hide a worksheet
Answers may vary but may include references to setting the Visible property
of a sheet to False.
2. How do you set the print area using VBA?

By referring to the .PageSetup.PrintArea property against a range.
Turns off workbook sharing.
4. If you store passwords to unprotect workbooks or ranges in the code, how do

you protect the code from being accessed by another user?
Protect the Project with a password to prevent unauthorised access.
5. What is the resultant effect of this code?
With ActiveWindow
.SplitColumn = 0
.SplitRow = 1
End With
Freezes the top row of the current worksheet.
Notes
www.qa.com
Page 187

Review Questions:
1. Which Reference Library needs to be loaded to work with files and folders?
Windows Script Host Object Model
2. What would this code do to the Q3_Sales.xlsx file?
Fso.CopyFile "C:\Q3_Sales.xlsx", "C:\Audit\Old_Sales.xlsx"
Move the file into the Audit folder and rename it.
3. Name 3 file Attributes

Answers can include System, Read Only, Hidden, Normal, Compressed,
Archive status.
4. Give an example where you might need to work with an Attribute in VBA.
Answers will vary. An example answer might be a file is set to Read Only and
it needs editing. The Attribute can be temporarily changed during run time,
the file edited and then reapplied using VBA. What would this code do to the
Q3_Sales.xlsx file?
Fso.CopyFile "C:\Q3_Sales.xlsx", "C:\Audit\Old_Sales.xlsx"
5. Name 3 actions you can carry out on a folder with VBA

Answers can include create, delete, search, rename, move, copy.
Notes
www.qa.com
Page 188
Notes
www.qa.com
Page 189

VBA Training

Uploaded by

Copyright:

Available Formats

VBA Training

Uploaded by

Document Information

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

VBA Training

Uploaded by

Copyright:

Available Formats

QAEX10VBAA v1.

3 Microsoft VBA 2010 – Excel Advanced

Copyright © 2013 QA Limited

All Rights Reserved

Topic B - Controlling Visual Elements of a Workbook ...................................... 98

Lab 4-2: Make a Chart and Copy it to PowerPoint ......................................... 161

Table 1 – UDT Scope .......................................................................................... 14

Figure 1 - Object Browser ..................................................................................... 6

How to use this workbook

IMPORTANT IDEA OR HELPFUL HINT

Use of Shortcut Keys

{SHIFT} Use of Braces (Squiggly brackets) specifies keys that should be

[INSERT] Use of Parameter (Square Brackets) specifies a key that should be

Module 1 - Excel 2010 Object Hierarchies and the

Topic A - The Excel Component Object Model

Understanding where Objects are stored or referenced, the hierarchical structure

The Type of Object

How the Object is to be Referenced by Your Code

Whether the Object is Visible to Your Code

For examples, imagine your application is a supermarket. A supermarket will

Excel itself can be seen as a part of a Collection called Office 2010. It is an

For further information on the members of a Collection, use a combination of the

Topic B - Using Methods, Events and Constants

A Method is an action that code may invoke on an Object. The effect of a

Topic C - Using the Object Browser

Figure 1 - Object Browser

Topic D - Object Variables and Binding

Information: Object Data Type

Referencing an Existing Object

Creating an Object (Instantiating)

Instantiating Objects using Object variables is not limited to creating application

the Common Object Model or COM architecture to create a connection to the

Late Binding v Early Binding

 Explicitly declaring the Object variable as a type at design time, is known as

In addition to the code design enhancements, another advantage offered by early

Dim MyRange As Excel.Range

Code for steps 1 to 5 might look similar to this:

Code for step 6 might look like this:

If ActiveCell.Value <> "" Then

Possible code for steps 8 and 9:

Topic E - Creating User Defined Types and Classes

User Defined Types (UDTs)

Figure 2 - New Class

Creating a User Defined Type

Using UDTs when Declaring Variables

DoSales.SalesBook = Workbooks.Open("Monthly Sales.xlsx")

Module/Procedure Create a UDT as Declare variable based on the UDT as

Standard Module Public or Private Public or Private

Class Module Public or Private Public or Private

Form Module Private only Private only

Procedure Cannot be created Local scope only

Table 1 – UDT Scope

It is also possible to pass procedure arguments using your UDTs.

Topic F - Creating a Class: What is a Class?

Why Use a Class?

Create and Add the Class to the Project

Property Get Statement This returns the value of a property.

Used for object properties in the same way as object

Property Let Statement Sets a new value of a property.