Excel VBA For Complete Beginners A Home A - Ken Carney

EXCEL VBA
for
Complete Beginners
Ken Carney for Home and Learn

2020
Excel VBA – Course Contents
Excel VBA - Introduction
Getting Started
The Visual Basic Development Environment
Watch a Macro Being Recorded
Excel Dot Notation
Methods
The Range Property
The Worksheets Object
The VBA Developer Toolbar
The ActiveCell
Offset
The Resize Property
VBA Programming Variables
Variable Names
Variables - Practice
Using variables to add, multiply, divide, and subtract
Subtraction
Multiplication
Division
Other Numerical Variable Types
Variable Types and Text
Conditional Logic
Else
ElseIf
Conditional Operators
Logical Operators
Boolean Values
Logical Not
Logical And
Logical Or
Nested If
VBA Practice 1
ActiveCell Referencing
Changing the Background Colour of a Cell
Cell Content Alignment
VBA Practice 2
Select Case
VBA Practice 3
With ... End With
Working with Strings of Text in Excel VBA
Changing Case
Trim and Len
Space
Replace
StrReverse
InStr, InStrRev
Left, Right
Mid
Programming Loops in Excel VBA
For Loops
The Cells Property
The For Each Loop
The Do While Loop
Do Until Loop
Excel VBA and Arrays
Arrays and Loops
Multidimensional Arrays
Arrays and the Split function
Join
Subs and Functions
Subroutines
Passing Values to a Sub
Exit Sub
The Message Box
Functions
Using Excel's own WorkSheet Function in VBA
The Set Keyword in Excel VBA
VBA and Text Files
Writing to a text file
User Forms
Adding Controls to a User Form
Adding Code to a User Form Button
Add a Button to the Excel Ribbon
Launch a Form from a Button on a Spreadsheet
Launch a Form from the Excel Task Bar in Excel 2007
Adding New Tabs to the Excel Ribbon (versions 2010 and later)
Adding Tabs to the Excel Ribbon
An Excel Picture Viewer
Spreadsheet Image Information
Designing the User Form
Adding Labels to a Form
Adding Textboxes to a Form
Adding Option Buttons to a Form
Adding Command buttons to a Form
The Add New Photo Tab
Add an Open File Dialogue Box to a Form
The Form Initialize Event
Loading Image Information
Load Image Information
Get the Data for the Textboxes
Option Buttons
Load an Image into an Image Box
The Next Photo button
The Previous Photo Button
The Add New Photo tab
64 Bit Open File Dialogue Box
Get the Image Name
The Save New Details button
TextBox Error Check
ComboBox Error Check
Option Button Error Checking
Transferring Data to a Spreadsheet
Copy an Image to a Folder
Excel VBA Charts
Excel VBA and Embedded Charts
Formatting the Chart
Deleting Charts
Adding Charts to an Excel VBA User Form
Loading the Combo Box
Excel Chart Images
A TreeView Project
Design the User Form
Adding Nodes to a TreeView
Parent Nodes
Child Nodes
The Node Click Event
Appendix - Chart Type Constants
Column Charts
Bar Charts
Line Charts
Pie Charts
XY Scatter Chart Constants
Area Charts and Other Chart Constants
Excel VBA - Introduction
Hello and welcome to the Home and Learn course for Excel VBA. This is a
free beginners programming course, and assumes that you have no
experience with coding. In this course you'll learn how to create your own
macros, so that you can bring your Excel skills to the next level, and put
yourself ahead of the competition.
What is Excel VBA?
VBA stands for Visual Basic for Applications. It's a programming language
that enables you to control just about everything in Excel. You'll learn how
to create Macros that can be run from things like a button on a spreadsheet,
the Excel Ribbon - in fact, lots of places. Learning Excel VBA will enable
you to do a lot more with the software than you can via the normal
spreadsheet view.
Course Requisites
You don't need to be an Excel Guru to take this course, just average Excel
skills will do. If you're new to Excel, then this is not the place to start. We
have a full and free Excel course here on our main site:
http://www.homeandlearn.co.uk. (Free Beginners Excel Course.)
What software do I need?
You need Excel 2007 or greater to do this course. You can probably follow
along if you have a version of Excel earlier than this, but the screenshots are
all from later versions.
Do I need any programming skills?
No. We take you through the very basics of programming, so don't worry if
you have no skills at all in this area.
Let's make a start.

GETTING STARTED
The first thing you need to do is to add the Developer ribbon tab at the top
of Excel.
If you have Excel 2007, click the round Office button, then click Excel
Options at the bottom. When you get the Options dialogue box up, click on
Popular from the left in 2007. In the section labelled "Top options for
working with Excel" check the box for "Show Developer tab in the
Ribbon":
In Excel 2010 to 2016 click the File menu then select Options . From the
dialogue box, click on Customize Ribbon on the left side. From the right
hand side you'll then see an area called "Customize the Ribbon". Under
"Main Tabs" check the box for Developer :
When you have the developer toolbar, you'll see the following tab in the
Ribbon (this is from Excel 2013, so you may not have all the items below):
In order to run macros without any annoying security warnings, click on

Macro Security , on the Code panel. Select the Option for Enable all
macros . Then make sure that "Trust access to the VBA object model" is
checked:
NOTE: If you're worried about macro security then you can always bring
this box up again and disable the macros before you exit Excel.
Now that you have the developer tab added to the Ribbon you can do things
like bring up the Visual Basic Editor, run macros, record macros, and insert
form objects onto your spreadsheets. First, let's have a look at the Visual
Basic Development Environment. This is, after all, where you'll be writing
all your code.
The Visual Basic Development Environment

There are a few ways to open up the Visual Basic Editor. From the
Developer tab, on the Code panel, you can click the Visual Basic button.
On the Controls panel of the Developer tab, you can click View Code . A
keyboard shortcut is to hold down the left ALT key on your keyboard. Keep
it held down and press the F11 key.
Whichever method you choose you should see a screen like this one:
It's a little daunting at first glance. But the more your work with the editor
the less daunting it will become.
There are a few things to notice about the editor. Firstly, there's an area on
the left called Project - VBA Project . In the white area are all the object
that your project currently has (we'll get into what object are a little later).
By default, there are three Sheet objects: Sheet1, Sheet2, and Sheet3 (Excel
2013 and 2016 only have 1 worksheet by default, so you only see Sheet1 in
this version). These obviously represent the Excel worksheets. The fourth
object is called ThisWorkbook and refers to the workbook where all your
current macros are. There's also individual items for each worksheet.
One other object not visible above is called the Personal Macro
Workbook . This workbook will appear as soon as you record a macro. The
Personal Workbook can be used to store macros that you use quite
frequently. They will be available whenever you close down Excel and open
up a new workbook. The other sheet objects are specific to a particular
workbook.
The big grey area is where you'll write your code. The reason it's grey
above is because no coding window has been opened yet. To open up a
coding screen double click an object on your left. Double click the Sheet1
object and you'll see this:
All the macros you'll write need to be typed here, on this white screen.
Because we double-clicked Sheet1 the code written here will only affect the
worksheet called Sheet1. Likewise, if you add code to Sheet2 it will be run
in the worksheet called Sheet2.
To give you a quick idea of what VBA code looks like, add the following in
the white area for Sheet1:
Sub HelloWord()
MsgBox "Hello VBA World!"
End Sub
Your coding window will then look like this:
The Sub at the start of our code is short for Subroutine. A Subroutine is just
a chunk of code that does a particular job. It has a corresponding End Sub
to show where the code chunk ends. (What you should have noticed,
though, is that as soon as you typed the first line and hit the enter key VBA
adds the End Sub for itself.)
A Sub needs a name followed by a pair of round brackets. There is a space
between Sub and the name. In between Sub and End Sub is where you add
the code for your macro. You can have practically anything you like as a
name for your Subroutines. But try to keep them related to what the code
will be doing. If we'd give our Sub the name Colin, for example, it would
be a bit odd, to say the least. The Name HelloWorld describes what the code
will do, however.
Subroutines can't contain spaces, though. But you can type an underscore.
So this is OK;
Sub Hello_World()
But this is not:
Sub Hello World()
Take note of the following when coming up with a name for your
Subroutines:
They can't start with a number, only alphabetical characters (you
can have numbers elsewhere in your names, though)
You can't have full stops/periods in them
You can't use any of the following characters anywhere in your
names: #, $, %, &, !
Once you've added the code, it's time to run it. To run your code, have a
look at the toolbar at the top of the editor. Locate and click the green
triangle:
Another way to run your code is to click the Run menu. From the Run
menu, select Run Sub/User Form .
A shortcut to run your code is to press the F5 key on your keyboard.

What you should see when the code is run is this:
The MsgBox stands for Message Box. In between double quotes, we type
what we wanted the message box to display.
Watch a Macro Being Recorded

You can watch as Excel records a macro. That way you'll get a feel for how
things work. However, Excel tends to overdo it when it adds code, so don't
think this is the type of code you'll have to write. Your own code will be
much shorter!
First, make sure you have Excel and the coding window open side by side.
Like this:
If you have Windows 7 or greater you can easily have two programmes
open side by side by doing the following:
Click on Excel to activate it

