How to Add the Developer Toolbar to Excel Before you can make a start, you need to add the Developer ribbon to 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 and 2013 click the File menu then select Options. From the dialogue box, click onCustomize 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 securi ty then you can always alwa ys 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 t he Visual Basic Development Environment. This is, after all, where you'll be writing all your code.
The Excel Visual Basic for Applications Development Environment There are a few ways to open up the VBA Editor in Excel. 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. Which ever 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 only has 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 doubleclicked 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.
In the next part, you'll use see how to record a Macro and watch what happens when Excel adds the code for you.
Watch as Excel records a Macro 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 letters 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 selectPersonal Macro Workbook :
Click OK. To record the Macro, select the cells A1 to A6. Click on the Home tab on the Excel ribbon. Locate theAlignment 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, selectSave 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 theBold 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 work book is hidden, click Cancel on the dialogue box. From Excel, click on the View ribbon. Locate theWindow 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. We'll do that in the next section below.
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.) 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 parameters for buy. For example, we could have a Discount parameter, and a DeliveryCharge parameter: tv.buy PaymentType:=Cash Discount:=No DeliveryCharge:=No
Notice that we've used a space to separate the three parameters and their values. So, back to Excel. In the VBA programming language, you'll uses these object, methods, properties and parameters 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 an object method 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.)
The Range Property
Quite a lot of the macros you'll write will need to reference the Range property. But 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:
Range("A1:B7")
Notice the semicolon separating the two cell references, and no spaces between the two. The first cell reference is the top left cell, while the second cell reference is the bottom right cell in your selection - a square, in other words. Another way to refer to more than one cell is to separate the two cell references with a comma. Each cell is then surrounded with double quotes. 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 theSelect 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
In the next part, we'll explore the VBA Developer toolbar in more depth.
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. 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. But back to the Controls panel. The other items are: 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 a 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. We'll add a button to a spreadsheet and activate our Sub when the button is clicked. We'll do that in the next part of this lesson below.
Adding a Button to an Excel Spreadsheet In the previous lesson, you created a simple Sub in the code window. In this lesson, you'll activate that Sub from a button on a spreasheet.
At the top of the VBA 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 typeRange("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 Excel 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.
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
Your coding window will then look like this:
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.
In the next part of this tutorial, we'll take a look at the Offset property. So save you work before moving on.
The Excel VBA Offset Property 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 from the previous lesson. Create another Sub and give it the nameRange_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
Now try these two exercises. Exercise Suppose you've used Range to reference the cell A2. How would you get to cell C5 using Offset? Exercise If you've used Range to reference the cell E8, how would to get to cell B3 using Range and Offest?
OK, we'll move on. Before doing so, make sure you understand how Offset works. We'll take a look at the Resize property in the next lesson below.
The Excel VBA Resize Property When you use Offset, as we did in the previous lesson, 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. Exercise Suppose the selected cell is A2, use Resize to select cells A2 to C2.
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 Excel VBA language.
Excel VBA and 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.
In the next lesson, you'll get some practice using variables in Excel VBA.
Excel VBA Variable Practice In the previous section, you learnt about variables. To get the hang of variables, create a new blank spreadsheet. Click on the Developer ribbon at the top of Excel. On the Controls panel, click on View Code. This will open up the Visual Basic Editor with the Sheet1 coding window already open for you. Set up a new Sub and call it Variable_Practice. Add the following two lines for the Sub: Dim MyNumber As Integer MyNumber = 10
Your coding window will then look like this:
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: Dim MyNumber As Integer
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 MyNumber As Integer 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:
In the next lesson, you'll see learn about something called Option Explicit.
Option Explicit In order to ensure that you don't have any variables that start with the Dim keyword, you can typeOption Explicit at the very top of your coding window. What this does is to instruct VBA to check your variables for you. Take the following code as an Example:
At the very top of the code we have Option Explicit. The Sub has one variable set up with the Dim keyword - MyNumber. On the second line, we place a value of 1 in this variable. On the third line, however, we have this: MyNumbers = 2
We have accidentally typed MyNumbers instead of MyNumber. If we didn't have Option Explicit at the top then VBA would have run this code and set up MyNumbers as a new variable of type As Variant. This could have led to errors further down the code, if we didn't realise that MyNumber andMyNumbers were two different variables. Instead, VBA will now not run the code at all. We'll get this error message:
The error message is telling us that there is an undeclared variable somewhere in our code, a variable not set up with the Dim keyword. (NOTE: There are other keywords besides Dim that you can start a variable declaration with. We haven't covered these yet, though.) So it's a good idea to type Option Explicit at the top of your code window in order to prevent variables being set up accidentally.
General Declarations
If you look again at the top of the coding window, where we had Option Explicit, you'll see two dropdown lists:
The first dropdown list says General . The second one says Declarations. But the second one only says Declarations because we have clicked the cursor at the top of the coding window. If you click inside of the Sub, the second list changes to the name of that Sub. You can set up variables at the top of your code, in the General Declarations area. If you do, then those variables can be seen from anywhere in the code for that window. Otherwise, variables are local to whatever Sub or Function you set them up in. For example, our MyNumber variable can only be accessed from the option_explicit_test Sub. If we set up other Subs in this window then MyNumber would not be available to these new Subs. However, if we moved the line Dim MyNumber As Integerto the General Declarations area then other Subs could access the MyNumber variable. You'll get some practice with variables set up in the General Declarations in a later lesson. In the next lesson, you'll see how to add and subtract in Excel VBA.
Using variables to add and subtractin Excel VBA 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 Dim Number_2 As Integer Number_1 = 10 Number_2 = 20 Worksheets(1).Range("A1").Value = "Addition Answer" Worksheets(1).Range("B1").Value = Number_1 + Number_2
Your coding window will then look like this:
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 Integer Dim Number_2 As Integer Number_1 = 450 Number_2 = 387 Worksheets(1).Range("A2").Value = "Subtraction Answer" Worksheets(1).Range("B2").Value = Number_1 - Number_2
Your coding window will then look like this:
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 387 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 Number_1 As Integer Dim Number_2 As Integer Dim Number_3 As Integer 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: Dim Number_3 As Integer Dim Answer As Integer
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: Worksheets(1).Range("B2").Value = Answer
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: Dim Answer As Integer Answer = 50 + 40 - 30 Worksheets(1).Range("B2").Value = Answer
In the next lesson, you'll see how to multiply and divide with Excel VBA.
sing variables to multiply and divide in Excel VBA In the last lesson, you used variables to add and subtract with Excel VBA code. In this lesson, you'll learn how to multiply and divide.
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: Dim Number_1 As Integer Dim Number_2 As Integer Dim Answer As Integer Number_1 = 10 Number_2 = 5 Answer = Number_1 * Number_2 Worksheets(1).Range("A3").Value = "Multiplication Answer" Worksheets(1).Range("B3").Value = Answer
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 code above. 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 selectMultiply_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 (/). (Quite bizarrely, though, you can also use the back slash (\) without getting any error messages. You may get errors in your calculations, though) Try out some division for yourself. Return to your coding window and add a new Sub. Call itDivide_Numbers. In between Sub and End Sub, type the following code: Dim Number_1 As Integer Dim Number_2 As Integer Dim Answer As Integer Number_1 = 10 Number_2 = 5 Answer = Number_1 / Number_2 Worksheets(1).Range("A4").Value = "Division Answer" Worksheets(1).Range("B4").Value = Answer
Your coding window will then look like this:
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): Dim Number_1 As Integer Dim Number_2 As Integer Dim Number_3 As Integer Dim Answer As Integer Number_1 = 8 Number_2 = 8 Number_3 = 4 Answer = Number_1 + Number_2 / Number_3
Worksheets(1).Range("A4").Value = "Division Answer" Worksheets(1).Range("B4").Value = 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 addition. 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.
In the next part, you'll learn about some more variable types in Excel VBA.
Other Variable Types in Excel VBA As well as declaring a variable to be of type Integer, as you have been doing so far, you can also have the following numerical 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 Longcomes 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 holds 4 bytes of data while As Doublecan 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. You can create a new spreadsheet for this, if you want. Or use the one you currently have open. But create a new Sub in a coding window (you should know how to do this by now). Call it RowCount. Add the following code: Dim NumberOfRows As Long NumberOfRows = Worksheets(1).Rows.Count MsgBox NumberOfRows
Your coding window will then look like this:
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 (2013). 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 see 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: Dim FloatingPoint As Single
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: Dim FloatingPoint As Single 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.
If Statements in Excel VBA 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.
If Statements
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 wordsEnd 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: Dim MyNummber As Integer
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 inMyNumber. 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 calledMyNumber. The first line of the If statement is this: If MyNumber = 10 Then
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:
MsgBox "Number = 10"
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 .
In the next lesson, you'll see how to extend your If Statements by using Else and ElseIf.
Excel VBA - Else ... ElseIf In the last lesson, you saw how to set up an If Statement. In this lesson, we expand the If Statement with Else and ElseIf.
Else
Between If and End If you can also add an Else part. The structure of an If … Else Statement looks like this: If Condition_To_Test Then Else End If
In our code from the previous lesson, nothing happened because we 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 from the last lesson to this (the new lines are in bold): Dim MyNummber As Integer MyNumber = 11 If MyNumber = 10 Then MsgBox "Number = 10" 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 see 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 ElseIf Statement: If Condition_To_Test Then ElseIf Condition_To_Test Then End If
You can have more than one ElseIf parts, as many as you need, in fact: If Condition_To_Test Then ElseIf Condition_To_Test Then ElseIf Condition_To_Test Then End If
Notice that the only difference between If and ElseIf is the word Else tacked on to 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: If Condition_To_Test Then ElseIf Condition_To_Test Then Else End If
To test all this out, add a new Sub. Call it If_Test_2. Then add the following code: Dim MyNummber As Integer MyNumber = 11 If MyNumber = 10 Then MsgBox "Number = 10" ElseIf MyNumber = 11 Then MsgBox "Number = 11" End If
Your coding window should look like this:
This is almost the same as before. The difference is the ElseIf part: ElseIf MyNumber = 11 Then MsgBox "Number = 11"
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. If MyNumber = 10 Then MsgBox "Number = 10" ElseIf MyNumber = 11 Then MsgBox "Number = 11" 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.
In the next lesson, you'll learn about the conditional operators that are used with If Statements.
Excel VBA Conditional Operators In the previous lessons on If Statements, you've already used one conditional operator - the equal sign. But there are others. Here's a list:
By using the various Operators, you can set up more sophisticated conditions for your If Statements.
Add another Sub to your code from the previous the previous lessons lesson s. Call it If_Test_3. As the code for your new Sub, add the following: Dim MyNummber As Integer MyNumber = 10 If MyNumber < 20 Then MsgBox MyNumber & " is Less L ess than 20" End If
Your coding window should look like this:
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 and End If gets gets executed. If it's FALSE 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 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: Dim MyNummber As Integer 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: Dim MyNummber As Integer
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: Dim MyNummber As Integer MyNumber = 19 If MyNumber = 20 Then 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.
In the next lesson, we'll take a look at the Logic Operators.
Logical Operators in Excel VBA In the previous lesson, you used the Conditional Operators. In this lesson, you'll learn about the Logic Operator.
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 table of them:
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 have 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 BooleanFlagvariable from above to demonstrate this concept. Create a new Sub in your code code window from the previous section. 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
Your coding window will then look like this:
Press F5 to run your code and you should see 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 word And goes between your two conditions. Be careful of doing this, though: If Age > 20 And < 30 Then
Here, we've only used the variable Age once. But you need to type your variable twice, once before the And and once after it. Only if both conditions evaluate to TRUE does the entire line become TRUE. If one of them is FALSE then the entire line is FALSE. You can test for more than two conditions: If Age > 20 And Age < 30 And BooleanFlag = True Then
In the code above, we're test three things: Age has to be greater than 20, Age has to be less than 30,BooleanFlag has to have a value of true. All three conditions have to evalute to TRUE before the entire line is TRUE.
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 Statement 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 20 and 25 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!
In the next part below, we'll take a look at some more practical ways to use the lessons you have learned in this and previous sections.
Excel 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 in 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. So, the question is, How can we do all this with VBA code? Let's start by breaking the problem down. Here's what will happen: 1. 2. 3. 4.
Click inside cell B1 Get the value of cell B1 Use an If Statement to test the value of cell B1 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 from the last lesson 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 up 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 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 assigned 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: ActiveCell(1, 2).Value
If you wanted to move one column to the left of where you are, you deduct 1: ActiveCell(1, 0).Value
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 selectSetGrades 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 anInterior.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: ActiveCell(1, 2).Interior.Color = RGB(0, 255, 0)
If you like, though, try a ColorIndex instead: ActiveCell(1, 2).Interior.ColorIndex = 4
Your code will then look like this:
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 andVerticalAlignment. 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:
OK, save your work as you'll need the spreadsheet for Excel VBA practice 2 in the next lesson below.
Excel VBA Practice 2 Suppose we added another student to the spreadsheet from our previous lesson. 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 Excel 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).Interior.Color = RGB(255, 0, 0)
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: 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: 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:
In the next lesson, we'll take a look at another option you have when you need some Conditional Logic - Select Case.
Select Case in Excel VBA 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 so that you can get some practice with Select Case in Excel VBA. We'll do that in the next lesson below
Excel VBA Practice 3 Add another name to your spreadsheet from practice from practice 1 and practice and practice 2. 2. This time, type the name in cell C1. In cell C2, enter a score. What we'll do is to put a grade in cell C3 and a some text in cell C4. We'll use Select Case. Add another Sub to your 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, 2).Value = "F" ActiveCell(1, 2).HorizontalAlignment = xlCenter
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:
OK, we're going to cover one final thing in this section if the course - With Statements. We'll do that in the next section below.
With ... End With in Excel VBA In a previous lesson, you had code like this: ActiveCell(1, 2).Value = "F" ActiveCell(1, 2).HorizontalAlignment = xlCenter 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 change 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 what it is you're trying to manipulate. We want to manipulate the Font property of ActiveCell. The Font property 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. We'll do that in the next section below.
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: Dim MyString As String MyString = "Some text" ActiveCell.Value = MyString
And you can get text out of cell on your spreadsheet: Dim MyString As String 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!
In the next few lessons, we'll take a look at the above list of methods and how to use them in your Excel VBA code. First up is changing case.
LCase and UCase in Excel VBA 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: Dim FullName As String 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.
In the next lesson, we'll cover three more inbuilt string functions: Trim, Len, and Space.
Trim, Len, and Space in Excel VBA These three string functions are not related, but they are easy enough to use. We'll use them together in this next example. You can use your Excel workbook and code from the previous section.
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 FullName As String 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. TheLengthFullName 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: Dim FullName As String 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 box 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.
In the next lesson, we'll take a look at the Replace function in Excel VBA.
Excel VBA Replace Function
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. You can use your spreadsheet and code from the Change Case section for this. 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
Your coding window will then look like this
We have two String variables set up here, OriginalText and CorrectedText. The value for theOriginalText 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: 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".
In the next lesson, we'll look at three more Excel VBA string functions: InStr, InStrRev, StrReverse .
Excel VBA Functions: InStr, InStrRev, StrReverse InStr is short for InString. This string function 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 (you can use your spreadsheet from the previous section for this): Dim Email As String Dim Location As Integer Email = "
[email protected]" 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.
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.
In the next lesson, we'll take a look at two more Excel VBA functions: Left and Right.
Excel VBA Left and Right functions 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 (you can use your spreadsheet from the previous section for this: Dim Email As String Email = "
[email protected]" 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 FullName As String 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 why you need 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: The above code only works for names that have two parts. It will fall down if the name is, say, David Lloyd George. But there is an easier way to do the exercise above, and one that will cover names of any length: by using the Split function. You'll see how to achieve all this after we cover something called Arrays. If you want to jump ahead, the page is here: Arrays and the Split function.)
In the next lesson, we'll look at one final string function - Mid.
he Mid Function in Excel VBA The final String function we'll look at is 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
Email = "
[email protected]" GrabbedChars = Mid(Email, 16, 4) MsgBox GrabbedChars
We've set up two String variables 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.
In the next lesson, we'll present you with a probem that will test your new String Method skills.
An Excel VBA String Method Exercise To get some practice with String methods, we're going to work through a problem. Try to solve the problem yourself before going through the solution below. OK, here's the problem. Problem
Suppose you had a product code on a spreadsheet that looked like this: PD-23-23-45
However, it's in the wrong format. First, you're told to remove all the hyphens. Then, you're told to turn the letters PD in the product code to PDC. So your finished work should look like this: PDC232345
The question is, how would you do this with Excel VBA code? If you get stuck solving the problem above, then here's the solution.
Solution
The first part of the problem, removing the hyphens, is fairly easy - just use Replace: Dim ProductCode As String ProductCode = "PD-23-2345" ProductCode = Replace(ProductCode, "-", "")
In between the round brackets of Replace we have the text we want to search, which is the variable called ProductCode. After a comma, we have the character we want to replace, the hyphen. Next, we have the new character, which is no text at all. This is done with two double quotes with no space between them. The second part of the problem, adding the "C" after "PD", would be easy too, if VBA had an Insert function. But it doesn't. Which makes this part of the problem a little bit harder. There are a few ways to insert the "C" in the correct place. We'll do it using the Left and Mid string functions. We'll use Left to grab the first two characters, then add the "C". We'll then use Mid to get the numbers. To get the first two characters and add the "C", the code is this: Dim Letters As String Letters = Left(ProductCode, 2) & "C"
Starting from the left of ProductCode, we grab two characters: Left(ProductCode, 2)
The "C" is added with concatenation: Left(ProductCode, 2) & "C"
The new three letter code is then stored in the variable called Letters. To get the numbers, use Mid: Dim Numbers As String Numbers = Mid(ProductCode, 3) Mid first needs the string you're searching, which is ProductCode for us. Next, we have 3 as the starting point to grab characters. This will grab the characters starting from the 3rd character in the string. Because we haven't specified an end number, Mid will grab the rest of the characters to the end of the string.
The only thing left to do is to join the two parts together:
Dim NewCode As String NewCode = Letters & Numbers MsgBox NewCode
This just uses concatenation to join the Letters variable to the Numbers variable. The final line uses a message box to display the results. The whole of the code, though, looks like this:
When you meet a problem like the one above, the solution is usually to use one of the Left, Right, or Mid functions (or all of them) to chop the string into pieces and then join them back together again. In the next lesson, we'll move on and tackle programming loops.
xcel VBA Programming Loops Consider the following code: Dim MyNumber As Integer 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 variableMyNumber.
And that's nice and 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, an 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. In 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 calledEndNumber. We've stored a value of 5 in the EndNumber variable. Then comes the first line of the loop: For StartNumber = 1 To EndNumber
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 1 in theStartNumber 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 wordFor 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 our 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 StartNumber As Integer Dim EndNumber As Integer Dim answer As Integer
EndNumber = 5 For StartNumber = 1 To EndNumber 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:
answer = answer + StartNumber
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. TheStartNumber 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.)
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: For StartNumber = 1 To EndNumber answer = answer + 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!
For Each Loops in Excel VBA For Each loops are normally used with collections and arrays (you'll learn about Arrays in a later lesson). This just means that you have one or more items to cycle through. It's still a For loop, though. But this time the word "Each" follows the word "For", with a space between the two. The Each part means "Each individual item in the collection or array". The structure of a For Each loop looks like this: For Each variable_name In collection_name Next variable_name
So you start with For Each. You then need a variable name. This can be almost anything you want, just like normal variables. VBA will store individual items into this variable. After your variable name you need the word "In". You then follow that with the name of the collection or array you're trying to access. The final line is the same as for normal For loops - Next variable_name. As the code for the For Each loop, though, you do something with Each item stored in thatvariable_name. Let's see an example. Suppose you have the following spreadsheet:
You want to remove the hyphens from each cell in the A column. To do this, you can use a For Each loop. The collection is the Range of cells A2 to A6. Each item is a cell in that range. The For Each loop would then start like this: For Each MyCell In Range("A2:A6")
The variable name that will store individual cells from the range is called MyCell. The collection after the word "In" is Range("A2:A6"). The end of the loop would then be: Next MyCell
As the code for the For Each loop we can do this: MyCell.Value = Replace(MyCell.Value, "-", "")
The variable called MyCell is now a cell in its own right. This means that MyCell will have a Value property - the contents of the cell. The first time round the loop this will be the contents of cell A2. After an equal sign, we're using the Replace function. We're replacing the hyphen with no space at all (this is done with two double quotes). So with just a couple of lines of code we can cycle through all the cells in the A column, doing whatever we want with the contents of each cell. A pretty powerful technique, hey!
Excel VBA - 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 loops. 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 in your coding window from the previous section. Call it CellsExample. Add the following code: Dim StartNumber As Integer Dim EndNumber As Integer EndNumber = 5 For StartNumber = 1 To EndNumber Cells(StartNumber, "A").Value = StartNumber Next StartNumber
The only difference between this For loop and the last one in the previous section 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 Cells(2, "A").Value = 2 Cells(3, "A").Value = 3 Cells(4, "A").Value = 4 Cells(5, "A").Value = 5
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 code 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 StartNumber As Integer Dim EndNumber As Integer 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: For StartNumber = 1 To EndNumber
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.
Excel VBA Do Loops There are two kinds of Do loops in Excel VBA, Do While and Do Until. Let's take a look at the Do While loop first.
The Do While Loop
The Do While loop is a lot easier to use than the For loop you used in the previous lesson, as you don't need to set a start condition, just the end condition. Here's the structure of a Do While 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 did 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 FALSE 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: Do While counter < 5 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 counter = counter + 1 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 an example, though. Add a new Sub to your coding window from the previous lesson. Call itDoWhileExample. 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: counter = counter + 1
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 While 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: Do While counter < 5 counter = counter + 1 Loop
And here is the Do Until Loop with a condition on the end. Do Until counter > 5 counter = counter + 1 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: just put Exit For if you need to bail out early.
Excel VBA Programming Arrays So far, you have been 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 l ooks 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 i n 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.
Excel VBA Programming 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 of the array. 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 in the coding window you created for the last lesson. Call itArrayExercise_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 caused 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: Cells(2, 1).Value = MyArray(2)
The third time round, these: Cells(3, 1).Value = MyArray(3)
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 in Excel VBA
The arrays in our last lesson were all one dimensional arrays. It's one dimensional because there's only one column of items. But you can have arrays with more than one dimension. In theory you could have an array with up to 60 dimensions, in Excel VBA. 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 . (You can use your spreadsheet from the previous lesson, if you like.) 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 3 times. The first time round, the values in the inner loop 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.
The Excel VBA Split Function In a 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, enter the name David Lloyd George into cell A1 on your spreadshet. Now 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 thetxt 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. When you enter a name into cell A1 of your spreadsheet, 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.
The Join Function
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 separat ed by spaces. We then use Join J oin 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 FullNameSpaces = Split(txt, " ") FullNameHyphens FullNameHyphens = Join(FullName, "-") MsgBox FullNameHyphens
The Join line is this: FullNameHyphens FullNameHyphens = Join(FullName, "-")
In between the round brackets of Join, you first need the name of the array you're puting back together as one piece pie ce of text. After a comma, you type the new separator, sep arator, surrounded by by double quotes.
More on Subs in Excel VBA 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 b y assigning the Sub to a button butt on 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 With ActiveCell.Font .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 fon t to bold, Arial, 16 points. Now this code is fine as it stands. stan ds. But suppose we wanted to use this same sa me 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 for matting code in its own subroutine? subrou tine? 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 two Subs, one called FirstCode and one called SecondCode. We could put a button on the spreadsheet spreads heet and then attach the th e macro FirstCode to it. But this wouldn't be much good, as theSecondCode 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. 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 wordPrivate 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 In the previous lesson, you saw how to set up and Call a Sub. In this lesson, you'll see how to hand values over to your subroutines. So open up the code you wrote last time and we'll make a start.
The code for our second Sub is this: With ActiveCell.Font .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: 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: Sub SecondCode(BoldValue As Boolean, NameValue As String)
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: Sub SecondCode(BoldValue As Boolean, NameValue As String)
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: 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: With ActiveCell.Font .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 wordsExit 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: 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.
More about the Excel VBA 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 goes 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: 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. That's because MsgBoxis actually a function (you'll learn about functions in the next lesson). The Response part 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 they return a value. The message box above needs to return a value of which of the buttons was 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 sign (+). 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 constantvbOK , which you can use instead of typing 1. Using the constant instead of the numeric values makes your code easier to read.
