Visual Basic is an object-oriented programming language developed by Microsoft. It makes it fast and easy to create type-safe .NET apps. Some common uses for Visual Basic are creating Windows-based applications, utilities to perform specific tasks, and adding functionality to existing applications.

We just published a full visual basic course for beginners on the freeCodeCamp.org YouTube channel.

Kevin Drumm created this course. Kevin is the head of computer science at a school in the UK. He has also created hundreds of programming tutorials.

In this course you will learn about the basic constructs of high level programming languages, including sequence, selection and iteration. You will learn how to build an event-driven, form-based, user interface to capture input, and you will learn how to write code to validate and process the data collected.

Here are the sections in this course:

• Hello Visual Studio
• Customise The Visual Studio IDE
• Output and Variables
• Variable Data Types
• Input with Windows Forms
• Debugging Code
• Arithmetic Operators
• Complex Arithmetic Expressions
• Selection with If Statements
• Logical and Relational Operators 1
• Logical and Relational Operators 2
• Select Case
• For Next
• Practice For Next Loops & If Blocks
• Do While
• Condition Controlled Loops
• Array Variables
• Practice Arrays & Loops
• Linear Search
• Two Dimensional Arrays
• 2D Arrays & Nested Loops

Watch the full course below or on the freeCodeCamp.org YouTube channel (3-hour watch).

I use VBA, or Visual Basic for Applications, to automate my repetitive tasks. This saves me quite a lot of time in my day-to-day life.

Based on the fact that you're reading this tutorial, I assume that you would like to be able to do the same.

So I will take you through the basics of VBA, such as how to use variables, loops, if-else statements, arrays, and dictionaries.

Intro to VBA and Overview of the Project

Although VBA was declared legacy in 2008, this implementation of Visual Basic can help you automate the repetitive tasks in your daily life.

The language is object oriented, it's written in C++, and it includes all the features you would expect in a coding language these days.

In this tutorial we will be writing a macro that prepares a template to note the temperatures in several European cities – Amsterdam, Barcelona, Berlin, Brussels, London, and Rome.

The template will be automatically created by a macro we build together based on the cities and date that we use as parameters.

Finally, we will also learn how to import the data from another file in our template.

In order to follow along with this tutorial, I expect you to have a basic understanding of Excel. But you don't need any experience with VBA (although it might help you understand the different concepts I'll introduce).

I have downloaded several Excel files of data which are free to use. You can also find them on my GitHub together with the final result.

Let's Define Our Parameters

First we will define the different cities that we will be using for this tutorial. If we were to do this manually, increasing the number of cities would consequently mean more work.

But in our case, since the macro will do the work for us, we can add as many cities as we like. Then we'll just run the macro and go do something else.

In order to define the cities, we can do one of two things. Either we can include the cities in an array in the code, or we can define them in a separate worksheet in our template.

For this tutorial, we'll be doing the latter, which makes it easier for other people to add cities later on without any knowledge of VBA.

How to Set Up the Excel Workbook

Open a new Excel file, save it as Excel Macro-Enabled Workbook (extension .xlsm), and name it whatever you like.

Rename the first worksheet to Cities and add the six cities by typing their names in the first column with a city on every row, as shown here:

Figure 01: Cities Worksheet

To define the date, we will create another worksheet called Date where the user can define the month they want the template to be created for.

Since not every year and month are the same (for example different number of days, different number of weekdays), the template for your data needs to be adjusted every single month in order to correctly show these differences.

I have also added data validation to make sure we can only enter a date and to make sure it's a date not beyond today's date (but this is completely optional). As long as you have a cell with a date, preferably cell B1, that is enough.

For this tutorial, we'll only need the month and year, so I have chosen a different date format as shown below:

Figure 02: Date Worksheet

How to Build a Macro with Visual Basic for Applications

Now that we have set the parameters, we can move on with actually building the macro.

If this is the first time you are using VBA, you might need to customize your ribbon to get the necessary functions available.

In order to do so, make sure that the Developer box is ticked (as shown in Figure 3 below) and that the Developer options are available in your ribbon after you have saved your changes.

Figure 03: Settings Excel

You should now be able to open Visual Basic from the Developer tab in Excel, which should look something like this:

Figure 04: Visual Basic

This is the editor provided by Excel where you will be able to create, adjust, and remove your functions and macros. I will not go into too much detail for now, but I'll just explain some of the elements as we go.

Now, let's got our hands dirty and write our first macro.

You could choose to write macros for a single worksheet only, or choose to have them available in the entire workbook.

As the two worksheets we created earlier only maintain the parameters, I chose to write the macros for the entire workbook by double-clicking the "ThisWorkbook" option in the sidebar for our project.

Don't worry about the PERSONAL.xlsb in my screenshot for now – this is a file containing functions that I can use in all my files and will be handled in a future tutorial.

How to Create Our First Macro

After you've selected the workbook, you are ready to start your first program.

