To create a VBA function in Excel for Microsoft 365 on Windows, press Alt+F11, choose Insert > Module in the Visual Basic Editor, and type a Function procedure. Save the workbook as an Excel Macro-Enabled Workbook (.xlsm), then call the function from a worksheet cell like a built-in function.
What a VBA function does
A VBA function, also called a user-defined function or UDF, is a procedure that receives inputs, performs a calculation, and returns one result. You can use it in a worksheet formula when Excel’s built-in functions do not express a calculation in the way you need, or when you want to reuse a calculation without repeating a long formula.
For example, a custom function could calculate a discounted price from a price and a discount rate. Once the function is stored in a workbook, you can use it in multiple cells and pass different cell values to it. The function’s name appears in formulas just as a built-in function’s name does, although Excel may not provide the same documentation or function suggestions for a custom function.
A worksheet UDF is not the same as a Sub macro. A Sub performs actions, such as formatting cells or copying data, and is generally run from the Macro dialog, a button, or the VBA editor. A Function returns a value, so a worksheet formula can display that value. A function used in a cell should generally calculate and return a result rather than change other cells or perform actions such as displaying a message box.
Prepare Excel and open the VBA editor
VBA is available in the desktop versions of Excel for Windows and Mac. Excel for the web can display a workbook containing VBA, but it cannot create, edit, or run VBA macros. Use desktop Excel to build and use the function.
On Windows, open the editor with Alt+F11. If the Developer tab is not visible, turn it on through File > Options > Customize Ribbon, select Developer in the list of main tabs, and choose OK. You can then open the editor from Developer > Visual Basic.
On a Mac, open the Visual Basic Editor from Developer > Visual Basic. The Developer tab can be enabled in Excel > Settings (or Preferences, depending on the release) > Ribbon & Toolbar. Some Mac keyboards require Fn along with the Option+F11 shortcut. The available ribbon labels and keyboard behavior can differ slightly by Excel release.
Create the function in a standard module
In the Visual Basic Editor, select the workbook project in the Project Explorer. Choose Insert > Module. A standard module is the usual place for a function intended for worksheet use; do not put it in a worksheet’s code module or the ThisWorkbook module unless you have a specific reason to use event-related code.
Type or paste the following function into the new module. It takes a price and a discount rate as inputs and returns the price after applying the discount.
Public Function PriceAfterDiscount(ByVal price As Double, ByVal discountRate As Double) As Double
' discountRate is a decimal, such as 0.15 for 15 percent.
Dim discountedPrice As Double
discountedPrice = price * (1 - discountRate)
PriceAfterDiscount = discountedPrice
End Function
The declaration names the function, its inputs, and the type of value it returns. The two inputs are Double values, suitable for ordinary decimal-number calculations. The local variable discountedPrice holds the intermediate result. Assigning a value to PriceAfterDiscount sets the function’s return value. The comment explains that the rate must be passed as a decimal; a worksheet percentage cell does this automatically.
Save the workbook in a macro-enabled format
Before running any macro, save a copy of the workbook. Macro actions cannot be undone with Ctrl+Z, and a coding mistake can affect workbook content. When the function is ready, return to Excel and use File > Save As. Choose Excel Macro-Enabled Workbook (*.xlsm), then save the file. The standard .xlsx format cannot store VBA code; if you save a workbook with VBA as .xlsx, Excel warns that the code will be removed if you continue.
When reopening an .xlsm file, Excel may show a security notification asking whether to enable macros. Only enable macros in a workbook you trust and expected to receive. For a workbook you create yourself, follow the prompt as appropriate for your organization’s security rules. Do not lower Excel’s general macro security settings simply to make an unfamiliar file run.
Call the function from a worksheet
With the price in A2 and the discount rate in B2, enter this formula in C2:
=PriceAfterDiscount(A2,B2)
For example, if A2 contains 100 and B2 contains 15%, the result in C2 is 85: 100 multiplied by 1 minus 0.15. Enter the rate as a percentage or as its decimal equivalent, 0.15. If you enter 15 instead, the function interprets that as a rate of 1,500%, because it does not convert whole numbers into percentages.
You can fill the formula down to use the function for other rows. A cell reference passed to a UDF supplies its value as an input; it does not make the function a macro that writes back into that cell. If Excel does not recognize the function after you enter the formula, check that the code is in a standard module in the same open workbook and that macros are enabled for that file.
Understand arguments, data types, and return values
Function arguments are the inputs listed between the parentheses in the declaration. In this example, the names price and discountRate are used inside the code, while the worksheet formula supplies A2 and B2. Argument order matters: the first value goes to price and the second to discountRate. If a function has several inputs, give them descriptive names and document units, expected formats, or special cases in comments.
VBA data types help define what the function expects and returns. Double is useful for decimal calculations; Long is commonly used for whole-number counts; String is for text; and Boolean represents True or False. A return type after the closing parenthesis tells VBA what kind of result the function provides. You can use Variant when inputs or outputs need to accommodate several kinds of values, but a specific type can make the function’s intended use clearer.
The sample intentionally keeps its scope narrow: it assumes numeric inputs and does not validate whether the discount rate is between zero and one. If the function will be used by others, decide how it should handle blank cells, text, negative prices, or rates outside the expected range. Add and test suitable checks rather than assuming every user will supply valid data. For complex error handling, use a return type and design that communicate invalid inputs clearly.
Common problems and how to troubleshoot them
- #NAME? Usually means Excel cannot find the function. Check the spelling in the formula and code, confirm the procedure is declared as a Public Function in a standard module, and make sure the workbook containing it is open.
- The code disappears after saving. Check the file type. Save as .xlsm rather than .xlsx if the workbook needs to retain VBA.
- The formula returns an unexpected number. Verify the order of the arguments and the units. For the sample, a 15% discount must be passed as 15% or 0.15, not 15.
- Excel blocks the code. Check the security warning and your organization’s policy. Enable macros only for files you trust; do not treat a security warning as a reason to enable every macro globally.
- A code change does not appear in a result. Recalculate the worksheet. Press F9 on Windows to recalculate, or use Formulas > Calculate Now. If results still look stale, check that calculation is not set to Manual and that the formula refers to the intended cells.
When debugging, place the cursor inside the function in the Visual Basic Editor and use F8 to step through code on Windows. You can inspect variables as execution advances. On Mac, use the editor’s Debug menu and check the keyboard shortcut shown for stepping through code, since function keys may be assigned to hardware controls. A compile error often points to a missing End Function, misspelled variable, or invalid declaration; resolve it before relying on worksheet results.
Know the limits of worksheet UDFs
A worksheet UDF is best for returning a calculation from its inputs. It is not a replacement for every macro. Excel restricts what worksheet functions can do: a UDF called from a cell should not change other cells, insert sheets, or alter Excel settings. A function that depends on outside state or performs actions may not recalculate when you expect, and it is harder to reason about than a function based only on its arguments.
VBA functions are also specific to macro-enabled desktop workbooks. They are not available as worksheet functions in Excel for the web, and they are not portable to Google Sheets as VBA. If colleagues need to use the workbook in a browser, consider replacing the UDF with built-in formulas or a supported automation approach. Keep a brief description of the function’s purpose and input expectations near the workbook so another user can understand its results.
When to use VBA instead of a built-in formula
Before writing VBA, see whether standard Excel functions or operators already solve the problem. Built-in formulas are usually easier to share, audit, and use in Excel for the web. A custom UDF is useful when a calculation is repeated, has logic that would be unwieldy in a cell formula, and must return a value based on worksheet inputs. A Sub macro is a better fit when the goal is to carry out a sequence of actions rather than calculate a cell result.
For workbooks shared widely, consider whether users can enable macros and whether your organization permits VBA. If not, a formula-based design may be more practical. For larger automation tasks, Office Scripts may be an option in supported Microsoft 365 environments, but scripts are not VBA functions and do not work as custom worksheet formulas. Choose the tool according to where the workbook will run and what the task needs to do.
Frequently Asked Questions
Can I use a VBA function in an Excel cell?
Yes. Put a Public Function in a standard module in a macro-enabled workbook, then enter its name in a worksheet formula with the required arguments. The workbook must be open, and macros must be permitted for the file.
Why does my custom function show #NAME?
Excel may not recognize the name because it is misspelled, the function is not Public, its code is in the wrong module, or the workbook containing it is closed. Check those points and confirm that the file was saved as .xlsm.
Can I create a VBA function in Excel for the web?
No. Excel for the web does not provide the VBA editor or run VBA macros. Create and use the function in desktop Excel, or redesign the calculation with features supported in the web app.
What is the difference between a VBA Function and a Sub?
A Function returns a value and can be called from a worksheet formula when it is designed as a worksheet UDF. A Sub runs a procedure or action, such as formatting a range, and is not entered into a cell as a formula to return a result.
Do I need to save as XLSM to keep the function?
Yes. Save the workbook as an Excel Macro-Enabled Workbook (.xlsm) to retain VBA. An .xlsx workbook does not store VBA code, so choose a macro-enabled format when the function must remain available.
Related
- Excel Heat Map by City: Steps, Data, and Map Options
- Excel Heat Map by ZIP Code: Setup, Limits, and Fixes
- Excel Group Rows Upwards? Put Summary Rows Above
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- VBA InputBox Default: How to Set It and Handle CancelIn VBA, set an InputBox default by supplying the third argument: InputBox(prompt, title, default). That value appears in…
- Filter a Dictionary in Excel VBA: Practical ExamplesExcel VBA has no built-in Filter method for a Scripting.Dictionary. The usual approach is to loop through the…
- Excel VBA UserForm: Bring It to the FrontTo bring a modeless Excel VBA UserForm forward on Windows, show it with vbModeless, then use the Windows…
- VBA Dictionary: Search by Key and Check Whether It ExistsTo search a VBA Dictionary by key, call its Exists method before retrieving the value: use If dict.Exists(searchKey)…
- Excel VBA UserForm Progress Bar: Build One That WorksTo add an Excel VBA UserForm progress bar, create a UserForm with a Frame and a colored Label…
- Excel VBA: How to Copy a Module to Another WorkbookTo copy an Excel VBA module to another workbook, open both files in desktop Excel, press Alt+F11, and…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.