Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, 2021, 2024, and Excel in Microsoft 365. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Understanding Macros.
Written by Allen Wyatt (last updated October 18, 2025)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, 2021, 2024, and Excel in Microsoft 365
A macro is similar to a computer program. It consists of a series of instructions that the computer follows in a sequence you specify. The macro is given a name that is used to run the instructions it contains. Excel provides two general ways to create a macro. The first (and easiest) method is to record a macro using the macro recorder. The other method is to write a macro from scratch using the VBA Editor. While writing from scratch is perfectly acceptable, it is often a good idea, especially for smaller macros, to record the basic steps you want performed and then edit the recorded macro to create the final instructions.
Anything you do in Excel that is of a repetitive nature is a good candidate for a macro. For instance, you might have the job of creating financial analysis reports for your company and you want to create a macro that will enter the company name in the current cell and format it using the proper font. Such a task is easily done with a macro.
When you create a macro, you have the opportunity to store it in any of three places. Where you store a macro determines when it is available and how it can be later used. The following are the storage options available in Excel:
Remember that macros are only available if the workbook in which they are stored is open. Thus, only those stored in your Personal Macro Workbook will be available at all times. This works because the Personal Macro Workbook is always open (even if it is not visible). Macros you store in other workbooks are only available if that workbook is open.
Note:
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (8148) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, 2021, 2024, and Excel in Microsoft 365. You can find a version of this tip for the older menu interface of Excel here: Understanding Macros.
Excel Smarts for Beginners! Featuring the friendly and trusted For Dummies style, this popular guide shows beginners how to get up and running with Excel while also helping more experienced users get comfortable with the newest features. Check out Excel 2019 For Dummies today!
Want to have you macro completely hide the Excel interface? You can do so by using the Visible property for the Excel ...
Discover MoreIf you have a macro that selects different columns in a worksheet while processing information, you may get some ...
Discover MorePart of writing macros is to make sure they work as you expect. This involves a process known as debugging. Here's how ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2025-10-20 04:30:05
Mike J
@J.Woolley
Perhaps I should have added:
... unless the function is always preceded by "personal.xlsb!", which seems very tedious, or each of your workbooks have a reference to personal.xlsb. Mine do not since I already had 100s of workbooks before I even started using VBA - hence the add-in comment.
I suppose one could ensure all new workbooks have that reference, but I don't know how that would work when sharing them with other users.
2025-10-19 14:42:07
J. Woolley
A macro is sometimes defined as any VBA code, which typically begins with Sub or Function. I prefer a more specific definition that is consistent with the Macros dialog opened by Developer > Macros (Alt+F8):
A macro is a Sub with no parameters (a.k.a. arguments).
Therefore, a user-defined function (UDF) is not a macro because it begins with Function, not Sub. And the Macros dialog will not include this procedure
Sub MacroNot(Optional arg As Variant)
MsgBox "arg is " & IIf(IsMissing(arg), "missing", arg)
End Sub
but it will include this macro
Sub MyMacro()
MacroNot
MacroNot "Hello World"
End Sub
2025-10-18 10:38:05
J. Woolley
@Mike J
Yes, the Tip should have discussed an add-in (.xlam), which is similar to Personal.xlsb but must be installed.
See https://excelribbon.tips.net/T008527_Understanding_Add-Ins.html
However, a user-defined function (UDF) in Personal.xlsb should work whenever Personal.xlsb is loaded. Please provide an example of one that doesn't.
2025-10-18 09:31:48
Mike J
personal.xlsb seems to only work globally with SUBs.
If you want to create a UDF that works everywhere, it is better to put it into an Addin.
So that's a 4th important location.
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2025 Sharon Parq Associates, Inc.
Please Note:
This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, 2021, 2024, and Excel in Microsoft 365. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here:
Comments