Excel blocks user-defined functions with a security warning to protect your computer. This happens when you open a workbook containing custom VBA code. The warning prevents potentially harmful macros from running automatically. This article explains why the warning appears and how to safely enable your custom functions.
Key Takeaways: Enabling User-Defined Functions
- Macro Security Settings: Adjust the Trust Center settings to allow macros from trusted locations or with digital signatures.
- Enable Content Button: Click the yellow security warning bar in the workbook to enable macros for the current session.
- Save as Macro-Enabled Workbook: Use the .xlsm file format to preserve VBA code and function definitions.
Why Excel Blocks User-Defined Functions
Excel’s security model treats user-defined functions as macro code. Macros can contain instructions that might harm your system or data. To manage this risk, Excel disables all macros by default and shows a security warning. The warning appears as a yellow bar under the ribbon with a button labeled “Enable Content.”
This default setting is part of the Trust Center. It applies to any workbook with a .xlsm extension or any file containing VBA project components. The blocking occurs regardless of the function’s purpose. Even a simple, harmless calculation formula written in VBA will trigger the alert. You must take explicit action to tell Excel you trust the file’s source.
Understanding Trusted Documents and Locations
Excel can remember files you have previously enabled. After you click “Enable Content,” Excel may mark the file as a trusted document on your computer. This means the warning will not appear the next time you open the same file on the same PC. You can also designate specific folders as trusted locations. Any workbook with macros placed in a trusted location will open without security prompts.
Steps to Enable Custom Functions in a Workbook
Follow these steps to allow your user-defined functions to run. Start with the simplest method for a single file.
- Open the workbook with the security warning
Launch the Excel file containing your custom functions. Look for the yellow “Security Warning” bar under the ribbon. It states “Macros have been disabled.” - Click the Enable Content button
On the yellow security bar, click the “Enable Content” button. This action allows macros to run for this session only. Your user-defined functions should now calculate correctly. - Save the workbook in a macro-enabled format
Go to File > Save As. Choose a location on your computer. In the “Save as type” dropdown, select “Excel Macro-Enabled Workbook (*.xlsm).” Click Save. This preserves the VBA code for future use.
Changing Macro Security Settings for All Files
If you frequently use trusted macros, you can lower the security setting. This method affects all workbooks you open.
- Open the Trust Center
In Excel, go to File > Options. Select the “Trust Center” category on the left. Click the “Trust Center Settings…” button on the right. - Adjust Macro Settings
In the Trust Center window, select “Macro Settings” on the left. You will see four radio button options. Select “Disable all macros with notification.” This is the default and recommended setting. It shows the yellow warning bar but lets you choose to enable content. - Add a Trusted Location (Optional)
For maximum security, use trusted locations instead of lowering global settings. In the Trust Center, select “Trusted Locations” on the left. Click “Add new location…” and browse to a folder where you store your safe macro files. Workbooks opened from this folder will not show security warnings.
If Your Custom Functions Still Do Not Work
Excel Shows “#NAME?” Error After Enabling Content
The “#NAME?” error means Excel cannot find the function name. This often happens if the VBA code module was deleted or saved incorrectly. Open the Visual Basic Editor by pressing Alt + F11. In the Project Explorer pane, check if your workbook contains a module with the function code. If the module is missing, you must reinsert the VBA code.
Security Warning Bar Does Not Appear
If the yellow bar is missing, your macro security might be set to “Disable all macros without notification.” Go to File > Options > Trust Center > Trust Center Settings > Macro Settings. Verify the setting is “Disable all macros with notification.” Also, ensure the file is saved as a .xlsm file. Standard .xlsx files cannot contain macros.
Function Works on One Computer But Not Another
Macro security settings are specific to each Windows user profile and computer. Enabling a macro on your office PC does not enable it on your home laptop. You must enable the content on each device where you open the file. For shared network files, consider using a digital signature for the VBA project or placing the file in a trusted location on each machine.
Macro Security Settings Comparison
| Item | Disable all macros with notification | Disable all macros without notification | Enable all macros |
|---|---|---|---|
| Security Level | Recommended default setting | High security, no user choice | Low security, high risk |
| User Prompt | Shows yellow security warning bar | No warning, macros silently disabled | No warning, all macros run |
| Best For | Most users who occasionally use macros | Environments where macros are strictly forbidden | Isolated testing machines only |
| Effect on UDFs | Functions blocked until user clicks Enable Content | Functions permanently disabled, user cannot enable | All user-defined functions run automatically |
You can now use your custom Excel functions by enabling macros for trusted files. Always verify the source of a workbook before clicking Enable Content. For ongoing use, save your macro workbooks in the .xlsm format. Explore using digital signatures for VBA projects if you need to distribute macros securely across a team.