Macros in VBA start with the keyword Sub, short for subroutine, followed by their name and two parentheses. Although the editor is nothing compared to an IDE like Visual Studio Code, it will complete the code with End Sub when you hit enter after the two parentheses.

For now, I have created an empty macro called CreateTemplate which looks like this:

Sub CreateTemplate()

End Sub

It is a bit sad that the function does not do anything for now, so let's add the following code and find out what it does:

Sub CreateTemplate()

    'Adding a new worksheet called NewSheet to the workbook, next to the worksheet we currently have selected.
    Sheets.Add.Name = "NewSheet"

End Sub

If we were to run this code right now, the function would create a new worksheet in our Excel workbook called NewSheet.

Note that I have included a comment in the code by starting the line with an apostrophe. This will not be executed, but is only there to help you and myself understand the code.

We can run this code by placing our cursor somewhere in the function and pressing the green 'play' icon on top of the editor, which says Run Sub when you hover over it.

After you have pressed this button, you will see that new worksheet called NewSheet has been created in our workbook and has also been added in the sidebar, next to the sheets we already had.

Personally, I do not like the fact that the sheet is created next to the sheet we had (maybe on purpose, maybe not) selected. Therefore, I will add a parameter to the add method to define its location:

Sub CreateTemplate()

    'Adding a new worksheet called NewSheet to the workbook, next to the worksheet Date.
    Sheets.Add(After:=Sheets("Date")).Name = "NewSheet"

End Sub

Delete the newly created sheet, as we will now create the worksheets for every city we defined earlier. As the number of cities entered might differ, we want to know how many rows are actually being used in our Cities worksheet.

Sub CreateTemplate()

    Debug.Print Worksheets("Cities").UsedRange.Rows.Count

End Sub

To test whether we are able to extract the date from the file, we use Debug.Print (similar to print in Python or console.log in JavaScript) to print the numbers of rows, which Excel will calculate for us based on the code we provided.

Make sure to open your Immediate Window (in Visual Basic, by selecting View > Immediate Window) and run the macro above. It will print six, just like we expect, after we have defined the same number of cities in our Cities worksheet earlier in this tutorial.

How to Store Values as Variables in VBA

Rather than printing this value, I want to store it as a variable instead. To do so, add the following code:

Sub CreateTemplate()
    'Variable that will contain the number of cities.
    Dim NumberOfCities As Integer

    NumberOfCities = Worksheets("Cities").UsedRange.Rows.Count
    Debug.Print NumberOfCities

End Sub

We use the Dim keyword in VBA to declare a variable, which should always include the type (for example String, Integer, Double).

After we've declared our variable, we can assign the number of rows used, as printed before, to this variable and print the number to test that we get the same result. You can see this in the image below:

Figure 05: Printing to Immediate Window

We can now use this number to define a loop that will create a sheet for every city.

Sub CreateTemplate()
    'Variable that will contain the cityname that is used to name a sheet.
    Dim CityName As String
    'Variable that will contain the number of cities.
    Dim NumberOfCities As Integer
    'Variable that will be used to track the index of the loop to create worksheets.
    Dim SheetIndex As Integer

    NumberOfCities = Worksheets("Cities").UsedRange.Rows.Count

    For SheetIndex = 1 To NumberOfCities
        'Setting the cityname variable based on the index.
        CityName = Worksheets("Cities").Cells(SheetIndex, 1)
        'Adding a new worksheet called NewSheet to the workbook as last to ensure the alphabetical order.
        Sheets.Add(After:=Sheets(Sheets.Count)).Name = CityName
    Next SheetIndex

End Sub

How to Work with Loops in VBA