Hold down the Windows key on your keyboard (the Windows key
is between the left CTRL and left ALT key)
Keep it held down and press the left arrow key
Click on the coding window to activate it
Hold down the Windows key on your keyboard
Keep it held down and press the right arrow key
Both Excel and the Editor should now be side by side, as above
If the above doesn't work for you, then just position the two windows as
best you can.
But we can now record a macro and watch as Excel does its thing.
We'll record a simple macro that right aligns text in a cell. So enter the letter
A to F in the A column of Excel:
On the Developer tab, locate the Code panel and click Record Macro :
The Record Macro dialogue box should appear:
For the Macro Name, type TestMacro . Click the dropdown box for "Store
macro in" and select Personal Macro Workbook :
Click OK.
To record the Macro, select the cells A1 to A6. Click on the Home tab on
the Excel ribbon. Locate the Alignment panel and click the right-align
option:
Switch back to the Developer tab. On the Coding panel, click Stop
Recording :
Now have a look at the coding window. If you haven't yet recorded a macro
you should see a new item appear in the object window, just below your
sheet objects:
The new object is called Personal.XLSB . Click the plus symbol to expand
the entry and you'll see some yellow folders. Expand these as well:
Double click Module1 and you'll see the code for the Macro you've just
recorded:
(Don't worry if your coding window doesn't have the Option Explicit at the
top. We'll explain about this in the variables chapter, later.)
Excel has created a Sub of its own. You don't need to understand all the
code at this early stage. But you can probably pick out a few things that
make sense. The cells A1 to A6 appear on the first line (the green lines are
comments and will be ignored). This happened when you selected them.
With the selection, Excel has added some formatting. The one that makes
sense is xlRight for the horizontal alignment.
All those other lines highlight the fact that Excel tends to add lots of code
unnecessarily. When you get some experience writing VBA code you'll
spot lots of ways you can reduce the code Excel writes. For example, the
whole of the macro above could have been replaced with just one line. This
Range("A1:A6").HorizontalAlignment = xlRight
We'll explore Ranges in the next section. But this single line of code is more
readable – it reduces Excel's code to just the bare minimum.
Now that you've recorded a macro, click the File menu in the VB Editor.
From the File menu, select Save Personal.XLSB .
Let's try another macro. With this one, we'll just select the text and make it
bold. Keep Excel and the Coding Editor open side by side.
Return to Excel. Click on the Developer tab again, and click Record
Macro on the Code panel. You should see the Record Macro dialogue box
appear. Change the name from Macro2 to MakeBold . Leave "Store macro
in" on Personal Macro Workbook. Click OK.
Highlight cells A1 to A6 again. Click on the Home tab in the Ribbon. From
the Font panel, click the Bold icon.
Keep an eye on the coding window when you do all this. You should see
Excel automatically adding the code for you Sub. Something like this:
Range("A1:A6").Select
Selection.Font.Bold = True
Stop the recording by clicking on the Developer tab, then the Code panel.
Exercise
Record a new macro to make the text in cells A1 to A6 a different font, a
different font size, and a different colour.
When you complete the exercise above, you should find that Excel adds
quite a lot of code for what seems like simple job. However, you should
bear in mind that most of what Excel adds is not needed – your own VBA
code will be a lot shorter!
Before we move on, it's best to delete all these macros. To do that, click on
the Developer tab. From the Code panel, click on Macros :
You should see the following Macros dialogue box appear:
If you get an error message about Excel not being able to delete macros
while the Personal workbook is hidden, click Cancel on the dialogue box.
From Excel, click on the View ribbon. Locate the Window panel and click
Unhide :
From the Macro dialogue box, though, select a macro on the left and the
click the Delete button. Do the same for all the macros on the list. Save
your Personal workbox in the editor again (File > Save ).
We’re going to take a look at something called the Range object. Before we
get onto Ranges, though, let's take a closer look at how the Excel dot
notation works.
Excel Dot Notation

Excel VBA uses dot notation to separate the various things you can access
and manipulate with the programming language. Dot notation is
hierarchical, and usually starts with an object. (In Excel, an object is the
thing you're trying to manipulate, such as a worksheet or a cell.) After the
object, you type a dot. You then specify what you want to do with this
object, or what you want to manipulate. The doing is called a method. The
manipulating is done via properties or parameters.
If all this is confusing, let's try and clear it up.
Think of a television. This is an object. We can notate it like this:
tv
OK, all very simple so far. But you'll need some more information if you
were going to buy a television. One thing you may want to know is how big
the tv is. To add a size property, you'd do this:
tv.size
You'd want this to equal something, though, so add an equal sign and a size:
tv.size = "55 inch "
We now have an object (the tv) and a property (the size). We also have a
value for the size (55 inch).
If we wanted to buy this tv then we'd be doing something (buying). We can
call this "doing" a method. It is a method of the tv:
tv.buy
Methods can come with extra settings, called parameters. A parameter of
the buy method could be PaymentType. The PaymentType would then
come with its own values (credit card, cash, cheques, etc). We could
represent all this as follows:
tv.buy PaymentType:=Cash
We have a space between the method (buy) and the parameter
(PaymentType). The value for the parameter comes after a colon and equal
sign (:=), with no spaces in between.
We could add more paramters for buy. For example, we could have a
Discount paramater, and a DeliveryCharge paramater:
tv.buy PaymentType:=Cash Discount:=No DeliveryCharge:=No
Notice that we've used a space to separate the three paramaters and their
values.
So, back to Excel. In the VBA programming language, you'll uses these
object, methods, properties and paramaters a lot. As an example, there's an
object called ActiveCell. This is the cell where your cursor currently is. The
ActiveCell object can have a font set for it:
ActiveCell.Font
Fonts have a name property all of their own. So after another dot, you type
the Name property:
ActiveCell.Font.Name
Because it's a property, you need a value for it. Let's add the name of a font:
ActiveCell.Font.Name = "Times New Roman "
This sets "Times New Roman" to be the font of the ActiveCell.
We can also set a bold value for the Font:
ActiveCell.Font.Bold = True
Again, we have an object called ActiveCell. This is followed by the Font
property. The Font property has a bold property of its own. This is set to
True.
Methods
An example of a method of an object is Quit:
Application.Quit
To Quit is to do something, which is why it's a method rather than a
property. If you're not sure whether something is a method or a property, try
putting the word "to" before it. To Quit makes sense, but to Font doesn't. So
Font would not be a method.
Another example of a method is Add. You can use this to add a new
worksheet to your Excel workbook:
Worksheets.Add After:=Worksheets(1)
The object above is Worksheets. The Add method comes after a dot. Next,
we have a space. One parameter of the Add method is called "After". The
value for "After" is the name of a worksheet. (Inside the round brackets you
can have either the name of a worksheet in double quotes, or a number. The
number 1 means worksheet 1 in the current workbook.)
We'll leave it there, though. Don't worry if you didn't fully grasp the above
as you'll soon pick it up as we go along. Let’s move on to an Excel property
you’ll use quite a lot – the Range property. (Range can also be an object.)
The Range Property

Quite a lot of the macros you'll write will need to reference the Range
property. In this section, you’ll get some practice with Range. You’ll also
learn how to add form controls to an Excel worksheet.
So, what is a Range? A Range just means a range of cells on a worksheet. A
Range can be an individual cell, or a group of cells. The Range you want to
refer to goes between round brackets. Inside the round brackets you
surround your Range of cells with double quotes. Here’s a Range object that
just refers to the cell A1:
Range("A1")
And here's a Range property that refers to a group of cells (notice the colon
between the two cells):
Range("A1:B7")
Another way to refer to more than one cell is to specify the top left corner
of your range and the bottom right corner. Like this:
Range("A1", "B7")
The above Range property refers to the cells A1 to B7.
Once you have a Range you can do something with it. One method you can
use with Ranges is the Select method. As its name suggest, the method
Selects a Range of cells:
Range("A1").Select
Range("A1:B7").Select
The Worksheets Object

We mentioned above that Range is a property. But what is it a property of?
Well, Range is a property of the Worksheets object. If we wanted to be
really specific we should have used the Worksheets object first, then the
Range property. Like this:
Worksheets("Sheet1"). Range("A1").Select
Here, the Worksheet comes first. In between round brackets and quote
marks you type the name of a worksheet, "Sheet1" in this case. You can
also type a number between the round brackets:
Worksheets(1) .Range("A1").Select
The 1 above refers to the first worksheet in your open spreadsheet.
When you use Range by itself, Excel takes it mean the Currently Active
Worksheet, the one you currently have selected and displayed. If you want
to refer to a different worksheet, say Sheet 2, you need to refer to it by
name:
Worksheets(2) .Range("A1").Select
Or
Worksheets("Sheet2") .Range("A1").Select
But let's get some practical work done. First, we'll explore the VBA
Developer toolbar.
The VBA Developer Toolbar

Start a new spreadsheet for this. Now click on your Developer toolbar at the
top of Excel. You should see this:
The above image is taken from Excel 2013 (Excel versions 2016 and later is
very similar). If you have an earlier version, you may not have the Add-Ins
and Modify panels. But that's OK because we won't be using these.
The first panel to explore is the one on the left – Code :
There are five items on the Code panel: Visual Basic, Macros, Record
Macro, Use Relative References, and Macro Security. You've already seen
the Macro Security item. If you can't run your Macros then click this item
and change the security settings.
Clicking the Visual Basic item opens up the Visual Basic editor. You can
also press ALT + F11 on your keyboard as a shortcut.
Clicking the Macros item on the Code panel brings up the following
dialogue box:
Once you create a few Macros, they will appear in the list. You can then run
them by selecting a Macro Name and clicking the Run button.
The other two items are Record Macro and Use Relative References .
We'll skip over these two, as we won't have too much use for them.
The panel we'll use a lot, though, is the Controls panel. It looks like this:
The first item, Insert , is expanded when you click on it. Doing so will
reveal the following:
These are all the controls you can add to an Excel spreadsheet or a user
form. We'll be adding one of these controls to a spreadsheet shortly.
The Design Mode item is used to edit a control on a spreadsheet.
The Properties item shows you properties for a selected control.
The View Code item takes you to the Visual Basic editor again.
The Run Dialog item can be ignored, as we won't be using it.
We'll now create a Macro that selects a range of cells. These cells will be
selected when we click a button on our spreadsheet.
To create the Macro, click the Visual Basic item on the Code panel. The
Visual Basic Editor will open up. We want this Macro to be only for Sheet1
in the current Workbook. On the left hand side of the Editor, locate the
Project Explorer panel. (If you can't see it, Click View > Project Explorer
from the menu at the top of the Editor.)
In the Project Explorer right click the Sheet1 item under VBAProject
(Book1) :
From the menu that appears, select View Code . A blank coding window
will open up. (You can also double click on Sheet1 to open up the code
window.)
What we want to do here is to create a Subroutine. This Subroutine will be
our Macro. Type the following line into the white coding area:
Sub Range_A1_D6()
Press the Enter key on your keyboard and the Editor should add the
corresponding End Sub for you. Your coding window will then look like
this:
So we now have a Subroutine (a Macro) with the name Range_A1_D6 . In

between Sub and End Sub enter the following code:
Range("A1:D6").Select
Your coding window should then look like this:
As you can guess, this code selects a range of cells. It selects the cells A1 to
D6.
Before we try it out, you need to save your work.
From the menu bar at the top of the Editor, click File > Save Book1 . The
Save As dialogue box should appear. Change the name of the file to
Range_Exercises.xlxs . Click the Save button and you should see an error
message appear. This one:
Click No to return to the Save As dialogue box.

You get the error message because your file contains Macros. Excel can't
save Macros in a file that end in xlxs . You need to change the file ending.
To do that, click the Save As Type dropdown list. From the list, select
Excel Macro-Enabled Workbook(*.xlsm) :
Click the Save button again. When you click the Save button this time, the
file should save OK. But note that the file ending is now xlsm .
Now that you know how to save a spreadsheet with a Macro in it, it's time
to do something with that Macro.
At the top of the Editor, locate the Excel icon, just under the File menu:
Click this icon to return to your spreadsheet. We'll now place a button
control on the spreadsheet.
Locate the Controls panel on the Developer toolbar, and then click the
Insert item. From the Insert menu, click the first item, which is a button:
Now move your mouse to your spreadsheet. Hold down your left mouse
button somewhere on the F column (F3 will do). Keep it held down and
draw out a rectangular button. Let go of the left mouse button when your
cursor is on H4
As soon as you let go of the left mouse button, you'll see the Assign Macro
dialogue box appear:
Select your Macro from the list and click OK. The button on your
spreadsheet should now look like this:
You can edit the text on a button quite easily. Right click the button to see a
menu appear. From the menu, select Edit Text :
When you select Edit Text , a cursor will appear at the start of the text. Use
the arrow keys on your keyboard to move the cursor to the end of the line.
Delete the text Button 1 and type Range("A1:D6").Select instead (If you
accidentally click away from the button, click on it again with right mouse
button and not the left button. This will select your button again.):
Click away from the button to exit edit mode and you'll see the sizing
handles disappear.
You can now test your button out. Give it a click and you'll see the cells A1
to D6 highlighted:
Congratulations! You have now written VBA code to select a range of cells
on a spreadsheet. And all with the click of a button!
Now return to the Visual Basic editor (From the Developer toolbar, click
Visual Basic on the Code panel.) Type a single quote before your Range
line. The line should turn green:
The reason it turns green is because a single quote is used for comments.
When the line is commented out it means Visual Basic will no longer see it
as code, so doesn't do anything with it. You can add comments to remind
yourself what your code does, as in the image below:
Adding comments to your code is a good habit to get in to. Especially when
you come back to your code after a few weeks or so. If you haven't added
comments you may not quite understand what it was you were trying to do.
Back to the Range code, though. Notice how we referred to the range of
cells A1 to D6:
Range("A1:D6")
Another way to refer to the same range is like this:
Range("A1", "D6").Select
This time, the start cell A1 and the end cell D6 are enclosed with double
quotes. In between the two we have a comma.
Both the examples above do the same thing: they first select the top left cell
of the range, and then the bottom right cell of the range. It's entirely up to
you which you use. But with the second version you can use something
called the ActiveCell.
The ActiveCell
Instead of typing the name of a cell you can also refer to which cell on your
spreadsheet is currently highlighted. The currently highlighted cell is called
the ActiveCell (no spaces and with capital letters for the "A" and "C"). You
can use this in your code. Let's see how it works.
After the End Sub of your first Subroutine, add the following:
Sub ActiveCell_Example()
Press the enter key on your keyboard to let the VB editor add the End Sub
for you. Now add the following line between the Sub and End Sub of your
code:
Range(ActiveCell, "D6").Select
So the top left cell we want to select is the ActiveCell, which is whatever
cell you clicked in on your spreadsheet. The bottom right cell we want to
select is D6.
Click the icon to return to your Excel spreadsheet. Now draw another
button on your form, just below the first one. You should see the Assign
Macro dialogue box appear again:
Select your new Macro (your Sub) from the list and click OK.
When you get back to your spreadsheet, edit the text of the button again.
Type ActiveCell as the text. When you have finished editing the text, click
away. Click inside another cell on your spreadsheet, cell A2 for example.
Now click your button. You should see the cells A2 to D6 highlighted:
Click inside any other cell on your spreadsheet and click the button again.
The cells from your active cell to D6 will be selected.
Offset
Another useful way to refer to a cell or group of cells is with the offset
property. This is used with the Range property so that you can specify a
new location. But the new location is based on cells that you specify. As an
example, examine this code:
Range("A1").Offset (RowOffSet:=1, ColumnOffset:=1).Select
The above code will select cell B2. The location we've used is cell A1.
We've then typed a dot followed by the word Offset . In between round
brackets, you tell Excel the new location. This is done with the parameters
RowOffSet and ColumnOffSet. (The two are separated by a comma.) In
other words, move one row from cell A1 and one column from cell A1.
You can use a shorthand instead of RowOffSet and ColumnOffSet. The
shorthand just uses the numbers:
Range("A1").Offset (1, 1).Select
You can also just specify the Rows and not the columns:
Range("A1").Offset (1).Select
Here, we've missed off the column parameter and its comma. Excel takes
this to mean you want to move one row down from the cell you specified
with Range. The cell selected will now be A2. (We've gone down a row but
stayed in the same column.)
Similarly, you can specify the columns but not the rows:
Range("A1").Offset (, 1 ).Select
Now, the row position is blank and the column position is filled in. Notice
that we have left in the comma, though. Excel takes this to mean that you
don't want a new position for the rows, just the columns. The cell selected
will now be B1. (We've gone across one column but stayed in the same
row.)
You can also specify negative numbers:
Range("B2").Offset (-1, -1 ).Select
This time, we're starting in cell B2. In between the round brackets of Offset
we have -1, -1. A negative number means go up one row or column from
the starting position. Going up one row and one column from cell B2 takes
you to cell A1.
You can also specify more than one cell for the Range:
Range("A1:C3").Offset (1, 1).Select
Here, we've started with the range of cells A1 to C3. We want to offset this
entire range by one row and one column. The new range of cells in the line
of code above will be B2 to D4. When specifying a range of cells with
offset, not only does the first cell (A1 above) get moved across and down 1
but the last cell (C3) gets moved across 1 and down 1.
If all this is confusing, let's clear it up with a few practical examples.
Return to your coding window. Create another Sub and give it the name
Range_Offset. Add the following code:
Range("A1").Offset(RowOffSet:=1, ColumnOffset:=1).Select
Your coding window will then look like this (we've added some comments):
Return to Excel and draw out another button on your spreadsheet. When the
Assign Macro dialogue box appears, select your new Sub, Range_Offset .
Change the text on the button, as well. To test it out, select the cell A1 on
your spreadsheet. Then click your button. You should find that Excel now
selects cell B2:
Return to your coding window. Now change the code for your new Sub to
this:
Range("A1").Offset(2, 2).Select
Test out the new code by returning to Excel and clicking your button.
Which cell does Excel select now?
Try out the other Offset positions we've mentioned in this lesson. Amend
your code and try each of these in turn:
Range("A1").Offset(3).Select
Range("A1").Offset(, 2 ).Select
Range("B2").Offset(-1, -1 ).Select
Range("A1:C3").Offset(1, 1).Select
OK, we'll move on. Before doing so, make sure you understand how Offset
works. We'll take a look at the Resize property now.
The Resize Property

When you use Offset, you're selecting a new range of cells based on a
starting position. But the starting position will change. If you want to
change the size of a range of cells, but keep the starting position, then
Resize is the better option. Take the following line of code as an example:
Range("A1").Resize (RowSize:=2, ColumnSize:=2).Select
This is almost identical to the Offset code you used earlier. The only
difference is the word Resize instead of Offset . Again we start with a
Range (cell A1). In between the round brackets of Resize you type a
RowSize and ColumnSize. Like before, though, you can shorten this and
just have the numbers between the round brackets:
Range("A1").Resize (2, 2).Select
If you only want to resize the rows then you can miss out the ColumnSize
parameter:
Range("A1").Resize (2).Select
If you only want to resize the columns then you can miss out the RowSize
parameter:
Range("A1").Resize (, 2).Select
Let's see how Resize works in practice.
Return to your coding window. Set up a new Sub and call it Range_Resize
. In between Sub and End Sub , type the following line:
Range("A1").Resize (RowSize:=2, ColumnSize:=2).Select
Your code will then look like this:
Go back to your spreadsheet and add a new button. When the Assign
Macro dialogue box appears, select your Range_Resize Sub from the list.
Click OK to return to your spreadsheet. Change the text of the button to
Range Resize .
To test out your new button, click inside cell A1 just to highlight your
starting position. Now click the button. You should find that the cells A2 to
B2 are highlighted:
To get a better understanding of how Resize works, try the following

(amend your code one example at a time):
Range("A1").Resize(3, 4).Select
Range("A1").Resize(3).Select
Range("A1").Resize(, 4).Select
Range("A1:A3").Resize(, 2).Select
Notice that the last example uses the cells A1 to A3 for the Range property.
The others use A1 as the starting position.
You can also resize AND offset. It's a little trickier to understand, though:
Range("B1").Offset (, -1).Resize (, 3).Select
In the code above, we start with the Range B1. We then offset this by one
column to the left (the -1). The Resize then stretches the range of cells 3
columns. Try it and see which cells are selected in the line above.
OK, we'll move on from Ranges. In the next section, we'll have a look at
something called programming variables, and how they work with the VBA
language.
VBA PROGRAMMING
VARIABLES
In computer programming, you need to store things in memory, things like
numbers and strings of text. You store them so that you can retrieve them
later and manipulate the numbers or strings of text in some way. To store
things in memory you use a variable . Variables have a name (something
that you yourself come up with) and a type (such as Integer or String). In
the Visual Basic programming language you also need to declare that you
are setting up a variable. This is done (mostly) with the word Dim. As an
example, study the following:
Dim MyNumber As Integer
The above code sets up a variable with the name MyNumber . The type of
variable is a whole number (As Integer ). The Dim keyword goes at the
start, and tells the programme to set up a variable of this name and type.
However, there's nothing stored inside of the MyNumber variable – VBA
has just allocated the memory at this point. To store something inside of a
variable, you use the equal sign (=), also known as the assignment operator.
To store a value of 10, say, inside of the variable called MyNumber , you
do it like this:
MyNumber = 10
The above line reads, "Assign a value of 10 to the variable called
MyNumber". So the name of your variable comes first, then the equal sign.
After the equal sign you type the value that you want to store inside of your
variable.
Sadly, you can't set up a variable and assign a value all on the same line.
(You can in Visual Basic .NET but not in Visual Basic for Applications.) So
you can't do this:
Dim MyNumber As Integer = 10
Variable Names
You can call your variable just about anything you like. But there are a few
things you're not allowed to do. They are:
You can't start a variable name with a number
You can't have spaces in your variable names, or full stops
(periods)
You can't use any of the following characters: !, %, £, #, $
So these variable names are OK

MyVariable
My_Variable
myvariable2
But these variable names will get you an error:
2MyVariable
My Variable
$myvariable
The first one above starts with a number, the second one has a space, and
the third starts with a dollar sign.
With variable names, it's best to come up with something relevant. So if
you're storing a discount rate, then call it DiscountRate and not something
odd like var1.
Variables - Practice
To get the hang of variables, return to your coding window. Set up a new
Sub and call it Variable_Practice . Add the following two lines for the
Sub:
MyNumber = 10
So this code just sets up a variable called MyNumber . We then store a

value of 10 into this variable. This is important in programming: whatever
you put on the right hand side of an equal sign is what you are trying to
store; whatever you have on the left of the equal sign is the place where
you're trying to store it.
However, you'd want to actually do something with this value. After all,
what's the point of storing a value if you're not going to use it? What we can
do is to transfer our stored value to a cell on a spreadsheet.
You have been using Range in previous lessons. One property of Range is
Value . The Value property can get or set the value of a cell, or group of
cells. To set a Value, the Range goes before an equal sign:
Worksheets(1).Range("A1").Value =
Here, we're first accessing the Worksheet 1 object. We want to point to the
Range A1 . After a dot, we then type the Value property. This is followed
by the equal sign. After the equal sign, you can type your value:
Worksheets(1).Range("A1").Value = 10
Now, the Value for Range A1 on Worksheet 1 is set to 10 .
Instead of typing a number, you can type the name of a variable. We have
already stored a value of 10 inside of the MyNumber variable. So we can
just use this:
Worksheets(1).Range("A1").Value = MyNumber
VBA sees the variable called MyNumber and then fetches whatever value
is stored inside of it. That value is then stored inside of whatever is on the
left of the equal sign.
Add that line to your code, just below the other two. Your coding window
will then look like this:
Now try it out. Go back to your spreadsheet and add a button. When the
Assign Macro dialogue box appears, select your Variable Practice Sub
from the list. Change the text on the button to Variable Practice . Deselect
your button by clicking away. Now click it again to test it. You should find
that the number 10 appears in cell A1 on your spreadsheet:
Now return to your code. Locate the Dim line from your Variable Practice
Sub. This one:
Comment the line out by typing a single quote mark before it. You code will
then look like this:
A comment, remember, means that the line will be ignored. The code that
VBA will execute is now only this:
MyNumber = 10
Worksheets(1).Range("A1").Value = MyNumber
The question is, will the code still run, or will it throw up an error?
Try it out by returning to Excel and clicking your button. You should find
that it runs OK, with no problems at all. But why? We didn't set a variable
name with the Dim keyword, as this is now commented out. So what's
going on?
Well, you can actually set up a variable like this:
Variable_Name = Value_Here
In other words, you don't actually need to say Dim Variable_Name As
Ineteger . You can miss it out entirely. However, if you do VBA sets the
variable type up as something called a Variant . Variant data types can hold
just about any kind of value. VBA will decide, when your programme runs,
what type of data to store in your variable.
So if you don't need to set up variables with the Dim keyword, why bother
using them at all? Well, it turns out that using the Variant data type will
cause your code to run really slowly compared to setting up variables with
the Dim keyword and using a named type like As Integer .
We'll be setting up our variables with the Dim keyword wherever possible.
Exercise
Uncomment your Dim line by deleting the single quote. Now change the
Range from "A1" to "A1:A10". Return to Excel and run your code again.
What happens?
You can set up more than one variable, of course. Amend your code to this
(the new lines are in bold):
Dim MyOtherNumber As Integer
MyNumber = 10
MyOtherNumber = 20
Worksheets(1).Range("A1:A10").Value = MyNumber
Worksheets(1).Range("B1:B10").Value = MyOtherNumber
We've set up another Integer variable (As Integer ) and called it
MyOtherNumber . A value of 20 is being stored in this new variable. The
Value of the Range B1 to B10 is being set to whatever value is stored in
MyOtherNumber .
When you click the button on your spreadsheet you should now see this:
Using variables to add, multiply, divide, and subtract

When you store numbers inside of variables, one of the things you can do
with them is mathematical calculations. We'll start with simple addition.
Return to your code from the previous section. Set up another Sub and call
it Add_Numbers . Inside of Sub and End Sub type the following code:
Dim Number_1 As Integer
Number_1 = 10
Number_2 = 20
Worksheets(1).Range("A1").Value = "Addition Answer"
Worksheets(1).Range("B1").Value = Number_1 + Number_2
Return to Excel. Select any cells that have numbers in them, then press the
Delete key on your keyboard to get rid of them. Now add a new button.
From the Assign Macro dialogue box select your Add_Numbers Sub.
Change the button text to Addition . Click away from the button to deselect
it. Now click the button again. The result should be this (you'll need to
widen the A column):
We've done two things here: we've used VBA to add some text inside of
cell A1, and we've put the result of our addition into cell B1. The line that
puts the text into cell A1 is this:
Worksheets(1).Range("A1").Value = "Addition Answer"
This time after the equal sign we have some direct text. If you want direct
text to the right of the equal sign you need to enclose it between double
quotation marks. We have the text Addition Answer between double
quotes. The Value of Range A1 on Worksheets(1) is then set to this new
text.
The part that does the adding up is this line:
Worksheets(1).Range("B1").Value = Number_1 + Number_2
To the right of the equal sign we now have this:
Number_1 + Number_2
We have already stored a value of 10 inside of the variable called
Number_1 . Inside of the variable called Number_2 we have stored a
value of 20. Just like in ordinary mathematics, the plus symbol is used to
add up. VBA will add the two values for you and store the answer into the
cell B1.
You can add up more than two numbers, of course. If we had a third
variable, we could have done this:
Worksheets(1).Range("B1").Value = Number_1 + Number_2 +
Number_3
You can add up numbers that aren't inside of variables. So this is perfectly
fine:
Worksheets(1).Range("B1").Value = Number_1 + Number_2 + 30
Or this:
Worksheets(1).Range("B1").Value = 10 + 20 + 30
Or you can store your addition in just one variable:
Number_1 = 10 + 20 + 30
Worksheets(1).Range("B1").Value = Number_1
Subtraction
In the VBA programming language, the minus sign (-) is used to subtract
one value from another. Again, you can use actual values, values stored in
variables, or a combination of the two.
Go back to your code. Set up another Sub and call it Subtract_Numbers.
Add the following lines of code:
Dim Number_1 As Intger
Number_1= 450
Number_2 = 387
Worksheets(1).Range("A2").Value = "Subtraction Answer"

Worksheets(1).Range("B2").Value = Number_1 - Number_2
Notice that we're using the same variable names, here: Number_1 and
Number_2. This is perfectly acceptable as both are enclosed within their
own Sub and End Sub . The thing you can't do is to set up two variables
with the same name between the same and Sub and End Sub lines. But if
they are in two different Subs, that's OK. (This is known as Variable
Scope.)
Return to your spreadsheet and add a new button. From the Assign Macro
dialogue box select your Subtract_Numbers Sub. Change the button text
to Subtraction . Test it out and you should see a new line appear on your
spreadsheet:
So we set up two variables and stored values of 450 and 487 in them. We
added some direct text to cell A2, and used the following subtraction for
cell B2:
Worksheets(1).Range("B2").Value = Number_1 - Number_2
The only difference between this and the addition code (apart from the B2
cell reference) is the use of the subtraction symbol (-) in place of the
addition symbol (+). When the code is run, VBA looks at the values in the
two variables. It then deducts one from the other. The answer is then stored
as the Value for the Range on Worksheets(1).
Just like addition, you can use more than one variable, a mixture of
variables, or no variables at all, as in the following lines:
Number_1 - Number_2 - Number_3
Number_1 - 10
300 - 200
You can also mix the addition and subtraction. Amend the code for your
Subtract_Numbers to this (the new or amended lines are in bold):
Dim Answer As Integer
Number_1 = 50
Number_2 = 40
Number_3 = 30
Answer = Number_1 + Number_2 - Number_3
Worksheets(1).Range("A2").Value = "Subtraction Answer"

Worksheets(1).Range("B2").Value = Answer
We've set two new variables here:
After setting up the variables, we have this:
Number_1 = 50
Number_2 = 40
Number_3 = 30
Answer = Number_1 + Number_2 - Number_3
The first three lines just store the numbers into the variables. The fourth line
is where we perform a calculation. The first thing we do is add the value
stored in Number_1 (50) to the value stored in Number_2 (40). Once this
addition is performed (50 + 40), we deduct the value stored in the variable
called Number_3 (30).
Return to your spreadsheet and click your button. You should see a value of
60 appear in cell B2. The reason it does so is because of this line:
The answer to our calculation has been stored in the variable called Answer
. We then use this variable as the Value for the Range B2 in Worksheets(1)
.
But what we're really doing is just this:
Answer = 50 + 40 - 30
Multiplication
In programing languages, the multiplication sign is the asterisk (*). So if
you want to multiply 10 by 5 in VBA you could do it like this:
Number_1 = 10
Number_2 = 5
Answer = Number_1 * Number_2
Try it out for yourself. Return to your coding window. Add another Sub and
call it Multiply_Numbers . In between Sub and End Sub type the
following code:
Number_1 = 10
Number_2 = 5
Answer = Number_1 * Number_2
Worksheets(1).Range("A3").Value = "Multiplication Answer"

The code is more or less the same as before. The only differences are the
cell references (A3 and B3) and the multiplication sign (*). Your coding
window should look like this:
Once you have added the code, return to your spreadsheet. Add a new
button and select Multiply_Numbers from the Assign Macro dialogue box.
Change the text on the button as before. When you click your button, you
should see a new line added:
As with Addition and Subtraction, you can use more than two numbers or
variables in your calculations. So these are fine:
Answer = Number_1 * 10
Answer = Number_1 * Number_2 * Number_3
Answer = Number_1 * Number_2 * 10
You can mix the Addition, Subtraction and Multiplication, but you need to
take care. For example, what is the correct answer to the sum below?
Answer = 10 * 2 + 5
If you do the sum from left to right you'd first multiply the 10 and the 2 to
get 20. Now add the 5 to get and answer of 25. However, if you work form
right to left, you'd first add the 5 and the 2 to get 7. Multiply 7 by 10 and
you'd get 70, a totally different answer!
VBA works things out from left to right. But you can force the answer you
need by using round brackets:
Answer = 10 * (2 + 5)
The round brackets above surround the 2 + 5. VBA takes this to mean you
want to add these two numbers first. Once it has an answer it will then do
the rest of the calculation. It's a good idea to use round brackets to avoid
any confusion.
Division
The symbol to use when you want to divide numbers is the forward slash
(/).
Try out some division for yourself. Return to your coding window and add
a new Sub. Call it Divide_Numbers . In between Sub and End Sub, type
the following code:
Number_1 = 10
Number_2 = 5
Answer = Number_1 / Number_2

Worksheets(1).Range("A4").Value = "Division Answer"
Return to Excel and add a new button to your spreadsheet. From the Assign
Macro dialogue box select your Divide_Numbers Sub. Change the text on
the button. When you click your new button, you should see a new line
appear:
Now go back to your code. Change Number_2 from 5 to 4:

Number_1 = 10
Number_2 = 4
So we're now dividing 10 by 4. Return to Excel and click your Division
button. What answer do you get? Instead of the expected 2.5 you still get 2!
The reason VBA has chopped off the .5 at the end is because we're using As
Integer in our code. When you use the As Integer variable type you only
get whole numbers, not fractions. To get a "point something" you need to
use a different variable type. You'll learn more about the different variable
types shortly. For now, change your code to this (the new lines are in bold):
Number_1 = 8
Number_2 = 8
Number_3 = 4
Answer = Number_1 + Number_2 / Number_3
Worksheets(1).Range("A4").Value = "Division Answer"
What we're doing here is mixing some addition with division. Our sum is
really this:
Answer = 8 + 8 / 4
You may think that this says "first add 8 and 8 then divide by 4". The
answer you'd be expecting is 16 / 4, which is 4. However, try out the code
by clicking the button on your spreadsheet and you'll find that the answer
you actually get is not 4 but 10! So what's going on?
The reason you get 10 and not 4 is because of something called operator
precedence . All this means is which of the mathematical operators (+, -, *
/) has priority. VBA sees division as more important than addition, so it
does the dividing first. Replace the / symbol with the * symbol and you'll
find that multiplication is also more important than division. So the answer
to this sum:
Answer = 8 + 8 * 4
is 40, according to VBA, and not 64.
With operator precedence you have to take into account the following:
Division and Multiplication are done before addition and
subtraction
Division and Multiplication have equal priority and so are
calculated from left to right, as long as there's no addition and
subtraction to do
Addition and subtraction have equal priority and so are calculated
from left to right, as long as there's no division and multiplication
to do
If the above is somewhat confusing, just remember to use round brackets to

make it clear to VBA what you want to do:
Answer = (8 + 8) / 4
In the above sum, VBA will now add 8 to 8, because of the round brackets.
The answer to whatever is between the round brackets will then get divided
by 4.
One last thing about the division symbol: don't confuse it with the backslash
character (\), which is used for something called Integer Division. This is
when you take the whole number answer and throw away any "point
something" at the end. For example, try this code:
Dim answer As Double
answer = 11 / 2
MsgBox (answer)
As you expect, the answer in the message box is 5.5, because 11 divide by 2
is 5.5. However, change the forward slash into a backslash:
answer = 11 \ 2
Now the answer you get in the message box is just 5: the point 5 at the end
has been chopped off. So just be aware that the backslash in Excel VBA
gets you Integer Division, not regular division.
Other Numerical Variable Types
As well as declaring a variable to be of type Integer, you can also have the
following variable types:
As Long
As Single
As Double
As Currency
The difference between all these numerical data types is how many digits
they can hold, and whether or not you want a decimal point (there is
actually an As Decimal variable type, but it's a bit fiddly to use).
The first one on the list, As Long , is a great alternative to As Integer .
We've been using As Integer exclusively up until now. The problem with
the Integer variable type, however, is that it can only hold numbers up to a
value of 32, 767. At the lower level the Integer variable type can only hold
negative numbers up to -32, 768.
If you want store bigger numbers then clearly 32, 767 may not be enough.
This is where As Long comes in. The Long variable type can hold positive
numbers up to a value of 2, 147, 483, 647. The lowest negative number is
-2, 147, 483, 648.
But Integer and Long are both used to store whole numbers. They would be
no good if you wanted divide 10 by 3, say. If you want a remainder, you'll
need a different variable type.
The variable types you can use for greater precision are As Single and As
Double . The difference between the two are how many digits they can
hold. As Single hold 4 bytes of data while As Double can hold 8 bytes. If
you want a really, really long floating point number (a number with "point
something" at the end) then use As Double , otherwise just use As Single .
Let's test some of this theory out, though. Create a new Sub and call it
RowCount . Add the following code:
Dim NumberOfRows As Long
NumberOfRows = Worksheets(1).Rows.Count
MsgBox NumberOfRows
We've set up a variable called NumberOfRows . Instead of As Integer ,

we've used As Long . The second line uses Rows.Count to get the number
of rows on Worksheet 1. When VBA gets this number, it stores it in our
NumberOfRows variable. The third line is this:
MsgBox NumberOfRows
The MsgBox stands for Message Box. We just want to quickly test some
values here, so there's no need to add a button to the spreadsheet. You'll
learn more about Message Boxes a little later. But the Message box will
display whatever value is in the variable NumberOfRows .
Rather than a long explanation about MsgBox, try it out to see what it does.
Make sure your cursor is flashing between the Sub and End Sub of your
new code. Now locate the green arrow on the toolbars at the top of the
coding window:
This is the Run icon. When you click it, VBA will try to run your Subs
inside of the coding environment. You can also click Run Sub/User Form
from the Run menu at the top:
A shortcut to running your Subs is to press the F5 key on your keyboard.
When you run your Sub, though, you should see a message box appear:
The number 1048576 is coming from our NumberOfRows variable. In

other words, just over a million rows in this version of Excel.
Click OK on your Message Box to return to your coding window. Now
change As Long to As Integer:
Dim NumberOfRows As Integer
Try running your Sub again. This time, you should see an error message:
The error is Overflow . You get this error because the value you're trying to
store in your variable is too big for the variable type. The As Integer
variable type can only hold numbers up to a value of 32, 767. Storing a
value of over a million causes the programme to bail out with Overflow
error.
To test out floating point numbers, add a new Sub and call it Floats . Add
the following code:
Dim FloatingPoint As Single

FloatingPoint = 10 / 3
MsgBox FloatingPoint
Your coding windows will then look like this:
With your cursor inside of the Sub and End Sub code, Press F5 on your
keyboard to run it. You should a Message Box appear:
The value in the variable called FloatingPoint is being displayed in the

Message Box. It is showing the answer to 10 divided by 3 to six decimal
places.
Now change this line in your code:
To this:
Dim FloatingPoint As Double .
Run your code again and the Message Box will display the following:
Now the FloatingPoint variable is showing the answer to 10 divided by 3

to fourteen decimal places.
So if you want greater accuracy in your calculation, use As Double rather

As Single . Here's one final example of that.
Change your code to this:

FloatingPoint = WorksheetFunction.Pi
MsgBox FloatingPoint
This time, we're using WorksheetFunction . After a dot, you'll see a list of
Excel functions you can use. Select Pi from the list.
When you run your code, the Message Box will be this:
Now change As Single to As Double . When you run your code this time,
the Message Box will be as follows:
In the As Single version, the sixth number after the floating point is a 3. In
the As Double version, the sixth number is a 2. VBA has rounded the value
up for As Single .
So, again, if your calculation need to precise, and you don't want Excel to
automatically round things up or down, then use As Double rather than As
Single .
One final variable type that can come in handy is Variant:
Dim FloatingPoint As Variant
Variant is used when you're not sure what value will be returned. It can hold
numbers, text, and objects. However, using it too much can slow down your
programmes, as it uses 16 bytes of data for numbers and 22 bytes for text.
Variable Types and Text
If you want your variables to hold strings of text then the variable type to
use is As String :
Dim MyText As String
There are lots of inbuilt methods you can use on strings of text, and you'll
learn about these later in the course. Mastering these methods will greatly
improve your VBA programming skills.
In the next section, you'll learn about Conditional Logic.

CONDITIONAL LOGIC
Conditional Logic is all about the IF word. It's about saying what should
happen IF a certain condition is met, or what should happen if it's not met.
You use Conditional Logic all the time in your daily life. You say things
like this:
IF I buy these shoes I will be happier

IF I eat this ice cream I will ruin my diet
IF I go online I might have some emails
Programming in any language is heavily reliant on Conditional Logic like

the IF Statement. It allows you to go down different paths, depending on an
initial condition.
The structure of a VBA IF Statement looks like this:
If Condition_To_Test Then
'CODE HERE
End If
You start with the word If (uppercase "I" lowercase "f"). After a space, you
have a condition that you want to test. This conditional is something that
can either be TRUE or FALSE. After your condition, you type a space
followed by the word Then (uppercase "T"). An If Statement ends with the
words End If .
In Between If and End If is where you type your code. But this code will
only be executed IF your condition is TRUE. If it's FALSE then VBA skips
past the End If and continues on its way.
Let's clear things up with a few coding examples. You can start a new
spreadsheet for this. When you save the new file, don't forget to save it as
an Excel Macro- Enable Workbook. The file ending will then be XLSM .
Click the Developer tab in Excel then the View Code item on the Controls
panel:
Clicking View Code will open the VBA Editor. If the coding window for
Sheet1 is not already open, double click Sheet1 in the Project Explorer on
the left. (If you can't see the Project Explorer, click View > Project
Explorer from the menu at the top.)
Create a new Sub in your coding window, and call it If_Test_1 . Add the
following code for your Sub:

MyNumber = 10
If MyNumber = 10 Then
MsgBox "Number = 10"
End If
Your Coding window will then look like this:

The first two lines set up an Integer variable called MyNumber . We're
storing the number 10 in MyNumber . Notice the first equal sign:
MyNumber = 10
The equal sign here means "Assign a value of". So we're assign a value of
10 to the variable called MyNumber .
The first line of the If statement is this:
We have the word "If" and the word "Then". Between the two we have the
condition we wish to test:
MyNumber = 10
You might think that this is the same as line two from our code. But it's not.
It means something entirely different. When you use an equal sign in an If
statement it doesn't mean "Assign a value of" anymore, it means "has a
value of". What you're saying now is "If MyNumber has a value of 10".
The equal sign in an If Statement is known as a Conditional Operator. You'll
meet more of these later.
But by saying "If MyNumber has a value of 10" you're creating a statement
that can either be TRUE or FALSE. That's what VBA will be checking for:
"Can this statement be evaluated as either TRUE or FALSE?"
If MyNumber does indeed have a value of 10 then the If Statement is

TRUE. In which case the code between If an End If will get executed. For
us, this is just a simple Message Box:
We have surrounded Number = 10 with double quotes. This will turn it into
text.
With your cursor flashing between the Sub and End Sub of your code,
press F5 on your keyboard to run it. You should see the Message Box
display:
Click OK on the message box to return to your coding window. Now

change the second line of your code from MyNumber = 10 to MyNumber
= 11 . Leave the IF Statement alone, though. Now press F5 again to run the
code. You should find that nothing happens.
The reason nothing happens is that the message box is enclosed between an
If and an End If . The message box line will only get executed if the If
Statement evaluates to TRUE. Since MyNumber now equals 11 the If
statement will now evaluate to FALSE. A value of FALSE means that VBA
can skip past the If statement without executing any of the code between If
and End If .
Else
Between If and End If you can also add an Else part. The structure of an If
… Else Statement looks like this:
Else
End If
In our code above, nothing is happening because we've set MyNumber to a

value of 11. Our IF Statement only executes when MyNumber has a value
of 10, making the condition TRUE.
However, instead of VBA skipping past the entire IF Statement, we can add
an Else part. The Else part is where you tell VBA what should happen if the
opening line is FALSE. Change your code to this (the new lines are in
bold):

MyNumber = 11
Else
MsgBox "Number is not 10"
End If
What we're saying is, "If MyNumber has a value of 10 Then display one
message box, Else display another message box".
Run your code by pressing F5 on your keyboard. You should the message
from the Else part appear:
Click OK on the message box to return to your coding window. Change the
number from the second line of your code from 11 back to 10. When you
run your code again, you'll see the first message box.
Using an Else part for your If Statements is handy programming technique

to learn.
ElseIf
Not every condition can be reduced to a simple either/or. Quite often, you'll
have more than two options you want to check for. In our code, we may
want to check for a value of 10, 11, and any other number. This is where
ElseIf comes in. Here's the structure of an Else If Statement:
ElseIf Condition_To_Test Then
End If
You can have more than one Else If parts, as many as you need, in fact:
End If
Notice that the only difference between If and ElseIf is the word Else
tacked onto the word If . You still have a condition to test and the keyword
Then at the end.
You can also add an Else part, to catch anything you may have missed:
Else
End If
To test all this out, add a new Sub. Call it If_Test_2 . Then add the
following code:

MyNumber = 11
ElseIf MyNumber = 11 Then
End If
Your coding window should look like this:
This almost the same as before. The difference is the ElseIf part:
First of all, VBA checks the variable MyNumber for a value of 10. If this
evaluates to TRUE then the code for the IF statement gets executed. If
MyNumber doesn't have a value of 10 then VBA drops down to the ElseIf
part. It then checks MyNumber for a value of 11. If this is TRUE then the
second message box will display. If neither is TRUE then VBA will skip to
the End If and continue on its way.
Test it out. Run your code and you'll see "Number = 11" appear in a
message box. Now return to your coding window and change the second
line from MyNumber = 11 to MyNumber = 10 . Run your code again and
the first message box displays.
Return to your coding window again and change MyNumber to 12. If you
try to run your code now, nothing will happen. Nothing happens because
both the IF and the ElseIf line evaluate to FALSE.
Now add an Else part to your code:
Else
MsgBox "Not 10 or 11"
End If
Run your code again and you'll find that the third message box display. By
adding Else you've guaranteed that at least one part of your If statement will
be TRUE.
Conditional Operators
You've already used one conditional operator - the equal sign. But there are
others. Here's a list:
Operator Meaning
= Has a value of
< Less than
> Greater than
<= Less than or equal to
>= Greater than or equal to
<> Not equal to
By using the various Operators, you can set up more sophisticated

conditions for your If Statements.
Add another Sub to your code. Call it If_Test_3 . As the code for your new
Sub, add the following:

MyNumber = 10
If MyNumber < 20 Then

MsgBox MyNumber & " is Less than 20"
End If

Again, we've set up an Integer variable called MyNumber and stored a
value of 10 in it. Look at the first line of the If Statement, though:
If MyNumber < 20 Then
If you consult the table above, you'll see the < symbol means "Less than".
So the condition to test is, "If MyNumber is less than 20". If MyNumber
is indeed less than 20 then the condition evaluates to TRUE. In which case,
the code between If and End If gets executed. If it's FLASE then VBA will
skip to any lines after End If .
One other thing to note about the code is the message box line:
MsgBox MyNumber & " is Less than 20"

After MsgBox we have the variable MyNumber then a space then an
ampersand (&) symbol. The & symbol is used to concatenate (join together)
things. After the & symbol we then have another space followed by some
direct text in double quotes. So whatever is in the variable MyNumber will
get joined with the direct text in double quotes.
Run your code and you should see the message box display the following:
Return to your coding window and change MyNumber = 10 on the second
line to MyNumber = 20 .
Run your code again and you'll find that nothing happens.
The reason nothing happens is that 20 is not less than 20, so the If
Statement is FALSE. We haven't got ElseIf or Else parts to check for a
TRUE value, so the code just ends.
Now change the < symbol to <=. The <= symbols together mean "Less than
or equal to". Change you message to this:
MsgBox MyNumber & " is Less than or equal to 20"
Your code should now be the same as ours below:

MyNumber = 20
If MyNumber <= 20 Then
MsgBox MyNumber & " is Less than or equal to 20"
End If
When you run your code, here's what the message box will look like:
Click OK and return to your code. Change the <= symbol to >=. The >=
symbols together mean "Greater than or equal to".
Change your message text to this:
MsgBox MyNumber & " is Greater than or equal to 20"

When you run your code, you'll see the message box appear again:
To test out the Greater Than sign by itself delete the = sign next to the >
symbol. Change MyNumber to 25. Amend the text in your message. Your
code will then look like this:

MyNumber = 25
If MyNumber > 20 Then

MsgBox MyNumber & " is Greater than 20"
End If
Run your code again to see the new message:

You can have ElseIf and Else parts with your Conditional Logic. Take the
following as an example:

MyNumber = 19
MsgBox MyNumber & " is 20"
ElseIf MyNum > 20
MsgBox MyNumber & " is Greater Than 20"
Else
MsgBox MyNumber & " is below 20"
End If
In the code above, we have an If and ElseIf and Else . The If Statement
tests for a value of exactly 20. The ElseIf tests if the variable MyNumber
is Greater Than 20. The Else part catches any other value. Try it out and see
how it works. Change the value of the variable MyNumber and run your
code again.
Logical Operators
You can have more than one condition on the same line of your If and
ElseIf Statements. To test for more than one condition, you need the Logic
Operators. Here's a list of them:
Operator Meaning
Not Test if value is NOT something
And Test for more than one condition
Or Test if the value is either OR something
Xor Test if one and only one value is true
Only the first three are used regularly. Let's see how they works. Before
doing so, however, there's one more important variable type we need to
discuss – Boolean values.
Boolean Values
You can set up something called a Boolean variable. Boolean variables have
only two values: either true or false . You set them up like this:
Dim BooleanFlag As Boolean

BooleanFlag = True
Here, we set up a variable called BooleanFlag. Instead of setting the

variable to As Integer or As Long we've set this one up with As Boolean .
The second line puts something in the variable. But notice that that
something is a value of True . The only other value we could have used
here is False .
You can use your Boolean values like this:
If BooleanFlag = True Then

MsgBox "It's True"
Else
MsgBox "It's False"
End If
The first line test if the variable called BooleanFlag is equal to a value of
True. If it is, then we display a message. Because there are only two
options, we can just an Else part to test for all other values, as any other
value will be False .
You can miss out the = True part, if you like, and just have this:
If BooleanFlag Then
VBA will then take this to mean "If BooleanFlag has a value of True".
With all that in mind, let's take a look at the Not operator.
Logical Not
The Not operator is used to test if a value or variable is NOT something.
We'll use our BooleanFlag variable from above to demonstrate this
concept.
Create a new Sub in your code. Call it Bool_Test and add the following
code:
Dim BooleanFlag As Boolean

BooleanFlag = True
If BooleanFlag = True Then

MsgBox "It's True"
Else
MsgBox "It's False"
End If
Press F5 to run your code and you should the first message box appear
saying "It's True".
Now change the first line of your If Statement to this:
If Not BooleanFlag = True Then
We're using the Logic Operator Not after the word If . Before we added Not
the If Statement read "If BooleanFlag has a value of True". By place the
word Not before BooleanFlag we're now saying, "If BooleanFlag DOES
NOT has a value of True". Since BooleanFlag actually does have a value of
True then the Else part gets executed.
Run your code again and you should see the second message box appear,
saying "It's False".
Logical And
The And operator test if two or more conditions are true. If, for example,
you wanted to check if a number was greater than 20 but less than 30 you
can use the And operator to test this condition:
Dim Age As Integer

Age = 21
If Age > 20 And Age < 30 Then

MsgBox "Between 20 and 30"
Else
MsgBox "Not Between 20 and 30"
End If
The first line of the If Statement is this:
If Age > 20 And Age < 30 Then

We have two conditions, here:
If Age > 20
If Age < 30
In between the two is the word And . For the whole of the first line to
evaluate to True BOTH of these statements must be True. If one of them is
False (Age equals 18, say) then the whole if the If Statement is False.
Test the code out. Create a new Sub and call it Test_And . Your coding
window should look like this:
When you run your code, you should see the first message display,
"Between 20 and 30".
Now change the Age = 21 to Age = 18 . Run your code again and the
second message will display, saying "Not Between 20 and 30".
You can extend on this idea and add ElseIf parts. Change your code to this:
Dim Age As Integer

Age = 21
If Age >= 20 And Age <= 30 Then

ElseIf Age >= 31 And Age <= 40 Then
ElseIf Age >= 41 And Age <= 50 Then
Else
MsgBox "None of these age groups"
End If
We're now using multiple ElseIf Statement to test for an age range. We've
also change the symbols to Greater Than or Equal To (>=) and Less Than or
Equal To (<=).
Test out the code by change the Age = 21 variable. Try different number to
get a feel for how Logical And works.
Logical Or
With Logical And, you're testing if two or more conditions are true. Only if
BOTH conditions are true does the entire If Statement become true. Logical
Or, on the other hand, tests if just one of two or more conditions are true.
The whole of the If Statement becomes true if at least one of the conditions
are true.
To clear this up, create a new Sub in your code window. Call it Test_Or .
Add the following between Sub and End Sub :
Dim FirstNumber As Integer

Dim SecondNumber As Integer
FirstNumber = 10
SecondNumber = 20
If FirstNumber = 10 Or SecondNumber = 20 Then

MsgBox "Valid Number"
Else
MsgBox "Non Valid Number"
End If
The code just sets up two integer variables, FirstNumber and

SecondNumber . We have a value of 10 in FirstNumber and a value of 20
in SecondNumber . The IF Statement is trying to test what's in these
numbers:
If FirstNumber = 10 Or SecondNumber = 20 Then
The valid numbers are 10 and 20. We don't really care if FirstNumber
AND SecondNumber hold 10 and 20. Just as long as at least one of them
holds the correct number, then that's OK.
Run your code and you should see the first message box display, "Valid
Number". Now change your code so that FirstNumber holds a value of 30.
Run the programme again and you'll find the first message box still
displays. However, change the value of SecondNumber to 40 and now
neither number holds the correct value. In which case, the second message
box will display when the programme is run.
Nested If
You can nest one (or even more than one) If Statements inside another.
Examine the following code:
An Integer variable has been set up called score. This has been set to a
value of 27. The first If Statement, the outer one, is this:
If score > 20 And score < 30 Then
So we want to test score to see if it's greater than 20 AND less than 30. If
it's not, then we have an Else part:
Else
MsgBox "Not between 20 and 30"
However, If score is indeed between 20 and 30 then the outer If Statement

evaluates to TRUE. In which case, the code that gets executed is another If
Statement. This one:
If score < 25 Then

MsgBox "A-"
Else
MsgBox "A+"
End If
This new If Statement checks to see if score is less than 25. If it is, then we
know that the variable called score is between 20 and 25. A score of
between gets the message ""A-". Any other result and we know that score is
between 25 and 30. In which case, the message is "A+".
Nested If Statements are a great way to narrow down your search for a
particular value. They can be tricky to use, though. But stick with them and
your programming skills will come on a treat!
Let's now take a look at some more practical ways to use the lessons you
have learned in this and previous sections.
VBA PRACTICE 1
Suppose we had a spreadsheet with a person's name in cell A1 and a score
in cell B1. We'd like to examine this score and see if it falls within a certain
range. If it's 85 or above, for example, we'd like to award a grade of "A".
We want this grade to appear on cell C1. As well as placing a grade in cell
C1, we want to change the background colour of the cell. So we want to go
from this:
to this:
We want this to happen on the click of a button. How can we do all this
with VBA code?
Let's start by breaking the problem down. Here's what will happen:
1. Click inside cell B1

2. Get the value of cell B1
3. Use an If Statement to test the value of cell B1
4. If it's 90 or greater then do the following:
a. Use an Offset to reference the cell C1
b. Using the offset, place the text "A" in cell C1
c. Use the offset to colour the cell C1
d. As a bonus, we can also centre the text in cell C1
To make a start, go back to your spreadsheet and type a name in cell A1

(the name can be anything you like). Type a score of 90 in cell B1.
Now go back to your coding window. Create a new Sub and call it
SetGrades . As the first line of your code, set an integer variable called
score :
Dim score As Integer
What we want to do is to place the value from B1 on our spreadsheet inside

of this score variable.
To get a value we can from a cell, you can use ActiveCell.Value. This goes
on the right of an equal sign:
score = ActiveCell.Value
The ActiveCell is whichever cell is currently selected.
The Value refers to whatever you have typed inside the cell. This Value is
then assign to the score variable. Remember: anything you type to the right
of an equal sign is what you want to store. The variable on the left of the
equal sign is where you are storing it.
But your coding window should now look like this:

Now that we have a score from the spreadsheet, we can test it with an If
Statement. We want to check if it's in the range 90 to 100. The If Statement
to add to your code is this:
If score >= 90 And score <= 100 Then

End If
You should know what the above does by now. If not, revise the section on
conditional and logical operators.
If the score is indeed greater than or equal to 90 and less than or equal to
100 then the first thing we need to do is place an "A" in cell C1.
ActiveCell Referencing
When you use ActiveCell you can point to another cell. You do the pointing
with Row and Column numbers. These are typed between round brackets.
For example, to point to the currently ActiveCell (the cell you have clicked
in to select) the numbers you need to type are 1, 1:
ActiveCell(1, 1).Value
If you want to move one column over from where you are (1, 1) then you
add 1 to the Column position:
If you wanted to move one column to the left of where you are, you deduct
1:
To move two columns to the left, you’d need a minus number:
ActiveCell(1, -1).Value
You can move up and down the rows in a similar way – just add or deduct
from the first 1 between round brackets.
We want to type an "A" in cell C1, which is one column to the right of the
ActiveCell. The code to do that is this:
ActiveCell(1, 2).Value = "A"
So we're storing "A" into the Value property of ActiveCell (1,2).
Your coding window should now look like this:
You can test it out at this stage. Go back to your spreadsheet. Add a new
button and select SetGrades from the Assign Macro dialogue box. Change
the button text to Set Grades . Now click inside your B1 cell, the cell with
the score of 90. This will be the ActiveCell referred to in the code. When
you click your button, the letter "A" will appear next to it, in cell C1:
There are only two things left to do, now: change the background colour of
cell C1 from white to green, and centre the text.
Changing the Background Colour of a Cell

As well as ActiveCell having a Value property, it also has an Interior.Color
property, and an Interior.ColorIndex property. You can use either of these
to set the background colour of a cell.
If you want to use Interior.Color then after an equal sign, you need to
specify an RGB colour:
ActiveCell(1, 2).Interior.Color = RGB(0, 255, 0)
RGB colours use the numbers 0 to 255 to set a Red, a Green, and a Blue
component. If you want full Red, you set the R position to 255 and the
Green and Blue parts to 0:
RGB(255, 0, 0)
If you want full Green you set its position to 255 and switch the other two
positions to 0:
RGB(0, 255, 0)
Likewise, Blue has 255 in its position and 0 in the R and G positions:
RGB(0, 0, 255)
If you want White, you switch all positions to 255:

RGB(255, 255, 255)
If you want Black, you switch all the positions to 0:

RGB(0, 0, 0)
You can have mixture of colours by setting the various positions to any
number between 0 and 255:
RGB(255, 255, 0)
RGB(100, 100, 255)
RGB(10, 10, 100)
The Interior.ColorIndex property, on the hand, uses a single number to

return a colour:
ActiveCell(1, 2).Interior.ColorIndex = 1
The numbers are built-in constants. This means that the number 1 stands for
Black, the Number 2 for White, the number 3 for Red, and so on up to a
value of 56.
The problem with using ColorIndex, though, is that the index numbers don't
really correspond to a colour – the index number is simply the position of
the colour in the Excel colour palette. So the first colour in the palette is
index 1, the second colour index 2, the third colour index 3, etc. ColorIndex
4 is a green colour at the moment. But if Microsoft reordered its colour
index, the new colour at position 4 might end up being red!
Add the following line to your code:
If you like, though, try a ColorIndex instead:
ActiveCell(1, 2).Interior.ColorIndex = 4
Or like this:
If you want to clear a background colour from a cell you can use the
xlColorIndexNone constant:
ActiveCell(1, 2).Interior.ColorIndex = xlColorIndexNone
Cell Content Alignment

You can align data in a spreadsheet cell with the properties
HorizontalAlignment and VerticalAlignment . After an equal sign, you
then type one of the following constants for HorizontalAlignment:
xlCenter
xlLeft
xlRight
For VerticalAlignment the constants are these:
xlBottom
xlCenter
xlTop
So to align the contents of your cell in the centre, the code would be this:
ActiveCell.HorizontalAlignment = xlCenter
If you wanted to right-align the contents, the code would be this:
ActiveCell.HorizontalAlignment = xlRight
If you only wanted bottom-left for your cell alignment, you only need a
vertical alignment of bottom:
ActiveCell.VerticalAlignment = xlBottom
The table below shows all the various alignment position you can have with
VBA. The table should be seen as a single cell:
Add one of the alignment options to your own code. Try the following:
ActiveCell. HorizontalAlignment = xlCenter
But the whole of your code should now look like this:
Click your button and try it out. When the code is run, your spreadsheet
should look like ours below:
VBA PRACTICE 2
Suppose we added another student to the spreadsheet above. We want to set
a grade for a score of 35 or below. The grade will be an "F". However,
because this is such a low score, we want to highlight the student's name,
the score and the grade. We want to colour all three cells red, so we can
quickly see that this student is failing and needs extra attention. When a
button is clicked, we want to go from this:
To this:
How can we do all this with VBA?
The first thing to do is the grade. This is more or less the same as before. In
fact, you can add an ElseIf part to the code you already have. We want to
test for a score of 0 or greater to a score of 35 or lower. The ElseIf part
would be this:
ElseIf score >= 0 And score <= 35 Then

The code to place a value of "F" in the cell to the right of the ActiveCell is
also about the same as before:
ActiveCell(1, 2).Value = "F"

ActiveCell(1, 2).HorizontalAlignment = xlCenter
So add that code to your own SetGrades Sub and it will look like this:
This code colours the cell to the right of the ActiveCell. The only thing left
to do is to colour the ActiveCell itself, and the cell to the left of the
ActiveCell. This is slightly trickier.
If the ActiveCell is cell B2 then the range of cells we're trying to colour is
cells A2 to B2:
Range("A2:B2")
Except, we don't want to specifically name the cells, otherwise we couldn't

reuse the Sub further down, for student Jack, say, on row 3. So we need a
way to refer to our two cells based on the ActiveCell. We can use Offset
with ActiveCell.
ActiveCell.Offset(, -1)
Notice the comma between the round brackets of Offset. With Offset, you
type the Row number first then the Column number. If you don't want to
change the Row number then you can leave it blank. Type a comma, and
then the column number you want to change. In the code above, we're
pointing to 1 column to the left of the ActiveCell (-1). For us, this will get
us the "A" column
To get to the B column, we'd need this:
ActiveCell.Offset(, 0))
We can then wrap all that up in the Range object (one line of code):
Range(ActiveCell.Offset(, -1), ActiveCell.Offset(, 0))
The first ActiveCell code between the round brackets of Range is for the
first cell of the range we want to highlight. After a comma, we have the
second ActiveCell code. This refers to the end of the range we want to
highlight.
Now that we have the correct range of cells, we can add the interior colour
part (one line of code):
Range(ActiveCell.Offset(, -1), ActiveCell.Offset(, 0)). Interior.Color = RGB(255, 0, 0)
This line is a lot longer and more complicated than any you've met before.
So don't worry if you don't completely understand it at first glance. Try it
out though, and get a feel for how it works.
Here's the complete code, though:
To wrap up this section, we'll take a look at another option you have when
you need some Conditional Logic – Select Case.
Select Case
Another way to select a value from a list of possible values is with a Select
Case statement. For example, suppose you had three colours, Red, Blue and
Green. You want to test which value a user has selected. You could use an
IF Statement. Like this:
If User_Choice = "R" Then

MsgBox "Red"
ElseIf User_Choice = "G" Then
MsgBox "Green"
ElseIf User_Choice = "B" Then
MsgBox "Blue"
Else
MsgBox "None"
End If
But you can use Select Case for this instead. Examine the following:
Select Case User_Choice

Case "R"
MsgBox "Red"
Case "G"
MsgBox "Green"
Case "B"
MsgBox "Blue"
Case Else
MsgBox "None"
End Select
A Select Case statement begins with the words Select Case . You then type
the thing you're testing for. This can be a variable, a number, text in double
quotes, and even a built-in Excel function. For each possible answer, you
then have one Case . Each Case is evaluated to TRUE or FALSE. If it's
TRUE then the code for that Case gets executed. Only one Case per Select
Statement will get executed. The whole thing ends with the words End
Select .
In the code above, we're saying "If it's the Case that the variable
User_Choice contains the letter R Then display a message box saying Red."
If it's not TRUE then VBA drops down to the next Case and check if that's
TRUE. You can have an optional Case Else to catch anything else that the
value at the beginning could be. So if User_Choice does not contain an R, a
G or a B then we display a message saying "None".
You can check for more than one value by using the word To . For example,
if you want to check a range of ages you can do it like this:
Case 0 To 35
Now, VBA will check if the value is 0 to 35.
You can also check several values at once. Simply separate each one with a
comma:
Case 10, 20, 30, 40
VBA will now only see the above Case as TRUE if the value you're
selecting for is 10, 20, 30, or 40. Any other values and this Case will
evaluate to FALSE.
Let's have a look at a more practical example, though. We'll go back to our
student score spreadsheet.
VBA PRACTICE 3
Add another name to your spreadsheet, in cell C1. In cell C2, enter a score.
What we'll do is to put a grade in cell C3 and a comment in cell C4. We'll
use Select Case, this time.
Add another Sub to code. Call it SelectCaseTest . We start the coding the
same as before:
Dim score As Integer

score = ActiveCell.Value
We then start our Select Case:
Select Case score
The Select Case will examine what is in the variable called score . The first
Case to evaluate as either TRUE or FALSE is this:
Case 0 To 35
This says, "is it the Case that score has a value from 0 to 35?".
If the score is indeed 0 To 35 then we can execute some code:

ActiveCell(1, 3).Value = "Terrible - needs attention"
The first two lines are the same as before. The third line puts the text
"Terrible - needs attention" in the cell next to the grade.
We can fill out the possible score values in the same way as for the first
Case. Here's the full code:
Try it out. Put another button on your form. Select your new Sub from the
Assign Macro dialogue box. Enter a name in cell C1. Enter a score in cell
C2. Now click on cell C2 to make it the ActiveCell. Click your button and
you should go from this:
To this:
With ... End With

In a previous lesson, you had code like this:

ActiveCell(1, 3).Value = "Terrible - needs attention"
We were accessing various properties of the ActiveCell. In the code above,

we have ActiveCell three times. However, you can speed up your
subroutines by using the repeated object only once, on the first line. You
then type the property you need after a dot. The syntax is this:
With object
.property
End With
As an example, suppose we want to change various aspects of the

ActiveCell. We want to the font name, the font size, the boldness, the
italics, etc. We could do it like this:
ActiveCell.Font.Bold = True
ActiveCell.Font.Color = vbBlue
ActiveCell.Font.Name = "Arial"
ActiveCell.Font.Size = 22
ActiveCell.Font.Italic = True
But notice the repetition here. We've used ActiveCell.Font five times. By
using a With Statement, we can just type the ActiveCell.Font once. Like
this:
With ActiveCell.Font
.Bold = True
.Color = vbBlue
.Name = "Arial"
.Size = 22
.Italic = True
End With
So you start with the word With . After a space, you type the name of the
object you want to manipulate. We want to manipulate the Font property.
This has lots of properties of its own. These ones:
Type a dot and then the name of the Font property in the above list that you
want to change. The equal sign and the value are used in the normal way.
The whole thing end with the words End With .
With Statements are quite intuitive, so we don't really need to say too
much about them. But just remember: if you're typing the same object over
and over, you might do better to use a With … End With statement.
OK, that's enough of Conditional Logic. We'll move and take a look at
another variable type that's quite common – As String .
WORKING WITH STRINGS
OF TEXT IN EXCEL VBA
One variable type that we haven't touched upon yet is the As String type.
As its name suggest, it is used to hold strings of text. You'll need to work
with strings of text quite a lot in Excel VBA, so it's well worth getting the
hang of.
Setting up a variable to hold text is quite straightforward. You simply Dim a

variable As String :
Dim MyString As String
To store text inside of your variable you need to surround it with double
quotes:
MyString = "Some text"
Even if you place numbers between double quotes they still gets treated as
text and not Integers:
MyString = "25"
The above line means store 25 as text, and NOT store the number 25.
You can place text into a cell on your spreadsheet:

MyString = "Some text"
ActiveCell.Value = MyString
And you can get text out of cell on your spreadsheet:

MyString = ActiveCell.Value
Quite often, though, you'll need to do something with the text that you get
from a cell on a spreadsheet. For example, you may need to take a full name
from one cell and place the first name in another cell and the surname in yet
another. To do things like this, you need to know how to use Excel VBA's
built-in string functions. The functions we'll study are these:
LCase, UCase
Trim and Len
Space
Replace
StrReverse
InStr, InStrRev
Left, Right
Mid
As you can see, there are quite a lot of them. And this is not even a full list!
CHANGING CASE
Two of the easier in-built functions to get the hang of are LCase and UCase.
As you might expect, these are used to changes letters into lowercase or
uppercase characters. Let's see how they work.
To try these out, create a new blank workbook in Excel. Save the file as
string_functions.xlsm . Enter some headings in cells A1, B1, and C1.
Enter: Text, LCase, and UCase. Enter a name in cell A2, anything you like.
Your spreadsheet might then look like this:
Click the Developer ribbon at the top of Excel, then click the View Code
item on the Controls panel.
In the VBA Editor, if you can't see a blank code window for Sheet1, double
click Sheet1 in the Project Explorer on the left. (If you can't see the Project
Explorer, click View > Project Explorer from the menu at the top.)
Create a new sub in your blank Sheet1 coding window. Call it ChangeCase
. To get the text out of cell A2 on your spreadsheet, add the following lines:
Dim FullName As String

FullName = Range("A2").Value
All this code does is to set up a variable called FullName . The variable is
declared with As String . To place something into this variable, we've used
Range("A2").Value.
Your code should now look like this:
To use the lowercase function, you only need the following on the right of
an equal sign:
LCase ( Text_To_Convert )
Whatever you're trying to convert goes between the round brackets of the
LCase function. The text you're trying to convert can be direct text
surrounded by double quotes, or a variable that holds some a string of text.
We want to place the converted text in cell B2, just under the LCase
heading. All we need to do is use offset with our A2 Range:
Range("A2").Offset(, 1).Value = LCase(FullName)
Range("A2").Offset(, 1) moves us one column to the right of cell A2. We

then access the Value property. To the right of the equal sign we have out
LCase function. VBA will convert whatever we have in the variable called
FullName to lowercase, and use that as the Value for cell B2.
Add the line to your own Sub and your code will look like this:
Click anywhere inside of your Sub to try it out. Now press F5 on your
keyboard to run the code. Switch to your spreadsheet and you should see
this:
The code to convert the name to uppercase is very similar. It's this:
Range("A2").Offset(, 2).Value = UCase (FullName)
Only two things have change, here. For the Offset, we have a 2 instead of a
1. This moves us two columns to the right of cell A2. The function that
converts to uppercase is UCase. It's used in exactly the same way as LCase.
Add the line to your own code. Run your Sub and your spreadsheet will
look like this:
So we have now converted the name in cell A2 to lowercase and uppercase.

Notice that the name in A2, David Gilmour, is in Proper Case. This is when
you capitalise the first letter of each word. Sadly, Excel VBA doesn't have a
nice, easy function to convert to Proper Case, so there's no PCase.
There is, however, a Worksheet function called Proper. To use it, try the
following code (three lines):

FullName = "DAVID GILMOUR"
Range("A2").Offset(, 3).Value = Application.WorksheetFunction.Proper(FullName)
The code that converts to Proper Case is this:
Application.WorksheetFunction.Proper(FullName)
Application is a top-level object, meaning the whole of Excel.

WorksheetFunction is used to access Excel's in-built function. One of
these functions is Proper . In between the round brackets of Proper, you
type the variable you're trying to convert. As well as typing a variable
name, you can type direct text surrounded by double quotes.
TRIM AND LEN
These two string functions are not related, but they are easy enough to use.
We'll use them together in this next example.
The Trim function is used to trim unwanted white space for text. So if you
had the following string:
" some text "
Using Trim on it would remove the spaces to leave this:
"some text"
The Len function is used to get how many characters a string has.
Create another Sub in your code window. Call it TrimAndLen . Add the
following code:

Dim LengthFullName As Integer
FullName = " David Gilmour "
LengthFullName = Len(FullName)
MsgBox LengthFullName
We've set up two variables here, one called FullName and one called
LengthFullName . The LengthFullName variable has been set up as an
Integer. Into the variable called FullName we've stored the text " David
Gilmour " . But notice where the double quotes are. We have three blank
spaces to the left of the name and three blank spaces to the right of the
name.
The fourth line is this:
LengthFullName = Len(FullName)
We're using the Len function to the right of an equal sign. In between the
round brackets of Len, we have our FullName variable. The Len function
will count how many characters are in the text that we've stored inside of
FullName . When VBA has an answer to the Len function it stores it into
the variable called LengthFullName . Because the Len function counts
characters, the value returned will be an Integer.
Run the code and you'll find that the message box displays the number 19.
However, the name David Gilmour is only 12 characters long. Add the
space and it 13 characters. The message box is displaying 19 because it has
counted the extra space at the beginning and the end.
To remove the space, use the Trim function:
FullName = Trim(" David Gilmour ")
The variable or direct text you're trying to trim goes between round
brackets. VBA will then remove any white space from the front and the end
of your string.
Run the code again and the message box displays a value of 13.
SPACE
You might actually want to pad out a string with blank space. If so, the
Space function is the one you want. In between the round brackets, you type
a number. This number is how many space characters you want. Here's
some code to illustrate this:
FullName = "David Glimour"

MsgBox Len(FullName)
FullName = Space(5) & FullName

MsgBox Len(FullName)
The first message box display a value of 13, which is how many characters
are in the name David Gilmour. The second message mob displays a value
of 18, the 13 original characters, plus 5 added to the start of the name.
We could have added 5 blank spaces to the end of the name with this:
FullName = FullName & Space(5)
You might be confused about the use of the FullName variable twice, here.
But start after the equal sign and it will make sense. We have this after the
equal sign:
FullName & Space(5)

This says, "Take whatever is in the variable called FullName and join 5
space characters to it." (The & symbol is used to join things together,
remember. This is called concatenation.)
Once VBA has joined the text and the space, it needs to store it somewhere.
Whatever is to the left of the equal sign is the place where it will be stored.
To the left of the equal sign, we have the FullName variable again.
Whatever was previously in the variable will be replaced. It will be replaced
by the value from the right of the equal sign, which was the name plus 5
characters.
REPLACE
The Replace function is used to replace text in a string with something else.
Suppose, for example, that you have a misspelled word in cell A5. You can
use Replace to change the incorrect letters with the correct ones.
To try it out, add two more headings in cells A4 and B4. Type the heading
Original in cell A4 and the heading Replace in cell B4. Now click inside
cell A5 and type the misspelled word Micrasaft. Your spreadsheet should
now look like this:
To use the Replace function, you need at least three things between it round
brackets:
Replace( string_to_search , string_to_replace , replace_with )
The first thing you need is a string of text to search. Next, you specify what
it is you're searching for. This is the character or characters you're going to
replace. The third thing you need is the new character or characters.
With the Replace function you also have an optional three things you can
specify. These are:
start, count, compare
The optional parameters go after the third item in replace, with each being
separated by a comma:
Replace( string_to_search, string_to_replace, replace_with, start, count, compare )
The start parameter is where in the string you want to start search from.
The default is character 1, which is the first character in the string. If you
want to start from a position in the string other than the first character then
you need to type your start number here.
The count parameter is how many occurrences you want to replace. The
default is to replace every occurrence of replace_with . If you only want to
replace, say, the first two occurrences then type the number 2 here.
The compare parameter has three options: vbBinaryCompare,
vbTextCompare, vbDatabaseCompare. Don't worry about compare, as it's
rarely used.
As an example, add a new Sub to your coding window. Call it

ReplaceExample . Add the following code for the new Sub:
Dim OriginalText As String

Dim CorrectedText As String
OriginalText = Range("A5").Value
CorrectedText = Replace(OriginalText, "a", "o")
Range("A5").Offset(, 1).Value = CorrectedText

We have two String variables set up here, OriginalText and CorrectedText
. The value for the OriginalText variable is coming from the Range A5 on
the spreadsheet. We then have this:
CorrectedText = Replace(OriginalText, "a", "o")
So we have our Replace function on the right of the equal sign. The first
item between the round brackets of Replace is the variable name
OriginalText . This is the text that Replace will be searching. The next item
is the character that is incorrect, the letter "a". The "a" is surrounded by
double quotes. Finally, we need the new text that we want in the string,
which is the letter "o". All three items are separated by commas.
The final line puts the corrected text into cell B5 on the spreadsheet.
Run your code and try it out. Your spreadsheet should change to this:
You can replace more than one character, if you need to. The following
code replaces the misspelled Microsft with Microsoft (one line):
CorrectedText = Replace(OriginalText, "sft", "soft")
You can replace spaces in text by typing two double quotes. The first set of
double quotes will have a space between them while the second set has no
space. For example:
CorrectedText = Replace("M i c r o s o f t ", " ", "")
This time, the word Microsoft has a space after every letter. We want to
remove the space. The second parameter of the Replace function is two
double quotes with a space between them. The third parameter of the
Replace function is two double quotes with no space between them. Two
double quotes together mean "no characters".
STRREVERSE
This one is quite easy to use. As its name suggest StrReverse reverses the
letters in a string of text. Here's some code to try:
Dim OriginalText As String

Dim ReversedText As String
OriginalText = "some text"

ReversedText = StrReverse(OriginalText)
MsgBox (ReversedText)
When the code is run, the message box will display "txet emos", which is
"some text" reversed.
INSTR, INSTRREV
InStr is short for InString. This string is used to search for one string inside
another. You need at least two items between the round brackets of the InStr
function, the text to search and what you want to find. VBA will then give
you an Integer back in return. This number will be 0 if the string is not
found. If the string is found then you get the location of the start of the
string you were search for. Here's an example for you to try:
Dim Email As String

Dim Location As Integer
Email = "myaddress@myisp.com"
Location = InStr(Email, "@")
MsgBox Location
We've set up two variables. One is a String variable that holds an email
address, and the other is an Integer called Location. The InStr line is this:
Location = InStr (Email, "@")
The first item between the round brackets is our Email variable. The second
item is what we want to search for in the email address. If the @ sign is not
in the Email variable then VBA will place a 0 in the Location variable.
When the above code is run the message box will display the number 10.
That's because the @ sign is the tenth character in the email address string.
Now delete the @ sign from the Email line:
Email = "myaddressmyisp.com"
Run the code again and the message box displays a value of 0. You can use
this for a basic test on email addresses:
Dim Email As String

Dim Location As Integer
Email = "myaddressmyisp.com"
Location = InStr(Email, "@")
If Location = 0 Then
MsgBox "Not a valid email address"
Else
MsgBox "email address OK"
End If
Two optional parameters for InStr are start and compare :
InStr(start, Text_To_Search, Find, comapre)
If you miss out the start number then InStr searches from the beginning of
your string. If you type a number for start then InStr starts the search from
that number in the string.
The compare parameter has four options: vbUseCompareOption,

vbBinaryCompare, vbTextCompare, vbDatabaseCompare. Don't worry
about compare, as it's rarely used.
Similar to Instr is InStrRev . The Rev stands for Reverse. This function is
the same as InStr but the difference is that InStrRev starts the search from
the end of the string rather than the beginning.
LEFT, RIGHT
The Left and Right functions are used to chop characters from a string. Use
Left to chop characters from the start of the string; use Right to chop
characters starting from the end of the string. In between the round brackets
of Left and Right you type the number of characters you want to chop. If
you miss out the number of characters to chop then Left and Right extract
just one character from the start or end of the string. Some example might
clear things up.
Create a new Sub and try this code out:
Dim Email As String
MsgBox Left(Email, 9)
MsgBox Right(Email, 9)
The first two lines just set up a String variable and place an email address in
the Email variable. The third line is a message box that uses the Left
function:
MsgBox Left(Email, 9)
When you run the code you'll see that the message box displays the first 9
characters of the email address, everything to the left of the @ sign.
The fourth line is this:
MsgBox Right(Email, 9)
The Right function will display 9 characters starting from the final character
in the email address, everything to the right of the @ sign.
That's fairly straightforward, we're sure you'll agree. But now for a more
complex use of Left and Right.
Suppose you have a full name in cell A1 in this format:
David Gilmour
However, suppose you want to have the surname first then the first name.
This format:
Gilmour, David
You can use Left, Right and the InStr Functions to achieve this.
Create a new Sub and call it LastFirst . Now set up four variables, three
Strings and an Integer:

Dim FirstName As String
Dim LastName As String
Dim SpacePos As Integer
Place the full name in the FullName variable:

FullName = "David Gilmour"
Now use InStr to locate the position of the space in the name:
SpacePos = InStr(FullName, " ")
To get just the first name you can start at the beginning of the full name and
go up to the SpacePos minus 1:
FirstName = Left(FullName, SpacePos - 1)
The reason to deduct 1 from the SpacePos variable is because the InStr
function will return the position of the space, a value of 6 for our name. The
final character of the first name, however, is 1 less than this, as David only
has 5 characters in it.
To get the last name, we need something slightly different. The starting
position is the length of the full name minus the length of the first name.
This will get us the correct number of characters to grab starting from the
right of the name. The code is this:
LastName = Right(FullName, Len(FullName) - Len(FirstName))
So as the final parameter of Right we have this:
Len(FullName) - Len(FirstName))
This uses the Len function to get the length of the FullName and FirstName
variables.
Finally, display the results in a message box:

