Tutorial 1Tutorial 2Tutorial 3Tutorial 4

“The less effort, the faster and more powerful you will be” – Bruce Lee

Welcome to Ultimate VBA Tutorial Part 3 – Building a simple application. If you haven’t seen part 2 then you can find it here.

In this tutorial, we are going to create a VBA application from scratch. In the various sections of the tutorial, you will learn the necessary skills to create the application. Then in the final assignment, you will put it all together.

The focus of this tutorial series is on learning by doing. All the tutorials are packed with coding examples, videos and activities to help you on your way. You will find an assignment and a quiz at the end of each one. You can use these to test your knowledge and see how much you have learned.

Learning Outcomes

When you finish this tutorial you will be able to:

1. Create a working VBA Application.
2. Clear existing data from a report.
3. Add buttons to your worksheet to run your code.
4. Read through a group of records on a worksheet.
5. Use an If Statement to make a decision.
6. Use Excel functions in VBA.
7. Read through any range using a For loop.
8. Get any column or row from a range.
9. Remove the header from a range.
10. Use constants to make your code more readable.

References

VBA For Loop – A Complete Guide
VBA IF Statement – A Complete Guide

The Tutorial Workbook

For this tutorial, we are going to be using a specific workbook. You can download this workbook here

Introduction

In this tutorial, our aim is to complete a small VBA application. The data is provided in the downloadable workbook and we will be using it throughout this tutorial.

Each section of this tutorial will build towards the final application. At the end you will have the necessary tools and skills to build a small VBA application.

The Application

The screenshot below shows the data for the application

The application works as follows

1. The user selects a fruit from the drop down menu.
2. The user clicks on the “Create Report” button to run the application.
3. When the button is clicked the application reads through the Data worksheet.
4. It checks for records that match the user’s fruit selection.
5. It copies those records to the Report worksheet.

The following video provides a demonstration of how the final application will work

Now that we have seen how it works, let’s start acquiring the necessary skills to create this application.

Using Constants

We have already looked at the variables which are declared using the Dim keyword.

VBA has another type of variable which is called a constant. As the name suggests, a constant has the opposite behavior of a variable.

The value of a constant is set once and cannot be changed when the application is running. In other words, constants are read-only variables.

We declare a constant like this:

Const Max As Long = 100

It is a popular practice to use capital letters for constants to distinguish them from normal variables.

Const MAX As Long = 100

We use constants to avoid having “magic numbers” in our code. These are seemingly random numbers in the code e.g.

Value = Value * 0.23
total = total - 88.66

The meaning of these numbers may be obvious when we are writing our code. However, as time passes it will become less obvious what these numbers represent. This can make reading, understanding and updating code more difficult, especially if we haven’t looked at it in a while.

When we use constants it makes the code much easier to understand

Value = Value * BONUS
total = total - FEES

The advantage of constants are

• They are simple to use.
• They make your code readable.
• They prevent you from incorrectly changing a fixed value.
• The value is set one place so it is easy to update. Without constants you would have to change the value everywhere it is used.

If you try to assign a value to a constant after it has been declared you will get the error “Assignment to constant not permitted” e.g.

Sub ConstError()

    ' Declare and set the constant value
    Const BONUS As Double = 6.78
    
    ' Error - Assignment to constant not permitted
    BONUS = 6

End Sub

In this activity, we will use the worksheet Data from the downloadable workbook.

1. Create a new sub and call it Act1.
2. Create a const variable called ROW_HEADER and set the value to 1.
3. Create a const variable called COL_SALES and set the value to 3.
4. Get a number between 1 and 22 from the user.
5. Print the number and the sales amount at this number to the Immediate Window e.g. Number 1 would return sales of 23.
   Use the constants as the Cells parameters and the user number as the Offset parameter when reading from the sales cell e.g. Cells(?,?).Offset(?).

Sub Act1Const()

    Const ROW_HEADER As Long = 1
    Const COL_SALES As Long = 3
        
    Dim number As Long
    number = InputBox("Please enter the number ")
        
    Debug.Print "The sales at number " & number & " are " _
            & shData.Cells(ROW_HEADER, COL_SALES).Offset(number)
    