Let me explain how this all works. In the example above, I have declared two more variables, namely CityName and SheetIndex, which will hold the name of the city (which we'll use for the sheet title) and maintain the index of the loop.

The loop itself starts with the keyword For. Thereafter, we define the start of the index (one in this case) and loop through it until the number of cities has been reached.

For every iteration, the city has been stored in the variable CityName, which we then use to create a new worksheet. Run the function and you will see the worksheets being created with the title as defined in our Cities worksheet. It's magic, I know!

Now that the worksheets are there, we can add the single dates for the month. Earlier, we already defined the month we want to use in the Date worksheet. This can now help us create another loop so that all single days in the months are represented.

How to Use Functions in VBA

But first we need to get the number of days of the month the user has entered in the tab Date. We can do this by using a function in VBA. Rather than the subroutines that we have used before, we can also use a function which returns something.

The function we are now using looks quite similar to the function you might use in Excel yourself when defining the number of days in a month (but you would use Date instead of DateSerial):

Function DaysInMonth(DateInput As Date)
    DaysInMonth = Day(DateSerial(Year(DateInput), Month(DateInput) + 1, 1) - 1)
End Function

You can enter this function either above or below the subroutine we defined before, as VBA is a compiled language rather than an interpreted one.

Note – although I will not paste the function again, it will be there for the remainder of this tutorial.

We can now use this function in our subroutine. This makes our lives a lot easier, as we can now use those superpowers without polluting our subroutine.

Sub CreateTemplate()
    'Variable that will contain the cityname that is used to name a sheet.
    Dim CityName As String
    Dim MonthNum As Integer
    'Variable that will contain the number of cities.
    Dim NumberOfCities As Integer
    'Variable that will contain the date entered in the Date worksheet.
    Dim ReportDate As Date
    'Variable that will be used to track the index of the loop to create worksheets.
    Dim SheetIndex As Integer

    NumberOfCities = Worksheets("Cities").UsedRange.Rows.Count

    For SheetIndex = 1 To NumberOfCities
        'Setting the cityname variable based on the index.
        CityName = Worksheets("Cities").Cells(SheetIndex, 1)
        'Adding a new worksheet called NewSheet to the workbook as last to ensure the alphabetical order.
        Sheets.Add(After:=Sheets(Sheets.Count)).Name = CityName
    Next SheetIndex

    ReportDate = Worksheets("Date").Cells(1, 2)
    Debug.Print DaysInMonth(ReportDate)

End Sub

For now, delete the worksheets with the city names if you had created them and run the macro (again) to make sure you got the correct number of days printed in the Immediate Window for the month you defined earlier in the Date worksheet.

We can then store the number of days as an integer in another variable (called NumberOfDays) for another loop.

Sub CreateTemplate()
    'Variable that will contain the cityname that is used to name a sheet.
    Dim CityName As String
    'Variable that will be used to track the index of the loop to add the single dates for the month.
    Dim DateIndex As Integer
    'Variable that will contain the number of cities.
    Dim NumberOfCities As Integer
    'Variable that will contain the number of days in the chosen month
    Dim NumberOfDays As Integer
    'Variable that will contain the date entered in the Date worksheet.
    Dim ReportDate As Date
    'Variable that will be used to track the index of the loop to create worksheets.
    Dim SheetIndex As Integer

    NumberOfCities = Worksheets("Cities").UsedRange.Rows.Count

    For SheetIndex = 1 To NumberOfCities
        'Setting the cityname variable based on the index.
        CityName = Worksheets("Cities").Cells(SheetIndex, 1)
        'Adding a new worksheet called NewSheet to the workbook as last to ensure the alphabetical order.
        Sheets.Add(After:=Sheets(Sheets.Count)).Name = CityName
    Next SheetIndex

    ReportDate = Worksheets("Date").Cells(1, 2)
    NumberOfDays = DaysInMonth(ReportDate)

    For SheetIndex = 1 To NumberOfCities
        'Setting the cityname variable based on the index.
        CityName = Worksheets("Cities").Cells(SheetIndex, 1)

        For DateIndex = 1 To NumberOfDays
            Worksheets(CityName).Cells(DateIndex + 1, 1) = DateSerial(Year(ReportDate), Month(ReportDate), DateIndex)
        Next DateIndex
    Next SheetIndex

End Sub

This will be a loop within a loop to spice it up a little! 🔥

I will tell you what we are doing here. Again, we are looping through the cities. But instead of then creating another worksheet, we will now be doing something else with the Cityname variable.

In the loop inside the loop (you still get where I am now?) we are printing the date to the sheet, where, just like earlier in this tutorial, we used the DateSerial function.

Make sure to remove the sheets with a city name from your workbook, run the macro again, and you should see the worksheets including the dates for the month.

Note that the dates will be printed from the second row onwards, as I included +1 for my cell column in my code earlier. Why, you might ask yourself? Well, because I want to add certain titles on the first row instead, which we will add to the same loop right now.

Rather than defining the cells by using the Cells property followed by the row and column number as an integer, we can use Range followed by a cell's (or multiple cells') location to target its value and other properties.

    For SheetIndex = 1 To NumberOfCities
        'Setting the cityname variable based on the index.
        CityName = Worksheets("Cities").Cells(SheetIndex, 1)

        For DateIndex = 1 To NumberOfDays
            Worksheets(CityName).Cells(DateIndex + 1, 1) = DateSerial(Year(ReportDate), Month(ReportDate), DateIndex)
        Next DateIndex

        Worksheets(CityName).Range("B1") = "Minimum"
        Worksheets(CityName).Range("C1") = "Mean"
        Worksheets(CityName).Range("D1") = "Maximum"
    Next SheetIndex

In the example above I have added the headers for our table, which will now be printed to every single worksheet because of our loop.

How to Use Arrays in VBA

Every heard of DRY? Don't Repeat Yourself! Unfortunately, that is exactly what we are doing here.

An alternative could be to store the three headers in an array instead and loop over them to get to the same result. Is that really necessary for three items? Its debatable to be honest. But below you will find my code example as described:

