Fix the Expected Sub, Function, or Property Error in Excel VBA
Question details
The user needs to correct an Excel VBA function that replaces zero values and handles worksheet errors.

- 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.
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.
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.
In Excel, open Developer > Visual Basic. In the VBA editor, select the workbook and choose Insert > Module.
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
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.
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.

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.

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.