End Sub

For Loop Basic Use

The For Loop allows us to run a line of code multiple times.

Running the same line of code each time would perform the same action. However, by changing the value of a variable each time, we will get different behaviour.

In the following code we print out the numbers 1 to 5 to the Immediate Window.

Sub Print1To5()

    Dim i As Long
    For i = 1 To 5
        ' Print to Immediate Window(Ctrl + G)
        Debug.Print i
    Next i

End Sub

When the code runs, the Debug.Print line will run 5 times. Each time the variable i will have a different value.

Let’s look at the construct of the For loop line.

We have three changeable parts:

1. The long variable which is normally i.
2. The start value.
3. The end value.

How it works is as follows:

1. The variable i is set to the start value.
2. The code will work through the lines between For and Next just like normal code.
3. When it reaches the Next line the code jumps back to the For line.
4. At the For line it adds one to the variable. 1 is the default value(we can change this value using Step).
5. If the variable value is less than the end value the code will go through the lines between For and Next again.
6. If the variable value is greater than the end value the code will exit the loop.

1. Create a new sub called Act2.
2. Declare a long variable and call it Total.
3. Declare a long variable and call it i.
4. Create a For Loop from 1 to 5 using the variable i.
5. Add code so that each time the For Loop runs, it adds the current value of i to total variable.
6. Print out the final total to the Immediate Window. The result should be 15.

Sub Act1()

    Dim i As Long, total As Long
    For i = 1 To 5
        ' Add each value of i to the total
        total = total + i
    Next i

    Debug.Print "The total is " & total
End Sub

The reason that For loops are important is that we use them to read through data in a spreadsheet.

The following code will read through all the cells with sales data from our spreadsheet. We use i as the row number.

Sub GetAppleSum()

    Dim i As Long, sum As Long
    For i = 2 To 23
        sum = sum + shData.Cells(i, 3)
    Next i
    
    Debug.Print "The sum of items is " & sum

End Sub

In Tutorial 2, we used the CurrentRegion property to determine the range at runtime. Once we have the range we can get easily get the number of rows. We can then use the number of rows in our For Loop.

We can use the Rows.Count property to get the number of rows as the following code demonstrates

Sub GetRgRowCount()

    Dim rg As Range
    Set rg = shData.Range("A1").CurrentRegion
    
    ' Get the number of rows in the range
    Debug.Print rg.Rows.count

End Sub

We will use this in our next activity.

1. Create a new sub called Act3 and add code to do the following:
2. Get the range of data from the data worksheet.
3. Declare a long variable called sum to add the sales values.
4. Create a For Loop to read through the range. Use 2 as the start value of the For Loop. Use the count of rows in the range as the end value of the loop.
5. Add a line between For and Next to add the Sales values to the sum variable.
6. Add a line after the loop to print out the total value for Sales.

Sub Act3()

    ' Get the range of data
    Dim rg As Range
    Set rg = shData.Range("A1").CurrentRegion

    Dim i As Long, sum As Long
    ' Read through the data
    For i = 2 To rg.Rows.count
        ' 3 is the third column of the range
        sum = sum + rg.Cells(i, 3)
    Next i
    
    Debug.Print "Total value for sales is " & sum

End Sub

Decisions and If

In VBA we often need to make decisions and perform an action based on them.

We do this using the If statement. This is similar to the If function in Excel.

In the following code, we get a number from the user and print a line if the number is greater than 5.

Sub UsingIf()
    
    Dim a As Long
    a = InputBox("Please enter a value 1 to 10")
    If a > 5 Then
        Debug.Print "a is greater than 5"
    End If
  
End Sub

We can check if values are equal, different, less than, greater than and so on. See VBA Conditions for a list of the different ways we can use If.

The are two types of lines we can use with the If statement.

1. ElseIf – we use this to check for a second condition.
2. Else – this is a catch-all for all remaining conditions. It must come after all If and ElseIF lines.