Sub CreateTemplate()
    'Variable that will contain the cityname that is used to name a sheet.
    Dim CityName As String
    'Variable that will be used to track the index of the loop to add the single dates for the month.
    Dim DateIndex As Integer
    'Variable that will be used to track the index of the loop to insert the headers.
    Dim HeaderIndex As Integer
    'Variant that will store the headers as used in every single worksheet.
    Dim Headers(2) As Variant
    'Variable that will contain the number of cities.
    Dim NumberOfCities As Integer
    'Variable that will contain the number of days in the chosen month
    Dim NumberOfDays As Integer
    'Variable that will contain the date entered in the Date worksheet.
    Dim ReportDate As Date
    'Variable that will be used to track the index of the loop to create worksheets.
    Dim SheetIndex As Integer

    Headers(0) = "Minimum"
    Headers(1) = "Mean"
    Headers(2) = "Maximum"

    NumberOfCities = Worksheets("Cities").UsedRange.Rows.Count

    For SheetIndex = 1 To NumberOfCities
        'Setting the cityname variable based on the index.
        CityName = Worksheets("Cities").Cells(SheetIndex, 1)
        'Adding a new worksheet called NewSheet to the workbook as last to ensure the alphabetical order.
        Sheets.Add(After:=Sheets(Sheets.Count)).Name = CityName
    Next SheetIndex

    ReportDate = Worksheets("Date").Cells(1, 2)
    NumberOfDays = DaysInMonth(ReportDate)

    For SheetIndex = 1 To NumberOfCities
        'Setting the cityname variable based on the index.
        CityName = Worksheets("Cities").Cells(SheetIndex, 1)

        For DateIndex = 1 To NumberOfDays
            Worksheets(CityName).Cells(DateIndex + 1, 1) = DateSerial(Year(ReportDate), Month(ReportDate), DateIndex)
        Next DateIndex

        For HeaderIndex = 0 To UBound(Headers) - LBound(Headers)
            Worksheets(CityName).Cells(1, HeaderIndex + 2) = Headers(HeaderIndex)
        Next HeaderIndex

    Next SheetIndex

End Sub

The variant is used as an array, which is declared where you would expect and the titles are inserted later per index direclty after the variable declaration.

Instead of providing the individual cell locations, I have used a loop which runs from zero to the number of items (minus one, as arrays in VBA start at zero too). The title is then printed to the sheet, just like in the previous situation.

You might need to add another header later on, in which case the latest solution is easier. But if you are a hundred percent sure you'll stick with three only, choose what is most convenient for you. It's best to know both ways after all.

It does not matter which of the two options you choose for the remainder of this tutorial, as long as you got the headers printed.

How to Import Data Using VBA

At this point we've created the actual template, so we can enter cities and a date in the workbook. Then the macro will do the rest for us.

I would like to go one step further in this tutorial and show you how to trigger an explorer window where you can choose a file and import the data from that file.

Make sure you have completed the steps above and have gotten the worksheets for all the cities. Let's start a new subroutine and define the following variables at the top:

Sub ImportData()
    Dim FileLocation As String
    Dim WorksheetTitle As String

    WorksheetTitle = ActiveSheet.Name
End Sub

We need the first variable to store the location of the file where we are going to import the data on your device. We use the second one to store the title of the sheet that is currently selected by the user.

Add the following code to the macro, right below the line where we set the WorksheetTitle equal to the sheet name (but still before End Sub):

FileLocation = Application.GetOpenFilename
If FileLocation = "False" Then
    Beep
    Exit Sub
End If

Application.ScreenUpdating = False
Set ImportWorkbook = Workbooks.Open(Filename:=FileLocation)

Debug.Print ImportWorkbook.Worksheets(1).Range("B1")

ImportWorkbook.Close
Application.ScreenUpdating = True

This might seem a little cryptic at first, but should not be to difficult to understand. The Application.GetOpenFilename function is what triggers a file explorer window and returns a location, which is then stored in the earlier defined variable.

If you decide not to select a file, meaning if the FileLocation = "False", you will hear the iconic beep sound (yes, Excel comes with awesome sounds too!😍) and the macro will end here.

If it's true, we'll continue with the macro, which first ends the screen updates of Excel. This helps make sure that everything will run smoothly.

For this macro you might not notice the difference without this line, but with more complex calculations you will. Believe me.

How to Work with Objects in VBA

After that, the data included in the file that's located where the FileLocation variable is gets stored into the constant ImportWorkbook.

Note that we are using the Set keyword which is mainly used for objects, such as an import of a workbook.

To test whether we are able to extract the date from the file, we again use Debug.Print to print the city, which is located in cell B1 of the files available per the link mentioned in the beginning of this tutorial.

If you import the file Data_Amsterdam, you should see Amsterdam printed in your Immediate Window. After the file is closed, Excel will behave normally again. We can easily paste the (just printed) city name in the in our own template by replacing the Debug.Print with the following:

ThisWorkbook.Worksheets(WorksheetTitle).Range("A1") = ImportWorkbook.Worksheets(1).Range("B1")

