Macros in Excel are code blocks that you can run in your workbooks to process a batch of commands. Macros are great for automating repetitive tasks such as copying or removing data from specific cells. However, macros can be daunting for new users. Creating a macro is actually very easy! In this article, we are going to show you how to create a macro in Excel.
How to Create a Macro in Excel
The steps involve opening the Visual Basic (VBA) window, entering your code, and saving your file with the correct extension.
Open Visual Basic for Applications (VBA) window
Before jumping into writing the code, you will first need to access the Visual Basic for Applications (VBA) window. There are 2 ways you can do this - Either press the Alt+F11 key combination on your keyboard, or click on the Visual Basic icon in Developer tab of the Ribbon. If you haven't added the Developer tab before, you will not be able to see it as the Developer tab is hidden by default. See how to display Developer tab in Excel for more details.
Add a Module
VBA window lists the elements of your project on the left panel. The workbook itself and existing worksheets are the default elements. The right side is empty if there isn't any existing code. You need to add a module to your project to enter your code. A module is a basic text editor where you can enter your code and save it within the workbook (project).
To add a module into your project:
- In the VBA window, click Insert on the toolbar
- Click the Module option
Write your code
After inserting a module, the module window becomes visible at the right panel once empty. You can write or copy and paste code here. If you need sample codes, see VBA samples.
There are also two dropdowns at the top of the module screen. Basically, while the left one lists the objects in its scope, the right one lists the events related with the selected object. On the other hand, if you are using a module, you won't see any items on the left dropdown and the right one lists the macro (subroutine) names. If you click on a macro name in the list, pointer will be moved to the macro.
Save as Excel Macro-enabled Workbook (XLSM)
With the 2007 version, Excel has started to use different file types for regular Excel workbooks and workbooks with macros. You need to save your file as a macro-enabled workbook or an XLSM file. Excel warns you if you try to save a file with a macro as an XSLX file. So remember to save your file as XLSM after creating a macro.
