Whether you are a test engineer formatting test results for reporting purposes, or a financial analyst developing spreadsheets for tracking key financial metrics, the ability to customize reports and/or manipulate data can be critically important to your business operations. Excel can be a powerful tool and has many built-in functions.  Popular functions include SUM and VLOOKUP, for manipulating data but sometimes those fall short of the needed task. Sometimes you need greater control.  Perhaps you need to delete the last two characters from each row in a given column. Or delete every third row. Fortunately, Excel has built-in development tools that allow users to write code to automate simple tasks. This blog post will show you how to write macros and explain some hierarchy and definitions.

Enabling the Developer ribbon

To start writing macros, we need to be able to launch the Visual Basic editor within Excel. By default, this is hidden. Follow these steps to display the Developer ribbon tab. You will only need to do this once. If you open another new Excel document or an existing one, the Developer tab will already be there for you.

Start by opening Excel -> right-click on “Home” ribbon -> Select Customize the Ribbon

On the next window, select the checkbox for Developer in the right-hand pane and press OK.

This will enable the Developer ribbon to appear.

Now we can select the first option in Code called Visual Basic. This will open a new window with the editor. Your editor will look similar to below:

Now that the editor is open, we can write code! But where? And how do we make it run? To do that, we need to insert a new Module into the project.

Right click in the VBAProject area -> hover Insert -> click Module

This creates a module in the VBAProject and an editor window

In this text box, we can start to write code. Start by writing “Sub test()” then press Enter. You’ll see the editor automatically adds the “End Sub.”

A Sub is a procedure, or bit of code, that is run to do something. Let’s write something basic to get started, like putting the number 5 into the first cell. To do that, we need to put in the following:

Sub test()

    Sheet1.Cells(1, 1).Value = 5

End Sub

There’s only 1 workbook open and it’s the active workbook Sheet1 indicates we want to work with that worksheet. The Cells property is defined by the row and column numbers, so 1 for the first row and 1 for column ‘A.’ Finally we want to assign the Value of the cell, as opposed to another property, like the background color.

You can run the code a number of ways. The easiest is pressing the F5 key. Or you can find the ‘Run’ button in the toolbar or select the Run menu.

If there are no errors, a ‘5’ will appear in the first cell of the worksheet.

We can take this concept further by using logic loops such as ‘for.’

Sub test()

    For i = 1 To 5

        Sheet1.Cells(i, 2).Value = i

    Next i

End Sub

This procedure counts from 0 to 5 and puts the value in column ‘B.’ Giving the result:

Where to next?

Excel can solve a wide number of problems, so finding out how to solve your exact problem is up to you. Fortunately, VBA is 27 years old, so there are many accessible tutorials and articles on the web that can help you out. You’ll find a lot of code snippets that can be copy/pasted with minimal adjustment.

Other times you may just want to see what exactly Excel is doing. The “Record Macro” on the Developer tab can offer valuable insight or generate code for you to copy and paste. Click the Record Macro button and the following window will pop up asking what you want to name your recorded macro. The default value is fine for this example.

Clicking OK will start the macro recording. Let’s select the numbers we put into column B and change the background color using the fill tool the Home tab.

Go back to the Developer tab and press Stop Recording.

Back in our VB editor, we can see a new module has been added (Module2) and a Sub named “Macro1”.

We can see the selection of the cells and then the Interior property updates using the with statement.

Knowing how to write VBA code provides anyone the ability to automate portions of their work. Basic programming skills are all that is needed to get started. There are no complicated development IDEs, compilers, or servers involved, everything is built into Excel. Check out our previous blog for some advanced Excel dashboard tips.

Looking to leverage a partner that helps you solve various software challenges and feels like an extension of your team? Contact us today to see how our programmers can help you.