'Equal to the following.
ThisWorkbook.Worksheets(WorksheetTitle).Cells(1, 1) = ImportWorkbook.Worksheets(1).Range(1, 2)

Note that we can now use our ImportWorkbook variable to get the data from the imported workbook. But we can also use ThisWorkbook (protected keyword name by VBA) to get to properties of our own template instead.

If we now run this macro and import Data_Amsterdam, the city should now be printed in the left-upper corner of your Amsterdam worksheet.

How to Use Dictionaries in VBA

As promised, now it's time to get the actual temperatures. You can use a loop and insert the numbers like demonstrated above, but I am feeling fancy today and would like to show you how we can do this using a dictionary.

Again, just like the array earlier in this tutorial, this approach might be a bit redundant for this situation. But if you need to work with larger amounts of data this will come in handy.

First thing we need to do is define several new variables here:

Dim DataIndex As Integer
Dim DaysIndex As Integer
Dim FileLocation As String
Dim Headers(2) As Variant
Dim WorksheetTitle As String
Set TemperaturesDict = CreateObject("Scripting.Dictionary")

Headers(0) = "Maximum"
Headers(1) = "Minimum"
Headers(2) = "Mean"

Both the indexes on top will track the index of the loops we will be using later. I copied the headers declaration and variables from our macro earlier.

Yes, we could have defined them as a global variable, but that is another topic for another day.

Note that I have changed the order, as this is how they are displayed in the imported file.

A dictionary is also an object, which we will therefore declare using the Set keyword directly below our variables.

But before you can actually use a dictionary this way, make sure that you have Microsoft Scripting Runtime (as you can see in Figure 6 above) ticked, which you can find under Tools and then References in your editor.

Figure 06: Visual Basic References

Before, I showed you how to copy a value from a single cell from one workbook to a second one. Now, rather than copying single values, I want to store them all together in a dictionary before moving on and pasting them.

    For DaysIndex = 11 To ImportWorkbook.Worksheets(1).UsedRange.Rows.Count
        Set DataDict = CreateObject("Scripting.Dictionary")
        For DataIndex = 0 To 2
            DataDict.Add Headers(DataIndex), ImportWorkbook.Worksheets(1).Cells(DaysIndex, DataIndex + 2)
        Next DataIndex
        TemperaturesDict.Add DaysIndex, DataDict
    Next DaysIndex

This might be the most difficult part of the tutorial, which I will try to explain as clearly as possible.

We start with a loop like we've used many times before in this tutorial – so far so good. The loop starts at eleven and ends when the number of rows being used in the imported workbook is being reached.

Then, I am defining a new dictionary. Why? In most other languages it is possible to add new key value pairs using dot notation – but not in VBA. In order to so-called nest (add a dictionary in a dictionary), you will have to create a new (inner) dictionary and then add it to your other (outer) dictionary.

This dictionary in the loop will therefore first be populated with the different temperatures available, namely minimum, mean, and maximum.

To do this, I'll use the headers array as discussed above and I'll loop over these values. You add a key value pair by using the name of the dictionary followed by calling the .Add method, the key you want to use, a comma, and then its value:

DictName.Add Key, Value

This is what will be entered after the first iteration when you import the Data_Amsterdam file:

Maximum: 22.105547
Minimum: 14.385546    
Mean: 18.25388

This dictionary then gets added to the 'main' dictionary called TemperaturesDict – meaning we'll now have something like this:

10: 
    Maximum: 22.105547
    Minimum: 14.385546    
    Mean: 18.25388

We do the same for all the other days and temperature types, until the file is completely scraped and we are ready to insert this information into our template. But then what?

    Headers(0) = "Minimum"
    Headers(1) = "Mean"
    Headers(2) = "Maximum"

    For DaysIndex = 2 To ThisWorkbook.Worksheets(WorksheetTitle).UsedRange.Rows.Count
        If TemperaturesDict.Exists(DaysIndex - 1) Then
            For DataIndex = 0 To 2
                ThisWorkbook.Worksheets(WorksheetTitle).Cells(DaysIndex, DataIndex + 2) = TemperaturesDict(DaysIndex - 1)(Headers(DataIndex))
            Next DataIndex
        End If
    Next DaysIndex