MsgBox (LastName & ", " & FirstName)
We have the LastName variable first and then the FirstName . The two are
separated by concatenation symbols (&). We also need a comma, and we
have this in double quotes so that VBA sees it as text. So we're saying,
"Join together the Last Name, then a comma, then the First Name".
The whole of your code, then, should look like this:
Run your code and you should see this message box:
Click OK to return to your code. Now type a new name. Change this line,
for example:
FullName = "David Gilmour"
to this:
FullName = "William Shakespeare"
Run your code again and the message box will display this:
That final exercise illustrates that the more string functions you know and
are comfortable with the more you can achieve in your programming.
(NOTE: There is an easier way to do that exercise by using the Split

function. But this uses something called an array. You haven't covered
arrays yet, which is why we're doing it the hard way!)
MID
The final String function we'll look at is the Mid. This function is used to
grab characters from a string of text. It has three parts:
Mid( string_to_search , start_position ,

number_of_characters_to_grab )
The first part is the string you want search. This can be a variable or direct
text between double quotes. The second part is where in the string you want
to start grabbing characters from. The final part is how many characters you
want to grab.
To demonstrate the Mid function, examine the following code:
Dim Email As String

Dim GrabbedChars As String
GrabbedChars = Mid(Email, 16, 4)
MsgBox GrabbedChars
We've set up two String variable here, one called Email and one called
GrabbedChars . We've stored an email address in the Email variable. Then
comes our Mid code:
GrabbedChars = Mid(Email, 16, 4)

The text we're searching is in the Email variable. We want to start grabbing
characters from position 16 in the string. The numbers of characters we
want to grab is 4.
When the programme is run, the message box will display ".com ".
The Mid function is very useful in loops, as it allows you to examine one
character at a time from a string of text.
Speaking of loops, they're up next.

PROGRAMMING LOOPS
IN EXCEL VBA
Consider the following code:

MyNumber = 1 + 2 + 3 + 4 + 5 + 6 + 7 + 8 + 9 + 10
All the code does is to add up the numbers 1 to 10. The result is then stored
in the variable MyNumber .
And that's nice an easy. But suppose you want to add up the numbers 1 to a
1000. You wouldn't have to type them all out, surely? Thankfully, you don't
have to, as there's a handy programming tool at your disposal – the loop.
A loop is something that goes round and round. Programming loops go

round and round until you tell then to stop. You set a starting number for
your loop, and end condition, and a way to get from the starting number to
the end condition. In VBA, there are four types of loop to choose from: For
loops, For Each loop, Do Loops, and While loops. The first loop we'll look
at is the For Loop.
FOR LOOPS
VBA is a sequential programming language. What this means is that each
line of code gets executed from top to bottom until there are no more lines
of code to read. If you want to go back up then you have to force the
programme to do so. You do the forcing with a loop. The reason why you'd
want to force the programme to go back up is to execute a line or lines of
code repeatedly.
The most common type of loop is called a For Loop. Don't worry about the
name of the loop. Just bear in mind that a For Loop goes round and round
until it meets an end condition. Once the end condition is met then the
programming flow will continue downward, in its natural direction.
All this may be very confusing, so let's clear things up with an example.
Create a new, blank Excel Workbook for this. Save it with the name
loops.xlsm . Now click the Developer ribbon at the top of Excel, and click
View Code on the Controls panel. Ij the Sheets1 coding window, type the
following:
Sub LoopExample()
Dim StartNumber As Integer

Dim EndNumber As Integer
EndNumber = 5
For StartNumber = 1 To EndNumber

MsgBox StartNumber
Next StartNumber
End Sub
The first two lines just set up two Integer variable, one called StartNumber
and one called EndNumber . We've stored a value of 5 in the EndNumber
variable. Then comes the first line of the loop:
You start with the type a loop you want, which is a For loop in this case.
Next, you need a start point for your loop. We want to start the loop at the
number 1. Notice that we now store a value in the StartNumber variable:
For StartNumber = 1
We can't do it like this below as we'd get an error:

StartNumber = 1
EndNumber = 5
For StartNumber To EndNumber
VBA needs you to assign a value to a variable as the starting point for you
loop. This value can be any number you like. We've started at 1.
The next thing you need is the word To . This just mean, "Your starting
number TO which end number?".
After the word To, you need an end number or condition. We've set up an
end number of 5. So our loop goes from 1 to 5.
After the end condition, you can add an optional Step value. The default is
for VBA to go from your start number to the end number in steps of 1 each
time round the loop. If you wanted to go in steps of any other number, say
2, you'd add the following:
For StartNumber = 1 To EndNumber Step 2
You can add a negative value, here, if you want:
For StartNumber = 10 To EndNumber Step -1
Now the loop starts at 10, and end with 5. To get from 10 to 5 we need to go
down. The line above is going from 10 to 5 in steps of -1 each time round
the loop.
To recap, then, the first line of your loop is where you set a start number
and end number. This will tell VBA how many times you want to go round
and round.
The next line of our loop is this:
MsgBox StartNumber
This is just a simple message box that displays the value of the
StartNumber . We'll come back to this.
The final line of a For loop is the word Next followed by the variable name
you typed after the word For on the first line. For us, this was
StartNumber . What this tells VBA to do, though, is to add 1 to whatever
number is in your variable.
We began with a value of 1 in out StartNumber variable. The first time

round the loop, VBA sees the words Next StartNumber and adds 1 to the
variable. The programming flow is then forced back up to the For line,
ready to repeat the whole process.
In between the For line and the Next line is where you type the code for
your loop. Our code is just a message box that display whatever is in the
StartNumber variable.
Run your code and you'll see the message box appear 5 times. Each time it
appears, the number in the message box changes by 1.
OK, let's do something a bit more practical than displaying a message box
over and over again. Change your code to this, (the new lines are in bold):

Dim answer As Integer
EndNumber = 5

answer = answer + StartNumber
Next StartNumber
MsgBox answer
The whole of your code should look like this:

We've added a new Integer variable called answer . The message box has
been moved to the end. Between the For line and the Next line we have
some new code. This:
To understand this line, start after the equal sign:
answer + StartNumber
This says, "Add together whatever is stored in the variable called answer
and whatever is stored in the variable called StartNumber . However, we
haven't stored anything in the answer variable yet. So what value does it
hold? If you don't store a value in an Integer variable then it gets set to 0.
The StartNumber variable is 1 the first time round the loop. So the sum on
the right of the equal sign is really this:
0+1
When VBA has finished calculating this it needs to store the result
somewhere. That "somewhere" is whatever you have to the left of the equal
sign. We have the variable called answer to the left of the equal sign. So
this is where VBA stores the result of 0 + 1. In other words, the answer
variable will be overwritten with the new value.
The next time round the loop the two variables to the right of the equal sign
will hold the following values:
1+2
The third time round the loop the two variables to the right of the equal sign
will be this:
3+3
The fourth time round it will be:
6+4
And the fifth time round, the two variables will be:
10 + 5
Here's a table of those values: (Notice the right-hand column in our table
below, for StartNumber . This shows that the values in StartNumber
increase by 1 each time round the loop.)