Sub UsingElseIf()
    
    ' Get a letter from the user
    Dim code As String
    code = InputBox("Please enter a letter")
    
    ' Use the If statement to determine the action
    If code = "A" Or code = "C" Then
        Debug.Print "You are level 1."
    ElseIf code = "D" Or code = "E" Then
        Debug.Print "You are level 2."
    Else
        Debug.Print "You are level 3."
    End If
    
End Sub

1. Create a new sub called Act4 and add code to do the following:
2. Create a variable of type Long and call it Sales.
3. Ask the user to pick a row between 2 and 23.
4. Read the sales amount from the user selected row into the variables using shData.Cells() to get the cell value.
5. If the amount is greater than 30 then print the amount and the text “Above quota”.
6. For any other amount print “Below quota”.

Sub Act4()

    ' Get user input
    Dim row As Long
    row = InputBox("Please enter row between 2 and 23")
    
    ' Read the values to variables
    Dim amount As Long
    amount = shData.Cells(row, 3)
    
    ' Check the total value
    If amount > 30 Then
        Debug.Print "sales are " & amount & ". Above quota."
    Else
        Debug.Print " sales are " & amount & ". Below quota."
    End If

End Sub

Using If with the Range

In this section we are going to use For, If and the Range together.

In the following code we read through the data and count the number of times that Apple appears.

Sub GetAppleCountRg()

    ' Get the Range
    Dim rg As Range
    Set rg = shData.Range("B2").CurrentRegion

    Dim i As Long, count As Long, fruit As String
    
    ' Read through the cells
    For i = 2 To rg.Rows.Count
        fruit = rg.Cells(i, 2)
        If fruit = "Apple" Then
            count = count + 1
        End If
    Next i
    
    ' Print the result
    Debug.Print "The number of times that Apple appears is " & count

End Sub

In the above example we store the value in the variable fruit

fruit = rg.Cells(i, 2)
If fruit = "Apple" Then

We could actually do this in one line

If rg.Cells(i, 2) = "Apple" Then

However, using a variable is good practice. This is because it makes the code more readable.

In this example, there is not much difference between each method. When you create real-world code you would typically read from lots of cells. Using variables makes it much easier to quickly read and understand your code especially if you haven’t looked at it in a while.

1. Create a new sub called Act5 and add code to do the following:
2. Read through the range of data and get the total number of Oranges that have been sold.
3. Make sure to use variables when reading from a cell.

Sub Act5()

    ' Get the Range
    Dim rg As Range
    Set rg = shData.Range("B2").CurrentRegion

    Dim i As Long, sum As Long
    Dim amount As Long, fruit As String
    
    ' Read through the cells
    For i = 2 To rg.Rows.Count
        ' Read the row values
        fruit = rg.Cells(i, 2)
        amount = rg.Cells(i, 3)
        
        ' Check for Orange
        If fruit = "Orange" Then
           sum = sum + amount
        End If
        
    Next i
    
    ' Print the result
    Debug.Print "The total sales of oranges is " & sum

End Sub

Range Columns and Rows

Sometimes when we have a range we may want to access a particular row or column of the range.

The Range property provides an easy way of doing this

• The Rows property allows us to get a particular row.
• The Columns property allows us to get a particular column.

The following code shows how we use the Rows and Columns properties

Sub UseColumnsRow()

    ' Get the range
    Dim rg As Range
    Set rg = shData.Range("B2").CurrentRegion

    ' Get the second row A2:D2
    Dim rgRow As Range
    Set rgRow = rg.Rows(2)
    
    ' Get the third column: C1:C23
    Dim rgCol As Range
    Set rgCol = rg.Columns(3)
    
    ' Print the range addresses
    Debug.Print rgRow.Address
    Debug.Print rgCol.Address
    
End Sub

1. The number for Rows must be between 1 and the number of rows in the range i.e Range().Rows.Count.
2. The number for Columns must be between 1 and the number of columns in the range i.e Range().Columns.Count.

In the following code we get the first and last rows of the range

