MVP #60: Introduction To Excel VBA (macros)

A lot of people feel making macros in Excel is extremely hard and should be left only to people who make a living from doing it full-time. If you are one of such people, I have a pleasant surprise for you. Macros in Excel is very easy and in the next five minutes I will guide you through making one.

So just before we start, let me do a brief explanation of what a macro is, why you might need to make one and the benefits of being able to make one.

Macros are simply a means of automating tasks in Excel. It’s no more than that. You might need to do it when you have a daily or weekly report you make that is of an unvarying standard format, input and output-wise. Having a macro can cut your analysis time from hours to 15 seconds. It’s like magic and everyone in your office will see you as a special being.

To be able to make macros, you need to make a small settings change in your Microsoft Excel.

Go to Files, Options and Customize Ribbon. Check the box beside Developer.



Now you will be able to access the Developer menu.



And also the Macro record button, which we will use in this introduction to Excel VBA.


Next, I will show you how to create a macro by clicking the right button twice — the macro record button.

I have prepared a sample illustration data.


It is a fictitious table of Sales at an Autodealership by the different salesmen and the car make.

So the task I will use a macro to automate is a series of formatting steps. 

Note: For the gurus, it would be obvious that copy pasting format would have done the same thing our macro will do. Yes. But we have to do the illustration with something not too complex to confuse anyone. The good thing is that you will learn all the steps required to make any complex recorded macro you desire.

So here are the easy steps to creating a macro.

First, I select the month I want to manually do the formatting for and have the macro recorder save my steps.


Click on the macro record button.



Give the macro a name, a keyboard shortcut and a description.



Click on OK.

Then begin doing the formatting steps. I change the font type, font color and add border, making it have our corporate color feel. Once I am done, I click on the stop recording button.


And that’s all. We have created a macro. Next is to try it out and see it work.

Select another month’s record and press CTRL + k (the keyboard shortcut we used for the macro).




Voila! It works!

So let’s insert a macro button. A button you will click to run the macro. I am sure you’ve seen one before. They are super easy to create.

Go to the Developer menu, Insert and select Button under Form Controls.



Draw a rectangular button where you want the macro button to be. Immediately, Excel will ask you to select the macro to link it to. Select the macro we just created.


Click on OK.

Then edit the name of the rectangular button.


And that’s it! You’ve created a macro button.

Now select another month’s data and click on the macro button to see it work the magic we configured it for.


See the result!


Amazing, isn’t it?

I hope you are now convinced that creating a macro in Excel is very easy.

It’s now time for you to think up other creative ways to use a recorded macro.

Bonne chance!