how to easily write an macro with excel

Introduction

With Excel VBA you can automate tasks in Excel by writing so called macros. Learn how to create a simple macro in this blog – it’s easier than you think!

Why do I need Excel macro?

Very often we have to repeat the same actions and operations in Excel. Any office work assumes a certain “routine component”. For example, the same weekly reports, the same actions for processing received data, etc. The use of macros and user-defined functions makes it possible to automate these operations by using macros. Besides, you could use macros to add missing but necessary Microsoft Excel functions. For instance, changing the cell format to the company standard in one click. Otherwise, you could trace the precedents or dependents in a complex model in Excel, etc.

So, what is it again?

A macro is a programmed sequence of actions written in the Visual Basic for Applications programming language (VBA). We can run the macro as many times as necessary, causing Excel to execute a sequence of any actions we need, which we do not want to perform manually.

Macros sound scary to you? It’s much easier than you think!

Let me walk you through the example of creating a simple macro. We know that to fill the formula and format to the right is a shortcut “Ctrl+R”, to fill it down is “Ctrl+D”. Do you know how to fill the formula and format left without a mouse? It would be “Alt H Z E F I L” – not that easy to remember, hah? Why don’t we create a shortcut “Ctrl+L” for it using a macro?

So, to get you started, let’s start recording a macro with hot key “Alt L R” – Alt will give you the access to the Excel Ribbon, you need to click each of the keys here one after another.

The “Record Macro” window will appear, where you can name a macro and assign a shortcut for a macro. Be careful and don’t overwrite existing shortcuts here. We will put “Ctrl+L” for our macro to fill formula and format to the left.

After clicking “Ok” for the “Record Macro” window, a macro will start recording. It means that every action you’ll take in Excel from now on will be recorded in this particular macro – until you stop recording it. So, be careful and very precise with the actions you take while recording a macro. How would you know if the macro is recording in the moment? You can find “Ready” with the square sign in the left bottom corner of your Excel (see the picture).

After you are done, stop recording a macro with the same hot key sequence “”Alt L R”. How would you know if the macro has stopped recording? You can find “Ready” with the different sign (looks like a micro Excel window) in the left bottom corner of your Excel (see the picture).

Now, let’s try our macro we have just recorded – let’s fill the formula and format to the left with the shortcut we have assigned for it “Ctrl + L” (don’t forget to highlight the necessary cells for that).

How to find “Developer Tab” if it’s not there? How to Edit or Delete a Macro?

It’s as easy as to create a macro. Please, read our second blog about Excel macros for that.
You also can learn it quickly and efficiently with our Game – where you can learn and try Excel tricks at the same time – keySkillset.

What if I need to learn more advanced Macros or lazy to do them myself at all?

There are great tutorials to learn Advanced Macros provided by Ryan Wells and some webinars that Jon Acampora provides at Excel Campus.
If you’d like to have a ready solution and don’t want to spend any time on writing the macros yourself – Jan Karel Pieterce at JKP Application Development Services would help you with that.

Conclusion

Laziness drives all progress. Creating macros to automate repetitive actions in Excel will make your life much easier and will save you tons of time! And as we know now – it’s much easier than it sounds. Good luck!

‍