Excel VBA Function Return Boolean Values: How It Works

VBA & macros · Updated November 21, 2026

Yes. An Excel VBA function can return a Boolean value: declare the function with As Boolean, then assign True or False to the function’s name. For example, a function can return whether one number is greater than another, and that result can be used in VBA code or, with the usual user-defined-function limits, in an Excel worksheet formula.

What a Boolean return value means

A Boolean is a data type with only two logical values: True and False. It is useful when a function answers a yes-or-no question, such as whether a value meets a threshold, a cell contains a particular condition, or a record qualifies for a rule. Using a Boolean makes the function’s purpose and expected result clear to anyone reading the code.

In VBA, the function declaration specifies its return type after the closing parenthesis. The assignment that supplies the return value uses the function’s own name. For instance, in Function IsReady() As Boolean, assigning IsReady = True sets the value that the function returns. This is different from a Sub procedure: a Sub performs actions but does not return a value.

Internally, VBA represents Boolean True as -1 and False as 0. Usually you should work with the named constants True and False rather than relying on those underlying numbers. In particular, do not assume that a true Boolean is numerically equal to 1 in every VBA expression.

How to write a Boolean function

Here is a small function that returns whether an amount exceeds a threshold. Its arguments are declared with types, and its return type is explicitly Boolean:

Public Function IsAboveTarget(ByVal amount As Double, ByVal threshold As Double) As Boolean
    ' Return whether the amount is greater than the threshold.
    IsAboveTarget = (amount > threshold)
End Function

The comparison inside the parentheses already evaluates to either True or False, so it can be assigned directly to the function name. An equivalent version could use an If...Then...Else block and assign one of the two Boolean constants. The direct form is compact; the branching form can be easier to follow when the conditions are more involved.

To add the function in Excel for Microsoft 365 on Windows, open the VBA editor with Alt+F11, choose Insert > Module, and put the code in the standard module. On Mac, open the Visual Basic Editor from Excel’s Developer tools; the keyboard shortcut can depend on the keyboard and function-key settings. A standard module is the usual place for a worksheet-callable function, rather than a worksheet’s code module.

Using the function in a worksheet

A public VBA function in a standard module can be used in a worksheet formula much like a built-in function. The workbook containing the function must be open, and the workbook must retain its VBA project. Save a workbook that contains VBA as an Excel Macro-Enabled Workbook (.xlsm) if you need to preserve that code.

With the amount in A2 and the threshold in B2, enter =IsAboveTarget(A2,B2) in C2. If A2 contains 85 and B2 contains 80, the result is TRUE; if A2 contains 75 and B2 contains 80, it is FALSE. Excel displays the logical result as TRUE or FALSE, and another formula can use that result as a logical test.

Include the relevant inputs as arguments rather than referring to changing worksheet values invisibly inside the VBA function. When cells are passed as arguments, Excel can track those references as dependencies and recalculate the formula when they change. If the formula shows an error or the function name is not recognized, check that the code is in the workbook that contains the formula and that macros are permitted under your organization’s security settings.

Returning a condition instead of displaying a message

A Boolean function should answer a question, not perform a user-interface action. For example, return whether a row passes a test, then let the calling code decide what to do with the answer. In VBA, the result can be stored in a Boolean variable and used in an If statement. In a worksheet, the result can be displayed, combined with other logical tests, or used as a condition in a supported formula.

For a more complex rule, combine comparisons with logical operators such as And, Or, and Not. A function that returns whether a customer is both active and within a spending limit is still a Boolean function, even if it checks several inputs. Keep the rule readable: split a long expression into named intermediate variables when that makes the conditions easier to inspect, and declare each such variable as Boolean.

A useful design distinction is whether the function reports a condition or changes something. A worksheet user-defined function (UDF) is intended to calculate and return a result. It should not try to edit other cells, change formatting, or display a message box while Excel is calculating a formula. Put workbook-changing actions in a Sub or another suitable macro instead.

Boolean versus other return types

Choose As Boolean when the meaningful answer really is yes or no. Use another type if the caller needs more information. For example, a function that must distinguish “not found” from “found but invalid” may be better designed to return a status code, text explanation, or a value together with a separate success indicator. A single Boolean cannot communicate several different failure reasons.

Do not return text such as "Yes" or "No" just to represent a logical result if callers need to make decisions. Text is not the same type as a Boolean and can behave differently in comparisons and formulas. Conversely, if the output is meant for a report with custom wording, a text-returning function may be more suitable. Match the declared type to what the caller is expected to do with the result.

