Macros in Excel with VBA code examples

Why use macros in Excel?

A macro is the diminutive of macro-command. It is a kind of computer program that most often executes actions such as those that the user would perform using the menu and It allows performing several commands automatically at once, without tedious user intervention.
Often macros also contain control instructions that allow, for example, the same action to be performed several times.
When to use macros?

  • If a file becomes too large because of formulas
  • Some of the formulas are no longer sufficient and/or become too complex (you can’t figure it out!)
  • When you need a more advanced user interface
  • Or simply when you like to write macros

Macros in Excel basics

Well when you want to create or use a macro in Excel 2016 or other versions, you need some basics concept. Here are the most common things you should know:

How to write macros in Excel?

To begin, we will perform only simple operations to familiarize you with the macro recorder.

  1. Open an empty workbook and launch the Macro Recorder:
    • Excel version 2003: menu ToolsMacroRecord New Macro
      Macro Excel 2003

      Macro Excel 2003

    • For Excel 2007 to 2016: menu ViewMacrosRecord Macro…
      (Or From the menu DeveloperRecord Macro (Don’t forget to add The Developer Tab to the Ribbon))

      Macro Excel 2007 to 2016

      Macro Excel 2007 to 2016

  2. The Macro Record Window
    Macro Record Window

    Macro Record Window

    NB: The macro name must not contain spaces. You can remove them or replace them with allowed characters (the underscore for example).

  3. Click OK, the recording will start. From this moment on, all your manipulations in Excel are translated and saved.
  4. Do some manipulations in the spreadsheet:
    • Click on cell A1 to select it,
    • Enter the text “Hello” in cell A1 and put the cell in Bold,
    • Then cut it (menu Edit / Cut or key combination Ctrl+x),
    • Paste it into cell B1 (Edit / Paste or key combination Ctrl+v).

    That’s a little simple Macro 🙂 but you can do any instruction you want and it will be recorded.

  5. Stop recording Macro
    • In the Excel 2003: using the menu Tools ➝ Macro ➝ Stop Recording
    • And Excel 2007 to 2016: using the menu View ➝ Macro ➝ Stop Recording or menu Developer ➝ Stop Recording
    Stop Recording Macro In Excel

    Stop Recording Macro In Excel

Congratulation your first Macros in Excel is created! Now let’s see how to execute it.
There are various ways to access a macro, we have already seen above that it is possible to associate a shortcut to them when recording.
To run the macro in Excel:

  • 2003: Tools ➝ Macro ➝ Macros…
  • 2007 to 2016: menu View ➝ Macros ➝ View Macros or menu Developer ➝ Macros

You should find it in the list (here under the name “myFirstMacro”). Then select it then click on “Run“.
It is not that complicated! The interest is now to know how to modify it and create your own macros.

Macros in Excel and VBA programming language

As we have seen, Excel’s macro recorder tools are very useful for creating macros in Excel 2016 or older versions without special knowledge. But the power of the language used is not limited to the reconstruction of simple repetitive tasks. You will now discover what programming is and all the advantages it can bring to your Excel workbooks.
You have recorded and executed a macro, now let’s see where it is stored…
From the window that runs the macro, you can also delete and edit the Macro selected.
If you click the Edit button a new software will be opened it’s the Visual Basic Editor.
Visual Basic is the programming language used by Excel for macros.
The recorded macro must be stored in Module1.
Double-click on this module to display its contents and discover what your macro really looks like!

Next tutorial we gonna go deep into the Visual Basic programming language and how to create advanced macros in Excel.
Check out also:

Excel Add-ins Step by Step Tutorial :

How to add developer tab in Excel? :

Leave a Comment