1 0 1
3 1 2
6 3 3
10 6 4
15 10 5
But by going round the loop 5 times, we've added up the numbers from 1 to
5. This gives a value of 15. Run your programme and test it out. The
message box should display an answer of 15.
Now move the message box from the end of the code to inside the loop, just
before Next StartNumber:

MsgBox answer
Next StartNumber
Now run the code again. The message box displays 5 times, once for each
time round the loop. This time, the values will be the same as from our table
above, from the left-hand column under answer = .
If you've never done any programming before then know that loops are one
of the harder concepts to get the hang of. So don't despair of you don't
understand them straightaway. Just go over the material above and it will
sink in. Eventually!
THE CELLS PROPERTY
As well as referring to cells on a spreadsheet with Range you can use Cells
. The Cells property has an Item property that you use to reference the cells
on your spreadsheet:
Cells.Item(Row, Column)
The Row is always a number. But the column can be a number or letter:
Cells.Item(1, 1)
Cells.Item(1, "A")
Both the lines above refer to the cell A1.
You can shorten this even further and get rid of the Item property
altogether:
Cells(1, 1)
Cells(1, "A")
The reason why we're discussing the Cells property is because it's very
useful in programming loop. That's because you can replace the numbers
between round brackets with a value from your loop. Let's clear that up.
Create another Sub to your coding window. Call it CellsExample . Add the
following code:
EndNumber = 5
Cells(StartNumber, "A").Value = StartNumber
Next StartNumber
The only difference between this For loop and the last one is this line:
Cells(StartNumber, "A").Value = StartNumber
Because the StartNumber gets 1 automatically added to it each time round

the loop, we can use it between the round brackets of Cells . So the code is
doing this:
Cells(1, "A").Value = 1
Or we could keep the Row number the same and change the column
number:
Cells(1, StartNumber).Value = StartNumber
Or we could do both:
Cells(StartNumber, StartNumber).Value = StartNumber
The above line will get us a diagonal line form cell A1 to cell E5, which
each cell filled with the numbers 1 to 5.
The point is, though, that we can manipulate the cells on a spreadsheet by
using just a number from our loop and the Cells property.
You can use Offset with cells, too. In this next exercise, we'll do the 10
times table. It will look like this:
Create a new Sub and call it TimesTable . Set up the following four Integer
variables, first:

Dim answer As Integer
Dim TimesTable As Integer
Now put a value of 10 in two of them:
EndNumber = 10
TimesTable = 10
This time, our end number is 10. We've also specified 10 for the
TimesTable variable.
The first line of the For loop is as before:
And so is the end of the loop:

Next StartNumber
In between For and Next the first line is this:
answer = StartNumber * TimesTable
To work out what this does, start on the right of the equal sign:
StartNumber * TimesTable
The TimesTable variable will always be 10. But the StartNumber variable
changes each time round the loop. The first time round the loop we'll have
this:
1 * 10
The second time round the loop we'll have this:
2 * 10
The third time it's:
3 * 10
And so on, right up to 10 times 10.

Each time VBA works out the result of the multiplication, it stores it in the
variable called answer .
The next line of the code is this:
Cells(StartNumber, 1).Value = StartNumber & " times " & TimesTable & " = "
It's rather a long line, so let's break it down to see what's happening.
To the right of the equal sign, we have this:
StartNumber & " times " & TimesTable & " = "
We're using three concatenation symbols, here (&). So we're joining four
things together:
StartNumber
" times "
TimesTable
"="
We're joining together: the StartNumber variable, the direct text " times ",
the TimesTable variable, and the direct text " = ".
Once VBA has stitched all this together it will place the result into whatever
is on the left of the equal sign. Which is this:
Cells(StartNumber, 1).Value
So the result of the concatenation is being stored in the Value property of

