Why Excel VBA Converts True to -1 Instead of 1
Question details
The user wants to understand why VBA represents True numerically as -1 rather than 1 and how to return 1 when needed.
- Product
- Microsoft Excel VBA
- Device & OS
- not provided
- Scenario
- Comparing Boolean-to-number conversions in Excel VBA with conversions in Excel worksheet formulas.
- Observed behavior
- VBA converts False to 0 and True to -1, whereas Excel worksheet formulas normally convert FALSE to 0 and TRUE to 1.
Identify whether the value is being converted inside VBA code or in an Excel worksheet formula; these environments use different numeric conventions. For the VBA examples, open Excel's Visual Basic Editor through Developer > Visual Basic.
Verify VBA's standard Boolean-to-number conversion
Use this explanation and a quick test to confirm that True becoming -1 is expected behavior, not a defect.
VBA converts False to 0 and True to -1. Its True representation has all bits set to one, which represents -1 in signed integer formats. Changing this convention is neither necessary nor a repair step.
A Boolean result and its numeric conversion are different things: a logical expression returns True or False, and converting that result to a number produces -1 or 0.
In Excel, select Developer > Visual Basic. In the Visual Basic Editor, select View > Immediate Window.
Enter ? CLng(True) in the Immediate window and press Enter; the result is -1. Enter ? CLng(False) and press Enter; the result is 0.
Return to an Excel worksheet and enter =--TRUE() in an empty cell, then =--FALSE() in another cell. The results are 1 and 0 because worksheet Boolean-to-number conversion uses a different convention.
Explicitly convert a VBA Boolean result to 1 or 0
Use explicit conversion when a calculation, exported value, or numeric function result must use 1 for true and 0 for false.
Use WPS Spreadsheets for Worksheet-Based Logical Calculations
WPS Office is a free, lightweight alternative with a familiar spreadsheet interface and Excel-format compatibility for easy migration. Use WPS Spreadsheets for worksheet formulas that return explicit 1 or 0 values. It does not change Microsoft VBA's Boolean convention; VBA support varies by edition and platform, so verify macro requirements before switching.
Frequently Asked Questions
Is True becoming -1 a VBA bug?
No. It is VBA's standard Boolean-to-number conversion. To confirm it, open Developer > Visual Basic, select View > Immediate Window, and enter ? CLng(True). The result is -1.
Does CBool(1) return True even though True converts to -1?
Yes. CBool converts 0 to False and nonzero numeric values to True. The conversion back to a numeric type uses VBA's standard values: CLng(CBool(1)) returns -1, not 1.
How can I make a VBA function return 1 instead of -1?
Declare the function's return type as a numeric type, such as Long, and assign Abs(CLng(condition)) to the function name, where condition is Boolean. Alternatively, use an If...Then...Else statement to assign 1 or 0. A Boolean return type does not preserve a numeric 1 as a distinct Boolean value.
Why does VBA's 1 And 2 return 0 while Excel's AND(1,2) returns TRUE?
VBA's And operator performs a bitwise operation on numeric operands. The binary values 1 and 2 share no set bits, so their bitwise And is 0. Excel's worksheet AND function treats both nonzero arguments as logically true and returns TRUE.
Can I get 1 and 0 without using a macro?
Yes. In Excel or WPS Spreadsheets, enter a worksheet formula such as =IF(A1>0,1,0). It returns 1 when A1 is greater than zero and 0 otherwise, without relying on VBA conversion behavior.
