My last post provided a beginners guide to getting started with VBA. This week we are going to build upon these skills and create an application within Excel that will read data and sort it into tabs based on its category. As part of this tutorial, we will cover the following features of VBA:
- Declaring and using variables
- Referring to workbooks, sheets and cells, also known as objects
- Loops – repeat a block of code until a condition is met
- Conditional statements – if something is true, then perform an action
This tutorial is focused on Excel but these features apply to all iterations of VBA.
Before We Start
Before we jump into this tutorial, there are a couple good practices we should implement to ensure you start your programming off on the right foot!
Adding the Option Explicit command to the top of your module requires all variables to be declared. This means that you wont accidentally miss-spell a variable and spend hours trying to work out why your code is not working.
A comment is a line of text in your program that is not executed. In VBA, a comment is defined by prefixing with the single tick like so:
' this is a comment
The benefit of comments are that you (and others) understand what your code is trying to achieve. Without a comment, it is up to the reader to decipher, usually incorrectly, the intent of the code.
Absolute References over Relative
What I mean by this is best explained by the ActiveSheet command. ActiveSheet refers to which ever tab is currently selected in Excel. The problem with referring to ActiveSheet is that you are relying on the user having selected the correct sheet before executing the code. There are some cases when this is OK (as you will see later), but usually, it is better to refer to a sheet by tab name:
' Not the best practice ActiveSheet.Range("A1").Value ' Best Practice Sheets("Target Sheet").Value
Give Your Sheets a Name
Even referring to sheets by tab name is not foolproof. If the tab name changes, then the reference will be lost. A way to avoid this is to update the name in the VBA editor. To do this, select the sheet in the project window and under properties, search for the name field. Enter a unique name here. Once done, you can refer to the sheet like so:
Let’s Get Started…
To play along at home, create a new workbook and add four worksheets. Rename the first sheet to Source Data and copy the below data into cell A1.
Rename the last three sheets to the following: Fruit, Vegetable, Car. These will be the target sheets for the data. It is possible to automate the creation of these sheets in the program but in order to explain the core features of VBA, I will save that for a later tutorial.
Put simply, a variable is a placeholder for a value, with an associated symbolic name, known as an identified. It allows a program to call on this value by it’s identifier. Variables have many types, but the most common are:
- String – This is text. When setting the value of a string, we encapsulate the text in “rabbit’s ears”.
- Integer – This is a number. There is no requirement to encapsulate numbers.
In VBA, a variable is declare like so:
Dim fruitCounter As Integer
where rowCounter is the unique identifier and Integer is the type of variable. Once a variable is declared, we can then set it’s value like so:
fruitCounter = 1
An exception to this rule is working with object variables. Object variables hold more complex information, such as worksheets or a range of cells. It allows the code to refer to a worksheet by referring to it’s unique identifier. When setting the value of a variable that is an object, we must use the command Set.
Dim sourceData As Worksheet Set sourceData = ActiveWorkbook.Sheets("Source Data")
Referring to Objects
Put simply, an object is a collection of stuff. In Excel, an example includes a workbook, which contains one or more worksheets. Each worksheet contains multiple cells.
In VBA, we can chain commands together in order to reference these objects.
' Referencing a workbook ActiveWorkbook ' Referencing a sheet within a workbook ActiveWorkbook.Sheets("Source Data") ' Referencing cells within a worksheet ActiveWorkbook.Sheets("Introduction").Range("A1")
A loop allows you to iterate through an object. This is useful when you want to look at all the sheets in a workbook or all the values in a list of cells.
For Each cellObject In sourceData.Range("A2:A9").Cells Debug.Print cellObject.Value Next cellObject
The above snippet of code loops through a range of cells (A2, A3, A4 … A9) and prints their content to the immediate window.
A conditional statement performs a test. If the test is true, then an action will be executed. If the test is false, the action will be skipped.
If cellObject.Value = "Fruit" Then ' store the value on the next row ActiveWorkbook.Sheets("Fruit").Cells(fruitCounter, 1).Value = cellObject.Offset(0, 1).Value ' keep record of what row we are up to for next time fruitCounter = fruitCounter + 1 End If
The above code tests if the value of the current cell matches one of our target sheets. If there is a match, we save the value, if not, then the code is skipped.
The Finished Product
We have touched on many of the core features of VBA, most of which are all you need to write a functioning program. Below is the finished product.
Option Explicit Sub categoriseData() ' declare variables Dim fruitCounter As Integer Dim vegCounter As Integer Dim carCounter As Integer Dim cellObject As Variant Dim sourceData As Worksheet ' set the default value of the variables fruitCounter = 1 vegCounter = 1 carCounter = 1 ' this links to the sheet that contains the copied data Set sourceData = ActiveWorkbook.Sheets("Source Data") ' loop through the categories For Each cellObject In sourceData.Range("A2:A9").Cells ' add the data to the correct sheet If cellObject.Value = "Fruit" Then ' store the value on the next row ActiveWorkbook.Sheets("Fruit").Cells(fruitCounter, 1).Value = cellObject.Offset(0, 1).Value ' keep record of what row we are up to for next time fruitCounter = fruitCounter + 1 ElseIf cellObject.Value = "Vegetable" Then ActiveWorkbook.Sheets("Vegetable").Cells(vegCounter, 1).Value = cellObject.Offset(0, 1).Value vegCounter = vegCounter + 1 ElseIf cellObject.Value = "Car" Then ActiveWorkbook.Sheets("Car").Cells(carCounter, 1).Value = cellObject.Offset(0, 1).Value carCounter = carCounter + 1 End If Next cellObject End Sub
Congratulations! You have written a short program that leverages the basic foundations of all VBA programming. With this knowledge, you can easily build more complex applications that will automate everyday tasks and save you hours of time. If you have any feedback leave it in the comments below or hit me up on any of my social networks!