Getting Started with Visual Basic for Applications in Microsoft Excel

Although Microsoft Excel is best known for creating tables and plotting data, there is much more to it than that. Beyond the usual budget tables and sales estimate charts, Microsoft's Visual Basic for Applications (VBA) allows anyone with only basic or even no programming skills to build robust complements to automate routine responsibilities. Be it applying complex formatting to cells, manipulating values, or anything in between (or beyond), VBA is the tool for the job. This Guide will explain how to perform these operations with the click of a button or the press of a combination of keys. Thus, you will not only save time but also reduce the likelihood of human error when carrying out repetitive tasks.

Before we begin, be sure to enable the Developer tab in Microsoft Excel. To do so, launch the application, go to File and then choose Options. Finally, click Customize Ribbon and check Developer as shown below.

To return to the spreadsheet and follow along with this Guide, click OK to confirm the changes. If you wish, you can download the sample file here.

When you start working with a set of records in Microsoft Excel, it is usually in the form of raw data. Said another way, you have several rows and columns without any format applied. Additionally, you still have not drawn any business intelligence from those records. Thus, the first thing that you will do probably consists of adding borders, applying background colors, formatting date and currency columns as such, and perhaps creating pivot tables to understand the data better. If you execute those actions often, you can automate them by using a macro--a set of code that runs inside of Microsoft Excel and other Office applications.

Consider the spreadsheet shown in the image below. In this example, the table will be given borders and a light blue background, and white font on the column titles. It will also have columns C and E formatted as yyyy-mm-dd, and D and F as currency.

The easiest way to create a macro is to record it. To do so, go to the Developer tab and browse to the Code section. Next, click Record Macro and enter a name, a shortcut key, and an optional description. You will use this key combination (Ctrl + l) in the example below to execute the macro later, so take note of it. Finally, hit OK to start recording the actions mentioned above.

If you choose to store the macro in This Workbook, it will only be available in the current file. If you wish to use it whenever you launch Microsoft Excel, select Personal Macro Workbook instead.

When you finish, click Stop Recording (also in the Code section) and save the file as a macro-enabled workbook (with the *.xlsm extension). At this point, the current worksheet should look as follows:

It is important to note that, for security purposes, Microsoft Excel disables macros by default. This is particularly important if you will be manipulating files coming from other people. Each time you open a macro-enabled workbook, you will see a prompt to enable the macros it includes.

To test the macro, you will need to remove the formatting by selecting the entire table first. You can do that by using the Ctrl + A + A key combination (press Ctrl and then A twice) and then clicking Alt + H + E + F. This sequence of keys is short for the Home/Editing/Formats menu.

When this macro was created, Ctrl + l (lowercase L) was chosen as a shortcut. Pressing that key combination should trigger the expected change:

Another way that to run a macro is by clicking a button. Go to the Developer tab, choose Insert and then select a button:

The cursor will change to a small cross. Draw the button by holding the click until you get the desired size. Next, select the macro and click OK to confirm:

Finally, edit the initial button caption by right-clicking on it and choosing Edit Text. If you click the button at this point, it will execute the macro as expected:

What if the result is not exactly what is needed? In the next section, this Guide will explore how to update the macro to make the necessary adjustments. For now, remove the formatting as before. We will add it back in with further changes shortly.

If you look at the column headers, you will see that you could improve them a bit. For example, you could replace OrderId with Order, CompanyName with Company Name, OrderDate with Order Date, and so on.

To edit the macro, go to the Developer tab and click Macros. Next, choose the macro named FormatTable and then click Edit:

This will launch the Microsoft Visual Basic for Applications Editor (or VBE for short). There, you will see the macro in a file called Module1 inside the Modules folder. Also, the editor on the right displays the code that performs the actions of the macro behind the scenes:

In the editor, insert the following lines somewhere between Sub FormatTable() and End Sub outside of a With / End With block.

The following image shows those lines right below

which is the part of the macro where you highlighted the top row of the table and made the font bold. Also, you will notice that when you type a dot after the range, a prompt appears with the available methods and properties. Choose Value and then assign the column name.

In VBA, lines that begin with a ' represent comments and are ignored by the interpreter.

One of the great things about the editor is that you don't need to save the changes--it will perform that action for you. Now, go back to the spreadsheet and press Ctrl + l to run the macro. The formatted table should look as follows:

After creating the FormatTable macro, feel free to experiment with other functions and properties as explained in the Visual Basic language reference.

This Guide has explained what macros are and covered how to create and run them. Using this powerful tool will allow to you automate tasks and reduce human error. Using macros effectively can free you from mundane tasks and give you more time for in-depth data analysis.