As discussed above, the imported file has another order. But since we are using a dictionary, we can now easily reorder to headers array and have it displayed as we originally thought through in our template, as it will search for the key (#winning).

We are then using an old-fashioned loop to get over all the days we have in our template. The loop starts at two, as we defined our headers on the first row. Now starting at index one will override those, which we do not want. The loop ends after we have had all rows (that is to say, all days).

Since only a limited number of days are included in the imported file, we have to check whether the day equal to the index actually exists in our dictionary.

We do this with an if statement, which you might recognize from other coding languages. There is a minus one since our loop starts at two, though days of the month always start at one. The same counts for the minus one used later on in this block of code.

If the data actually exists in our array, we will insert it in our template. Then the row is defined by the index of the loop and the column comes from the DataIndex.

Eventually, we end up with the following code for our second macro:

Sub ImportData()
    Dim DataIndex As Integer
    Dim DaysIndex As Integer
    Dim FileLocation As String
    'Variant that will store the headers as used in every single worksheet.
    Dim Headers(2) As Variant
    Dim WorksheetTitle As String
    Set TemperaturesDict = CreateObject("Scripting.Dictionary")

    Headers(0) = "Maximum"
    Headers(1) = "Minimum"
    Headers(2) = "Mean"

    WorksheetTitle = ActiveSheet.Name

    FileLocation = Application.GetOpenFilename
    If FileLocation = "False" Then
        Beep
        Exit Sub
    End If

    Application.ScreenUpdating = False
    Set ImportWorkbook = Workbooks.Open(Filename:=FileLocation)

    For DaysIndex = 11 To ImportWorkbook.Worksheets(1).UsedRange.Rows.Count
        Set DataDict = CreateObject("Scripting.Dictionary")
        For DataIndex = 0 To 2
            DataDict.Add Headers(DataIndex), ImportWorkbook.Worksheets(1).Cells(DaysIndex, DataIndex + 2)
        Next DataIndex
        TemperaturesDict.Add DaysIndex, DataDict
    Next DaysIndex

    Headers(0) = "Minimum"
    Headers(1) = "Mean"
    Headers(2) = "Maximum"

    For DaysIndex = 2 To ThisWorkbook.Worksheets(WorksheetTitle).UsedRange.Rows.Count
        If TemperaturesDict.Exists(DaysIndex - 1) Then
            For DataIndex = 0 To 2
                ThisWorkbook.Worksheets(WorksheetTitle).Cells(DaysIndex, DataIndex + 2) = TemperaturesDict(DaysIndex - 1)(Headers(DataIndex))
            Next DataIndex
        End If
    Next DaysIndex

    ImportWorkbook.Close
    Application.ScreenUpdating = True

End Sub

When you now run this code, you should see the the temperatures being imported from the data file to the template.

Note that we have also changed the order in which they originally come – now starting with minimum and ending with maximum.

Conclusion

Congratulations! You have now made yourself a template which you can easily populate with data, all using macros.

Thank you for reading :) If you have any questions about this tutorial or other VBA implementation, please don't hesitate to send me a direct message on Twitter so I can help you out.

Introduction

This is a tutorial about writing code in Excel spreadsheets using Visual Basic for Applications (VBA).

Excel is one of Microsoft’s most popular products. In 2016, the CEO of Microsoft said "Think about a world without Excel. That's just impossible for me.” Well, maybe the world can’t think without Excel.

• In 1996, there were over 30 million users of Microsoft Excel (source).
• Today, there are an estimated 750 million users of Microsoft Excel. That’s a little more than the population of Europe and 25x more users than there were in 1996.

We’re one big happy family!

In this tutorial, you’ll learn about VBA and how to write code in an Excel spreadsheet using Visual Basic.

Prerequisites

You don’t need any prior programming experience to understand this tutorial. However, you will need:

• Basic to intermediate familiarity with Microsoft Excel
• If you want to follow along with the VBA examples in this article, you will need access to Microsoft Excel, preferably the latest version (2019) but Excel 2016 and Excel 2013 will work just fine.
• A willingness to try new things

Learning Objectives

Over the course of this article, you will learn:

1. What VBA is
2. Why you would use VBA
3. How to get set up in Excel to write VBA
4. How to solve some real-world problems with VBA

Important Concepts

Here are some important concepts that you should be familiar with to fully understand this tutorial.

Objects: Excel is object-oriented, which means everything is an object - the Excel window, the workbook, a sheet, a chart, a cell. VBA allows users to manipulate and perform actions with objects in Excel.

If you don’t have any experience with object-oriented programming and this is a brand new concept, take a second to let that sink in!

Procedures: a procedure is a chunk of VBA code, written in the Visual Basic Editor, that accomplishes a task. Sometimes, this is also referred to as a macro (more on macros below). There are two types of procedures:

• Subroutines: a group of VBA statements that performs one or more actions
• Functions: a group of VBA statements that performs one or more actions and returns one or more values

Note: you can have functions operating inside of subroutines. You’ll see later.

Macros: If you’ve spent any time learning more advanced Excel functionality, you’ve probably encountered the concept of a “macro.” Excel users can record macros, consisting of user commands/keystrokes/clicks, and play them back at lightning speed to accomplish repetitive tasks. Recorded macros generate VBA code, which you can then examine. It’s actually quite fun to record a simple macro and then look at the VBA code.

Please keep in mind that sometimes it may be easier and faster to record a macro rather than hand-code a VBA procedure.

For example, maybe you work in project management. Once a week, you have to turn a raw exported report from your project management system into a beautifully formatted, clean report for leadership. You need to format the names of the over-budget projects in bold red text. You could record the formatting changes as a macro and run that whenever you need to make the change.

