VBA & Macro Problems

Fix the Expected Sub, Function, or Property Error in Excel VBA

Natalie TaylorNatalie Taylor Nov 20, 2026 869 views

Question details

The user needs to correct an Excel VBA function that replaces zero values and handles worksheet errors.

Fix the Expected Sub, Function, or Property Error in Excel VBA
Product
Microsoft Excel VBA
Device & OS
not provided
Scenario
A custom IFZERO function should return a replacement for zero and otherwise return the input, including an unchanged #N/A error.
Observed behavior
The code produces a compile error, and error-valued inputs need explicit handling to avoid an unwanted #VALUE! result.
Before you start

Open the workbook in a version of Excel that supports VBA, and locate the IFZERO code in the Visual Basic editor. Enable macros only if you trust the workbook.

Solution 1Recommended

Declare IFZERO in a standard VBA module

Use a complete function declaration and return worksheet errors unchanged before checking for zero.

VBA returns a function result by assigning a value to the function's name. Declare the arguments and result as Variant so the function can receive worksheet error values.

This example treats numeric text such as "0" as a number; nonnumeric values are returned unchanged.

1
Open a standard module

In Excel, open Developer > Visual Basic. In the VBA editor, select the workbook and choose Insert > Module.

2
Add the function

Paste this code into the module, outside any other Sub or Function: Function IFZERO(number As Variant, new_value As Variant) As Variant If IsError(number) Then IFZERO = number ElseIf IsNumeric(number) Then If CDbl(number) = 0 Then IFZERO = new_value Else IFZERO = number End If Else IFZERO = number End If End Function

3
Compile the project

In the VBA editor, choose Debug > Compile VBAProject. If an error remains, check that the function is not inside another procedure and that surrounding blocks have their matching End statements.

4
Save and test the formula

Use File > Save As to save the workbook as an Excel Macro-Enabled Workbook (.xlsm). In a worksheet cell, enter =IFZERO(A1,"tadaa") and test A1 with zero, a nonzero value, and #N/A.

Declare IFZERO in a standard VBA module
Returning a replacement for errors: To replace an error instead of preserving it, change IFZERO = number in the IsError branch to IFZERO = new_value.
Free Microsoft Office alternative

Work with Excel spreadsheets in WPS Office

WPS Office is a free, lightweight alternative with a familiar spreadsheet interface and support for common Excel formats, making it easy to move everyday spreadsheet work. VBA features and macro compatibility can vary, so test macro-enabled files before relying on them.

Use familiar spreadsheet tools to work with formulas and cell values.Open and save common Microsoft Excel formats, including .xlsx.Move everyday spreadsheet work into a free, lightweight office suite.
QA img-9

Frequently Asked Questions

Why does VBA report “Expected Sub, Function, or Property”?

VBA may encounter executable code where it expects a procedure declaration. In the Visual Basic editor, check that the code is inside a properly declared Sub or Function and that the procedure is not nested inside another one.

How does IFZERO return #N/A instead of #VALUE!?

Declare the input and function result As Variant, then use IsError(number) before comparing the input with zero. Assign the error directly with IFZERO = number.

Can I use a cell as the replacement value?

Yes. Enter a formula such as =IFZERO(B1,C1). If B1 is zero, the function returns the value from C1.

What happens when the input is text?

The example returns nonnumeric text unchanged. Numeric text is tested as a number, so text such as "0" returns the replacement value.

Why is my function unavailable after reopening the workbook?

Save the file through File > Save As as an Excel Macro-Enabled Workbook (.xlsm), and check Excel's macro security settings. Enable macros only for a workbook you trust.