Sub UseRow()

    ' Get the range
    Dim rg As Range
    Set rg = shData.Range("B2").CurrentRegion
    
    ' First Row
    Debug.Print rg.Rows(1).Address

    ' Last Row
    Debug.Print rg.Rows(rg.Rows.count).Address

End Sub

1. Create a new sub called Act6A.
2. Read through the range of data on the Data worksheet and print out all the row addresses.
3. Create a new sub called Act6B.
4. Read through the range of data on the Data worksheet and print out all the column addresses in reverse order. Hint: Use Step -1 at the end of the For line.

Sub Act6A()

    ' Get the range
    Dim rg As Range
    Set rg = shData.Range("B2").CurrentRegion

    Dim i As Long
    For i = 1 To rg.Rows.count
        ' This will cause the "Subscript out of Range" error
        Debug.Print rg.Rows(i).Address
    Next

End Sub

Sub Act6B()

    ' Get the range
    Dim rg As Range
    Set rg = shData.Range("B2").CurrentRegion

    Dim i As Long
    For i = rg.Columns.count To 1 Step -1
        ' This will cause the "Subscript out of Range" error
        Debug.Print rg.Columns(i).Address
    Next

End Sub

WorksheetFunction

We can use Excel functions like Sum and Count in VBA. In the previous For loop examples, we could actually use the worksheet functions instead.

In the real world VBA, our requirements will rarely be so simple as a Sum or Count function. Having said that, using the worksheet functions can be useful in certain situations.

Sub UseFuncCount()

    ' Get the Range
    Dim rg As Range
    Set rg = shData.Range("B2").CurrentRegion

    ' Use Count function
    Dim count As Long
    count = WorksheetFunction.count(rg.Columns(3))
    
    ' Print the result
    Debug.Print "The count of items is " & count

End Sub

In the next example we will use the Sum function.

Sub UseFuncSum()

    ' Get the Range
    Dim rg As Range
    Set rg = shData.Range("B2").CurrentRegion

    ' Use SumIf function
    Dim sum As Long
    sum = WorksheetFunction.sum(rg.Columns(3))
    
    ' Print the result
    Debug.Print "The sum of sales is " & sum

End Sub

1. Create a new sub called Act7.
2. Get the Range of data from the Data worksheet.
3. Use the SumIf function to count the number of Oranges that were sold. The parameters of SumIf are Range, Criteria and Sum_Range.
4. Use the Columns property for the Range and Sum_Range.

Sub Act7()

    ' Get the Range
    Dim rg As Range
    Set rg = shData.Range("B2").CurrentRegion

    ' Use SumIf function
    Dim count As Long
    count = WorksheetFunction.SumIf(rg.Columns(2), "Orange", rg.Columns(3))
    
    ' Print the result
    Debug.Print "The sum of Oranges is " & count

End Sub

Removing the Header

When we use CurrentRegion the returning range will include the header row. Sometimes we want to remove the header row.

We do this in two steps

1. We use the Resize property to remove one row.
2. We use the Offset property to move down one row.

The starting range

Resize the range to one row less

Move the range down one row

The code to do this is pretty simple

Set rg = rg.Resize(rg.Rows.count - 1).Offset(1)

We can use this code as follows

Sub ResizeOff()

    Dim rg As Range
    Set rg = shData.Range("A1").CurrentRegion
    
    ' Remove header
    Set rg = rg.Resize(rg.Rows.count - 1).Offset(1)

    ' Print address
    Debug.Print rg.Address
    
End Sub

How to Clear Data

Clearing or deleting existing data is a useful but often overlooked task when creating a VBA application.

When we are creating a report, it is good practice to write code to clear the data. There are two main reasons for doing this

1. It saves us having to do it manually each time we run the code.
2. The results won’t be mixed with data from a previous report.

We use the ClearContents function of Range to clear data from a cell. We can use the Clear function to remove everything including formatting.

When dealing with the data in the current worksheet, we don’t want to clear the text from the header cells. Therefore, we will remove the header from the range first.

Sub ClearData()
    
    Dim rg As Range
    Set rg = shReport.Range("A2").CurrentRegion
    
    ' Remove header
    Set rg = rg.Resize(rg.Rows.count - 1).Offset(1)

    rg.ClearContents