Cells . The cells we're accessing will change because of what we have
between the round brackets:
StartNumber, 1
StartNumber will change each time round the loop, but the hard-coded 1
means the "A" column. So it will be this, the first few times round:
Cells(1, "A")
Cells(2, "A")
Cells(3, "A")
Cells(4, "A")
The third and final line to add to your For loop is this:
Cells(StartNumber, 1).Offset(, 1).Value = answer
The Offset moves the column over 1 from where we were, which was the A
column. Whatever is in the answer variable is what will be used as the
Value for the cells being referred to with Offset.
The whole of your code should now look like this:
Try it out. When your run the code you should see the same values as ours
(We've formatted the cells slightly):
And there you go – the 10 times table using For loops, Cells, and Offset.
But before we move on, try these exercises.
Exercise
Make one single change to your code to display the 12 times table up to a
value of 120.
Exercise
Make another single change to print out the 12 times table up to a value
240.
THE FOR EACH LOOP
The For Each loop is a special kind of loop that is used to cycle through a
collection. It uses Excel objects, and we'll cover all this later in the course.
THE DO WHILE LOOP
The Do While loop is a lot easier to use than a For loop as you don't need
to set a start condition, just the end condition. Here's the structure of a Do
Loop:
Do While [CONDITION]
Loop
After Do While on the first line, you need a condition to test. This is usually
the same sort of thing you sort with in the Conditional Logic section. For
example:
Do While x < y
Do While counter < 5
Here, the loop goes round and round until the test condition at the start
evaluates to FALSE. As soon as VBA detects that your test condition is
FLASE it will exit the loop and continue on its way.
However, you do need a line of code to increase your loop variable,

otherwise you'll create an infinite loop. In the code below, we're increasing
(incrementing) the counter variable by 1 each time round the loop:

counter = counter + 1
Loop
If we didn't increment the counter variable then there would be no way for
VBA to reach the end condition of "Do while counter is less than 5".
If you like, you can add your condition at the end, just after the word Loop
:
Do
Loop While counter < 5
The difference here is that any code you have for you loop will execute at
least once, if the condition is at the end. If the condition is at the start then
the loop code won't get executed if the condition already evaluate to FALSE
(counter might not be less than 5, in our code above).
Let's try some an example, though. Add a new Sub to your coding window.
Call it DoWhileExample . Now add the following code:
We've set up an Integer variable called counter , and set it to a value of 1.

The Do Loop goes round and round while counter is less than 5. So as not
to set up an infinite loop, we have this line:
If you're having trouble understanding this line, just start to the right of the
equal sign to see what calculation is being performed:
counter + 1
Whatever value is currently in counter will get 1 added to it. But this value
needs to be stored somewhere. The "somewhere" just happens to be the
counter variable again, which is on the left of the equal sign. The old value
inside of counter will be overwritten, to be replaced with the new value
that VBA has just calculated on the right of the equal sign.
The other line in the code is this:
Cells(counter, "D").Value = counter
Because counter changes each time round the loop we can use it in
between the round brackets of Cells , for the Rows value. The Column
we've chosen is the D column.
Run your code and your spreadsheet will now look like this:
So we have the numbers 1 to 4 in the D column.

And here's the times table programme again, but this time using a Do Loop.
Give it a try:
DO UNTIL LOOP
A loop that's closely related to the Do While loop is the Do Until loop. You
use them the same way, except instead of typing While you type Until .
When you're using Until, though, the end condition will change slightly.
Here it is for the Do While Loop:

Loop
And here is the Do Until Loop with a condition on the end.
Do Until counter > 5

Loop
Now we're saying Do Until counter is greater than 5.
If you need to bail out early from any loop then you only need the word
Exit followed by the loop name:
Do Until counter > 5

If counter = 0 Then
Exit Do
Else
End If
Loop
The above applies to For loops, as well.
But that's enough of looping. We'll move on and look at arrays.

EXCEL VBA AND ARRAYS
So far, you have using variables that store one piece of information. You
have stored one Integer value, one Single value, or one string. An array is a
way to store more than one value under the same name. An array looks like
this when it is declared:
Dim MyArray(4) As Integer
The only difference between declaring a variable to be an array and

declaring an ordinary variable is the round brackets after the variable name.
In the code above, the variable name is MyArray . A pair of round brackets
follows the variable name. VBA takes this to mean that you want to set up
an array. The number of values you want to store using your variable name
is typed between the round brackets. However, one important point to bear
in mind is that the first position in the array is 0. So the above array can
actually hold 5 values, 0 to 4.
If you don't want the first position to be 0 then you can declare it like this:
Dim MyArray(1 To 5) As Integer
This time, the first position in the array is 1. The number of values it can
hold would then go from 1 to 5.
To place a value in an array position you do it like this:
MyArray(0) = 10
MyArray(1) = 20
MyArray(2) = 30
MyArray(3) = 40
MyArray(4) = 50
So you type the name of the variable again, followed by a pair of round
brackets. Inside of the round brackets you type the number of the array
position you want to fill. You then assign a value to that position in the
normal way, after an equal sign. In the code above, array position 0 has a
value of 10, array position 1 has a value of 20, and so on.
To get a value back out of any array, it's just like any other variable: put it
on the right of an equal sign:
ArrayValue = MyArray(1)
Notice that you still need the round brackets with a position number
between them. The line above will store whatever value is at position 1 in
the array into the variable to the left of the equal sign.
Let's try some arrays out. You can try these exercises in a new blank
workbook, or on a new sheet if you already have a file opened up in Excel.
If you have a version Excel prior to Excel 2013 then just click Sheet2 at the
bottom. For Excel 2013 users then you'll need to add a new Sheet. Now
return to your coding window. If you can't see the code for Sheet2, double
click it in the Project Explorer on the left: (If you can't see the Project
Explorer , click View > Project Explorer from the menu at the top.)
Create a new Sub in your code window. Call it ArrayExercise_1 . Add the
following code:
Click anywhere inside of Sub and End Sub. Now press F5 to run the code.
You should see the message box display the value at position 1 in the array,
which is a value of 2. Try changing MsgBox MyArray(1) to MsgBox
MyArray(4). Run the code again and a value of 5 will appear in the
message box.
Now try this. Change the message box line to MsgBox MyArray(5). Run
the code again and you'll get an error message. This one:
The Subscript is that number in round brackets. The message is telling you
that your array doesn't have that many positions.
ARRAYS AND LOOPS
Arrays are usually used with loops. This is because it's (fairly) easy to
access each array position in a loop – you just use the loop variable between
the round brackets inside of a hard-code number.
What we'll do now is to store some numbers in an array. We'll then use a
loop to print them out to cells in a spreadsheet.
To test this out, set up another Sub. Call it ArrayExercise_2 . Now type the
following code for your new Sub:
We've set up an Integer array called MyArray . This goes from 1 to 5:
Dim MyArray(1 To 5) As Integer
The reason why we need 1 as the lowest position in our array is because we
want to access rows in a spreadsheet. If we'd gone with the default array
then the first position would be 0. There is no row 0 in a spreadsheet, so this
would have cause errors. (You'll see a way round this in a moment.)
The next 5 lines store the values 10 to 50 into each position in the array.
We then have the For loop. The first line is curious:
For i = 1 To UBound(MyArray)
This sets up a variable called i and stores a value of 1 in it. The lowercase
letter i is a popular variable name in loop code, as it's nice and short. Think
of it as standing for "Initial Value".
After the word "To" we have this:
UBound(MyArray)
The UBound part is short for Upper Boundary. This gets you the highest
number in your array (there's also an LBound to get the lowest value). In
between the round brackets of UBound you type the name of your array.
Notice that you don't need round brackets for your array name here.
The loop, then, goes from 1 to the highest value in the array, which is 5 in
this case. So we're looping round 5 times.
The code for the loop is this:
Cells(i, 1).Value = MyArray(i)
The thing to bear in mind here is that the value of i will change each time
round the loop. By typing the loop variable between the round brackets of
not only Cells but MyArray you can access each row or column on the
spreadsheet and each position in the array. This is a very powerful
technique and one we highly recommend you learn.
The first time round the loop, the values will really be these:
Cells(1, 1).Value = MyArray(1)

The next time round, the values will be these:
The third time round, these:
And so on.
Test your code out. Run your Sub and then have a look at your spreadsheet.
You should see this:
The code has placed each value from the array into a cell on the
spreadsheet.
MULTIDIMENSIONAL
ARRAYS
The arrays above are all one dimensional arrays. It's one dimensional
because there's only one column of items. But you can have arrays with
more one dimension. In theory you could have an array with up to 60
dimensions. However, you'll be glad to know that it's a rare programme that
uses more than 3 dimensions, or even 2 dimensions.
To set up an array with more than one dimension, you simply add a comma
after the first number between the round brackets of your array name, then
add another number. Like this:
Dim MyArray(5, 4) As Integer
Or like this:
Dim MyArray(1 To 5, 1 To 6) As Integer
In the second declaration above, we've specified that the array positions
should start at 1 rather than the default 0.
The arrays above are both 2-D arrays. If you want to add another
dimension, just add another comma and another number:
Dim MyArray(5, 4, 6) As Integer
Dim MyArray(1 To 5, 1 To 4, 1 To 6) As Integer
In this next exercise, we'll set up a 2-D array. We'll then print out arrays
values in cells on the spreadsheet.
Create a new Sub and call it ArrayExercise_3. As the first line of code, add
this line:
Dim MyArray(2, 3) As Integer
This sets up a 2-D array. Think of this like the rows and columns on your
spreadsheet. The 2 means 3 rows (0 to 2, remember). The 3 means 4
columns.
To store data in the first row, add these lines:
MyArray(0, 0) = 10
MyArray(0, 1) = 10
MyArray(0, 2) = 10
MyArray(0, 3) = 10
This means row 0 column 0 has a value of 10, row 0 column 1 has a value
of 10, row 0 column 2 has a value of 10, and row 0 column 3 has a value of
10.
Of course, there is no row or column 0 on a spreadsheet, and you'll see how
we solve that in the loop. For now, add values for the other positions in the
2-D arrays:
MyArray(1, 0) = 20
MyArray(1, 1) = 20
MyArray(1, 2) = 20
MyArray(1, 3) = 20
MyArray(2, 0) = 30
MyArray(2, 1) = 30
MyArray(2, 2) = 30
MyArray(2, 3) = 30
The new lines add values to the rest of the positions in the array.
To go through all positions in a 2-D you need a double loop. A double loop
means one loop inside another. The outer loop takes care of the rows while
the inner loop takes care of the columns. (The rows are the first positions
between the round brackets of MyArray, while the column are the second
positions between the round brackets of MyArray)
For the loop, the outer loop, add this:
For i = 0 To 2
Next i
You now need the inner loop, in bold below:
For i = 0 To 2
For j = 0 To 3
Next j
Next i
The variable for the inner loop is j rather than i . But they are just variable
names, so we could have called them almost anything we liked. Notice, too,
that the outer loop goes from 0 to 2 while the inner loop goes from 0 to 3.
These equate to the numbers between round the brackets of MyArray when
we set it up.
The code for the loop is this, but it needs to go between the For and Next of
the inner loop:
Cells(i + 1, j + 1).Value = MyArray(i, j)
This is quite complex, so we'll go through it. Take a look at the Cells part:
Cells(i + 1, j + 1)
Because our arrays is set up to start at 0 we need to add 1 to i and j . If we
didn't then the first time round the loop the values would be these:
Cells(0, 0)
This would produce an error as there is no row 0, column 0 in an Excel
spreadsheet.
In case you're wondering why the first time round the loop would produce
values of 0, 0 for Cells, here's an explanation.
The first line in the outer loop is another loop. This means that the entire
inner loop will execute from 0 to 3. VBA will then drop to the Next i line.
The next i after 0 is 1. The end condition for the outer loop, however, is 2,
so we're not done with the outer loop yet. So, again, it drops down to
execute its code. Its code just happens to be the inner loop, so it executes
the whole of this inner loop again. In other words, the outer loop is going
round and round from 0 to 2 times. As it's going round and round, it just so
happens that it will run the inner loop 0 to times. The inner loop round from
0 to 3 times each time round.
The first time round, the values in the inner will be:
0, 0
0, 1
0, 2
0, 3
The second time round the inner loop values for i and j will be:
1, 0
1, 1
1, 2
1, 3
The third time it will be:
2, 0
2, 1
2, 2
2, 3
So the first number, which is i, goes up by 1 each time. These are the rows.
The second number, j, will always be 0, 1, 2 and then 3 (the columns).
Notice that after the equal sign of the Cells line, we have this:
= MyArray(i, j)
The i and j between the round brackets of MyArray will be the same as the
numbers above.
But the whole of your code should look like this:
Run the code and see what happens. Switch back to your spreadsheet and
you should see this:
Multidimensional arrays and double loops can be very tricky to understand,

so don't worry if hasn't all sunk in yet. Go over it a few times and you'll get
there.
ARRAYS AND THE SPLIT
FUNCTION
In the previous section on strings, we mentioned that there is an inbuilt
function called Split . We said that this comes in handy if you want to split
a name that has more than two part. Our previous code only worked for
people who had one first name and a surname. But what if the name you
come across in cell A1 is something like David Lloyd George? Well, that's
where the Split function can help.
The Split function looks like this:
Split(text_here, separator)
Between the round brackets of Split you need two things. The first is the
string you want to break up into separate pieces. After a comma, you then
need something called the separator . This is whatever character is
between each piece of text you want to split. It could be a blank space, a
comma, a dash, just about anything.
(NOTE: there are also two optional parameters you can add to Split, a limit
and a compare method. The limit is an integer and is used to restrict the
number of pieces that Split produces. The compare methods are
CompareMethod.Binary and CompareMethod.Text .)
As an example of Split , try out the following code:
The first two lines set up a String and an Integer variable, txt and i . The
third line sets up a variable of type Variant . A Variant type is needed if
you're going to be using Split. Any other variable type won't work.
The fourth line just gets the value of the ActiveCell on the spreadsheet and
places its contents into the txt variable. Next comes the Split line:
FullName = Split(txt, " ")
To the left of the equal sign we have our FullName Variant variable. The
pieces of the string will be split and stored here, turning FullName into an
array.
To the right of the equal sign we have the Split function:
Split(txt, " ")
The first item between the round brackets of Split is the text we want to
split. The text for us is held in the variable called txt . But you don't have to
store your text in variable. You could just have direct text surrounded by
double quotes:
Split("David Lloyd George", " ")
The second item between the round brackets of Split is the separator. We
want to separate each part of the string wherever spaces are found. We've
typed two double quotes. Between the double quotes, we tapped the space
bar on the keyboard once. The Split function will then search for 1 space
between each word of the text, and break it into chunks where this space is
found. Each chunk will be one item in the array.
To get at each piece of the new FullName array, we have a For loop:
For i = 0 To UBound(FullName)
Next i
The loop goes from 0 (the first position in the array) to the highest position
in the array. We get this highest position by using UBound .
As the code for the loop, we have this:
Cells(1, i + 1).Value = FullName(i)
Here, we're using Cells to access the first Row, which is the hard-coded 1.
To move across the columns we have i + 1. To the right of the equal sign we
have our new array, FullName . To get at each position in the array we have
a pair of round brackets after the array name. Between the round brackets
we have the loop variable, i . This will move us through the array, as i
increases by 1 each time round.
Try it out. Enter a name into cell A1 of your spreadsheet and it will look
something like this:
Return to the VBA editor and run your code. Your spreadsheet should
change to this:
So we've taken a name from one cell and split it over three cells.
Delete the names in all the cells of your spreadsheet. Now enter an even
longer name, something like "John Allen Joe Jones". Run your code again
and you'll find that all four parts end up in different cells.
JOIN
You can put the pieces of an array back together again with the Join
function. If you want, you can have the same separator as before, but you
can also have a new one. In the code below, we first have a name separated
by spaces. We then use Join to put the name back together again, but this
time separated by hyphens.
Dim txt As String
Dim FullNameSpaces As Variant
Dim FullNameHyphens As Variant
txt = "David LLoyd George"
FullNameSpaces = Split(txt, " ")
FullNameHyphens = Join(FullName, "-")

MsgBox FullNameHyphens
The Join line is this:
FullNameHyphens = Join(FullName, "-")
In between the round brackets of Join , you first need the name of the array
you're outing back together as one piece of text. After a comma, you type
the new separator, surrounded by double quotes.
SUBS AND FUNCTIONS
So far, all of your code has been written in Subs. It's time to take a closer
look at them. After you've taken a closer look at how Subs work, we'll
examine what a Function is, and you'll learn how to create your own.
SUBROUTINES
A Sub is a small chunk of code that you write to do a specific job. You can
run this Sub by pressing F5 in the VBA Editor, you can run it by assigning
the Sub to a button on a spreadsheet, and you can even run it from the menu
bar at the top of the Editor. In fact, there's quite a lot of different ways you
can run your Subs.
One other way to run a Sub is from another Sub. Let's see how.
Start with a new blank workbook. Open up your VBA Editor (you should
know how to do this by now). Add the following Sub:
Sub FirstCode()
Dim FormatCell As Integer
FormatCell = ActiveCell.Value
If FormatCell < 20 Then
.Bold = True
.Name = "Arial"
.Size = "16"
End With
End If
End Sub
All the code does is to format a cell depending on the value of ActiveCell.
If ActiveCell is below 20 then we change the font to bold, Arial, 16 points.
Now this code is fine as it stands. But suppose we wanted to use this same
code again. After all, we may to format cells further down the Sub, or from
another Sub entirely. Wouldn't it be better if we placed the formatting code
in its own subroutine? That way, we could call the formatting code into
action whenever we needed it. We wouldn't have to duplicate code that
we've already written.
Add another Sub to your code. Call it SecondCode . Move the With
Statement to your new Sub. Your coding window should now look like this:
So we have to Subs, one called FirstCode and one called SecondCode .

We could put a button on the spreadsheet and then attached the macro
FirstCode to it. But this wouldn't be much good, as the SecondCode is not
being called into action.
The way you activate one Sub from inside another is to simply type its
name. Optionally, you can add the word Call at the start of the line. Like
this:
Call SecondCode
That's enough to active the Sub you want to call into action. VBA then
executes the whole of the second Sub before returning to where it was.
Add that line to your first Sub and it will look like this:
Inside the If statement above, we have the call to SecondSub . Once VBA
has executed all the code for the second sub it returns to where it was. It
then drops down and executes the code for the rest of the FirstCode sub.
The next line that VBA executes, therefore would be the End If line.
You can try it out at this stage. On your spreadsheet, type a number less
than twenty into any cell. Now go back to your coding window. Click
anywhere inside of the Sub and End Sub of FirstCode . Press F5 on your
keyboard to run your FirstCode Sub. Now look at your spreadsheet again.
You should see that the number in the cell has the new formatting.
We mentioned that the first Sub could be assigned to a button on a
spreadsheet. To do so, you'd have to bring up the Assign Macro dialogue
box. If you did, you'd see that both Subs are on the list of Macros:
The reason they both show up is that by default they are Public Subs. This
means that they can be seen just about everywhere from Excel. If you don't
want a Sub showing up in the Assign Macro dialogue box then you can
make it Private . The way you make a Sub Private is by typing the word
Private before the word Sub:
Private Sub SecondCode()
Because it has the word Private at the start, this Sub won't show up in the
Assign Macro dialogue box:
PASSING VALUES TO A
SUB
The code for our second Sub is this:
.Bold = True
.Name = "Arial"
.Size = "16"
End With
So we're changing three values of the Font in the ActiveCell: Bold, Name
and Size. However, these are all hard-coded to values of True, Arial and 16.
It would be much better if we could replace these hard-coded values with
variables:
.Bold = BoldValue
.Name = NameValue
.Size = SizeValue
We could then put whatever we liked in them. This would make the code
much more reusable.
The question is, how do we put values in these variables? We could do it
like this:
Dim BoldValue As Boolean
Dim NameValue As String
Dim SizeValue As Variant
BoldValue = True
NameValue = "Arial"
SizeValue = "16"
But this wouldn't make our code reusable at all. In fact, it's just a longer
version of what we already have.
That's where the round brackets come in at the start of the Sub. The round
brackets of a Sub are not just there for show. They allow you to pass values
over to your subroutines. This means that the line that calls one Sub can
pass values to another Sub.
When setting up a Sub that accepts values, you place variable names
between the round brackets (two lines of code):
Sub SecondCode(BoldValue,NameValue, SizeValue)
End Sub
Each variable is separated by a comma. You can pass no variables, one

variable, or as many as you need.
As well as specifying the variable names, you can specify a variable type,
as well:
Sub SecondCode(BoldValue As Boolean, NameValue As String)

End Sub
If you miss out the "As Variable_Type" then the variables are treated As
Variant . Notice that you don't need the Dim word anymore.
But this is just setting up the variable. They have nothing in them yet. To
place something in these variables, you do so on the calling line.
Call SecondCode(True, "Arial")
Each value you place between the round brackets is known as an argument
(or sometimes a parameter). The arguments must match. So if you have set
up your Sub line to accept two arguments then you must pass two
arguments in on the calling line, otherwise you'll get an error. The order of
the arguments must match, as well. If the first variable is a Boolean, then
you can't pass in a value of "Arial". Likewise, if the second argument is a
String, then you can't pass in a number. Unless, that is, you set each variable
up as Variants by missing off As String , As Boolean between the round
brackets of the Sub line.
To clear that up, if our Sub is this:
Then this Calling line will get you an error:

Call SecondCode("value", "Arial")
But this won't
Call SecondCode(True, "Arial")
The error is because we have the text "value" as the first argument. The Sub
says that BoldValue should be a Boolean, however. Boolean values can
either be True or False , so VBA will give you a "Type Mismatch" error.
(You can have 1 or 0 for your Booleans, though. A value of 1 means True
and a value of 0 means False. You can even put the True and False in quote
marks, and in any case you like: upper, lower, or a mix.)
In the Sub where your calling line is, you can set up variables, place values
in the variables, and then type the variable names between the round
brackets. Like this:
Dim boolValue As Boolean
Dim strText As String
boolValue = True
strText = "Arial"
Call SecondCode( boolValue, strText )
Now, we have variable names between the round brackets of the calling
line. VBA will pass over to the SecondCode Sub whatever is inside of
these variables. It will transfer the values to the new variables names:
So the value in boolValue will get transferred to the BoldValue variable,
and the value in strText will get transferred to the NameValue variable.
Once you have values in the new variables between the round brackets of
your Sub, you can do something with these variables.
To see how that works, let's now amend our SecondCode Sub to accept
variables.
Change the first line of your code to this (all on oneline):
Private Sub SecondCode(BoldValue As Boolean, NameValue As String, SizeValue)
Here, we've set three arguments between the round brackets of

SecondCode . The first is a Boolean value called BoldValue , and the
second is a String value called NameValue . Notice that the third argument
is just the variable name itself, SizeValue . Because we haven't specified a
type, like As Boolean or As String then the SizeValue variable is a Variant,
meaning it can accept just about any kind of value. With a font size, the
value can be just a number, like 22. But it can also be a number surrounded
by quotes, like "22". Either will work with font size.
To actually do something with these new variables, change your With
Statement to this:
.Bold = BoldValue
.Name = NameValue
.Size = SizeValue
End With
Last time we had .Bold = True . This time, the value after the equal sign is
a variable name. So whatever value is inside of BoldValue will be used for
the Bold property of Font . Similarly, the value in NameValue will be used
as the Font Name, and the value in SizeValue will be used as the Font Size.
The point about doing it this way is that the code is much more reusable.
We can now call this Sub from just about anywhere. The only thing we
have to do is pass in the correct values when we call it into action.
Amend the calling line from FirstCode to this:
Call SecondCode(True, "Arial", 22)
This passes three values over to our SecondCode sub, and its variables
between round brackets: a value of True for the Bold font, a value of
"Arial" for the font name, and a value of 22 for the font size.
Your coding window should look like this, though:
Test it out. Enter a value below 20 into any cell of your spreadsheet. Go
back to your coding window. Click anywhere inside of the FirstCode sub.
Then press F5 to run your code. Go back to your spreadsheet and you
should find that the number in your ActiveCell has been formatted to bold,
Arial, 22 points.
Now add an Else part to the If Statement in FirstCode :
Else
Call SecondCode(False, "Times", 26)
FirstCode should now look like this:
This time, the values we're handing over to SecondCode are False,
"Times", and 26. Notice that we don't have to change the SecondCode Sub
at all. It has been set up to be reusable, so the only thing we need do is to
call it into action – and that can be from anywhere.
EXIT SUB
Sometimes, it's useful to exit a subroutine early. For example, you can have
some error checking at the start of a Sub. If a value not what you want, you
can bail out. The way you do this is with the words Exit Sub . Here's an
example:
Notice that the first line is:

Dim FormatCell As Variant
So we've changed the variable type from As Integer to As Variant . We've
changed it because in the real world, the value from ActiveCell could be
anything. The user may enter text, for example, instead of numbers. We're
going to be testing for this.
The code that tests the value of the FormatCell variable is in an If
Statement. This one (the message box should go on one line):
If Not IsNumeric(FormatCell) Then
MsgBox ("This needs to be a number - Exiting Now")
Exit Sub
End If
To test if something is a number you can use the inbuilt function IsNumeric
. Between the round brackets of IsNumeric you type whatever it is you
want to test. In this case, we're testing the FormatCell variable. Before the
IsNumeric function we have Not . We could have done this instead,
though:
If IsNumeric(FormatCell) = False Then
The result is the same: If the value in FormatCell is not a number then the
code for the If Statement will get executed. The code that gets executed is
this:
MsgBox ("This needs to be a number - Exiting Now")
Exit Sub
First, we display a message box for the user. The second line is Exit Sub .
On meeting this line, VBA bails out of the subroutine. Any lines below that
won't get executed.
You should always try to add as much error checking code as you can. Then
use Exit Sub so that your macros don't crash.
THE MESSAGE BOX
You have been using the MsgBox function quite a lot so far. But there's
more to it than the simple versions we've used. Our message boxes have
looked like this:
MsgBox("Number Required")
In between the round brackets of MsgBox we have typed a message in
double quotes. There are, however, other options (arguments) you can add.
Each of these options go after a comma.
The first option is always the message itself. But as a second option, you
can add a button argument. The button argument is what kind of buttons
you want on your message box, along with an optional icon to go with
them. For example, if you wanted OK and Cancel buttons with an
information symbol, the code would be this (one line):
Response = MsgBox("Number Required", vbOKCancel +
vbInformation )
The message box itself would then look like this:
You may have noticed that this message box has a Response = at the start.
The Response is a variable name we made up, and could have been almost
anything. A variable name then an equal sign are needed for more complex
message boxes because the return a value. The message box above needs to
return a value of which of the buttons were clicked, OK or Cancel . You
can then test the value that's in the variable, which is the button the user
clicked. More on that in moment.
But as soon as you type the comma after your message in double quotes
you should see a list appear. This one:
The buttons on the list are:

vbAbortRetryIgnore
vbOKCancel
vbOKOnly
vbRetryCancel
vbYesNo
vbYesNoCancel
The symbols are:
vbCritical
vbExclamation
vnInformation
vbQuestion
The type of button you need and the symbol (if you want one) are separated
by a plus symbol (+).
As well as the message, and the buttons and symbols, you can add a title to
the top of your message boxes. The title will replace the default Microsoft
Excel, and needs to go between double quotes:
Response = MsgBox("Number Required", vbOKCancel + vbCritical, "Exiting Sub" )
The above message box would then look like this:
You don't have to do anything with the value that the message box returns,
but you can if you need to. In the code below, we're using an If Statement to
test what is inside the Response variable:
Response = MsgBox("Number Required", vbOKCancel + vbCritical, "Exiting Sub")
If Response = vbOK Then

MsgBox ("OK")
ElseIf Response = vbCancel Then
MsgBox ("Cancel")
End If
The two buttons we've used are OK and Cancel. If the users clicked the OK
button then the value that the message box stores in the Response variable
is 1. This 1 is hidden behind the inbuilt constant vbOK , which you can use
instead of typing 1. Using the constant instead of the numeric values makes
your code easier to read.
However, if the user clicked Cancel then the value will be 2, or vbCancel .
Here are all the constants and the numeric values that they hide:
Constant Value
vbOK 1
vbCancel 2
vbAbort 3
vbRetry 4
vbIgnore 5
vbYes 6
vbNo 7
FUNCTIONS
Functions are closely related to Subs and are set up in a similar way. The
difference is that functions return a value (like the MsgBox function)
whereas Sub don't return a value – they just get on and execute the code.
You use a function when you want a chunk of code to return some sort of
answer for you.
When you set up a function, you set it up like this:
Function function_name() As variable_type
You start with the word Function . After a space, you need to come up with
a name for your function. Like Sub names, this should be something
relevant to what the function does.
After the name of your function, you need a pair of round brackets. Just like
Subs, the round brackets are used for any arguments you want to pass over
to your function.
One of the big differences between setting up a Sub and setting up a
Function is the return type at the end. This is exactly the same as setting up
a variable type. So you can have As String , or As Boolean , or As Integer
– any of the types you can use with ordinary variables can also be used with
functions. If you miss off the As Type at the end then the function will be
As Variant .
The function name you come up with is like a variable: whatever answer
you want your function to produce will be stored in the function name.
To return a value from your functions you need this:
function_name = value
Whatever name you gave your function goes on the left of an equal sign. To
the right of the equal sign is where you put the value to want your function
to return. The return value must match any As Type you have at the start.
So if you've set your function up with As Integer you can place a string
value to the right of the equal sign.
One word of warning about functions, though: you can't change anything
on a worksheet from inside of them . So you can't try to manipulate a
range, or format a cell directly from a function. There's no coding like
ActiveCell.Value allowed from a function.
To call a function into action you need to do so on the right of an equal sign.
To the left of the equal sign, you need a variable. Like this:
ReturnValue = function_name(arguments_here)
VBA will first execute the function on the right of the equal sign. When it
has worked out the value of the function for you, it will place the result in
your variable to the left of the equal sign. So it's just like normal variable
assignment, except that VBA is working out the result of a function.
To clear all this up, let's have a coding example. What we'll do is to set up a
function that does some error checking. We'll call the function from a Sub.
The return value of the function will be a Boolean. A value of True means
everything is OK, while a value of False means we need to bail out of the
Sub.
Add the following as the first line of your function:
Function CheckCell( CellValue) As Boolean
Now press the enter key on your keyboard. When you do, you'll find that
VBA adds End Function for you.
This function has the name CheckCell . The value that will be stored in
CheckCell will be a Boolean value (As Boolean ). Between the round
brackets of our function we have a variable name. This is just like we did
for Subs. Because this variable has no As Type ending it will be a Variant
type.
As the code for your function, add the following:
If IsNumeric( CellValue) Then
CheckCell = True
Else
CheckCell = False
End If
The function just checks the CellValue variable and tests if it is numeric or
not. If it is, then this line gets executed:
CheckCell = True
This means that a value of True will be placed inside of the function name,
which was CheckCell . This is what the function will return with, after it
has been executed.
However, if the CellValue variable is not a number, we set CheckCell to
False. The function will return with this value, after it has been executed,
and CheckCell is not a number.
CheckCell = False
In other words, we've set up two alternatives for our Boolean function, one
for True and one for False.
Now for the Sub that calls the function. Here it is:
The first line sets up a Boolean variable called ReturnValue . We need a
Boolean value because the function returns a either True or False (though
we could have set ReturnValue to be As Variant ).
The second line is this:
ReturnValue = CheckCell(ActiveCell.Value)
On the left of the equal sign, we have our Boolean variable. This is going to
hold the value that CheckCell returns with. On the right of the equal sign is
where the function call is:
CheckCell(ActiveCell.Value)
When VBA sees this line it looks for a function called CheckCell . It then
goes off and executes any code for this function and returns a value to be
stored on the left of the equal sign.
Because we set up the function with one argument between round brackets
then the call to the function needs one value between its round brackets.
Any more or any less and you'd get an error. The value we have between
the round brackets of the calling line is ActiveCell.Value . The value of the
active cell will then get handed over to the function. This value will end up
in the variable we called CellValue in the function.
The rest of the code is this:
If ReturnValue = False Then
MsgBox ("Not a number - Exiting Sub")
Exit Sub
End If
Here, we're testing the value that was returned from the function. If the
value is False then we display a message box and Exit the Sub . Exiting the
Sub here means that any code below it won't get executed. If ReturnValue
is True, however, then VBA will skip past the End If and run any code
from that point on.
Two other points to make about functions before we leave the topic. First,
you can set them up as Private , just like you can with Subs (the default is
Public ). Setting up a function as Private means it won't be available in the
coding window of other worksheets. If you want to access a Public function
you coded in another worksheet then you need the name of that worksheet
first. So this:
Sheet1.CheckCell("ten")
And not just this:
CheckCell("ten")
The second point to make about functions is that you can Exit them early by
typing Exit Function . This works in the same way as Exit Sub does.
USING EXCEL'S OWN
WORKSHEET FUNCTION
IN VBA
You've already met one worksheet function - Pi . But all the functions that
are available through the main Excel screen can also be accessed using
VBA code. The only thing you need to do is to type WorksheetFunction
on the right of an equal sign. After a dot, you'll see a list appear. This one:
Select the function you want to use from the list. You then need a pair of
round brackets. The round brackets are where you type some something for
the function to calculate.
To the left of the equal sign, you need a variable. This will store the answer
to the function. Let's see an example. We'll use the Sum function.
Type some names into cells A1 to A5 of a spreadsheet. Now type some

numbers into cells B1 to B5:
Bring up the coding window for this spreadsheet. Create a new Sub and call
it Worksheet_Functions . Set up a Long variable called SumTotal :
We'll now add up the figures in cells B1 to B5 with the use of the Sum
function. Add the following line to your code:
SumTotal = WorksheetFunction.Sum(Range("B1:B5"))
After WorksheetFunction and a dot, we have this:

Sum( Range("B1:B5") )
In between the round brackets of the Sum function we have a range a cells,
B1 to B5. These have their own round brackets. VBA will then take the
values from these cells and return the sum total. Once it has an answer it
will place that answer into the variable on the left of the equal sign, which
is SumTotal for us.
To do something with the answer to the Sum function, we can place it in

cell D1, and have some text in cell C1. Add these two lines to your code:
Range("C1").Value = "Total Sales:"

Range("D1").Value = SumTotal
Try it out. Click anywhere inside of Sub and End Sub . Press F5 to run the
code. Go back to your spreadsheet and you should see this:
You'll notice that the text is too big for cell C1. To widen it with code, you
can use the AutoFit property of the Columns object. Add this line to your
code:
Range("C1").Columns.AutoFit
Now, whatever is in cell C1 will automatically resize to fit the cell.
Run your code again and have a look at your spreadsheet. It should have
changed to this:
Now try these exercises.
Exercise
Use the Max WorksheetFunction to work out the maximum value of the
range B1 to B5. Put your answer in cell D2. Add the text "Highest Number
of Sales:" in cell C2. Your spreadsheet should look like this when you have
completed this exercise:
Exercise
Use the Min WorksheetFunction to work out the minimum value of the
range B1 to B5. Put your answer in cell D3. Add the text "Lowest Number
of Sales:" in cell C3. Your spreadsheet should look like this when you have
Exercise
Use the Average WorksheetFunction to work out the average value of the
range B1 to B5. Put your answer in cell D4. Add the text "Average Daily
Sales:" in cell C4. Your spreadsheet should look like this when you have
In the next lesson, you'll see how to get the name of the person with the
lowest or highest sale. We'll do this with the Set keyword.
THE SET KEYWORD IN
EXCEL VBA
The Set keyword is used to create new objects, creating a new Range, for
example. The Set keyword comes in handy when you want to simplify long
lines of code. It is also speeds up your code when VBA executes it.
Take these two lines as an example:
Dim NewRange As Range

Set NewRange = Range("A2:A6")
The first line introduces you to a new variable type - As Range . This
object variable type is used to hold a range of cells from your spreadsheet.
The next line starts with the word Set . Next, you need the name of your
object variable, which is NewRange for us. After an equal sign, you type
what it is you're trying to Set as a new object.
The code above, however, is pretty pointless, as we're not doing anything
with this new range. In this next example, we'll use the inbuilt Find method
to search the cells in the new range.
Suppose you had a spreadsheet that looked like this:

What you want to do is to find out the name of the person with the highest
score and the name of the person with the lowest score. How would you do
it with Excel VBA?
The first thing to do is to set up some variables:
Dim HighestScore As Long

Dim LowestScore As Long
Dim TopScorer As Range
Dim BottomScorer As Range
Notice that the first two variables are As Long and the last two or As
Range .
Next we can get the Min and Max scores from the B column (two lines of
code, here):
LowestScore = WorksheetFunction.Min(Range("B1:B5"))
HighestScore = WorksheetFunction.Max(Range("B1:B5"))
Once we have these scores we can use the Find method and Set a new
Range (again, just two lines of code):
Set BottomScorer = Range("B1:B5").Find(What:=LowestScore)

Set TopScorer = Range("B1:B5").Find(What:=HighestScore)
In between the round brackets of Find, you need at least one parameter - the
item that you want to find. (Find has an optional 7 parameters which we
won’t cover here.)
In our code we want to find the lowest and highest score from the specified
range. This score is the Set up as a new range, in the variables
BottomScorer and TopScorer .
Because these are now new range objects you can use things like Offset
with them (two lines of code):
Range("B7").Value = BottomScorer.Offset(, -1).Value
Range("B8").Value = TopScorer.Offset(, -1).Value
Here, we're using Offset to get the next cell to the left of the BottomScorer
and TopScorer . This will give is the name of the person with those scores.
The whole of the code looks like this:

The reason why we're doing it this way is so that we don't have one very
long line that is hard to read. This line:
LowScore = Range("B1:B5").Find(WorksheetFunction.Min(Range("B1:B5"))).Offset(, -1).Value
The line above does exactly the same job as our code. But our code is easier
to read and debug. Plus, the single line will actually execute slightly slower
than the full code.
Don't worry about Set too much. Just bear in mind that you can create new
objects variables with the Set keyword.
OK, we'll move on. Next up is text files.
VBA AND TEXT FILES
Being able to manipulate text files with Excel VBA is a useful

programming skill to add to your repertoire. In this section, you'll learn how
to do just that.
A CSV file is one where the items on each line are separated by commas,
and the line itself ends in a carriage return (pressing the Enter key on your
keyboard). They look like this:
You can save a file with comma separated lines in file ending in .csv or .txt
. Files ending in csv are, however, a common format, and we'll stick with
those types of files.
If a file has each item on a line separated with the Tab character then it is
said to be a TXT file. They look like this:
In both the text files above, each line has three items: author first name, last
name, and an ISBN.
You can, of course, open a text file straight from Excel. Just use the Data >
Get External Data > From Text options on the Excel Ribbon. This would
then bring up the Text Import Wizard . However, as a VBA programmer,
it's nice to know how to do it yourself. Plus, you can do things by
manipulating the text file yourself that you can't do with the Wizard. What
we're going to do is to open up the above CSV file and place the ISBN in
the A column, rather than in the last column as we have it at the moment.
To follow along with this lesson, there is a file called authors.csv that you
can download from our site. The page is here:
https://www.homeandlearn.co.uk/downloads.html
Scroll down to the bottom of the page and you’ll see a section for Excel
VBA Downloads. Click the link to download the extra files to your
computer. When you unzip the file, you should see the authors one.
In Excel, create a new blank workbook. Click the A column and format it as
Text . This is because our ISBN is in the number format in the text file. If
you leave the A column on General then you'll get a strange number for the
ISBN.
Now open up your VBA Editor to get at the coding window. Create a new
Sub and call it OpenTextFile . As the first line of your code, add the
following:
Dim FilePath As String
This just sets up a variable called FilePath .

We now need the location of our authors.csv file. We placed ours in the
Documents folder. Because it's in the Documents folder, we can use the
built-in Application.DefaultFilePath . The default file path is the
Documents folder. We then only need to add on the file name, preceded by
a backslash (one line of code):
FilePath = Application.DefaultFilePath & "\authors.csv"
If you placed your file somewhere else, you can do something like this
instead:
FilePath = "C:\Users\Owner\VBA\authors.csv"
This file path points to a folder called VBA that is in the Owner folder in
C:\Users . But amend your own file path to point to where you saved your
authors.csv file.
To open a file, you start with the word Open. You then specify a File Name,
a Mode, and a File Number.
Open FileName For Mode As FileNumber
The Mode above should be replaced by one of the following:
Append – used for adding data ton already existing file
Output – used to write to a file
Input – used to read a file
Binary – used to read or write data in byte format
Random – used to place character in a buffer of a set size
The Modes that we're interested in are the Input and Output ones.
The FileNumber can be any number between 1 and 511. You precede the
number with the # character. So if you're opening one file you'd have #1. If
you open up a second, different file it would be #2, and so on.
So add this line to your code:
Open FilePath For Input As #1
The file we want to open is the one we stored in the variable called
FilePath . You can also type the entire file path here, enclosed in double
quotes:
Open "C:\Users\Owner\VBA\authors.csv " For Input As #1
We want to read this file only, so we have For Input . This is the first file
we're going to open, so it #1 (file number 1).
The next line is to set a variable for row numbers. We'll use this for the
ActiveCell on the spreadsheet:
row_number = 0
At the moment, we've only told VBA to open the file. We haven't actually
done anything with it. The way you normally do something with a file is to
loop round, grabbing each line as you go. We'll use a Do Until loop for this:
Do Until EOF(1)
Loop
Notice the strange end condition: EOF(1) . The EOF means End Of File ,
while the 1 in round brackets is the file number you specified earlier.
Inside of the loop, we first have this somewhat complex line:
Line Input #1, LineFromFile
The first three items before the comma refer to a single line of input from
your file number (#1 ). After the comma, you tell VBA where you want to
place this line. This will be a variable. We've called ours LineFromFile .
Every time round the loop, a new line will be grabbed from the text file and
placed in this variable.
Now that you have a line from your text file, you need to do something with
it. However, the line will still have all the commas in it. So the first line for
us will be this:
Christopher, Brookmyre, 9780349114903
You need to parse the lines from your text file in some way. A good way to
parse a line is with the Split function you met earlier. By using Split, you
can place each item from a line into an array:
LineItems = Split(LineFromFile, ",")
Between the round brackets of Split, we have the variable we want to split,
which is LineFromFile . After a comma, we have the separator we want
Split to look for. The separator is the comma, for us.
When Split has finished, we'll have an array called LineItems . Our text file
always has three items per line (first name, last name, ISBN). So we know
the array goes from 0 to 2 positions. We can now go ahead and place each
item into a cell on the spreadsheet (three lines of code):
ActiveCell.Offset(row_number, 0).Value = LineItems(2)
Between the round brackets of Offset we have the row number and the
column number. We're using the variable called row_number for the rows.
We set to 0 earlier. (We'll increment this variable shortly). The columns are
always off set at 0, 1 and 2. A value of 0, remember, keeps you in the same
column. A value of 1 moves you 1 column over, and a value of 2 moves
you 2 columns over from the ActiveCell .
To the right of the equal sign, we have our LineItems array. Because we
want the ISBN in the A column, we've used LineItems(2) as the first value
after the equal sign. We next have LineItems(1) , which will get us the last
name in the B column. Finally, we have LineItems(0) , which will get us
the first name in the C column.
The final thing to do inside of the loop is to increment the row_number
variable. Otherwise, we'll be stuck on the first row of the spreadsheet.
row_number = row_number + 1
Only one line of code is needed. When you open a file, you should close it
somewhere in your code. This is fairly straightforward:
Close #1
You type the word Close and then, after a space, the file number you trying
to close.
But the whole of your code should look like this:
Test it out. Make sure any cell in the A column is the Active cell on your
spreadsheet (the A column is the one your formatted to Text). Go back to
your code. Click anywhere inside of your sub and press F5 to run it. Go
back to your spreadsheet and you should find that the data from the text file
has been imported:
And there we go! We've opened up a CSV file, reordered the columns and
placed the information into cells on a spreadsheet. We'll now look at writing
to a text file.
WRITING TO A TEXT FILE
The data you have on a spreadsheet can be written to a text file. What we'll
do is take our reordered columns and write them back to a CSV file.
The first job is to find a way to reference the cells we want. We can then
loop round all these cells getting the values.
Our spreadsheet is 3 columns wide by 8 rows high. We could have two
loops to cycle through the data, an inner and outer one. Like this:
For i = 1 To 8
For j = 1 To 3
Next j
Next i
The outer loop goes from 1 to 8. This is the number of rows we have. The
inner loop goes from 1 to 3, which is the number columns.
However, suppose we decided to add more rows, or more columns to the
spreadsheet. It would mean our loop would not pick up the new data. A
better way is to get the last row with data in it and the last column with
data. We could then have this for the loops:
For i = 1 To LastRow
For j = 1 To LastColumn
Next j
Next i
The question is, how do we get these values?
There are lots of way to get the last row and last column with data. A
popular way to get the last row with data in, for example, is this:
LastRow = Cells(1, "A").End(xlDown).Row
This code first goes to the very last row in Excel, no matter which version
you have (older versions of Excel have fewer rows than later versions). It
then goes back up to find the last cell in column A that has something in it.
A similar technique is used to find the last column with data.
The technique we prefer, though, is this (one line of code):
LastRow =
ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row
This refers to any range in your active worksheet with data in it. The inbuilt
SpecialCells is then used. In between the round brackets of special
SpecialCells is the constant xlCellTypeLastCell . This gets you the last cell
with data. After a dot, you can type either Row or Column , depending on
which last cell you want.
We’re almost ready to start writing the code. To actually open a text file for
writing, though, you need this:
Open FilePath For Output As #1
This is the same as before, except for the use of the keyword Output . VBA
will then try to open up your file. If a file of the one named in FilePath
doesn't exist it will be created for you. If the file does exist then it will be
overwritten. (If you want the new contents to be added to the end of your
file then you would use Append instead of Output .)
To do the actual writing to the file you need this:
Write #1, File_Contents
After the word Write you type your file number. After a comma, you need
the contents you wish to write to the file.
With that in mind, let's write some code.
Create a new Sub and call it WriteTextFile . Add the following four
variables to your new Sub:
Dim CellData As String
Dim LastCol As Long
Dim LastRow As Long
To get the last row and column with data, add the following two lines:
LastCol =
ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Column
LastRow =
You then need a file path, as before:
FilePath = Application.DefaultFilePath & "\auth.csv"
This points to a file called auth.csv in the Documents folder. If there is no
such file then VBA will create one for us.
The next line to add is the one that open the file for output:
Open FilePath For Output As #2
Notice that the file number at the end, #2. We've already used #1 previously,
so we'll try #2 to avoid any conflicts. (NOTE: On some systems, you may
get an error telling you the file is already open when you run the code. If so,
close down Excel and reopen it.)
The next code to add is the double For Loop. This is quite complex so don't
worry if you don't understand it at first. Keep studying it and it will make
sense. Here it is (the CellData lines are single lines of code):
For i = 1 To LastRow
For j = 1 To LastCol
If j = LastCol Then
CellData = CellData + Trim(ActiveCell(i, j).Value)
Else
CellData = CellData + Trim(ActiveCell(i, j).Value) + ","
End If
Next j
Write #2, CellData

CellData = ""
Next i
As we said, we're looping round cells on the spreadsheet. The outer loop
takes care of the rows and the inner loop takes care of the column.
Inside the inner loop, we have this If Statement:
If j = LastCol Then
Else
CellData = CellData + Trim(ActiveCell(i, j).Value) + ","
End If
The reason why we want to know if j = LastCol is because of the commas.

We want each line in our text file to look like this, remember:
9780349114903, Brookmyre, Christopher
We're getting one cell at a time from the spreadsheet. Each of the items
needs to be separated by a comma. However, there's no comma at the end,
which is the third item. If j does equal LastCol then this gets executed:
Whatever is in the ActiveCell i , j will have its Value placed into the
variable called CellData .
However, if j does not equal LastRow then this gets executed instead:
CellData = CellData + Trim(ActiveCell(i, j).Value) + ", "
The only difference is the comma on the end. The first time round the inner
loop, CellData will hold this value:
9780349114903,
The next time round it will hold this:
9780349114903, Brookmyre,
And the final time round it will hold this value:
9780349114903, Brookmyre, Christopher
Outside of the inner loop, but just before the Next i of the outer loop, we
have these two lines:
Write #2, CellData
CellData = ""
The first line is the one that actually writes the new line to the text file. The
second line resets the CellData variable to a blank string.
The final two lines of code are these, both outside of the two loops, at the
end:
Close #2
MsgBox ("Done")
These lines close the file and then display a message box.
The whole of the code looks like this:
Run your code and try it out. Now locate your new text file. You should
find that it looks like this: (Don't worry about the double quotes that VBA
has added to the beginning and end of each line. If you don’t want the quote
marks then you can use Print instead of Write in the code above.)
USER FORMS
In this section, you'll learn about User Forms. So you can get an idea of just
what User Forms are, we'll create a simple one. It will just be a button and a
text box.
You can start a new spreadsheet for this. Call it user_forms . Now bring up
the VBA Editor in the usual way. From the menu bar at the top of the
Editor, click Insert > User Form :
When you click on User Form, you should see a grey square appear with a
toolbox beside it:
If you can't see the toolbox, or accidentally lose it, click the View menu at
the top of the Editor. From the View menu, select Toolbox .
Now have a look at the Project Explorer on the left. You should see a new
folder appear, along with your User Form. If you accidentally get rid of the
form, double click here to bring it back.
By default, the form has the name UserForm1 . (You can change this name,
if you like, and you'll see how to do this shortly.)
Look closely at the toolbox and you'll see it’s a grid of items that you can
click on. Click on the Command Button item to select it, circled in red
below:
With the Command Button selected, move your mouse over to the grey
form. Hold down your left mouse button and drag to the right to draw a
button on the form. Draw one about a third of the width of the form, but not
very high. It should look like this when you let go of the left mouse button:
You can change the height and width of a button with the white resizing
handles. Simply click and drag a white square to resize the button.
Every object you draw on a form comes with a list of properties that you
can change. The properties area should appear on the left of the Editor, just
below the Project Explorer. If you can't see the Properties area, click the
View menu at the top of the Editor. From the menu, select Properties
Window .
With your button selected, have a look at the list of properties it has:
The word to the left of the list is the property itself. To the right is the value
for the property. Properties are mainly nouns. Think of a TV. It has Power
property. The values for TVPower would be On or Off. You'll also have a
Channel property that can take channel numbers as a value:
TVPower = On
TVChannel = 3
ADDING CONTROLS TO A
USER FORM
The first property in the Properties area is Name . The default value for the
Name of a button is CommandButton , then a number. Click inside of
where it says CommandButton1 . Now press the backspace key on your
keyboard to delete it. Type a new Name property. Call it cmdGetNumber .
You should see the top of the Properties area change to this:
The internal name of the button is now cmdGetNumber .

You might have noticed, though, that the button on the form still says
CommandButton1 . This is because this is the text on the button, and not
its name. The text on a button is a different property. With your button still
selected, locate the Caption property:
Again, click to the right of Caption and delete the default text. Type Get
Number instead:
The text on the button on your form will change to this:
We're going to add a text box next. First, we can move to the button to the
right of the form, in a more natural position. We'll then have the text box on
the left.
One way to move a control is via the properties area. Have a look at the list
and you'll see that there is a Top and Left property. These refer to top of the
form and the left side of the form. Simply type a new value for these and
watch your button jump around the form (You can also change the width
and height of your form in the same way.
Another way to move a button (or any control) around the form is with the
selection arrow, circled in red below:
You must have the arrow selected before moving your control. Don't, for
example, right click a button without the arrow being selected, otherwise
you'll get a new button on the form. If this happens to you, select the
unwanted button with the black selection arrow then press the delete key on
your keyboard. This will get rid of the button.
To add a text box to your form, click the TextBox item in the toolbox:
Now draw one out on your form. Change the Name property of the text box
to txtValue . Change the Width property to 50 and the Height to 25. Set the
Top property to 20 and the Left property to 25. Your form should now look
like this:
If you have a look in the top left of the form, you'll see the text UserForm1.
This is the form Caption. You can change this to anything you like. Click
anywhere on the grey from that is not a control (button and text box). With
the form highlighted, locate the Caption property in the Properties area.
Delete UserForm1 and type Get A New Number in its place. The top of
your form will then change to this:
Now that you have the form designed, press F5 to see what it looks like
when it's running.
You'll see that the dots on the grey area have vanished. The dots are there
only in design mode, to help you position controls on the form.
Although you can click inside of the text area, nothing will happen when
you click the button. That's because we haven't written any code for it yet.
Click the red X to get rid of the form and return to the VBA Editor. We'll
now add some code to the button.
ADDING CODE TO A
USER FORM BUTTON
Of course, a button has to do something when it's clicked. What we'll do is
to get a number from the text box and transfer it to a cell on the spreadsheet.
The form will then unload itself. And all on the click of a button.
To get at the code stub for a button, simply double click it on the form.
When you do, you'll see this:
The name of our button was changed to cmdGetNumber . You'll see this
name in two places: at the very top, in a dropdown list, and just after Sub in
the coding window.
If you click the dropdown list, you'll see this:
The items on the list are all the controls you have placed on the form, as
well as the form itself. Each item has something called an event attached to
it. You can see a list of all the events by clicking the second dropdown list:
An event is what happens when you do something with a control. For a
button, this "something" is usually a click. But you can also write code for
what happens when the button is double clicked, when the mouse is over
the button, or any events on the list above. By double clicking the button on
the form, the Editor automatically takes you into the Click event, which is
what you'll want most of the time.
The code stub for the click event looks like this:
Private Sub cmdGetNumber_Click()
End Sub
This is a Private Sub . The name of the Sub is cmdGetNumber . After an
underscore, comes the name of the event, Click . After the event, you'll see
a pair of round brackets. Any code you want to execute when the button is
clicked goes between the two lines above.
To manipulate a control on a form with code this syntax is this:
Control_Name.Property
You can access a control from any other control. So the button can access
certain properties of a text box and the text box can access certain
properties of the button. We want to get at the text in the text box when the
button is clicked. We also want to place this text into a cell on a
spreadsheet. The code, then, is this:
ActiveCell.Value = txtValue.Text
To the right of the equal sign we have this:
txtValue.Text
We changed the name of our text box to txtValue . This is the control name.
The property of the text box we want to access is the Text property. As its
name suggest, this will get you the text in the text box. If you want to set a
value for the text box, the code goes to the left of the equal sign:
txtValue.Text = "Some Text"
So it's just like the variable assignment you've been doing so far. Here,
we're assigning the string "Some Text" to the Text property of the text box
control.
There are lots and lots of properties of a control that you can manipulate. As
soon as you type a dot after the control name, you should see a list appear:
A lot of the items in the above list are also in the Properties area in the VBA
Editor. For example, the Top, Left, Height and Width properties. This
means that you can change these properties with code, as well as from the
Editor. If you wanted to, you could change the Top and Left properties of
the text box like this when the button is clicked:
txtValue.Left = 10
txtValue.Top = 10
When you change a property from an event like a button click you are said
to be making changes "on the fly", or making changes "at runtime".
So be aware that any properties in the Properties area in the Editor can also
be changed with code.
Time to try it out, though. Click anywhere inside of your code. Now press
F5 to run the form. Type anything you like into your text box. Now click
your button. You should see whatever is in the text box appear in the active
cell on your spreadsheet:
However, when the button is clicked, we'd also like the form to go away.
This is done with the Unload keyword.
Unload form_name
As well as the form name after a space, you can also type the keyword Me :
Unload Me
Add that line to your code and it will look like this:
Private Sub cmdGetNumber_Click()
ActiveCell.Value = txtValue.Text
Unload Me
End Sub
Try it out again. This time, when you click the button the form will unload.
Go back to your spreadsheet and you'll see whatever you typed in the text
box is also in the active cell.
Type a single quote before each of the two lines of code, thereby
commenting them out. Now try these exercises
Exercise
Write code to change the Height and Width of the text box when the button
is clicked. Change the Height to 35 and the Width to 75.
Exercise
Write code to change the background colour of the form (BackColor) when
the button is clicked. Try some of these after the equal sign of your
property:
vbRed
vbBlue
vbBlack
vbWhite
You can also try an RGB color:
RGB(255, 100, 12)
Exercise
Write code to change the Caption property of the button when it is clicked.
Change the property to GO.
Exercise
Write code to transfer the Caption property of the button to the text box
when the button is clicked.
ADD A BUTTON TO THE
EXCEL RIBBON
A User Form can be launched in a few different ways: you can launch your
user forms from a button on a spreadsheet, just like you did when running a
Sub from a button; and you can also add a new item to the ribbon bar at the
top of Excel. In this section, you'll see how to do both.
LAUNCH A FORM FROM
A BUTTON ON A
SPREADSHEET
Go back to your spreadsheet and add a new button. When the Assign Macro
dialogue box appears, select Button1_Click :
When you click OK, you should see a coding window appear.
To launch a form, you need the Show method after the name of your form:
Sub Button1_Click()
UserForm1.Show
End Sub
Add the line to your own Button1_Click Sub.
Now go back to your spreadsheet. Click your button and you should see
your form appear:
LAUNCH A FORM FROM
THE EXCEL TASK BAR IN
EXCEL 2007
If you don't want to place a button on the spreadsheet itself, you can add a
new button to the ribbon at the top of Excel. In Excel 2007, however, you
can only add items to the Quick Access toolbar which appears at the very
top:
Starting from Excel 2010, you can customize the ribbon itself, adding new
tabs and new groups. We'll do that in a moment. If you only have Excel
2007, then here's how to launch your form from the Quick Access toolbar.
(You can skip this part, if you have a later version of Excel.)
First, we need to create new Sub in the Excel VBA Editor. We can then add
our UserForm1.Show code to this Sub. Doing it this way means we don't
have to have a button on the spreadsheet that launches the form.
From the Project Explorer on the left, double click on Module1 :
When you double click Module1 you should see a new coding window
open. Add the following code to the window:
Sub CallUserForm()
UserForm1.Show
End Sub
So the name of the Sub is CallUserForm . We want to Show the form
called UserForm1 .
With the Sub created in the Module window, it will now appear as a macro
when we customize the Quick Access toolbar.
So click the round Office button at the top left of Excel 2007. Click on
Excel Options at the bottom:
When you click on Excel Options, you'll see Options dialogue box appear.
Click on Customize from the list on the left:
Now click the dropdown list at the top and select Macros from the list:
When you select Macros, you should see the CallUserForm Sub appear in
the listbox below it.
With CallUserForm selected, click the Add button in the middle:
The listbox on the right shows all the commands added to the Quick Access
toolbar. The first one is Save, then Undo, then Redo, and finally our
CallUserForm macro.
To change the icon, click the Modify button. You'll then see a dialogue box
appear. Select an icon, and change the Display name:
Click OK on the Modify Button dialogue box. Click OK on the Options

dialogue box as well to return to Excel.
Take a look at the Quick Access toolbar at the top of Excel 2007 and you'll
see the icon you selected:
Test it out. Click your new icon and your form should appear.
ADDING NEW TABS TO
THE EXCEL RIBBON
(VERSIONS 2010 AND
LATER)
If you have a version of Excel 2010 or greater then you can now add new
tabs to the Excel ribbon. When you create a tab, you can place items on it,
including buttons for your user forms. You can also add buttons to existing
tabs. For example, you could add a new button to the Home tab that
displays your user form when the button is clicked.
What we'll do is to add a new tab to Excel. (The screenshots are from
versions 2013 and 2016. They should be more or less the same in Excel
2010, though, and later versions.) We'll place a button on that new tab. If
you have a version of Excel lower than 2010, you can move on and ignore
this section.
Like in Excel 2007, we need to create a Sub first in a module. The Sub is
the Macro that will display the user form.
Locate Module1 in the Project Explorer on the left of the VBA Editor:
Double click to open a new coding window for this module. Add the
following code to the window:
Sub CallUserForm()
UserForm1.Show
End Sub
The name of the Sub is CallUserForm . We want to Show the form called
UserForm1 .
With the Sub created in the Module window, it will now appear as a macro
when we customize the Excel ribbon.
Return to Excel and click the File menu at the top. From the File menu,
click on Options . You'll then see the options dialogue box appear. From
the list on the left, click on Customize Ribbon . The dialogue box will then
look like this:
From the Choose commands from dropdown list, select Macros . You'll
then see the CallUserForm macro you created in the module coding
window:
You can add this macro to the Main Tabs on the right by selecting a tab.
Click the New Group button to add a new item to that tab. In the image
below, we've created a new group on the View tab:
Click the Rename button and you'll see the following dialogue box appear:
You don't need to select an icon, as this is just the group name. Type a new
display name in the text box at the bottom. Click OK to see the item in the
list of your chosen tab:
Although we've added a new item to the View tab, it will be blank at the
moment. To add something to a new group, select a Macro from the left and
click the Add button in the middle:
When you click the Add button you should see the macro added to the new
group:
The icon for CallUserForm is set to the default of three yellow boxes
surrounding a blue diamond. You can change this by clicking the Rename
button. From the dialogue box, select an icon and type a new name:
When you click OK, you should see your chosen icon appear to the left of
the new name:
You could click OK (but don't) on the Excel Options dialogue box at this
point. If you selected the View tab on the Excel ribbon, you'd then see your
new item:
This new item, when clicked, would display the user form.
However, rather than placing our macro on the View tab, what we want to
do is to add a brand new tab to the Excel ribbon. We could then have all our
macros on this new tab.
To remove an item from the list on the right, select the Group you want to
get rid of. Then click the Remove button in the middle:
We'll now add a new tab.
ADDING TABS TO THE
EXCEL RIBBON
To add a new tab to the Excel ribbon, click the New Tab button at the
bottom of the Customize the ribbon listbox. The new tab appears after the
item you selected in the listbox. In the image below, we have selected the
View tab. When the New Tab button is clicked this new tab is placed after
the View tab:
We now have two items: New Tab and New Group. You can rename these.
Click on New Tab to select it. Then click the Rename button. A small
dialogue box appears:
Type a new name for your tab. We've called ours My Macros .
Now select the default group name New Group . Click the Rename button
again and type a new name for the group (you don't need to select an icon):
Click OK to see the newly renamed tab and group:
If we wanted to, we could add more group names, here. Each group name
would then be a separate panel on our new tab.
We don't have any items in our new group, at the moment. To add a new
item, select a group name. From the listbox on the left, select the name of a
Macro. Then click the Add button in the middle:
So we've added CallUserForm to the My Forms group.
You can rename items in your groups, and change their icons. Select
CallUserForm under the My Forms group. Now click the Rename button
again. From the dialogue box choose an icon and type a new name:
Click OK to see how it looks:

You can add as many items to a group as you want by repeating the steps
above. Or create new groups on your new tab by clicking the New Group
button.
Click OK on your Excel Options dialogue box and have a look at the Excel
ribbon at the top. You should see your new tab:
Click on your tab to select it, and you'll see your new groups and any items
you've added to the groups:
In the image above, our group name was My Forms . The item we added to
this group was called Test Form . Both of these are on the My Macros
ribbon.
Click your new Test Form to try it out. You should find that your user form
displays when you click the Test Form icon.
In the next section, you'll create a more sophisticated user form – an Excel
Picture Viewer.
AN EXCEL PICTURE
VIEWER
In this section, you'll learn how to create a more elaborate form. This form
will have text and combo boxes, options buttons, tabs, and a picture box.
What you'll create is a picture viewer that looks like this:
The first tab pulls image information from a spreadsheet. The image
associated with the information is also displayed.
Notice the button at top of the View Photos tab. It says Load Image
Information . When this button is clicked the form changes to this:
Loading the image information activates the Previous Photo and Next
Photo buttons. Clicking these buttons allows us to display other images.
All this image information is coming from a spreadsheet. This spreadsheet:
The second tab on our picture viewer is for adding new image information
to the spreadsheet. It looks like this:
Clicking the Add New Picture button displays the Open File dialogue box
where we can select an image. This image will appear in the picture box on
the form. The Save New Details button becomes active once an image is
selected. The text boxes can then filled out before these new details are
added to the spreadsheet.
Let's make a start.
SPREADSHEET IMAGE
INFORMATION
Create a new blank spreadsheet for this project. Now recreate our
spreadsheet below:
If you don't fancy typing out all the information, you can copy and paste
from below. First, copy the four image names. Click inside of cell A2 on
your spreadsheet and press CTRL + V on your keyboard to paste the data.
Image Name
london_eye.jpg
st_peters.jpg
nefertiti.jpg
carpeaux.jpg
Here's the rest of the data to copy and paste.
Date Taken
26/04/2012
28/09/2011
17/06/2013
17/06/2013
Information
London, Thames
Saint Peter's, Sunderland
Indoors, Statue, Nefertiti
Indoors, Statue, JB Carpeaux
Dimensions
2048 x 1232
2049 x 1232
600 x 420
480 x 640
Size
1.26MB
1.23MB
168K
123K
Camera
Samsung GT-I9100
Samsung GT-I9100
Samsung GT-I9100
Samsung GT-I9100
Flash
No
No
Yes
Yes
Now that you have the data for the spreadsheet, save your work. But when
the Save As dialogue box appears, create a new folder. Call the new folder
sheets . For the file name, type picture_viewer.xlsm. Before clicking the
Save button, create another new folder and call it images . Your Save As
dialogue box should look like this:
Now double click the sheets folder and save your picture_viewer
spreadsheet inside of this folder.
You now need some images. You can use your own, of course, and change
the information in your spreadsheet. If you want to use ours, though, you’ll
need to download them and save then to your computer. The download
location is here:
Scroll down the page to the bottom and you’ll see a heading for Excel VBA
Downloads. Click the link to download the ZIP file.
Now that we're all set up, we can create the user form.
DESIGNING THE USER
FORM
Open up the Excel VBA Editor. From the menus at the top, click on Insert .
From the Insert menu, select User Form . Locate the properties area on the
left, and the Name property. (If you can't see any Properties, click View
from the menu at the top of the Excel VBA Editor. From the View menu,
select Properties Window . You can also press F4 on your keyboard as a
shortcut.) Delete the default of UserForm1 and type PictureViewer .
Change the following properties, as well:
Caption : Excel Picture Viewer
Height : 420
Width : 575
Your properties area should look like this:
The first object we want to add to the form is the MultiPage control. Make
sure your form is selected and examine the toolbox. (If you can't see the
toolbox, click View from the menu at the top of the Excel VBA Editor.
From the View menu, select Toolbox .) Now click on the MultiPage
control, which is circled in red in the image below:
In the properties area for the MultiPage, change the Height to 378 and the
Width to 558. Set the Top property to 10 and the Left property to 6.
By default, the MultiPage control has two pages. This is fine for us. To
change the captions on the pages, click on the first one, which has the
default name Page1. (Click exactly on the text Page1.) This will open up the
properties for that page, rather than the MultiPage control as a whole.
Locate the Caption property of Page1 and change it to View Photos . Now
do the same for page2. Click on the text Page 2 and change the Caption
property to Add New Photo . The top of your form should now look like
this:
We can now add the controls to the MultiPage. The controls we want on the
View Photos page are 7 Labels, 6 Textboxes, 1 ComboBox, 2 option
buttons, 3 command buttons, and 1 picture frame. We'll add the labels first.
ADDING LABELS TO A
FORM
Locate the label control in the toolbox, the one circled in the image below:
Draw out the first label anywhere on your form. Now set the following
properties for the label:
Caption : File Name
Height : 18
Width : 54
Top : 72
Left : 24
Locate the Font property and click the grey button with the three dots in it:
Clicking the button brings up the Font dialogue box:

Set the font to Tahoma, Bold, 10 points. Then click OK.
Instead of drawing out the other 6 labels and setting the properties, you can
copy and paste the first label. The pasted labels will then have all the same
properties as the copied one (except for Top and Left).
Click on your first label to highlight it, then press CTRL + C to copy it.
Press CTRL + V on your keyboard to paste it. The label should appear
somewhere in the middle of the form. Drag it to the left somewhere, and
then paste another 5 times. You'll then have 7 labels that say File Name .
To left-align all the labels, hold down the CTRL key on your keyboard.
Keep it held down and left click each label in turn until they are all selected,
as in the image below:
In the properties area, locate the Left property. Type in a value of 24 and
press the enter key on your keyboard. All your labels will then left-align to
the desired position.
Now set the following values for each label:
Label2
Caption : Date Taken
Width : 72
Top: 102
Label3
Caption : Information
Width : 66
Top : 132
Label4
Caption : Dimensions
Width : 66
Top : 162
Label5
Caption : Image Size
Width : 54
Top : 192
Label6
Caption : Camera
Width : 54
Top : 222
Label7
Caption : Flash
Width : 54
Top : 258
ADDING TEXTBOXES TO
A FORM
Now that the labels are all done, add 6 Textboxes. For the first Textbox you
can set the following properties:
Height: 20
Width: 120
Top: 66
Left: 96
Font: Tahoma, Bold, 10 points
Now copy and paste the first Textbox 5 times. Set the left property for the
other 6 textboxes by highlighting them all and typing 96 into the Left
property, just like you did for the labels.
Set the following individual properties for the Textboxes:
TextBox1
Name : txtFileName
Top : 66
TextBox2
Name : txtDate
Top : 102
TextBox3
Name : txtInfo
Top : 132
TextBox4
Name : txtDimensions
Top : 162
TextBox5
Name : txtSize
Top : 192
TextBox6
Name : txtCamera
Top : 222
Your form should now look like this:
ADDING OPTION
BUTTONS TO A FORM
To add the YES and NO option buttons, locate the Option Button control in
the toolbox:
Draw two of them on the form. Set the following Properties for your two
buttons:
OptionButton1
Caption : Yes
Height : 18
Width : 50
Left : 102
Top : 258
OptionButton2
Caption : No
Height : 18
Width : 50
Left : 162
Top : 258
To add the picture box, locate the Image control in the toolbox:
Draw one on your form and set the following properties for it:
Height : 210
Width : 288
Left : 258
Top : 66
ADDING COMMAND
BUTTONS TO A FORM
Finally, we need to add the three command buttons. So draw three of them
on the form. Set up the following properties for each button:
CommandButton1
Name : cmdLoad
Caption : Load Image Information
Height : 36
Width : 190
Left : 24
Top : 6
CommandButton2
Name : cmdBack
Caption : Previous Photo
Height : 30
Width : 114
Left : 258
Top : 312
CommandButton3
Name : cmdNext
Caption : Next Photo
Height : 30
Width : 108
Left : 438
Top : 312
And that's all the controls for View Photos page.
THE ADD NEW PHOTO
TAB
Now click on your Add New Photo page. You should see a blank page for
this tab.
Using the skills you've just learnt in the previous sections, design a page
like this one:
This is more or less the same as the last one. The only new control so far is
the ComboBox.
To add a ComboBox, click the item circled below in the toolbox:
When you draw one out, set the following properties for it:
Text : Camera
Height : 20
Width : 110
Left : 96
Top : 22
The properties for the labels and textboxes should be the same as last time,
except for the Name properties of the Textboxes. Set these as follows:
TextBox1
tbImageName
TextBox2
tbDateTaken
TextBox3
tbInfo
TextBox4
tbDimensions
TextBox5
tbImageSize
For the two buttons, set the Name property of the first one to cmdAddNew
. Set the Name property of the second one to cmdSave .
We're almost done – only one more control to add.
ADD AN OPEN FILE
DIALOGUE BOX TO A
FORM
The final control to add is not in the toolbox by default, you have to add it
as an extra. This control is the Open File Dialogue Box. However, you'll
only be able to add this control if you have the 32 Bit version of Windows.
For 64 Bit versions, the Open File Dialogue Box Control is no longer
available. You can still add an Open File dialogue box, though, and you'll
see how to do this in a later section. But follow along with the steps below
to see if the control is there. (Don't worry if it's not. Just move on to the
next section.)
To add one to your toolbox, from the menu at the top of the VBA Editor,
click on Tools . From the Tools menu, select Additional Controls :
When you click on Additional Controls you should see a dialogue box
appear. Scroll down until you come to Microsoft Common Dialog
Controls :
Check the box next to Microsoft Common Dialog Controls and click OK.
You should then see the new controls in the toolbox. Select the Open File
dialogue box, circled in the image below:
Draw one out somewhere at the bottom your form and it should look like
this:
The dialogue box control is not a visible control. What that means is that
when you run your form, you won't see it anywhere. The sole purpose of
this control is to manipulate dialogue boxes. You'll see how it works when
we write the code for it later.
The only property we'd like to change for this control is the Name property.
Change it from the default CommonDialog1 to CD1 , which is a bit
shorter.
And that's the form designed – we can now start coding.
THE FORM INITIALIZE
EVENT
When a form launches, a series of events occur. One of these events is
called Initialize . The Initialize event happens before any of the form and
its controls are drawn to the screen. This allows you to set up initialization
code, such as setting variables, switching off or on any controls, and a
whole lot more besides. What we'll do is to switch off some buttons so they
can't be clicked. We'll also add some data to the ComboBox on our form.
To get at the Initialize event, right click the name of your form in the
Project Explorer on the left. (If you can't see the Project Explorer, click
View > Project Explorer from the menu at the top of the VBA Editor.)
From the menu that appears, select View Code :
When you click on View Code , you should see a coding window open. At
the top, you'll see two dropdown boxes, one for General and one for
Declarations . Click the General dropdown box and select UserForm :
Now click the Declarations dropdown box and select the Initialize event:
You should then see a code stub appear for UserForm_Initialize.

Private Sub UserForm_Initialize()
End Sub
The first thing we can do is to switch off the Back , Next and Save buttons.
The Back and Next buttons are on our View Photo tab. We don't want these
to work until the Load Image Information button is clicked. Likewise, we
don't want the Save button on the Add New Photo tab to work until a new
image has been selected.
To switch a button off you can set its Enabled property to False . To switch
it back on, you set it to True . So add the following three lines to your
Initialize event:
cmdBack.Enabled = False
cmdNext.Enabled = False
cmdSave.Enabled = False
We can also add items to the ComboBox from the Initialize event. To add
an item to a ComboBox you need the AddItem method. Like this:
ComboBox1.AddItem "Samsung GT-I9100"
After the AddItem method, you need a space. After the space you type
whatever text you want for that item. The text goes in double quotes.
Add these lines to your Initialize event:
ComboBox1.AddItem "Samsung GT-I9100"
ComboBox1.AddItem "iPhone"
ComboBox1.AddItem "Canon Ixus"
ComboBox1.AddItem"FujiFilm FinePix"
ComboBox1.AddItem "Canon EOS"
Your coding window should then look like this (we've added some
comments):
Try it out. Click anywhere inside of your Initialize event. Now press F5 on
your keyboard to launch your form. The View Photo tab should look like
this:
Notice that the two buttons at the bottom are switched off, so that they can't
be clicked.
Select your Add New Photo tab. Click on your ComboBox to see the list of
items you added:
Return to the Excel VBA Editor and we'll write the code for the Load
Image Information button.
LOADING IMAGE
INFORMATION
Click back on your View Photo tab in the Editor. Double click the Load
Image Information button. This will open up the code stub for that button.
Before we write any code, have a look at the spreadsheet again:
What we're trying to do is to take the information from each cell and place
it in the text boxes on the form. For cells in the first column, we also need
to take the file name and load that image into the picture box. For the
Yes/No cells, we need to activate one of the option buttons. Here's an image
of the form showing the data from row 2:
The first job, though, is to test if the active cell is in the first column. Not
only that, we need to make sure there is some text in the active cell. We also
need to make sure that the active cell is not in row 1, where the headings
are. If we don't do that, if the active cell is in Column 2, for example, then
we'll get errors.
LOAD IMAGE
INFORMATION
We can use and If Statement to make sure the ActiveCell is in the correct
place. Add the following to your cmdLoad button (the first line is a single
line of code that ends in Then):
If ActiveCell.Column <> 1 Or ActiveCell.Row = 1 Or ActiveCell.Value = "" Then
Cells(2, 1).Select
End If
The first line is a bit long so let's break it down. The first part of the If
Statement is this:
If ActiveCell.Column <> 1
After ActiveCell we have the Column property. This flags up anything this
is not Column 1, which is the A Column. Next we have an Or part:
Or ActiveCell.Row = 1
Again, we use ActiveCell . This time, we check the Row property. We want
to know if this has a value of 1. If it does then that's an error we need to
catch. (It's an error because the headings are on row 1.)
The final part of the If Statement is this:
ActiveCell.Value = ""
We're now checking the Value property of ActiveCell . After an equal sign,
we have two double quotes together, with no space between them. This will
check for a blank cell.
If any of the three conditions above are true then the code for the If
Statement is this:
Cells(2, 1).Select
What this does is to select the cell at position 2, 1, which is the second row
in column one. This will ensure that we have an image name selected in the
A column.
GET THE DATA FOR THE
TEXTBOXES
We can now go ahead and get the data for the textboxes. Add this line to
your code:
Call GetTextBoxData
Here, we're calling a Sub. The name of the Sub is GetTextBoxData . You
need to create this. So add the following Sub to your code (the
txtDimensions line is a single line of code):
Private Sub GetTextBoxData()
txtFileName.Text = ActiveCell.Value
txtDate.Text = ActiveCell.Offset(, 1).Value
txtInfo.Text = ActiveCell.Offset(, 2).Value
txtDimensions.Text = ActiveCell.Offset(, 3).Value
txtSize.Text = ActiveCell.Offset(, 4).Value

txtCamera.Text = ActiveCell.Offset(, 5).Value
End Sub
To the left of the equal signs above, we have the name of the six text boxes
on our form. Each of these has a Text property that what we want to set.
The first thing we're setting is the Text property for the txtFileName text
box. To the right of the equal sign, we have this:
ActiveCell.Value
This just transfers the Value of the ActiveCell to the txtFileName text box.
In other words, whatever is in the active cell will end up in the txtFileName
text box.
The rest of the lines use the Offset property of the ActiveCell. Here's the
second line from the Sub:
txtDate.Text = ActiveCell.Offset(, 1).Value
We're setting the Text property of the txtDate text box. This needs to come
from Column B, which is 1 column to the right of the ActiveCell, Column
A. We want the Value property of this cell. Notice the comma between the
round brackets of Offset – it comes before the 1. VBA takes this to mean
that you want to stay on the same Row.
You can test it out, now. Run your form in the usual way. Click your Load
Image Information button. You should see the data from the spreadsheet
appear in the textboxes.
Return to your coding window and we'll set the value for the option buttons.
OPTION BUTTONS
Option buttons tend to work as a group. When one button is selected the
other (or others) become deselected.
So that your buttons are treated as a group, return to your form and click on
the YES option button. Now have a look at the properties for the button.
Locate the GroupName property and type OB1. Press the enter key on
your keyboard and do the same for the NO option button. Both buttons will
then have a value of OB1 for their GroupName property (OB1 is just
something we came up with – you can call your group of option buttons
anything you like.):
Go back to your coding window and create a new Private Sub. Call it
GetOptionButtonValue . What we need to do, here, is to get the value
from Column 7 on our spreadsheet (the G Column). We'll then use an If
Statement to test this value. Here's the full code for this new Sub:
Private Sub GetOptionButtonValue()
Dim OB As Variant
OB = ActiveCell.Offset(, 6).Value
If OB = "Yes" Then
OptionButton1.Value = True
Else
End If
End Sub
We've set up a Variant variable and called it OB. The second line gets the
value from the spreadsheet:
OB = ActiveCell.Offset(, 6).Value
The active cell is in column 1, remember, so we use Offset to move 6
columns over into the G Column. We then get the Value of this cell.
The If Statement checks what is inside of the OB variable:
If OB = "Yes" Then
Else
End If
If the value is "Yes" then we set the Value property of OptionButton1 to
True. If it's not "Yes" then we set the value of OptionButton2 to True. This
is enough to either switch on an Option button or switch it off.
We now need to call this new Sub into action. Click inside of your
cmdLoad code and add the following line just before End Sub:
Call GetOptionButtonValue
You can test it out again. Run your form and click the Load Image
Information button. You should find that the correct option button is
selected for the Flash item.
We can now load an image into the image box.
LOAD AN IMAGE INTO
AN IMAGE BOX
Return to your form and click on your image box. Now look at its
properties on the left. One if these is called Picture :
If you were to click the grey button with the three dots in it, you'd see a
dialogue box appear allowing you to choose a picture for your image box.
You can, however, write code to load a picture into an image box. You do it
with the LoadPicture method.
Before we can load an image, we need to construct a file path. We need to
do this because they only thing we have at the moment is image names in
cells on the spreadsheet. The LoadPicture method requires a full file path.
So you need something like this (single line of code):
C:\Users\Owner\Documents\images\london_eye.jpg
and not just this:
london_eye.jpg
We could, of course, have entered the full file path in the spreadsheet cells,
which would have been easier. But this is not recommended. Simply
because if you move your images to a different location then the file
references wouldn't work.
Another technique is to enter a partial file path in a cell on your
spreadsheet. So in cell H1, for example, we could have this:
C:\Users\Owner\Documents\images\
We could then get this file path and join it together with the image name.
Like this:
FilePath = Worksheets(3).Range("H1").Value
ImageName = ActiveCell.Value
FullPath = FilePath & ImageName
This would work fine, and should be considered as an option. What we'll
do, however, is to use ThisWorkbook.Path . What this does is to get you
the file path where the current workbook is. We have saved our workbook
to a folder called sheets . So the file path reference might be (single line of
code):
C:\Users\Owner\Documents\vba\projects\sheets\
Notice that this doesn't get us the image name. If you remember, we have a
sheets folder and an images folder:
By using ThisWorkbook.Path we'd only get a path to the workbook,
which is in the sheets folder. We'd need to go up one folder from this to
reference the image folder. (There is no relative file referencing, in case
you're wondering.) The file path to the image folder, then, is this:
C:\Users\Owner\Documents\vba\projects\images
In other words, we need to chop off the sheets folder at the end of
"C:\Users\Owner\Documents\vba\projects\sheets\" and add images
instead. We can do this with a bit of string manipulation. Let's make a start.
Set up a new Private Sub and call it GetImage . Add the following three
variables to the new Sub:
Dim ImageFolder As String
Dim FullImagePath As String
Now scroll up to the very top of you coding window, and add the following
variable:
Dim ImageName As String
The reason you're adding it to the top of the coding window is because it
will then be in the General Declarations area, which will make it available
to other Subs:
As soon as you press the enter key on your keyboard after typing the
variable name and As String , you should see the line appear underneath it.
Move your cursor back up to your variable name and you'll see the
dropdown boxes change to General and Declarations
Variables set up in the General Declarations area will be accessible from
anywhere. Much later, we'll need access to this ImageName variable. If we
had set it up in our Private Sub , it could only be seen from that Sub.
To get the name of the image from the cell on the spreadsheet, add this line:
ImageName = ActiveCell.Value
To set the images folder, add this:
ImageFolder = "images\"
To go one folder up from the sheets folder, we'll create a function. This way,
we can call the function into action whenever we need it. So create the
following function in your code window (the first line should be all on one
line):
Private Function NavigateFromWorkBookPath() As String
End Function
This is a Private Function called NavigateFromWorkBookPath , and
should be outside of the GetImage Sub. The return type is As String .
Add these lines of code to your new function (the last two lines should all
be on one line):
Dim WorkbookFolderPath As String
Dim SlashPos As Integer
Dim ImageFolderPath As String
WorkbookFolderPath = ThisWorkbook.Path
SlashPos = InStrRev(WorkbookFolderPath, "\")
ImageFolderPath = Left(WorkbookFolderPath, SlashPos)
NavigateFromWorkBookPath = ImageFolderPath
The first three lines just set up some variables. We then have this line:
WorkbookFolderPath = ThisWorkbook.Path
This gets the path of the workbook. In other words, the folder where you've
saved your spreadsheet.
The next line is this:
SlashPos = InStrRev(WorkbookFolderPath, "\")
This uses the string method InStrRev . This is short for "In String
Reverse." This method starts from the end of a string of text and searches
for a specific character. The character we want to search for is the "\"
character. This gets us the position of the last backslash in our file path.
The sixth line is this:
ImageFolderPath = Left(WorkbookFolderPath, SlashPos)
We're now using the Left string method. Between the round brackets of
Left , we first have the string we want to extract data from. After a comma
we have the backslash position. The Left method will then get all the
characters from the first position in our file path up to the final backslash.
So we're going from this:
C:\Users\Owner\Documents\vba\projects\sheets\
to this:
C:\Users\Owner\Documents\vba\projects\
The final line of the function is the return value.

NavigateFromWorkBookPath = ImageFolderPath
So the string we want to return from the function is stored in the variable
called ImageFolderPath .
We now need to call this function into action. Return to your GetImages
Sub. Add this line:
FilePath = NavigateFromWorkBookPath()
The NavigateFromWorkBookPath will return a string value. This value
will be the file path of the folder one directory up from the workbook path.
To complete the full file path that we need for the LoadPicture method,
add this line:
FullImagePath = FilePath & ImageFolder & ImageName
The variable called FilePath will hold something like:
C:\Users\Owner\Documents\vba\projects\
The ImageFolder variable will hold the name of the folder:
images\
The ImageName variable will hold the name of an image from the
spreadsheet:
london_eye.jpg
The FullImagePath line joins all these parts together to give us something
like:
C:\Users\Owner\Documents\vba\projects\images\london_eye.jpg
It's now time to load the image into the image box. We could just do it like
this:
Image1.Picture = LoadPicture(FullImagePath)
In between the round brackets of LoadPicture we have our new image
path. This all goes to the right of an equal sign. To the left of the equal sign
is this:
Image1.Picture
This sets the Picture property of Image1 .
However, we can do a little error checking, just to make sure that an image
at our location actually exists. Add the following If Statement to your code:
If Dir(FullImagePath) <> "" Then

Image1.Picture = LoadPicture(FullImagePath)
Image1.PictureSizeMode = 3
Else
MsgBox "Could not load image - no such file"
End If
The first line of the IF Statement is this:

If Dir(FullImagePath) <> "" Then
Dir is a method you can use to check if a directory (folder) exists. In
between the round brackets of Dir we have our FullImagePath variable. If
there's no such file in this directory then the Dir method will return a blank
string. Which is what we're checking for.
If everything is OK then we can use LoadPicture . Notice the second line:
The PictureSizeMode size property allows you to fill the entire image box.
We have ours set to a value of 3, which is really the value underneath a
constant called fmPictureSizeModeZoom . The other constants you can
use are:
fmPictureSizeModeStretch , which is a value of 1
fmPictureSizeModeClip , which is a value of 0
There is no 2, for some strange reason.
If there is no image at our specified location then we just have a message
box:
And that's it for the code to get the image. The code for your GetImage Sub
and NavigateFromWorkBookPath function should look like this:
The only thing left to do is to add the Call to GetImage from your
cmdLoad button:
Call GetImage
There's only two more lines to add to the cmdLoad button – the lines that
switch on the Previous Photo and Next Photo buttons:
cmdBack.Enabled = True
cmdNext.Enabled = True
The whole of your cmdLoad button code should look like this:
Try it out again. Run your form and you should see an image appear when
you click the Load Image Information button. You should also see the
Previous Photo and Next Photo buttons are now activated.
THE NEXT PHOTO
BUTTON
The question is, How do we move on to the next photo on our spreadsheet?
If you think about it, it's quite easy. The only thing you need to do is to
move the Active Cell down one:
ActiveCell.Offset(1, 0).Select
This line moves us down one row but keeps us in the same column. What
we then need to do is to call three of those Subs we set up:
Call GetTextBoxData
Call GetOptionButtonValue
Call GetImage
The three Subs do all the work of filling out the textboxes, the option
buttons and getting a new image.
What we can also do is to check that we haven't gone too far down the rows
and ended up on an empty row. We check for this in an If Statement:
If ActiveCell.Value = "" Then
Else
End If
The If Statement checks the ActiveCell for a blank value. If it is blank, we
can display an error message and then move the ActiveCell back one:
MsgBox "Last Row"
ActiveCell.Offset(-1, 0).Select
Exit Sub
To move back one row, use a negative number between the round brackets
of Offset. We're staying in the same Column, so we type a zero after the
comma:
Offset(-1, 0)
The Else part of the If Statement is where the calls to the Subs go. Here's
the whole of the code for your Next Photo button:
Run your form and try out your Next Photo button. You should be able to
move down through the spreadsheet, displaying all your images. When you
get to the end, you'll see the message box telling you that it's the last row.
THE PREVIOUS PHOTO
BUTTON
To move back through the images on the spreadsheet, the code is more or
less the same. Here it is:
The first line is slightly different, though. It's this:

ActiveCell.Offset(-1, 0).Select
The ActiveCell needs to be moved back one, which we do with Offset:
Offset(-1, 0)
A negative number is used for the Row value, and a 0 is used for the
Columns. This means move back one row but stay on the same column.
However, we can't keep going back as we have headers in the first row. The
If Statement checks for this:
If ActiveCell.Row = 1 Then
The Row property of ActiveCell is used to test for a value of 1, meaning
the first row on the spreadsheet. If it is the first row, we have this:
MsgBox "First Row"
Exit Sub
On the first line of the code for the button, we moved the ActiveCell back
one. If we've moved to the first row then we need to use Offset to move it
back to where it was:
This means move down one row from where the ActiveCell currently is.
The Else part again calls the Subs that do the works of filling out the form
with the data from the spreadsheet.
And that's it for the View Photos tab. Run your form and test it out. You
should now be able to cycle back and forward through all your photos.
THE ADD NEW PHOTO
TAB
What we'll do for the Add New Picture button is to display an Open File
dialogue box so that you can select a new image. The new image will then
be loaded into the Image Box control on the form. We'll also put the image
name into the tbImageName text box.
To make a start, select your Add New Photo tab at the top of your form.
Now double click your cmdAddNew button to open up a code stub.
Before we look at the Open File dialogue box, add a new variable to the
General Declarations area. Add the following:
Dim CopyImage As String
Your General Declarations area should now look like this:
The reason we need this variable here is because we're going to copy the
selected image over to the images folder. The image folder, remember, is
where we store all the pictures referred to on the spreadsheet. The image
you want to add might be in a different location. So copying your chosen
image over to the images folder ensures that all your pictures are in one
place.
When displaying an Open File dialogue box, you have the option of setting
an initial directory. This is the folder you want the dialogue box to be in
when appears. What we want is the images folder to display. To make that
happen, we can call our NavigateFromWorkBookPath function again. We
can then add the images folder to it:
FilePath = NavigateFromWorkBookPath()
FilePath = FilePath & "images"
Add the three lines of code to your own cmdAddNew button. The &
"images" is the part that adds the folder called images to the file path we
got from the function.
If you managed to add an Open File Dialogue Box Control earlier then
follow along with the steps below. If not, then click here to go to your
lesson: 64 BIT TUTORIAL
To display an Open File dialogue box the code is fairly simple. It's just:
CD1.ShowOpen
(The CD1 above is the name of the CommonDialog control you added
when you were designing the form.)
There are quite a lot of extra properties you can set, however. The Initial
Directory is one of these properties. You use it like this:
CD1.InitDir = FilePath
Before the equal sign, you have the name of your CommonDialog control
followed by a dot. After the dot, you type InitDir , which is short for Initial
Directory. After the equal sign, you can either hard-code a file path
surrounded by double quotes, or you can have the name of a variable. We
have our FilePath variable, which contains the path to our images folder.
You can also add a title to the top of the dialogue box. This is done with the
DialogTitle property:
CD1.DialogTitle = "Get Image File Name"
Whatever you want as the title goes between double quotes, after the equal
sign.
One thing you will want to set is the type of files that the dialogue box can
display. This is done with the Filter property. Examine the following line:
CD1.Filter = "JPEG Images|*.jpg|GIF
Images|*.gif|BITMAPS|*.bmp"
Before the equal sign, it's pretty straightforward: just type Filter after the
name of your CommonDialog control. After the equal sign, we have this:
"JPEG Images|*.jpg|GIF Images|*.gif|BITMAPS|*.bmp"
Here, we want to display three different image types: JPG, GIF, and BMP
files. Let's look at how the JPG image type is laid out:
JPEG Images|*.jpg
The part before the pipe character (the | symbol) is what appears in the
dropdown list to the right of File name :
To the right of the pipe character you type an asterisk (*). This means all
file names. You then need a dot followed by the type of file you want to
display, which is jpg in this case.
To add more file types to the dropdown list, you repeat the process:
|GIF Images|*.gif
Notice that there is now another pipe character, at the very start. This is
used to separate each file type.
If we wanted to specify files of any type and any name, we'd do this:
"JPEG Images|*.jpg|GIF Images|*.gif|BITMAPS|*.bmp|All
Files|*.*"
Notice the symbols used for All Files – two asterisks separated by a dot.
One final point to make is that all of your filters need to go between two
sets of double quotes.
The Open File dialogue box, however, doesn't actually open a file: it just
gets you a file name. This is done with the Filename property:
fName = CD1.Filename
Because you're trying to read a value, you need the Filename property to
the right of an equal sign. To the left of the equal sign is the name of the
variable that's going to store the file name.
With all that in mind, add the following code to your button (CD.Filter
should all be on one line)):
CD1.InitDir = FilePath
CD1.DialogTitle = "Get Image File Name"
CD1.Filter = "JPEG Images|*.jpg|GIF

Images|*.gif|BITMAPS|*.bmp"
CD1.ShowOpen
Dim fName As String
fName = CD1.Filename
Your coding window should now look like this (we've added some
comments):
64 BIT OPEN FILE
DIALOGUE BOX
To display an Open File Dialogue Box if you didn't add the control, the
code is quite different. It's still fairly straightforward, though. Add the
following to your coding window:
Dim fName As String
With Application.FileDialog(msoFileDialogOpen)
.InitialFileName = FilePath
.Filters.Clear
.Filters.Add "JPEGS", "*.jpg; *.jpeg"
.Filters.Add "GIF", "*.GIF"
.Filters.Add "Bitmaps", "*.bmp"
.AllowMultiSelect = False
If .Show = True Then
fName = .SelectedItems(1)
Else
MsgBox "Operation Cancelled"
Exit Sub
End If
End With
We're using a With Statement, here. But notice what comes after With:
Application.FileDialog(msoFileDialogOpen)
After the Application object, you can add a FileDialog method. In between
round brackets, you need to add one of four available built-in constants.
The four constants are these:
msoFileDialogFilePicker
msoFileDialogFolderPicker
msoFileDialogOpen
msoFileDialogSaveAs
The one we want is msoFileDialogOpen .
In between With and End With we first have this:
.InitialFileName = FilePath
The InitialFileName is like the InitDir above: it sets the folder that you
initially want to start from.
Next, we set up some filters:
.Filters.Clear
.Filters.Add "JPEGS", "*.jpg; *.jpeg"
.Filters.Add "GIF", "*.GIF"
.Filters.Add "Bitmaps", "*.bmp"
This sets the type of files we want to display in the dialogue box. The first
instruction clears the dropdown box, otherwise you'd end up with lots of
other file types that you don't need. Next, we add the filters, one per line.
The asterisk means "all files of this type".
The With Statement also sets AllowMultiSelect to False. The final thing it
does is to place the selected image into the fName variable. But we need to
do this in an If Statement:
If .Show = True Then
fName = .SelectedItems(1)
Else
MsgBox "Operation Cancelled"
Exit Sub
End If
To place the select image into the fName variable you use SelectedItems.
The 1 in round brackets means the first selected image. If you had set
AllowMultiSelect to True then you could have used a for loop to loop
through each file that the user selected.
The Show part is the one that actually displays the dialogue box. If it has a
value of True then a file was selected and the user clicked Open. If it has a
value of False then the Cancel button was clicked instead, and we can exit
the Sub.
Your coding window should look like this, though:
GET THE IMAGE NAME
Next, for 32 and 64 bit users, we can get the image name. We need this for
the text box. Here's the code to add:
If fName <> "" Then

SlashPos = InStrRev(fName, "\")
ImageName = Mid(fName, SlashPos + 1)
End If
If you click the Cancel button on the Open File dialogue box then the file
name will be blank. The If Statement checks for that. If it's not blank then
we extract the image name with these two lines:
SlashPos = InStrRev(fName, "\")
ImageName = Mid(fName, SlashPos + 1)
First, we get the position of the last backslash (\). This is then used in the
Mid string method. Between the round brackets of Mid we have the string
we're trying to chop, which is in the fName variable. After a comma, we
then need a starting point to start chopping text. The starting point is the
position of the backslash plus 1. Because we haven't specified an end
position, Mid will chop to the last character in the fName string. This will
get us the name of the image, which we stored in the ImageName variable.
The only other thing we need to do is to load the image into the Image Box
on the form. Here's the code for you to add:
If Dir(fName) <> "" Then
Image2.Picture = LoadPicture(fName)
CopyImage = fName
cmdSave.Enabled = True
tbImageName.Text = ImageName
Else
End If
This is more or less the same as we did before, for the Load Image
Information button. We're doing two things differently: one, we switch on
the Save button by setting the Enabled property to True ; two, we place the
image name into the tbImageName text box.
There is one line in the code above that may seem a little odd. This one:
CopyImage = fName
What this line does is to store a copy of the file name in the CopyImage
variable. This is the variable you set up in the General Declarations area.
We'll need this file name when we copy the picture to the images folder.
This is done when the Save New Details button is clicked. If we made
CopyImage local to the cmdAddNew button then the cmdSave button
wouldn't be able to see it.
But the whole of the code for the cmdAddNew button should look like this:
Try it out. Run your form and click the Add New Image button. Select any
image from your computer. When you click the Open button on the
dialogue box you should find that the image appears on your form, along
with the image name in the text box.
THE SAVE NEW DETAILS
BUTTON
To add a new entry onto the spreadsheet, the Save New Details button
needs to be clicked. The coding for this is fairly straightforward as we're
only transferring the form details to cells on the spreadsheet. We need to do
some error checking, however, just to make sure the correct data gets
transferred.
Return to your form and double click your cmdSave button to open up a
code stub.
The first thing we need to do is to select the next empty row on the
spreadsheet. If you have a look it again you'll see that this is row 6 for us:
After data gets added to row 6, the next empty row will be row 7, and so on.
Here's the line of code that selects the first empty cell in Column A:
LastRow =
Cells(LastRow + 1, 1).Select
You've met this code before, in a previous section. It searches for the last
row on the spreadsheet that has data in it. This row number is then stored in
the LastRow variable. The second line then uses this row number between
the round brackets of Cells . We need to add 1 to the LastRow variable to
get the next empty row down.
TEXTBOX ERROR CHECK
For the text boxes on the form, we'll only do some rudimentary error
checking. We'll just make sure that they're not blank.
Add the following function to your code:
Private Function IsBlankTextBox() As Boolean
End Function
We've called this function IsBlankTextBox . The return type for the
function is As Boolean .
The first line to add to your function is this:
IsBlankTextBox = False
All this does is to set the function to a value of False. The rest of the
function will check each textbox in turn to see if they are blank or not. If
they are blank then we can set IsBlankTextBox to True . We can then exit
the function and display an error message. Here's the code for the first
textbox:
If tbImageName.Text = "" Then
IsBlankTextBox = True
Exit Function
End If
To check for a blank textbox we only need a pair of double quotes with no
space between them. This goes after the equal sign. Before the equal sign
we just need the name of a textbox.
Here's the entire code for the function:
You can copy and paste the code for the first one, and then just change the
name of each textbox.
To call this function into action, return to your cmdAddNew button. Now
add this:
Dim IsEmptyTextBox As Boolean
IsEmptyTextBox = IsBlankTextBox()
If IsEmptyTextBox = True Then
MsgBox "Empty TextBox Detected"
Exit Sub
End If
The first line sets up a Boolean variable called IsEmptyTextBox . The next
line is this:
IsEmptyTextBox = IsBlankTextBox()
To the right of the equal sign is the call to our function. When the function
is executed, VBA returns with an answer. This will be True if there is an
empty textbox and False if everything is OK. The If Statement checks for
this value:
If IsEmptyTextBox = True Then
If an empty text box is found then we display an error and Exit the Sub .
COMBOBOX ERROR
CHECK
Our ComboBox has the default text Camera . We set this up when we
added the ComboBox to the form and typed something for the Text
property. If you want to know whatever a user selected from your dropdown
ComboBoxes then the property to use is this same Text property. The code
for us is this:
If ComboBox1.Text = "Camera" Then
MsgBox "No Camera selected"
Exit Sub
End If
What we're saying here is that if the Text property of ComboBox1 is equal
to "Camera" then execute some code. The code we want to execute is a
message box telling the user that no camera was selected from the
dropdown list. We then Exit the Sub .
That's all that's needed for the ComboBox error checking.
OPTION BUTTON ERROR
CHECKING
We have two option buttons on the form, one for Yes and one for No . We
want at least one of them selected. If no option button is selected then we
need to flag this as an error.
To check if an option button is selected you can use the Value property.
This will be either True (selected) or False (not selected). We can do both
in and If Statement, with the word And between the two. Like this:
If OptionButton3.Value = False And OptionButton4.Value = False
Then
MsgBox "No Option button selected"

Exit Sub
End If
This code just checks if OptionButton3 and OptionButton4 are both
False. If they are then it means no button was selected. In which case, we
display a message box and Exit Sub .
If everything is OK from all our error checking above then we can go ahead
and transfer the data from the form to the spreadsheet.
TRANSFERRING DATA TO
A SPREADSHEET
To transfer the data from the textboxes on the form to the cells on the
spreadsheet, we can use Offset:
ActiveCell.Value = tbImageName.Text
ActiveCell.Offset(, 1).Value = tbDateTaken.Text
ActiveCell.Offset(, 2).Value = tbInfo.Text
ActiveCell.Offset(, 3).Value = tbDimensions.Text
ActiveCell.Offset(, 4).Value = tbImageSize.Text
To the right of the equal sign, we have the name of a textbox followed by
the Text property. To the left of the equal signs, we have ActiveCell . The
first one is the cell that is currently selected, the one in the A column. We
can put the image name in here. The other four have Offset values. We use
Offset for the columns because we want to go across filling each cell.
To get the value from the Camera ComboBox, we can access the Text
property, just like we did for the textboxes:
ActiveCell.Offset(, 5).Value = ComboBox1.Text
To get the correct value from the option buttons, the code is slightly more
complex. It's this:
If OptionButton3.Value = True Then
ActiveCell.Offset(, 6).Value = "Yes"
ElseIf OptionButton4.Value = True Then
ActiveCell.Offset(, 6).Value = "No"
End If
Here, we have an If … ElseIf Statement. We're testing OptionButton3 and
OptionButton4 for a value of True. Our OptionButton3 is the Yes option,
while OptionButton4 is the No option. Depending on which option button
was selected, we put either Yes or No in the ActiveCell that has an Offset
column of 6.
There's only one more thing left to do now and that's copy the image over to
the images folder.
COPY AN IMAGE TO A
FOLDER
If you want to copy a file you can use the FileCopy method. This method
needs a source and a destination. The source is the location on your
computer where the file is; the destination is which folder you want to copy
it to.
Set up the following three variables in your code:
Dim ImageFolderPath As String
Dim SourceImage As String
Dim DestinationImage As String
We can get the destination folder by calling our
NavigateFromWorkBookPath function again and adding the images
folder (single lines of code):
ImageFolderPath = NavigateFromWorkBookPath()
ImageFolderPath = ImageFolderPath & "images\"
We can also add the name of the image to the end of the file path above:
DestinationImage = ImageFolderPath & ImageName

The ImageName variable is the one we set up earlier, in the General
Declarations area.
We also set the location of the new image in the General Declarations area,
in the CopyImage variable:
SourceImage = CopyImage
We don't really need to set up a new variable called SourceImage , but it's
there just to make things clearer when we copy the file.
When we copy the image over, we can test to see if the folder already
contains an image of this name.
If SourceImage <> DestinationImage Then
FileCopy SourceImage, DestinationImage
End If
The If Statement checks to see if SourceImage does not equal
DestinationImage . If it does then we know the file already exists in the
images folder. If they are not equal then we can go ahead and copy it:
FileCopy SourceImage, DestinationImage
The FileCopy method needs the path of the source file first. After a comma,
you type the destination file path. We have both of these in our
SourceImage and DestinationImage variables.
If you like, you can add a message at the end, just to show that everything
went OK:
MsgBox "New Image Information Added"
The whole of the code for the cmdSave button looks like this:
And that's it – the whole programme done! Test it out. Run your form and
select a new image. Click your Save New Details button and then return to
your spreadsheet. You should find that a new row has been added.
This has been a long project. But it should have given you lots of
experience at creating and coding user forms. There's obviously a lot more
to them, but experiment for yourself and it should bring your VBA skills on
a treat!
EXCEL VBA CHARTS
In this section, you'll learn how to manipulate charts with VBA.
There are two types of chart you can manipulate with VBA code. The first
is a chart sheet , and the second is an embedded chart . A chart sheet is a
separate sheet in your workbook, with its own tab that you click on at the
bottom of Excel. An embedded chart is one that is inserted onto a
worksheet. The two types of chart, sheet and embedded, use slightly
different code. We'll concentrate on embedded charts. But just to get a
flavour of how to create chart sheets with VBA, start a new spreadsheet.
Enter some data in cells A1 to B11. Something like the following:
Click Developer > Visual Basic to get at the coding windows. Create a new
Sub in Sheet 1. Call it ChartSheetExample . Add the following line:
Dim ChartSheet1 As Chart

Your code should look like this:
So instead of an Integer variable type or a string variable type, we now have

a Chart type. The name we've given this Chart variable is ChartSheet1 .
To add a chart sheet, all you need is the call to the Add method. However,
we'll set up our ChartSheet1 variable as an object, so that we can access
the various chart properties and methods. Add the following line to your
code:
Set ChartSheet1 = Charts.Add
Now add a With … End With statement:
With ChartSheet1
End With
The first thing we can do is to add the data for the chart. To do that, you
need the SetSourceData method. This method takes a parameter called
Source . The Source parameter needs a range of cells to grab data from.
Here's the code to add:
With ChartSheet1
.SetSourceData Source:=Sheets("Sheet1").Range("B1:B11")
End With
After Source:= we have this:
Sheets("Sheet1").Range("B1:B11")
This gets a reference to a Sheet called "Sheet1". The Range of cells we

want is cells B1 to B11. Your coding window should now look like this:
You can run your code at this stage. Press F5 on your keyboard, or click
Run > Run Sub/User Form from the menus at the top of the VBA
window. You should find that a new Chart sheet opens up in Excel:
Notice that Excel has automatically added a column chart. You can specify
what kind of chart you need, however, by using the ChartType property.
For the type of column chart Excel has added, you need the enumeration
xlColumnClustered :
With ChartSheet1
.SetSourceData
Source:=Sheets("Sheet1").Range("B1:B11")
.ChartType = xlColumnClustered
End With
There are lots of other values (constants) you can add for the ChartType in
place of xlColumnClustered. See the Chart Appendix at the end of the book
for more details.
The values we added in the A column of the spreadsheet have been used for
the X Axis (Category Axis) of the chart sheet, and the scores themselves as
the Y Axis (Values Axis). The same text has been used for the chart title
and the series legend - "Exam score". You can change all this.
To set a chart title at the top, your first need to switch on the HasTitle
property:
.HasTitle = True
You can then set the Text property of the ChartTitle property. Like this:
.ChartTitle.Text = "Chart Sheet Example"
Obviously, between the quotes marks for the Text property, you can add
anything you like. This will then be used at the top of the chart.
To set some text below the X and Y Axes, you need the Axes method. After
a pair of round brackets, you need two things: a Type and an Axis Group.
The Type can be one of the following: xlValue , xlCategory , or
xlSeriesAxis (used for 3D charts). The Axis Group can be either xlPrimary
or xlSecondary . Add the following to your With Statement:
.Axes(xlCategory, xlPrimary).HasTitle = True
Here, we're switching the HasTitle property on. In between the round
brackets of Axes, we've used the xlCategory type then, after a comma, the
xlPrimary axis group.
To add some text for the X Axis add this rather long line:
Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text =
Range("A1")
Again, we have .Axes(xlCategory, xlPrimary) . This points to the X Axis.
After a dot, we then have this:
AxisTitle.Characters.Text
This allows you to set the text. After an = sign, you can either type direct
text surrounded by double quotes, or you can specify a cell on your
spreadsheet. We've specified the cell A1. Whatever is in cell A1 will then
be used as the text for the X Axis.
To set some text for the Y Axis (the Values one), add the following code to
your With statement:
.Axes(xlValue, xlPrimary).HasTitle = True

.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text =
Range("B1")
This is similar to the code for the X Axis. The difference is that we are now
using the xlValue type. We've also set cell B1 to be used as the text.
Your code window should now look like this:
Delete the previous chart and run your code again. The chart that Excel
creates should now look like this
We now have a chart with a different chart title. The X Axis has been
changed to read "Student Number", and the Y Axis is "Exam Score".
You can delete the chart sheet now, if you like. We'll move on to embedded
charts.
EXCEL VBA AND
EMBEDDED CHARTS
An embedded chart is one that is on the same spreadsheet as your data,
rather than in a sheet all of its own. In this lesson, you'll see how to add an
XY Scatter chart. We'll use the same data as for the chart sheet in the
previous section.
Create a new Sub in your coding window. Call it EmbeddedChartExample.
For embedded sheets, you need a slightly different reference. For a Chart
Sheet, the referencing is like this:
Application.Workbook.Chart
Because an embedded chart goes onto a worksheet, you need a reference to

the Worksheet object:
Application.Workbook.Worksheet.ChartObject
Notice that an embedded sheet is a ChartObject rather than just a chart.

When you add embedded charts, you are adding them to a collection of
Chart Objects. Chart sheets don't need to get added to a collection.
Another complication is that embedded sheets are now shapes (Chart

Objects are a special kind of shape). So rather than having a Chart Object
collection, embedded charts get added to the shapes collection.
You start as before though, by setting up a Chart type:
Dim MyEmbeddedChart As Chart
However, examine this line of code to add an embedded chart (one line of
code):
Set MyEmbeddedChart = ActiveSheet.Shapes.AddChart.Chart
Again, we use Set to set up an object, this time called MyEmbeddedChart

. Notice what comes after the equals sign, though:
ActiveSheet.Shapes.AddChart.Chart
We start by referencing a worksheet, which can be the ActiveSheet. Next,
we need the Shapes collection. After Shapes, we use the method AddChart
. You can have round brackets after AddChart. Between the round brackets,
you can specify the type of chart you want to add. Like this (one line of
code):
ActiveSheet.Shapes.AddChart(xlXYScatter).Chart
(To add a different type of chart, see the Chart Appendix at the end of the
book)
We'll use a With Statement to add a chart type.
You can also specify a location and size for your chart between the round
brackets of AddChart (one line of code):
AddChart(Left:=200, Width:=300, Top:=50, Height:=300)

All these five parameters are optional, however.
If you miss out the round brackets, Excel gives you the default chart, which
is usually a column chart. If you want to choose a chart type, you can add it
on a new line:
MyEmbeddedChart.ChartType = xlXYScatter
Or use a With Statement, as we're going to do now. Add the following code
for you Sub:
Dim MyEmbeddedChart As Chart
Set MyEmbeddedChart = ActiveSheet.Shapes.AddChart.Chart
With MyEmbeddedChart
.SetSourceData
Source:=Sheets("Sheet1").Range("B2:B11")
.ChartType = xlXYScatterLines
End With
This is more or less the same as you did for a Chart Sheet. This time, the
ChartType is xlXYScatterLines . An XY Scatter Line chart, by the way, is
one that looks like this:
You could run your code right now. But let's add some formatting, first.
You can add formatting for the data points (the round dots above), and for
the lines joining the dots. The dots and the lines are all part of something
called a SeriesCollection . In the chart above, you can see we have only
one series - Series 1. You can have more than one series, and they can be
formatted separately. To do so, you add an index number between round
brackets:
SeriesCollection(1)
SeriesCollection(2)
SeriesCollection(3)
Etc
After a dot, you then type the formatting you need. The series dots are
called Markers. To set a background colour for your markers, the code is
this (single line):
.SeriesCollection(1).MarkerBackgroundColor = RGB(255, 255,
255)
So the properties is MarkerBackgroundColor . After an equal sign, you

type a colour. Here, we're using an RGB colour value. If you're meeting
these for the first time then the three numbers between the round brackets
are for Red, Green, and Blue values. The numbers go from 0 to 255. Values
of 255 in all three positions means white, and a 0 in all three position is
black. For our background colour, we went for white. You can have a mix
of colours by doing something like this:
RGB(14, 125, 66)
As well as setting a colour for your markers, you can set a style and a size.
The size is just a number:
.SeriesCollection(1).MarkerSize = 7
The style can any one of 12 values. To get round markers you need
xlMarkerStyleCircle (single line).
.SeriesCollection(1).MarkerStyle = xlMarkerStyleCircle
Here are all 12 marker styles. (Play around with them later.)
xlMarkerStyleAutomatic . Automatic markers

xlMarkerStyleCircle . Circular markers
xlMarkerStyleDash . Long bar markers
xlMarkerStyleDiamond . Diamond-shaped markers
xlMarkerStyleDot . Short bar markers
xlMarkerStyleNone . No markers
xlMarkerStylePicture . Picture markers
xlMarkerStylePlus . Square markers with a plus sign
xlMarkerStyleSquare . Square markers
xlMarkerStyleStar . Square markers with an asterisk
xlMarkerStyleTriangle . Triangular markers
xlMarkerStyleX . Square markers with an X
If you want to add a second data series to your chart, you use the Add
method of the SeriesCollection . Like this (single line):
MyChart.SeriesCollection.Add
Source:=ActiveSheet.Range("A2:A11")
You can add as many data series as you need with the above code.
FORMATTING THE
CHART
Two formatting options we can add are for the plot area (the area behind the
lines and markers), and the corners of the chart. To set a colour for the plot
area, the code is this (single line):
.PlotArea.Format.Fill.ForeColor.RGB = RGB(255, 255, 200)
Again, we have an RGB value after the equal sign. Before the equal sign,
we have this:
PlotArea.Format.Fill.ForeColor.RGB
This is self-explanatory: you're formatting the foreground fill colour of the

plot area.
To set rounded corners for your chart, the code is this:
ChartArea.RoundedCorners = True
Here's the adapted With Statement. Change your code to match:

You can run your programme, now. Click Run > Run Sub/User Form
from the menu at the top of the Visual Basic window, or press F5 on your
keyboard. You should see a Scatter Lines chart appear on your spreadsheet.
(You can add a button to your spreadsheet, if you want. Then attached the
Sub when you see the dialogue box appear.)
DELETING CHARTS
You can delete all the charts on your spreadsheet quite easily. Because
embedded charts are part of a collection (the ChartObjects) collection, you
can use a For … Each loop.
Add a new Sub to you coding window. Call it DeleteCharts. Now add the
following code for your new sub:
Dim MyCharts As ChartObject
For Each MyCharts In ActiveSheet.ChartObjects

MyCharts.Delete
Next
The first line sets up a variable of type ChartObject . The For … Each
loop then accesses each object in the ChartObjects collection. The code for
the loop uses the Delete method to delete each object that is in the
collection.
And that's it! Run the code, or attach it to a button on your spreadsheet. Any
chart you have on the spreadsheet will be deleted.
Exercise
Play around with the code for embedded charts. Try a different
MarkerStyle, change the MarkerSize, and amend the numbers for the RGB
colours.
In the next section, you'll learn how to add a chart to a user form.
ADDING CHARTS TO AN
EXCEL VBA USER FORM
In this tutorial, you'll learn how to add a chart to a user form. What we'll do
is to have a dropdown list at the top of a form. When you select an option
from the list, a chart will appear based on data from a spreadsheet.
Unfortunately, you can't just embed a chart onto a form like you can with a
spreadsheet. You need to create an image of the chart you want and then
load it into a picture box. The first thing to do, though, is to design the User
Form.
Start a new spreadsheet for this. Open up the Visual Basic Editor. From the
menus at the top of the Editor, click Insert > User Form . You should see a
grey form appear. With the form selected, locate the toolbox (View >
Toolbox ). Add a Command button to the top left of the form. Type Load
Chart as the Caption property. Change the Name property to cmdLoad .
Now locate and select the Combo Box item in the Toolbox:
Draw one out in the top right of your form. Set the Text property to Select a
Chart .
Now locate and select the Image control:
Draw an Image Box the size of the rest of your form. The whole of your
form will then look like this:
With the design of the form out of the way, let's add the data to the
spreadsheet.
Return to Excel. Create a spreadsheet like the following:

The data is just some running points total for four football teams in the
English premiership (season 2013 to 2014, when Cardiff and Newcastle
United were in the Premiership). To save you typing all the data, you can
grab the extra files for this book. You’ll need to download them from here:
There is a CSV file called football_data.csv that comes with the download
above. Open up this file and you’ll see the data. You can then import it.
To import the data, click inside cell A1 on your spreadsheet. Then click on
the Data ribbon at the top of Excel. Locate the Get External Data panel
then click the From Text item. You'll then get a dialogue box where you
can select the CSV file you downloaded above. A Text Import Wizard will
appear to guide you the rest of the way. On step two of the Text Import
Wizard, uncheck Tab as the delimiter and select Comma instead. Leave the
other steps of the Wizard on the defaults. When you're done, you should
have the data that you need for the rest of the tutorial.
LOADING THE COMBO
BOX
We can use the Initialize event of forms to load the combo box with data.
The data will be the team names in cells B1 to E1 on our spreadsheet. When
a team is selected, a chart will appear in the Image box displaying the data
for that team.
Go back to the coding window and your form. Double click your Command
button to get at its code. You should see your cursor flashing away in the
Click event of cmdLoad . We'll need to code here later. To get at the
Initialize event, have a look just above Private Sub cmdLoad_Click .
You'll see a dropdown list. From the list, select UserForm .
From the dropdown list just to the right of the UserForm one, select
Initialize :
When you select Initialize, a code stub will be created for you. This one:
Private Sub UserForm_Initialize( )

End Sub
(A code stub for the UserForm_Click event may also be created. You can
delete this.) To preload a combo box with text, you need to add items. You
do this with the AddItem method of Combo boxes. Here's the first line for
you Initialize event:
ComboBox1.AddItem ("Arsenal")
In between the round brackets of AddItem, you type whatever it is you want
as text for the dropdown list. Enclose this between double quotes.
Add three more items:
ComboBox1.AddItem ("Man City")
ComboBox1.AddItem ("Newcastle")
ComboBox1.AddItem ("Cardiff")
You can run your form to test it. Press F5 on your keyboard, or click Run >
Run Sub User Form from the menu at the top of the Visual Basic Editor.
Expand the drop down list and it should look like this: (we centred our list
text with the TextAlign property.)
Stop your form from running and return to the Visual Basic Editor. In the
code window, locate the cmdLoad_Click event code stub you got when
double clicking your command button. The first thing we can do here is
some error checking. We'll check to see if the dropdown list was left on the
default "Select a chart". If it was, we can bail out. Add this code, then:
If ComboBox1.Text = "Select a chart" Then

MsgBox "Select a chart from the dropdown list"
Exit Sub
End If
We're using the Text property of Combo boxes and checking if this text
reads "Select a chart". If it does, we display a message box and then Exit
the Sub . That way, the rest of the code won't get executed.
For the next four lines, set up some variables:
Dim MyChart As Chart

Dim ChartData As Range
Dim chartIndex As Integer
Dim ChartName As String
We're setting up a Chart variable, a Range variable to hold the data from
cells on the spreadsheet, an Integer variable to hold a value returned from
the combo box, and a String variable for the name of the chart (this will
appear at the top of the chart).
The next line to add is for grabbing that value from the combo box. It's this:
chartIndex = ComboBox1.ListIndex
The ListIndex property tells you which of the items from your list was
selected. The first item in your list is at position 0, the next item at position
1, and so on.
Your code so far should look like this:
Once we have which item was selected from the combo box, we can use a
Select Case statement to set the data from the spreadsheet. We can use
ActiveSheet.Range for this:
Set ChartData = ActiveSheet.Range("B2:B20")

The ChartData variable will then contain a column of data from the
spreadsheet.
We can also add a chart name:
ChartName = ActiveSheet.Range("B1")
We want the headings at the top, the team names. These are in cells B1, C1,
D1 and E1.
Here's the Select Case statement to add to your code (Each case has two
lines, the Set line and the ChartName line.):
Select Case chartIndex

Case 0
Set ChartData = ActiveSheet.Range("B2:B20")
ChartName = ActiveSheet.Range("B1")
Case 1
Set ChartData = ActiveSheet.Range("C2:C20")
ChartName = ActiveSheet.Range("C1")
Case 2
Set ChartData = ActiveSheet.Range("D2:D20")
ChartName = ActiveSheet.Range("D1")
Case 3
Set ChartData = ActiveSheet.Range("E2:E20")
ChartName = ActiveSheet.Range("E1")
End Select
So if the chartIndex variable contains a 0 (the first item in the combo box)
then the range B1:B20 from the spreadsheet will end up in the ChartData
variable. The chart name will be taken from cell B1. But if the user selects
the second item in the combo box then chartIndex will be 1, in which case
the data from the range C1:C20 will end up in the ChartData variable. The
chart name will be taken from cell C1. We continue like this for Case 2 and
Case 3.
The next line to add is this rather curious one:
Application.ScreenUpdating = False
What this does is to turn off something called ScreenUpdating. This has two
effects: one, it makes the code run faster; and two, it hides what Excel is
doing. Our code will add a chart to the spreadsheet. But we only need to
turn this chart into an image - we don't need to see it on screen. Once we
have the chart as an image, we can delete it from the spreadsheet. We'll then
turn ScreenUpdating back on later in the code.
For the next line, we can add a chart (this should be one line, and not two, if
the formatting of the book spreads it out.):
Set MyChart =
ActiveSheet.Shapes.AddChart(xlXYScatterLines).Chart
Here, we’re setting the chart to be an XY Scatter Line one.
The problem we have now is that, expect for columns A and B, the data we
want to use for the X axis and Y axis are not in adjacent columns. We want
the X axis (the bottom one) to be the consecutive numbers in the A column
(cells A2 to A20). We want this for all the charts. For the Y axis, we want
the values from the B, C, D or E columns. Column B is OK because you're
just selecting data like this in the spreadsheet below:
Excel will automatically use the A column for the X Axis. This is because
it's adjacent to the B column, the Y axis.
But if want, say, the Newcastle chart, then we need to select data like this:
These two columns are not adjacent, so Excel doesn't know what to use for
the X and Y Axes.
To select data the way we want it, we can use two properties of the
SeriesCollection: Values and XValues . You use them like this (two lines of
code):
MyChart.SeriesCollection(1).Values = ChartData
MyChart.SeriesCollection(1).XValues =
ActiveSheet.Range("A2:A20")
Type a dot after SeriesCollection(1) and then type Values . After an equal
sign, type the range of data you want to use as the values for this series.
These values will then be used for the Y axis. For us, this is our range data
that we stored in the ChartData variable. For the XValues, we want the
range of values in cells A2 to A2.
Here's some code to add:
MyChart.SeriesCollection.NewSeries
MyChart.SeriesCollection(1).Name = ChartName
MyChart.SeriesCollection(1).Values = ChartData
MyChart.SeriesCollection(1).XValues =
ActiveSheet.Range("A2:A20")
Notice the first two line. First we add a new series to the collection:
MyChart.SeriesCollection.NewSeries
Then we add the chart name:
MyChart.SeriesCollection(1).Name = ChartName
Whatever is in the variable ChartName will be used as the Name property

of SeriesCollection(1) .
EXCEL CHART IMAGES
Now that we have a chart, we can create an image from it. First, we need a
file name and location to save the chart. Add these two lines to your code
(the second one is a bit long):
Dim imageName As String

imageName = Application.DefaultFilePath &
Application.PathSeparator & "TempChart.gif"
The fileName variable is a string. To get a location to save the file, you can
use Application.DefaultFilePath . The default file path is usually the
Documents folder in Windows. You can check this location for yourself by
adding a message box (one line of code):
MsgBox "The default file path is " &

Application.DefaultFilePath
This will tell you where on your computer Excel is going to save the image
of the chart. The Application.PathSeparator part just gets you a backslash
character ("\"). At the end, you can then type a name for your file. Ours is
"TempChart.gif". As well as the GIF format, you can save your images as a
JPEG file, or a PNG file. If you want to keep the size of the image file
down, though, then use GIF or PNG.
To actually save a file, you need the Export method (one line):
MyChart.Export FileName:= imageName, FilterName:="GIF"

After a space, can have up to three parameters:
FileName - The name of your file

FilterName - An export filter (you get graphics filters when you
install Excel/Microsoft Office)
Interactive - Either True or False. (Supposed to display a dialogue box
with graphic filters, but doesn't seem to work. You can ignore this
parameter.)
We're just using the first two parameters, FileName and FilterName .
We're setting FileName to be whatever is in the variable imageName . The
FilterName is just whatever format you want to save your image as. If you
want to create a JPEG image then change the filter name to JPEG.
When the Export command is run, Excel will save the file for you. If a file
of that name already exists then it is overwritten, which is exactly what we
want. Nothing else is needed here.
The next step is to delete the chart on the spreadsheet: We need to do this so
that we don't have a lot of chart objects embedded in the spreadsheet. The
process is quite easy:
ActiveSheet.ChartObjects(1).Delete
All we're doing here is to using the Delete method of the ChartObjects
collection. The number between the round brackets is which number chart
you want to delete.
Only two more lines to go, now. The first of these is to switch back on
ScreenUpdating:
Application.ScreenUpdating = True
The last thing we need to do is to load the saved chart into our Image
control:
UserForm1.Image1.Picture = LoadPicture(imageName)
The Image control has a property called Picture . After an equal sign we're
then using the LoadPicture method:
LoadPicture(imageName)
In between the round brackets of LoadPicture you type a file path for the
image you want to load into your image control. For us, this is contained in
the variable called imageName.
And that's it. The whole of your code should look like this:
Test it out. Run your form and select the Arsenal item from the dropdown
list. Then click the Load Chart button. You should see this:
Now select the Newcastle item from the dropdown list. The chart will
change to this:
You can quickly see the difference in data as each chart is displayed. And
all from a dropdown list on a user form!
In the next part, you’ll tackle a TreeView Project.
A TREEVIEW PROJECT
In this section, you're going to be learning about the TreeView control.
You'll design a small form like this one:
The TreeView on the left of the form has five headings.
Each heading is called a parent node. Click the plus symbol next to a parent
node and you'll see other headings appear:
Because these headings are underneath a parent node, they are called child
nodes.
What we'll do is to pull information from a spreadsheet when a child node

is clicked. This information will appear in the labels and textboxes on the
right of the form:
In the form above, Botswana, a child node of the parent node Africa, has
been selected. The data for Botswana, therefore, appears on the form.
To follow along with the TreeView tutorial, we have a spreadsheet prepared
for you. It is one of the extra files you download previously. If you haven’t
yet downloaded the files, the link is here:
The file you need is called TreeViewProject.xlsx .

Once you have located the spreadsheet, open it up. Now save it as a macro
enabled workbook (XLSM file), so that you can do some coding with it.
The workbook consists of two sheets. The first sheet is the country data for
the Treeview. (Many apologies if your country is not on the list - no slight is
intended!) The second sheet contains the information we want to appear on
the form whenever a child node is clicked.
With that in mind, let's add a User Form to the project.

DESIGN THE USER FORM
With your macro-enabled spreadsheet open, click on the Developer ribbon
at the top of Excel. Click on Visual Basic in the Coding panel to open up
the VBA Editor.
When your editor opens, click on Insert > User Form from the menu bar at
the top.
In the properties panel for the new form, change the Name property to
Nations . Locate the Caption property and change it to Nations .
In the VBA toolbox locate the TreeView control:
Draw a TreeView onto your form. Draw it on the left, and then use the
resizing handles to make it a decent height. Make it wide enough for some
text, but not too wide.
With your TreeView selected, change the following two properties:
LineStyle : tvwRootLines
Style : tvwTreeLinesPlusMinusText
Your TreeView should look like this when you're done:
Notice that the default parent and child nodes just say, "Sample Node".
We'll change these defaults with code.
Add 5 labels to the right of your TreeView. Change the Caption properties
to: Populations (in millions), Life expectancy, Currency, Capital, and Fun
Facts. Add four more labels to the right of the first four labels. You can
change the style of your labels. Experiment with the following properties of
labels: BorderStyle, BorderColor, BackColor, Font.
Now add a textbox just below the Fun Facts label. Just below the textbox,
add a Command Button . Change the Caption property to Exit .
Your form in design view should look something like ours below:
Before getting into the TreeView code, what we can do is to add the code
for the Command Button. Double click your Exit button to open up its code
stub. Now add the following line:
The Me keyword refers to the current form. When this line is executed, the
form will close down.
ADDING NODES TO A
TREEVIEW
Items on a TreeView are called Nodes. The top level nodes are said to be
parent nodes. If a parent node has items of its own, these items are called
child nodes.
To add parent nodes to a TreeView the syntax is:
TreeviewObject.Nodes.Add relative, relationship, key, text, image,

selectedimage
First, you specify your TreeView object. You then use the Add subroutine
of the Nodes object. After the Add subroutine, you can specify up to 6
optional arguments: relative , relationship , key , text , image , and
selectedimage
The first two arguments are relative and relationship . If you're using the
relative argument, then you need to specify a unique value for each parent
node that you want to add. The relationship can be one of 5 values:
tvwFirst, tvwLast, tvwNext, tvwPrevious, tvwChild. We'll be using this last
one, tvwChild , when we add a child node.
The next two arguments are key and text. You can use just these two
arguments to set up a parent node. Like this (three lines of code):
Treeview1.Nodes.Add Key :="item 1", Text :="Parent 1"

Treeview1.Nodes.Add Key :="item 2", Text :=" Parent 2"
Treeview1.Nodes.Add Key :="item 3", Text :=" Parent 3"
Each key value above (item 1, item 2, item 3) is unique. The text can be
whatever you want as the node's text.
To add a child node to these parents, you need to use the relative and
relationship arguments (one line of code):
Treeview1.Nodes.Add "item 1" , tvwChild , "some unique key", "text

value"
The relative argument is now one of those unique key values from the
parent node (item 1, item 2, item 3). The relationship is tvwChild. This
means, "Set up a child node for the parent node called item 1". This child
node needs its own key and text value.
To clear all this up, have a look at the following code.
So we have three parent nodes. Each parent node has two child nodes. The
parent node with the Key "item 1", for example, has two child nodes. The
relative value for these child nodes comes first after Add : "item 1". The
unique keys are Cstr(one) and CStr(two) . This converts to a string the
values one and two. (These are quite arbitrary values and we could have
used almost anything as the unique keys.)
When this code is run, the TreeView will look like this:
The parent nodes we want for our TreeView are all continents: Africa,
Americas, Asia, Australasia, and Europe. We could set up the parent nodes
like this, then (five lines of code):
Treeview1.Nodes.Add Key :="Africa", Text :="Africa"
Treeview1.Nodes.Add Key :="Americas", Text :="Americas"
Treeview1.Nodes.Add Key :="Asia", Text :="Asia"
Treeview1.Nodes.Add Key :="Australasia", Text :="Australasia"
Treeview1.Nodes.Add Key :="Europe", Text :="Europe"
Here, the unique key is the same as the text value. Which is perfectly fine.
Instead of hard-coding the values, however, we can get them from the
spreadsheet, Sheet 1. Have a look at this and you'll see that the continents
are all in row 1 of Sheet1:
To get the value of Africa, for example, the code would be this:
Sheet1.Cells(1, 1).Value
This gets the value of the Cell in Row 1, Column1.
We can use this code as the Key and the Text for our Treeview (one line of
code):
Treeview1.Nodes.Add Key:= Sheet1.Cells(1, 1).Value , Text:= Sheet1.Cells(1, 1).Value
To get the other continents, we only need to change the number for the
column between the round brackets of Cells:
Treeview1.Nodes.Add Key:=Sheet1. Cells(1, 2) .Value, Text:=Sheet1. Cells(1, 2) .Value
We're now referring to column 2, but still pointing to row 1.

PARENT NODES
To add the parent nodes to our TreeView, we'll use the Initialize event of
the User Form.
From the dropdown boxes at the top of the coding editor, then, select the
UserForm item:
This will give you a click event, which you don't want. We need the
Initialize event. From the dropdown on the right, select Initialize:
An Initialize event will be added to your coding window. You can delete the
Click event, as you won't need it.
The first thing we can do in the Initialize event is to activate Sheet1. This is
the sheet with all the TreeView data. If we don't make Sheet1 the active
sheet then Treeview will try to pull its data from Sheet2, if that sheet is
displayed in Excel.
The code is only one line. This:

Worksheets("Sheet1").Activate
In between the round brackets of Worksheets, you can type the name of
worksheet in between quote marks.
The next five lines get the parent nodes for the TreeView. Add the
following:
Treeview1.Nodes.Add Key:=Sheet1.Cells(1, 1).Value, Text:=Sheet1.Cells(1, 1).Value
Press F5 to run your form. You should find that the TreeView looks like
this:
We now have five parent nodes on our TreeView. These parent nodes don't
have any child nodes, yet, however.
CHILD NODES
Have a look again at the spreadsheet called Sheet1:
The countries we want to add as child nodes are all underneath the headings
in row 1 of the spreadsheet. The number of items in each column are
different. What we can do here is to get the last cell with data in it for each
column. Then use a For Each loop to add the child nodes. We can do all this
with one Subroutine.
Add the following Sub to your code (two lines):
Sub FillChildNodes( ByVal col As Integer, ByVal continent As

String )
End Sub
We're going to be passing two things to this Sub called FillChildNodes : an

integer value for the column number, and a string value for the continent.
We're going to be using this string value with a counter to get a unique
value for the child nodes.
First, add the code to get the last cell with data in it (three lines of code):
Dim LastRow As Long

With Sheet1
LastRow = .Cells(.Rows.Count, col).End(xlUp).Row
End With
So if we pass in a value of 1 as the value for the col variable, then this line:
Cells(.Rows.Count, col).End(xlUp).Row
will read Cells(8, 1). This means that Excel has found 8 rows in column 1
with data in them.
If we pass a value of 2, however, as the value for the col variable then the
line would read Cells(7, 2), meaning Excel has found 7 rows in column 2
that have data in them.
In other words, we're getting the last row with data in it for a particular
column. Which column this is will depend on the value we pass in when we
call the Sub into action later.
The next thing we can do is to set up a counter. The counter will be used to
set a unique value for each child node. Add the following lines to your
code:
Dim counter As Integer

counter = 1
We can loop round and add the child nodes for the col variable we passed
in. Add this For Each loop to your code (two lines):
For Each country In Range(Cells(2, col), Cells(LastRow, col))
Next country
The variable that's going to hold the countries from the cells on the
spreadsheet is country . (We could have called this variable almost
anything.) Have a look at this part, though:
Range(Cells(2, col), Cells(LastRow, col))
We want the range of cells that hold data. The first cell we want is on row 2.
The column number is held in the col variable. That would get us
something like Cells(2, 1) as the starting point for Range . The end point is
the last row with data in it, Cells(LastRow, col) , plus the column number
held in the col variable.
If you look at Africa as an example, the For Each loop would read like this:
For Each country In Range( Cells(2, 1), Cells(8, 1))

This loops round every cell in column 1, the "A" column, and ends at the
last row with data in it, which is row 8 for Africa.
To add child nodes to each parent, place these two lines in your loop:
Treeview1.Nodes.Add Sheet1.Cells(1, col).Value, tvwChild,

continent + CStr(counter), country
The first thing we need after Node.Add is the relative node. This is the
parent node we added earlier, which is the name of the continents in the top
row of the spreadsheet:
Sheet1.Cells(1, col).Value
Next, we need the relationship , which is the inbuilt constant tvwChild .
As a unique ID for the child node we're just combining the name of the
continent with the counter:
continent + CStr(counter)
So the first time round the loop the ID will be "Africa1", then "Africa2",
"Africa3", etc.
Finally, to add some text for the child nodes we can use whatever is in the
country variable, which we're getting from the cells when we loop round.
Your Sub should look like this:

We now need to call this Sub into action.
Go back your form Initialize event. Underneath the parent nodes, add this
line:
Call FillChildNodes(1, "Africa")
The first value we pass in is the number 1. This means column 1. As the
second value passed in, the continent, we've typed "Africa". Instead, we
could have done this:
Sheet1.Cells(1, 1).Value
This would pull the value from row 1, column 1 on the spreadsheet.
Now add calls for the other four continents:
Call FillChildNodes(2, "Americas")

Call FillChildNodes(3, "Asia")
Call FillChildNodes(4, "Australasia")
Call FillChildNodes(5, "Europe")
Your Initialize event should now look like this:
You can run your form now. Press F5 to launch it. Expand one of the parent
nodes and you should see the child nodes have been added:
Now that we have child nodes, we need something to happen when we click
on them. Specifically, we want to pull data from Sheet2 on the spreadsheet
and place it in the labels and text box. We'll do that with a Node Click
event.
THE NODE CLICK EVENT
To detect if a parent or child node was clicked on, there is inbuilt event
called NodeClick . To access it, from the dropdown boxes at the top of the
coding editor, select the name of your TreeView, which is Treeview1 for us:
From the dropdown on the right, select NodeClick :
The NodeClick event will look like this (two lines of code):
Private Sub Treeview1_NodeClick( ByVal Node As

MSComctlLib.Node )
End Sub
Notice that between the round brackets of the NodeClick event, there is an
object variable called Node. This object variable has properties of its own
that you can access:
Node.Key
Node.Text
We can use the Key properties to detect if a parent node is selected:
If Node.Key = "Africa" Then

MsgBox "Parent Node"
End if
We only have five parent nodes so we can create a long line of Or clauses:
If Node.Key = "Africa" Or Node.Key = "Americas" Or Node.Key

= "Asia" Or Node.Key = "Australasia" Or Node.Key = "Europe"
Then
End if
If the node key is anything else then it must be a child node:
If Node.Key = "Africa" Or Node.Key = "Americas" Or Node.Key

= "Asia" Or Node.Key = "Australasia" Or Node.Key = "Europe"
Then

Else
MsgBox "Child Node"
End if
The child node that has been clicked can be retrieved using the Text
property of the Node object:
MsgBox "Child Node is: " & Node.Text
If you have a look at the data in Sheet2 of your spreadsheet, you'll see that
the countries are all in the "A" column. We can loop round this column and
check if the value in the cell matches Node.Text . In other words, Check the
text from the Node that was clicked and see if matches a value from the "A"
column. If it does, then we can use Offset to get the information from that
row. Here's the full code to add to your NodeClick event:
If Node.Key = "Africa" Or Node.Key = "Americas" Or Node.Key = "Asia" Or

Node.Key = "Australasia" Or Node.Key = "Europe" Then
Else
Dim LastRowID As Long
LastRowID = Sheet2.Cells(Sheet2.Rows.Count, "A").End(xlUp).Row
For Each ID In Range(Sheet2.Cells(2, 1), Sheet2.Cells(LastRowID, "A"))
If ID.Value = Node.Text Then

Label2.Caption = ID.Offset(, 1).Value
TextBox1.Text = ID.Offset(, 5).Value
End If
Next ID
End If
And here's what your coding window should look like (we've used the
underscore character to break up that long first line):
For the first part of the If statement, we don't do anything. That's because it
will be a parent node, and we haven't got any code to execute if a parent
node is clicked.
The code for the Else part of the If statement, however, handles the child
node clicks. First, we get the last row for column "A" that has data in it:
LastRowID = Sheet2.Cells(Sheet2.Rows.Count,
"A").End(xlUp).Row
We can then use that LastRowID in the For Each loop, as we did before
(two lines):
For Each ID In Range(Sheet2.Cells(2, 1),

Sheet2.Cells(LastRowID, "A"))
Next ID
Instead of the variable being called country, as in previous For Each loop, it
is now called ID. We then check to see if the value of ID matches
Node.Text:
If ID.Value = Node.Text Then

End If
If it does, then we've found a row of data that matches the node that was
selected. We can then grab data from Sheet2 using Offset:

TextBox1.Text = ID.Offset(, 5).Value
And that's it! Try it out. Run your form and select a child node. You should
find that the information for that entry on Sheet2 appears in your labels and
text box:
As an exercise, exit your form. Return to your spreadsheet. Enter a new
country on Sheet1. On Sheet2, enter some data for that country. Run your
form again and you should find that your new country appears on the
Treeview. When you click the country, its data from Sheet2 should appear
in the labels and text box.
And that’s it for this Excel VBA course. Hope you enjoyed it!
APPENDIX - CHART TYPE
CONSTANTS
Excel VBA has quite a lot of constants you can use for chart types. In a
previous section, we used xlColumnClustered . We used it like this:
.ChartType = xlColumnClustered
In the sections below, you'll find all the VBA constant you can use in place
of xlColumnClustered. You'll also see images of what each chart type
looks like.
Quick Jump
Column Charts Bar Charts Line Charts Pie Charts

Scatter Charts Area Charts Other Charts
COLUMN CHARTS
There are quite a lot of Column charts you can use in Excel. Consult the
table below for the correct constant to use with the Add or AddChart
method. Example uses:
ActiveSheet.Shapes.AddChart(xlColumnClustered ).Chart
.ChartType = xlColumnStacked
The table on the next page shows the Excel VBA Constant you need to use,
and the Chart Type you get.
BAR CHARTS
There are quite a lot of Bar charts you can use in Excel. Consult the table
below for the correct constant to use with the Add or AddChart method.
Example uses:
ActiveSheet.Shapes.AddChart(xlBarClustered ).Chart
.ChartType = xlBarStacked
LINE CHARTS
Consult the table below for the correct constant to use with the Add or
AddChart method. Example uses:
ActiveSheet.Shapes.AddChart(xlLine ).Chart
.ChartType = xlLineStacked
PIE CHARTS
Example uses:
ActiveSheet.Shapes.AddChart(xlPie ).Chart
.ChartType = xlPieExploded
XY SCATTER CHART
CONSTANTS
Example uses:
ActiveSheet.Shapes.AddChart(xlXYScatter ).Chart
.ChartType = xlXYScatterSmooth
AREA CHARTS AND
OTHER CHART
CONSTANTS
Example uses:
ActiveSheet.Shapes.AddChart(xlArea ).Chart
.ChartType = xlSurface
Area Charts
Other Charts

Excel VBA For Complete Beginners A Home A - Ken Carney

Uploaded by

Copyright:

Available Formats

Excel VBA For Complete Beginners A Home A - Ken Carney

Uploaded by

Document Information

Original Description:

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

Excel VBA For Complete Beginners A Home A - Ken Carney

Uploaded by

Copyright:

Available Formats

EXCEL VBA

Ken Carney for Home and Learn

Let's make a start.

In order to run macros without any annoying security warnings, click on

The Visual Basic Development Environment

A shortcut to run your code is to press the F5 key on your keyboard.

Watch a Macro Being Recorded

Click on Excel to activate it

The Record Macro dialogue box should appear:

You should see the following Macros dialogue box appear:

Excel Dot Notation

The Range Property

The Worksheets Object

The VBA Developer Toolbar

So we now have a Subroutine (a Macro) with the name Range_A1_D6 . In

Click No to return to the Save As dialogue box.

The Resize Property

To get a better understanding of how Resize works, try the following

So these variable names are OK

So this code just sets up a variable called MyNumber . We then store a

Using variables to add, multiply, divide, and subtract

Worksheets(1).Range("A2").Value = "Subtraction Answer"

Your coding window will then look like this:

Worksheets(1).Range("A2").Value = "Subtraction Answer"

Answer = Number_1 * Number_2

Worksheets(1).Range("A3").Value = "Multiplication Answer"

Answer = Number_1 / Number_2

Now go back to your code. Change Number_2 from 5 to 4:

If the above is somewhat confusing, just remember to use round brackets to

Your coding window will then look like this:

We've set up a variable called NumberOfRows . Instead of As Integer ,

A shortcut to running your Subs is to press the F5 key on your keyboard.

The number 1048576 is coming from our NumberOfRows variable. In

Dim NumberOfRows As Integer

Dim FloatingPoint As Single

Your coding windows will then look like this:

The value in the variable called FloatingPoint is being displayed in the

Dim FloatingPoint As Single

Dim FloatingPoint As Double .

Now the FloatingPoint variable is showing the answer to 10 divided by 3

So if you want greater accuracy in your calculation, use As Double rather

Change your code to this:

Dim FloatingPoint As Single

One final variable type that can come in handy is Variant:

Dim FloatingPoint As Variant

Dim MyText As String

In the next section, you'll learn about Conditional Logic.

IF I buy these shoes I will be happier

Programming in any language is heavily reliant on Conditional Logic like

The structure of a VBA IF Statement looks like this:

Dim MyNumber As Integer

Your Coding window will then look like this:

The first line of the If statement is this:

If MyNumber does indeed have a value of 10 then the If Statement is

MsgBox "Number = 10"

Click OK on the message box to return to your coding window. Now

In our code above, nothing is happening because we've set MyNumber to a

Dim MyNumber As Integer