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.
Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!
When writing a macro, you may want to fill a range of cells with different values. The easiest way to do this is to use ...
Discover MoreWant to get some input from the users of your workbooks? You can do it by using the InputBox function in a macro.
Discover MoreNeed to get rid of extraneous spaces before or after the text in a string? VBA provides three different functions you can ...
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