Table of contents
- Why use macros in Excel?
- Macros in Excel basics
- How to write macros in Excel?
- Macros in Excel and VBA programming language
*Click images to enlarge
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:
- The developer Tab and The Ribbon or the menu bar you can find more here https://www.macrosinexcel.com/add-developer-tab-in-excel
- VBA programming language
How to write macros in Excel?
To begin, we will perform only simple operations to familiarize you with the macro recorder.
- Open an empty workbook and launch the Macro Recorder:
- Excel version 2003: menu Tools ➝ Macro ➝ Record New Macro
- For Excel 2007 to 2016: menu View ➝ Macros ➝ Record Macro…
(Or From the menu Developer ➝ Record Macro (Don’t forget to add The Developer Tab to the Ribbon))
- The 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).
- Click OK, the recording will start. From this moment on, all your manipulations in Excel are translated and saved.
- 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.
- 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
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.