Write Your First VBA Macro in Excel

Writing macros can make things in Excel much easier. And as an added bonus, you get to impress everyone when you drop technical jargon like “I wrote a macro to do that”. It’s a win-win!

This article will take you step-by-step through the process to write your own macro.

Step 1: Make sure you have the Developer tab available

Check the ribbon at the top of your Excel workbook, and see if the Developer tab is available:

If it is not available for you, same as me, then you’ll need to correct that.

Navigate to File > Options:

Select Customize Ribbon, check the Developer box, and click OK.

You should now see the Developer tab:

Step 2: Create the sample data to add two columns of numbers

For this example, our macro will add the numbers from two columns. Enter some sample data like this:

If you were adding these by using Excel formulas, you would probably do something like this:

Option 1: =Sum(A2, B2)

Option 2: =A2 + B2

Then you would drag that formula down to row 4 to populate the rest of the rows, and end up with something like this:

However, if you ended up adding more values to the First Number and Second Number columns, you would need to remember to drag the formula down again to calculate for that row:

In the following steps, you’ll create a macro instead to do this, and see if it makes things easier.

Clear out your total column, so that your sample data is back to looking like this:

Step 3: Create a module in VBA

Select the Developer tab:

Select Visual Basic:

With the Microsoft Visual Basic for Applications window open, select Insert > Module:

A name like Module1 will be automatically given to this module:

You can rename the module in the properties pane. Delete the Module1 value and rename it to something else. I chose Example for my module name:

You now have a module that can be used to write your macro.

Step 4: Write the macro

Right-click your new module, and select View Code:

In the code editor that opens up, add in the following to create a macro:

Sub AddTwoNumbers()

End Sub

There is no logic yet added to this macro, but this does create a macro called AddTwoNumbers:

In between the first line and the last line is where the logic for our macro will go.

Here is the full logic you can use to create the macro to add the two columns:

Sub AddNumbers()

'Declare variables
Dim row_count As Integer
Dim row_count_without_header As Integer
Dim row_iterator As Integer
Dim row_number As Integer

'Get total row count
row_count = Sheet1.Cells(Sheet1.Rows.Count, 1).End(xlUp).Row

'Get total row count without header
row_count_without_header = row_count - 1

'Iterate/loop through rows
For row_iterator = 1 To row_count_without_header
    row_number = row_iterator + 1
    Sheet1.Cells(row_number, 3) = Sheet1.Cells(row_number, 1) + Sheet1.Cells(row_number, 2)
Next iterator

End Sub

Notes:

1. First, declare the variables and their data types that you will use. In this example, I used four different variables.
2. The row_count variable is looking at the first column to see how many total rows are in that column. This helps us know how many rows will need to be added. I am referencing Sheet1 in use case, since that is what the sheet is named for my sample data. (Note: the logic used to get that value is very handy. Keep that in mind for future usage)
3. The row_count_without_header variable is subtracting one from row_count to give us the actual number of rows we need to sum, since the first column is the header and doesn’t need to be summed.
4. The Sheet1.Cells formula takes two arguments — the first one is the Row Index and the second one is the Column Index. As we iterate through the rows, we are incrementing the row_number each time. As you can see in the For/Next loop, you are setting the value of column 3 equal to the value of columns 1 and 2 added together.

The full macro:

You can now close out of the VBA editor to go back to the main Excel view:

Step 5: Run the macro

Under the Developer tab, select Macros:

Select the AddTwoNumbers macro just created, and click Run:

Look at your sample data now, and you can see that the Total column is now populated with data; also note that the formula bar is populated with the value, not an Excel formula:

Our macro summed both columns successfully.

Step 6: Test the macro

Let’s test a couple of scenarios with our macro to see how useful it could be.

Add an extra row like this to your sample data, and then run the macro again. It should look like this:

Now delete the Total value for the middle two rows…

…and run the macro again. It should look like this:

Your macro has enabled you to be very flexible now. You don’t have to wonder if the Excel formula copied correctly or panic if the formula got deleted or corrupted. You can run that macro any time and it will fix and/or update that Total column.

Step 7: Save your workbook as a Macro-Enabled file

When saving this workbook, save it as an .xlsm (Excel Macro-Enabled Workbook) file to allow macros to run in the file:

And that’s it!

Now you can tell everyone that you know how to write a macro. Hopefully this inspires you to search for ways to automate some of your Excel work in the future.

Thanks for reading!