End Sub

Note: When you are creating code like this for the first time you may make mistakes. It is therefore a good idea to create a copy of the data so you can easily revert to the original if something goes wrong.

1. Add some sample data below the header on the worksheet Report.
2. Create a new sub called Act8.
3. Add code to get the range and remove the header.
4. Run the code.
5. Add data in a different number of rows and run the code again.

Sub ClearData()
    
    Dim rg As Range
    Set rg = shReport.Range("A2").CurrentRegion
    
    ' Remove header
    Set rg = rg.Resize(rg.Rows.count - 1).Offset(1)

    rg.ClearContents

End Sub

Adding a Button

We are going to use a button to run our report.

When the user clicks on the button we can set it to run a sub. We refer to this sub as a Click Event.

To add a button to the worksheet, we use the following steps

1. Go to the Developer tab on the ribbon.
2. Select Insert from the Controls section.
3. Click on the top left item in the Form Controls and the cursor will turn into a cross.
4. Drag the cursor on the worksheet to draw the button.
5. The Macro dialog will appear – you can cancel this dialog.
6. Right-click on the button and select “Edit Text” to change the text.

The video below shows how to add a button to a worksheet

How to Organise our Click Events

Before we connect the button to code, let’s have a look at a simple way to organise our code.

We assign a sub to a button and when the button is clicked it will run that sub. We can these subs Click Events.

To make our code more organised, we can create a module called Click_Events and place all our Click Event subs here. This makes it easier to navigate our code.

There are 3 simple practices we can follow

1. Create a module and call it Click_Events.
2. Place _Click at the end of the name of subs that are click events.
3. Only code in this sub for calling another sub.

We will see how to do this in the following activity.

1. 1. Open a new workbook.
   2. Add a button to the worksheet.
   3. Change the text on the button to “Display Message”.
   4. Go to the Visual Basic Editor(Alt + F11).
   5. Create a new module and call it Click_Events.
   6. Add a sub and call it DisplayMsg_Click.
   7. Add a new module and add the following sub

Sub DisplayMsg()
    MsgBox "The button was clicked"
End Sub

1. Go to the sub DisplayMsg_Click and add the line DisplayMsg.
2. Go to the worksheet, right-click on the button and select Assign Macro.
3. Select DisplayMsg_Click from the list.
4. Click on the button. The code should display the message “The button was clicked” in a dialog.

' In the Click_Events module
Sub DisplayMsg_Click()
    DisplayMsg
End Sub

' In Module1
Sub DisplayMsg()
    MsgBox "The button was clicked"
End Sub

In this activity, we called one sub from another. This is something that we will look at this in more detail in later tutorials

Tutorial Assignment

We’ve covered a lot of stuff in this tutorial. So let’s put it all together in the following assignment

I have created a simple workbook for this assignment.

You can download it using this link.

The Assignment

In the workbook, there are two worksheets

1. Data – contains a list of fruit sales.
2. Report – where the output will be written.

The application will work as follows: On the Report worksheet, the user selects a fruit in cell F1 and clicks on a button(to be added). The sales of the selected fruit are then displayed in columns A to D.

The Instructions

Open the assignment workbook. You can place your code here

1. Create a module and call it Click_Events.
2. Create a sub in this module and call it Report_Click.
3. Add a button to the Report worksheet. Assign the button to use Report_Click when it is clicked.
4. Create a module and call it Report.
5. Create a sub in this module and call it Assignment.
6. Declare a constant variable for the Report row and set it to 2.
7. Read the value from Cell F1 on worksheet Report to a string variable called UserFruit.
8. Add code to clear the existing data from the Report worksheet. The header cells should not be cleared.
9. Declare a variable called RowDest and set it to the constant for the report row.
10. Get the range of the source data on the Data worksheet.
11. Declare a string variable call it Fruit.
12. Declare a range variable call it rgDest.
13. Create a For Loop to read through the data range starting at row 2.
14. In the loop read a value to the variable Fruit from the fruit column.
15. Use an If statement to check if the Fruit matches the UserFruit variable.
16. If they match then get the destination range. Get the first cell in the row and resize using the column count of the source range.
17. Add 1 to the RowDest variable.
18. After the loop, add an If statement to check if no records were found. You can compare the value of RowDest to REPORT_ROW constant.
19. If no records were found then a message should be displayed to the user.
20. Select a fruit in cell F1 and click the button on the Report worksheet. A list of all sales records for the selected fruit should appear. Try with different fruit selections to ensure the application works correctly.