VBA’s Variant type can hold different kinds of values, including Boolean values, but it is usually clearer to declare a function’s return type when the result is known. An explicit declaration helps catch incompatible assignments and communicates the contract of the function. It also prevents a reader from having to infer the result type from the code.

Common Boolean function problems

  • Forgetting the return assignment: Declaring a function As Boolean does not automatically calculate its answer. The code must assign a result to the function name, such as IsAboveTarget = (amount > threshold). If a Boolean function exits without assigning a value, its default result is False, which can conceal a logic error.
  • Using a Sub when a value is needed: A Sub cannot be called as a worksheet formula and does not provide a return value. Use a Function when the caller needs a result.
  • Placing the code in the wrong module: For a worksheet UDF, use a public function in a standard module. A function marked Private is not available as a normal worksheet function.
  • Confusing the Boolean value with its display: The worksheet may show TRUE or FALSE, but these are logical values, not the text strings “TRUE” and “FALSE.” Code or formulas that expect text may therefore behave differently.
  • Ignoring invalid input: A comparison involving empty cells, text, errors, or unexpected data can produce a result different from the intended business rule, or cause a VBA type mismatch. Decide how the function should handle missing or invalid inputs, and validate them explicitly when necessary.

For example, a numeric argument declared as Double communicates that the function expects a number. If callers may pass arbitrary worksheet contents, consider accepting a Variant and checking its type and error state before comparing it. That extra flexibility also requires deliberate handling; it is not a substitute for deciding what invalid input should mean.

Calling the function from VBA

The same Boolean function can be called from another VBA procedure. The caller can store the result in a Boolean variable, or use the function call directly as the condition in an If statement. Assigning it to a variable is often helpful when the condition is used more than once or when you want to inspect the result while debugging.

When a function has several branches, make sure every path produces the intended result. You can assign the function name in each branch, or use a single expression where appropriate. Exit Function ends the function early, but it does not magically supply the answer: assign the return value before exiting. Test boundary cases as well as ordinary inputs. For a “greater than” test, values equal to the threshold should return False; if equality should count as passing, use a greater-than-or-equal comparison instead.

VBA’s editor lets you step through a procedure with the debugger and inspect variables, which is useful when a function returns a surprising Boolean. Keep a copy of the workbook before running a macro: macro actions generally cannot be undone with Ctrl+Z. A worksheet UDF that only calculates a result normally should not modify the workbook, but the reminder matters whenever you run other VBA procedures.

Excel compatibility and macro security

VBA runs in the desktop Excel application, not in Excel for the web. Excel for the web can open and edit many workbook contents, but it cannot create, run, or edit VBA macros there. To use a VBA function, open the workbook in desktop Excel. Macros may also be blocked by file-origin warnings, Trust Center policy, or an organization’s administrator, so follow the applicable security guidance rather than enabling all macros indiscriminately.

On Windows, the Developer tab includes Visual Basic tools; if it is hidden, it can be enabled through File > Options > Customize Ribbon. On Mac, ribbon customization is available through Excel’s settings, though labels and editor access can vary by release. The VBA function syntax itself is the same across desktop Windows and Mac, but keyboard shortcuts and some surrounding interface details differ.

If you distribute the workbook, remember that a worksheet formula referring to a custom VBA function depends on the VBA code being present and available. A recipient opening a version without that code may see a name error or an unavailable function result. If the workbook needs to work in Excel for the web or in environments where macros are disabled, consider implementing the calculation with built-in worksheet functions instead.

Frequently Asked Questions

Can a VBA function return True or False?
Yes. Declare it with As Boolean and assign True, False, or a logical expression to the function’s name.

How do I return a Boolean from a VBA function?
Use a declaration such as Function CheckRule() As Boolean, then assign the result with CheckRule = condition. The condition must evaluate to True or False.

Can I use a Boolean VBA function in an Excel cell?
Yes, if it is a public function in a standard module in a workbook whose VBA code is available. Enter its name as a worksheet formula and pass its inputs as arguments.

Why does my Boolean function always return False?
Check that every code path assigns the function name before returning. A Boolean function that reaches its end without an assignment has the default value False; also verify comparison operators and input types.

Can Excel for the web run a VBA Boolean function?
No. Open the workbook in desktop Excel to run VBA. If web compatibility is required, use a calculation built from Excel worksheet functions rather than a VBA UDF.

Related

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.