by Svetlana Cheusheva, updated on
This tutorial will set you on your way to learning Excel macros. You will find how to record a macro and insert VBA code in Excel, copy macros from one workbook to another, enable and disable them, view the code, make changes, and a lot more.
For Excel newbies, the concept of macros often looks insurmountable. Indeed, it may take months or even years of training to master VBA. However, this does not mean you cannot take advantage of the automation power of Excel macros right away. Even if you are a complete novice in VBA programming, you can easily record a macro to automate some of your repetitive tasks.
This article your entry point to the fascinating world of Excel macros. It covers the essential basics that you need to know to get started and provides links to the related in-depth tutorials.
Excel macro is a set of commands or instructions stored in a workbook in the form of VBA code. You can think of it as a small program to perform a predefined sequence of actions. Once created, macros can be re-used anytime. Running a macro executes the commands it contains.
Typically, macros are used to automate repetitive tasks and daily routines. Skilled VBA developers can write really sophisticated macros that go well beyond reducing the number of keystrokes.
Quite often, you may hear people referring to a "macro" as "VBA". Technically, there is a distinction: a macro is a piece of code while Visual Basic for Applications (VBA) is the programming language created by Microsoft to write macros.
The main purpose of macros is to have more work done in less time. Like you use formulas to crunch numbers and manipulate text strings, you can use macros to perform frequent tasks automatically.
Let's say, you are to create a weekly report for your supervisor. For this, you import various analytics data from a couple or more external resources. The problem is that those data are messy, superfluous, or not in the format that Excel can understand. That means you need to reformat dates and numbers, trim extra spaces and delete blanks, copy and paste information into appropriate columns, build charts to visualize trends, and do a lot more different things to make your report clear and user-friendly. Now, imaging that all these operations can be performed for you instantly in a mouse click!
Of course, building a complex macro takes time. Sometimes, it can take even more time than performing the same manipulations manually. But creating a macro is a one time set-up. Once written, debugged and tested, VBA code will do the job quickly and flawlessly, minimizing human errors and costly mistakes.
There are two ways to create macros in Excel - by using the Macro Recorder and Visual Basic Editor.
Tip. Within Excel, most operations with macros are done via the Developer tab, so be sure to add Developer tab to your Excel ribbon.
Even if you don't know anything about programing in general and VBA in particular, you can easily automate some of your work just by letting Excel record your actions as a macro. While you are performing the steps, Excel closely watches and writes down your mouse clicks and keystrokes in the VBA language.
The Macro Recorder captures nearly everything that you do and produces a very detailed (often redundant) code. After you've stopped the recording and saved the macro, you can view its code in the Visual Basic Editor and make small changes. When you run the macro, Excel goes back to the recorded VBA code and executes the exact same moves.
To start recording, click the Record Macro button on either the Developer tab or the Status bar.
For the detailed information, please see How to record macro in Excel.
The Visual Basic for Applications (VBA) Editor is the place where Microsoft Excel keeps the code of all macros, both recorded and written manually.
In the VBA Editor, you can not only program a sequence of actions, but also create custom functions, display your own dialog boxes, evaluate various conditions, and most importantly code the logic! Naturally, creating your own macro requires some knowledge of the structure and syntax of the VBA language, which is beyond the scope of this tutorial for beginners. But there is nothing that would prevent you from reusing someone else's code (say, the one you've found on our blog :) and even a complete novice in Excel VBA should have no difficulties with that!
First, press Alt + F11 to open the Visual Basic Editor. And then, insert the code in these two quick steps:
When done, press F5 to run the macro.
For the detailed steps, please see How to insert VBA code in Excel.
There are several ways to start a macro in Excel:
Additionally, you can launch a macro by clicking a custom button or pressing the assigned shortcut. For full details, please see How to run macros in Excel.
Because of security reasons, all macros in Excel are disabled by default. So, to use the magic of VBA codes to your advantage, you need to know how to enable them.
The easiest way to turn on macros for a specific workbook is to click the Enable Content button in the yellow security warning bar that appears at the top of the sheet when you first open a workbook with macros.
To learn more about macro security, please see How to enable and disable macros in Excel.
Microsoft Excel determines whether to allow or disallow VBA codes to be executed in your workbooks based on the macro setting selected in the Trust Center.
Here are the steps to access the Excel macro settings and change them if needed:
In the screenshot below, the default macro setting is selected:
For more information, please see Excel macro settings explained.
Any changes to the code of a macro, whether it's generated automatically by the Excel macro recorder or written by you, are made in the Visual Basic Editor.
To open the VB Editor, either press Alt + F11 or click the Visual Basic button on the Developer tab.
To view and edit the code of a specific macro, in the Project Explorer on the left, double-click the module that contains it, or right-click the module and pick View Code. This open the Code window where you can edit the code.
To test and debug a macro, use the F8 key. This will take you through the macro code line-by-line letting you see the effect that each line has on your worksheet. The line currently being executed is highlighted in yellow. To exit debug mode, click the Reset button on the toolbar (blue square).
You created a macro in one workbook and now want to reuse it in other files too? There are two ways to copy a macro in Excel:
In case the target macro resides in a separate module or all the macros in the module are useful for you, then it makes sense to copy the whole module from one workbook to another:
In the screenshot below, we are copying Module1 from Book1 to Book2:
If the module contains many different macros while you need just one, then copy only the code of that specific macro. Here's how:
If you no longer need a certain VBA code, you can delete it by using the Macro dialog box or the Visual Basic Editor.
To delete a macro directly from your Excel workbook, carry out these steps:
Tips:
A benefit of using the VBA Editor is that it enables you to delete an entire module with all the macros it contains in one go. Also, the VBA Editor allows deleting macros in the Personal Macro Workbook without unhiding it.
To permanently delete a module, perform these steps:
To remove a specific macro, simply delete its source code directly in the Code window. Or, you can delete a macro by using the Tools menu of the VBA Editor:
To save a macro in Excel, either recorded or written manually, just save the workbook as macro enabled (*.xlms). Here's how:
If you'd like to share your VBA codes with someone or move them to another computer, the fastest way is to export the entire module as a .bas file.
To export your VBA codes, this is what you need to do:
To import a .bas file with VBA codes into your Excel, please follow these steps:
One of the best ways to learn Excel VBA is by exploring code samples. Below you will find examples of very simple VBA codes that automate some basic operations. Of course, these examples won't teach you coding, for this there exist hundreds of professional-grade VBA tutorials. We just aim to illustrate a few common features of VBA that will hopefully make its philosophy a little more familiar to you.
In this example, we use the ActiveWorkbook object to return the currently active workbook and the For Each loop to go through all the sheets in the workbook one-by-one. For each found sheet, we set the Visible property to xlSheetVisible.
To manipulate the currently active sheet, use the ActiveSheet object. This sample macro changes the Visible property of the active sheet to xlSheetHidden to hide it. To make the sheet very hidden, set the Visible property to xlSheetVeryHidden.
If you want to perform certain operations on a range rather than the entire worksheet, use the Selection object. For example, the below code will unmerge all the merged cells in a selected range at one fell swoop.
To show some message to your users, use the MsgBox function. Here's an example of such a macro in its simplest form:
In real-life macros, a message box is typically used for information or confirmation purposes. For instance, before performing a action (unmerging cells in our case), you display a Yes/No message box. If the user clicks "Yes", the selected cells are unmerged.
To test the code, select one or more range containing merged cells and run the macro. The following message will appear:
Below are the links to more complex macros that automate challenging and time-consuming tasks:
If you'd like to prevent your macro from being viewed, modified or executed by others, you can protect it with password.
To protect your VBA codes from unauthorized viewing and editing, do the following:
When you try to view the code in the Visual Basic editor, the following dialog box will appear. Type the password and click OK.
To unlock macros, just open the Project Properties dialog box again and remove a tick from the Lock project for viewing box.
Note. This method protects the code from viewing and editing but does not prevent it from being executed.
To protect your macro from being executed so that only the users who know the password could run it, add the following code, replacing the word "password" with your real password:
The macro uses the InputBox function to prompt the user to enter password:
If the user's input matches the hardcoded password, your code is executed. If the password does not match, the "Incorrect Password" message box is displayed. To prevent the user from peeping the password in the Visual Basic Editor, remember to lock the macro for viewing as explained above.
Note. Given the numbers of various password crackers available on the web, it is important to realize that this protection is not absolute. You can regard it rather as a protection against accidental use.
Excel VBA pros have devised tons of ingenious tricks to make their macros more effective. Below I will share a couple of my favorite ones.
If your VBA code actively manipulates the cell contents, you can speed up its execution by turning off screen refreshing and formula recalculating. After executing your code, turn this on again.
The following lines are to be added to the beginning of your code (after the lines that start with Dim or after the Sub line):
The following lines are to be added to the end of your code (before End Sub):
When writing code in the VBA editor, at times you may create very lengthy statements, so you have to scroll horizontally to view the end of the line. This does not affect the code execution but makes it difficult to examine the code.
To split a long statement into several lines, type a space followed by an underscore (_) at the point at which you want to break the line. In VBA, this is called the line-continuation character.
To continue the code on the next line correctly, please follow these rules:
The following code example shows how to break the statement into two lines:
When you write or record a macro in Excel, usually it can only be accessed from that specific workbook. If you'd like to reuse the same code in other workbooks, save it to the Personal Macro Workbook. This will make the macro available to you whenever you open Excel.
The only obstacle is that the Personal Macro Workbook does not exist in Excel by default. To create it, you will need to record at least one macro. The following tutorial provides all the details: Personal Macro Workbook in Excel
After executing a macro, its action cannot be reverted by pressing Ctrl + Z nor by clicking the Undo button.
Experienced VBA programmers can, of course, validate the input values and/or starting conditions before allowing the macro to make any changes to a worksheet, but in most cases that is quite complicated.
An easier way is to save the active workbook from within the macro's code. For this, simply add the below line before letting your macro do anything else:
Optionally, you can also show a message box informing the user that the current workbook was saved right before executing the main code of the macro.
This way, if you (or your users) are not happy with the results, you can simply close, and then reopen the workbook.
Have you ever found yourself in a situation where Excel continuously asks if you want to enable macros while you definitely know that there are no macros in this particular workbook?
The most likely reason is that some VBA code was added and then removed, leaving an empty module, which triggers the security alert. To get rid of it, simply delete the module, save the workbook, close and reopen it. If this does not help, do the following:
That's how you create and use VBA macros in Excel. I thank you for reading and hope to see you again on our blog next week!
Table of contents