VBA & Macro Problems

Fix Excel VBA UDF Returning #VALUE! Error with DisplayFormat

Steve K Sep 25, 2026 870 views

Question details

User needs to fix a VBA User-Defined Function (UDF) that returns a #VALUE! error when used in a worksheet formula to count cells by color.

How to Fix Excel VBA UDF Returning #VALUE! Error with DisplayFormat

Product: Excel VBA
Device & OS: not provided
Scenario: Writing a VBA custom function to count cells based on their displayed background color and text, then applying this function directly into a worksheet cell.
Observed behavior: The function works correctly inside the Function Arguments dialog box but returns a #VALUE! error on the spreadsheet because the DisplayFormat property is restricted in worksheet formulas.

Before you start

Ensure the Developer tab is enabled in your spreadsheet ribbon and that you have access to the Visual Basic Editor to modify your custom macro functions.

Solution 1Recommended

Use Evaluate to Bypass DisplayFormat Restrictions in Worksheet Formulas

Retrieve the displayed color of a cell successfully by wrapping the DisplayFormat property inside an Evaluate call, bypassing standard UDF restrictions.

Spreadsheet software restricts the use of the DisplayFormat property directly within User-Defined Functions (UDFs) called from worksheet cells. Attempting to use Range.DisplayFormat directly results in a #VALUE! error.

By utilizing the Evaluate method to process the color property, you can bypass this restriction and retrieve the necessary color index safely within your counting function.

Open the VBA Editor

Press Alt + F11 to open the Visual Basic for Applications (VBA) editor. Locate the module containing your current cell-counting UDF in the Project Explorer.

Modify the Color Retrieval Logic

Inside your code, replace the direct call to 'Range.DisplayFormat.Interior.Color' with an Evaluate-based helper. For example, instruct the code to evaluate the cell's color index indirectly rather than pulling it directly from the range object.

Add Application.Volatile

Insert 'Application.Volatile' at the very beginning of your main UDF function. This ensures the calculation engine flags the formula for recalculation during standard workbook update cycles.

Use Evaluate to Bypass DisplayFormat Restrictions in Worksheet Formulas

Manual Recalculation Required: Changing a cell's formatting (like its background color) does not automatically trigger the calculation engine. You must press F9 to force a recalculation and update the formula result.

Write and Execute VBA Macros Flawlessly in WPS Office

WPS Spreadsheets provides excellent built-in support for VBA macros, allowing you to run complex custom functions and user-defined scripts natively and efficiently.

1. Open WPS Spreadsheets: Launch WPS Office and open your macro-enabled workbook (.xlsm) containing the custom functions.
2. Access Developer Tools: Navigate to the Developer tab on the top ribbon. If it is not visible, enable it from the WPS Options menu under Customize Ribbon.
3. Edit Your VBA Code: Click on 'Visual Basic' or press Alt + F11 to open the VBA Editor, where you can easily modify your UDFs and apply the Evaluate solution.

Fully compatible with Microsoft Excel VBA syntax and macro-enabled formats (.xlsm, .xla)Built-in Developer tools for editing modules, UserForms, and advanced functionsEasily bypass common formatting evaluation limitations with a robust calculation engineLightweight application footprint that runs smoothly on standard hardware

microsoft office alternative - wps office

Frequently Asked Questions

Why does my VBA UDF work in the Function Arguments dialog but return #VALUE! in the cell?

The Function Arguments dialog evaluates code differently than the actual worksheet calculation engine. The calculation engine restricts properties like DisplayFormat in worksheet formulas to prevent cyclic dependencies, which leads to a #VALUE! error when executed directly on the grid.

Does Application.Volatile update my function when I change a cell color?

No. Changing a cell's physical formatting (such as filling it with a new background color) does not trigger a recalculation event in spreadsheets. Application.Volatile only forces the function to recalculate when cell values change or when you manually calculate the sheet by pressing F9.

How can I count cells by color without using complex VBA macros?

While native formulas cannot directly detect cell colors, you can use the built-in Filter feature from the Data tab to filter rows by cell color. Once filtered, you can use the SUBTOTAL function (e.g., =SUBTOTAL(103, A:A)) to count only the visible rows.

Excel & Data Problems