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.
Create Custom Apps with VBA! Discover how to extend the capabilities of Office 365 applications with VBA programming. Written in clear terms and understandable language, the book includes systematic tutorials and contains both intermediate and advanced content for experienced VB developers. Designed to be comprehensive, the book addresses not just one Office application, but the entire Office suite. Check out Mastering VBA for Microsoft Office 365 today!
Import a bunch of ZIP Codes into Excel, and you may be surprised that any leading zeroes disappear. Here's a handy little ...
Discover MoreCreating lot of copies of a worksheet is a snap within a macro. This tip introduces a macro that will make three copies ...
Discover MoreThe data stored in a worksheet can often correspond to information external to that worksheet. For instance, you might ...
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