What is VBA?

Visual Basic for Applications is a programming language developed by Microsoft. Each software program in the Microsoft Office suite is bundled with the VBA language at no extra cost. VBA allows Microsoft Office users to create small programs that operate within Microsoft Office software programs.

Think of VBA like a pizza oven within a restaurant. Excel is the restaurant. The kitchen comes with standard commercial appliances, like large refrigerators, stoves, and regular ole’ ovens - those are all of Excel’s standard features.

But what if you want to make wood-fired pizza? Can’t do that in a standard commercial baking oven. VBA is the pizza oven.

Yum.

Why use VBA in Excel?

Because wood-fired pizza is the best!

But seriously.

A lot of people spend a lot of time in Excel as a part of their jobs. Time in Excel moves differently, too. Depending on the circumstances, 10 minutes in Excel can feel like eternity if you’re not able to do what you need, or 10 hours can go by very quickly if everything is going great. Which is when you should ask yourself, why on earth am I spending 10 hours in Excel?

Sometimes, those days are inevitable. But if you’re spending 8-10 hours everyday in Excel doing repetitive tasks, repeating a lot of the same processes, trying to clean up after other users of the file, or even updating other files after changes are made to the Excel file, a VBA procedure just might be the solution for you.

You should consider using VBA if you need to:

• Automate repetitive tasks
• Create easy ways for users to interact with your spreadsheets
• Manipulate large amounts of data

Getting Set Up to Write VBA in Excel

Developer Tab

To write VBA, you’ll need to add the Developer tab to the ribbon, so you’ll see the ribbon like this.

To add the Developer tab to the ribbon:

1. On the File tab, go to Options > Customize Ribbon.
2. Under Customize the Ribbon and under Main Tabs, select the Developer check box.

After you show the tab, the Developer tab stays visible, unless you clear the check box or have to reinstall Excel. For more information, see Microsoft help documentation.

VBA Editor

Navigate to the Developer Tab, and click the Visual Basic button. A new window will pop up - this is the Visual Basic Editor. For the purposes of this tutorial, you just need to be familiar with the Project Explorer pane and the Property Properties pane.

Excel VBA Examples

First, let’s create a file for us to play around in.

1. Open a new Excel file
2. Save it as a macro-enabled workbook (. xlsm)
3. Select the Developer tab
4. Open the VBA Editor

Let’s rock and roll with some easy examples to get you writing code in a spreadsheet using Visual Basic.

Example #1: Display a Message when Users Open the Excel Workbook

In the VBA Editor, select Insert -> New Module

Write this code in the Module window (don’t paste!):

Sub Auto_Open() MsgBox ("Welcome to the XYZ Workbook.") End Sub

Save, close the workbook, and reopen the workbook. This dialog should display.

Ta da!

How is it doing that?

Depending on your familiarity with programming, you may have some guesses. It’s not particularly complex, but there’s quite a lot going on:

• Sub (short for “Subroutine): remember from the beginning, “a group of VBA statements that performs one or more actions.”
• Auto_Open: this is the specific subroutine. It automatically runs your code when the Excel file opens - this is the event that triggers the procedure. Auto_Open will only run when the workbook is opened manually; it will not run if the workbook is opened via code from another workbook (Workbook_Open will do that, learn more about the difference between the two).
• By default, a subroutine’s access is public. This means any other module can use this subroutine. All examples in this tutorial will be public subroutines. If needed, you can declare subroutines as private. This may be needed in some situations. Learn more about subroutine access modifiers.
• msgBox: this is a function - a group of VBA statements that performs one or more actions and returns a value. The returned value is the message “Welcome to the XYZ Workbook.”

In short, this is a simple subroutine that contains a function.

When could I use this?

Maybe you have a very important file that is accessed infrequently (say, once a quarter), but automatically updated daily by another VBA procedure. When it is accessed, it’s by many people in multiple departments, all across the company.

• Problem: Most of the time when users access the file, they are confused about the purpose of this file (why it exists), how it is updated so often, who maintains it, and how they should interact with it. New hires always have tons of questions, and you have to field these questions over and over and over again.
• Solution: create a user message that contains a concise answer to each of these frequently answered questions.

Real World Examples

• Use the MsgBox function to display a message when there is any event: user closes an Excel workbook, user prints, a new sheet is added to the workbook, etc.
• Use the MsgBox function to display a message when a user needs to fulfill a condition before closing an Excel workbook
• Use the InputBox function to get information from the user

Example #2: Allow User to Execute another Procedure

In the VBA Editor, select Insert -> New Module

Write this code in the Module window (don’t paste!):

Sub UserReportQuery() Dim UserInput As Long Dim Answer As Integer UserInput = vbYesNo Answer = MsgBox("Process the XYZ Report?", UserInput) If Answer = vbYes Then ProcessReport End Sub

