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.
Dive Deep into Macros! Make Excel do things you thought were impossible, discover techniques you won't find anywhere else, and create powerful automated reports. Bill Jelen and Tracy Syrstad help you instantly visualize information to make it actionable. You’ll find step-by-step instructions, real-world case studies, and 50 workbooks packed with examples and solutions. Check out Microsoft Excel 2019 VBA and Macros today!
Import information from a program external to Excel, and your numbers may be treated as text because of the way that the ...
Discover MoreStrings are used quite frequently in macros. You may want to swap the contents of two string variables, and you can do so ...
Discover MoreIf your workbook contains links, you are normally given the opportunity to update those links when you open the workbook. ...
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