Sub Report_Click()
    Assignment
End Sub

Sub Assignment()
    
    Const REPORT_START_ROW As Long = 2
    
    ' Get the fruit from F1
    Dim SelectedFruit As String
    SelectedFruit = shReport.Range("F1")
    
    ' Clear existing data
    Dim rgReport As Range
    Set rgReport = shReport.Range("A1").CurrentRegion
    If rgReport.Rows.Count > 1 Then
        ' If more than one row of data then remove header and clear data
        Set rgReport = rgReport.Resize(rgReport.Rows.Count - 1).Offset(1)
        rgReport.Cells.Clear
    End If
    
    ' Set start Row of Report
    Dim Rowdest As Long
    Rowdest = REPORT_START_ROW
    
    ' Get the Range or source data
    Dim rgSrc As Range
    Set rgSrc = shData.Range("B2").CurrentRegion

    Dim Fruit As String, rgDest As Range
    
    Dim i As Long
    For i = 2 To rgSrc.Rows.Count
        ' Check for fruit
        Fruit = rgSrc.Cells(i, 2)
        If Fruit = SelectedFruit Then
           
           ' Get the destination range
           Set rgDest = shReport.Range("A" & Rowdest)
           
           ' Resize to size of source row
           Set rgDest = rgDest.Resize(ColumnSize:=rgSrc.Columns.Count)
           
           ' Copy the row
           rgDest = rgSrc.Rows(i).Value
           
           ' Move to next destination row
           Rowdest = Rowdest + 1
        End If
    Next i
    
    ' Inform the user if there are no records
    If Rowdest = REPORT_START_ROW Then
        MsgBox "Could not find any records for " & SelectedFruit
    End If
        
End Sub

Tutorial Quiz

The following quiz is based on what we have covered this tutorial.

1. 1. Give three advantages of constants?

1. 1. What is the output from the following code?

Sub PrintValues()

    Dim i As Long
    For i = 10 To 2 Step -2
        Debug.Print i
    Next i

End Sub

1. 1. How do we get the number of rows in a range? How do we get the number of columns in a range?

1. 1. When using the If statement, which statement allows us to add more conditions as alternatives
      to the main If condition?

1. 1. Why does the following code give a “Type Mismatch” error if we run it on our data?

Sub GetAppleCountRg()

    ' Get the Range
    Dim rg As Range
    Set rg = shData.Range("B2").CurrentRegion

    Dim i As Long, sum As Long
    
    ' Read through the cells
    For i = 1 To rg.Rows.count
        sum = sum + rg.Cells(i, 3)
    Next i
    
    ' Print the result
    Debug.Print "Total sales are " & sum

End Sub

1. 1. How do we return the last row of a range?

rg.Rows(rg.Rows.count)

1. 1. How do we sum the range sheet1.Range(“A1:A10”) without using a loop?

WorksheetFunction.Sum (Sheet1.Range("A1:A10"))

1. 1. If you have the range rg, how do you remove the header?

Set rg = rg.Resize(rg.Rows.count - 1).Offset(1)

1. 1. How do we assign a macro to a button from the worksheet?

1. 1. How would we display the message “The application has completed.” to the user in a dialog?

MsgBox "The application has completed."

Conclusion of Tutorial

Congratulations on finishing this tutorial. If you have completed the activities, assignment and the quiz then you will have increased your VBA knowledge considerably. This will help as we move on to the next tutorial.

In the next tutorial, you are going learn how to master the Debugger. This is a powerful tool that helps step through your code and see what is happening in real-time.

Tutorial 1Tutorial 2Tutorial 3Tutorial 4