Sub ProcessReport() MsgBox ("Thanks for processing the XYZ Report.") End Sub

Save and navigate back to the Developer tab of Excel and select the “Button” option. Click on a cell and assign the UserReportQuery macro to the button.

Now click the button. This message should display:

Click “yes” or hit Enter.

Once again, tada!

Please note that the secondary subroutine, ProcessReport, could be anything. I’ll demonstrate more possibilities in example #3. But first...

How is it doing that?

This example builds on the previous example and has quite a few new elements. Let’s go over the new stuff:

• Dim UserInput As Long: Dim is short for “dimension” and allows you to declare variable names. In this case, UserInput is the variable name and Long is the data type. In plain English, this line means “Here’s a variable called “UserInput”, and it’s a Long variable type.”
• Dim Answer As Integer: declares another variable called “Answer,” with a data type of Integer. Learn more about data types here.
• UserInput = vbYesNo: assigns a value to the variable. In this case, vbYesNo, which displays Yes and No buttons. There are many button types, learn more here.
• Answer = MsgBox(“Process the XYZ Report?”, UserInput): assigns the value of the variable Answer to be a MsgBox function and the UserInput variable. Yes, a variable within a variable.
• If Answer = vbYes Then ProcessReport: this is an “If statement,” a conditional statement, which allows us to say if x is true, then do y. In this case, if the user has selected “Yes,” then execute the ProcessReport subroutine.

When could I use this?

This could be used in many, many ways. The value and versatility of this functionality is more so defined by what the secondary subroutine does.

For example, maybe you have a file that is used to generate 3 different weekly reports. These reports are formatted in dramatically different ways.

• Problem: Each time one of these reports needs to be generated, a user opens the file and changes formatting and charts; so on and so forth. This file is being edited extensively at least 3 times per week, and it takes at least 30 minutes each time it’s edited.
• Solution: create 1 button per report type, which automatically reformats the necessary components of the reports and generates the necessary charts.

Real World Examples

• Create a dialog box for user to automatically populate certain information across multiple sheets
• Use the InputBox function to get information from the user, which is then populated across multiple sheets

Example #3: Add Numbers to a Range with a For-Next Loop

For loops are very useful if you need to perform repetitive tasks on a specific range of values - arrays or cell ranges. In plain English, a loop says “for each x, do y.”

In the VBA Editor, select Insert -> New Module

Write this code in the Module window (don’t paste!):

Sub LoopExample() Dim X As Integer For X = 1 To 100 Range("A" & X).Value = X Next X End Sub

Save and navigate back to the Developer tab of Excel and select the Macros button. Run the LoopExample macro.

This should happen:

Etc, until the 100th row.

How is it doing that?

• Dim X As Integer: declares the variable X as a data type of Integer.
• For X = 1 To 100: this is the start of the For loop. Simply put, it tells the loop to keep repeating until X = 100. X is the counter. The loop will keep executing until X = 100, execute one last time, and then stop.
• Range("A" & X).Value = X: this declares the range of the loop and what to put in that range. Since X = 1 initially, the first cell will be A1, at which point the loop will put X into that cell.
• Next X: this tells the loop to run again

When could I use this?

The For-Next loop is one of the most powerful functionalities of VBA; there are numerous potential use cases. This is a more complex example that would require multiple layers of logic, but it communicates the world of possibilities in For-Next loops.

Maybe you have a list of all products sold at your bakery in Column A, the type of product in Column B (cakes, donuts, or muffins), the cost of ingredients in Column C, and the market average cost of each product type in another sheet.

You need to figure out what should be the retail price of each product. You’re thinking it should be the cost of ingredients plus 20%, but also 1.2% under market average if possible. A For-Next loop would allow you to do this type of calculation.

Real World Examples

• Use a loop with a nested if statement to add specific values to a separate array only if they meet certain conditions
• Perform mathematical calculations on each value in a range, e.g. calculate additional charges and add them to the value
• Loop through each character in a string and extract all numbers
• Randomly select a number of values from an array

Conclusion

Now that we’ve talked about pizza and muffins and oh-yeah, how to write VBA code in Excel spreadsheets, let’s do a learning check. See if you can answer these questions.

• What is VBA?
• How do I get set up to start using VBA in Excel?
• Why and when would you use VBA?
• What are some problems I could solve with VBA?

If you have a fair idea of how to you could answer these questions, then this was successful.

Whether you’re an occasional user or a power user, I hope this tutorial provided useful information about what can be accomplished with just a bit of code in your Excel spreadsheets.

Happy coding!

Learning Resources

• Excel VBA Programming for Dummies, John Walkenbach
• Get Started with VBA, Microsoft Documentation
• Learning VBA in Excel, Lynda

A bit about me

I'm Chloe Tucker, an artist and developer in Portland, Oregon. As a former educator, I'm continuously searching for the intersection of learning and teaching, or technology and art. Reach out to me on Twitter @_chloetucker and check out my website at chloe.dev.