8.5h saved in one month. From 10h per month to 1.5h per month.
All thanks to a Macro in Excel. One simple tool.

Let me tell you more. While holding a position in the data industry,
every day I needed to update in Excel scorecard with the results from the previous day.

I had to spend daily 30min running this report manually in Excel.

To put in perspective: 2.5h per week. 10h per month.
10h per month to run the same report without any value-added while executing it.

After a month, I realized that I needed a change. And that’s when I decided to Google the following:

The summary of the 15,700,00 results is: Excel VBA – also called your first Macro.
As a result of this, I managed to reduce by 85% the time needed to prepare my report in Excel.

And what’s more important you could drive similar improvements in your reports.

Your first Macro in Excel VBA

Every time I discussed with people the massive opportunities behind learning how to program Macros in Excel, people tend to doubt.

This is not for me.
I have no experience with programming.
I am not good with technology

Take a couple of deep breaths.

One.

Two

Let’s get started. You are about to start your journey as a “Macro expert”

You don’t need to be an expert programmer to program your first Macros in Excel.

I would say the vast majority of my day-to-day tasks in Excel are automated so I can focus that extra time on performing tasks that bring extra value. Why? I know how to Google, how to read the code and how to copy/paste.

Then the question is: What do you need for starting your journey in programming Macros?

Google, ability to read code (I will explain you along the way) and copy/paste.

Theory - How to write your first Macro in Excel

Mindset – “Think Like Excel”
Start adjusting your mindset. To be successful in programming Macros you need to adjust your mindset.
You need to “Think Like Excel” and you may wonder what think “Think Like Excel” mean.

Let me explain to you with an example.
Imagine you want to delete the content of cell B2 in sheet1.
what you would normally do would be select the cell with your mouse and click delete. (Your manual action)

However,when you “Think Like Excel” and you want to automate the actions in Excel,
you need to plan step by step like a machine.

Step by step:

• Activate Sheet1
• Select cell B2
• Delete content

This is the VBA code that would execute such actions:

Sub MisterPaton_firstMacro()
'Activate Sheet1 
 Sheets("Sheet1").Select

'Select cell B2
Range("B2").Select

'Clear content in cell selected
Selection.ClearContents
End Sub

It may sound Greek to you now - no worries.
You are only getting started.

Break your Macro tool into smalls tools
Imagine you are an architect, and you try to build a house in one day. It will not work.

The architect is defining step by step – in detail what needs to happen.
Level the floor, install footings, pour the foundations…

The same thing with your Macro, you need to think about what the first steps of your Macro would be. The first steps of your Macro would be. Clearing content, connecting to external software, running formulas …

My recommendation sequence of actions:

Clear the data from all the excel sheets
Get your data ready – ie: connect to external software like SAP
Run formulas for the latest data
Format the data
Send via email prepared report

You may think all these impossible and it is not possible to automate. Believe it is possible and on this site, you will see how.

Net - break down the tool you want to create for very small projects.

You will see your progress instantly, you will build immediate confidence and avoid the typical frustration of first-time programmers when things do not go as expected

Learn how to Google

One of the main sources of frustration, when people start to program Macros in Excel, is trying to automate everything at once.
Normally the result is people get frustrated, they give up the process of automating and keep doing the actions manually.

Forget about doing thousands of courses about programming – you need to learn how to Google.

Yes, you read it correctly.

95% of the challenges you are facing while programming your tool, someone in the world already faced this issue and someone in another part of the globe replied with the answer.

Imagine I want to program how to add a new column in Macro.

What would I do? I would google: “VBA how to add a new column”
Focus on the results from sites like MrExcel, StackOverflow and misterpaton.com

Enable developer Tab in Excel

You open your Excel, and you start to look for the Developer tab in excel and you don’t see it.
No worries – it is normal, by default the Developer tab is not visible in Excel.

This is what the Developer tab looks like:

If you don’t see it in your Excel – keep reading.
I will show you how to show the Developer Tab in Excel

Step by step. Uno, Dos, Tres – One, two three.

Click on File.

 Click on Options (bottom left)

Under the Customize Ribbon, find Developer on the left and move it to the right side.

Click OK, you are good to go.
The developer tab will be visible in your excel.

Time to start programming your first Macro

Step-by-step guide for your first Macro in Excel VBA

Open new Excel File – in this case, my file is called Book 1 and the Developer tab is already visible

Open the Microsoft Visual Basic for Applications - How? Click Alt+F11

Click on Insert –> Module

After this action a blank page will be visible – that’s the place where we will write our Macro.
It is the equivalent of the blank canvas where an artist is reflecting art.

We are becoming the artist of Macros – and we will be reflecting our art there.

How to write your first Macro in Excel

Each Macro in Excel always starts and ends in the same way.

Starts with Sub and name of the macro and brackets () -> Sub MisterPaton_firstMacro()  
ends with End Sub

Once you have your Macro programmed you to save the Macro in the Microsoft Visual Basic for Applications window – as per below.

and you save the file as Excel Macro-Enabled Workbook.

Examples of VBA code for your first Macro in Excel VBA

I leave you here some examples of VBA codes that you can paste into your Module.

Pop up message

Sub MisterPaton_firstMacro()

'Pop up message
MsgBox "Hello Mister Paton"


End Sub

Go to a sheet select a cell and clear content

Sub MisterPaton_firstMacro()
'Go to a sheet select a cell and clear content
'Activate Sheet1
 Sheets("Sheet1").Select

'Select cell B2
Range("B2").Select

'Clear content in cell selected
Selection.ClearContents
End Sub

Clear all content in the active sheet

Sub MisterPaton_firstMacro()
'Select all the cells in the sheet 
Cells.Select 
'Clear the content 
Selection.ClearContents 
End Sub

The next step is learning how to navigate through the Macro.
How to run the Macro end to end, how to add comments, how to run the macro line by line … you can find more details here.