Excel VBA Absolute Value: How to Use Abs

VBA & macros · Updated November 21, 2026

In Excel VBA, use the built-in Abs function to return a number’s absolute value: Abs(number). For example, Abs(-12.5) returns 12.5; the function removes the negative sign but does not change the number’s magnitude.

What absolute value means

Absolute value is the distance of a number from zero, regardless of direction. A positive number stays positive, a negative number becomes positive, and zero remains zero. In VBA, the function name is Abs, with the number or numeric expression inside parentheses.

For example, Abs(-8) returns 8, while Abs(8) also returns 8. This is useful when the sign is not relevant to the calculation, such as finding the size of a variance, measuring a distance, or comparing two readings without regard to which one is larger.

Absolute value is not the same as changing every negative value in a worksheet permanently. Abs returns a result; it does not edit its input by itself. Whether the original cell is changed depends on the code you write around the function.

Using Abs in a VBA procedure

VBA includes Abs as a built-in function, so you do not need to add a reference or enable a special library. Pass it a numeric expression, store the result in a variable, or use the result in a larger calculation.

Here is a small example for a standard VBA module. The function can be called by other VBA code, and the test procedure prints its result in the Visual Basic Editor’s Immediate window.

Public Function AbsoluteValue(ByVal number As Double) As Double
    ' Return the magnitude without changing the input.
    AbsoluteValue = Abs(number)
End Function

Public Sub CheckAbsExample()
    Dim number As Double
    Dim result As Double

    number = -12.5
    result = Abs(number)
    Debug.Print result
End Sub

The sample procedure prints 12.5. To open the editor on Windows, press Alt+F11. On Mac, use Tools > Macro > Visual Basic Editor; the keyboard shortcut can vary with the keyboard and macOS settings. Put the code in a standard module if you want to call the function from worksheet cells. If you adapt a procedure to change workbook data, save a copy first: VBA macros cannot be undone with Ctrl+Z.

VBA Abs versus the worksheet ABS function

Excel has a worksheet function named ABS as well as the VBA function named Abs. They express the same basic operation, but you use them in different places. Use Abs in VBA statements. Use ABS in a worksheet formula when you want a cell to calculate the result directly.

With the value -18.6 in cell A2, enter =ABS(A2) in another cell; it returns 18.6. The worksheet formula recalculates when A2 changes. By contrast, a VBA procedure has to run to perform its instructions, unless you call a VBA user-defined function from a worksheet formula.

There is usually no need to call the worksheet function through an Excel object in VBA. The built-in VBA expression Abs(value) is simpler for ordinary VBA calculations. A worksheet UDF such as =AbsoluteValue(A2) can be useful if you have added the sample function to a standard module, but it requires the workbook’s VBA project to remain available. Excel for the web can calculate the built-in worksheet ABS formula, but it cannot run or edit VBA macros.

Choosing a numeric type and avoiding overflow

The value passed to Abs needs to be numeric. For everyday measurements and decimal calculations, a variable declared as Double is a common choice. VBA’s numeric types have different ranges, however, and absolute value can expose a boundary problem: the most negative number of a signed type has no matching positive value in that same type.

For example, an Integer can hold values from -32,768 through 32,767. The positive counterpart of -32,768 is 32,768, which does not fit in an Integer; applying Abs at that limit can cause an overflow error. The same kind of issue applies to the minimum value of a Long. If your inputs can reach a type’s lower limit, convert to a wider type before taking the absolute value, or choose an appropriate wider type for the calculation from the start.

A Double handles a much larger range than Integer or Long, but it is a floating-point type, so it cannot represent every possible decimal value exactly. For values where exact decimal arithmetic matters, consider whether a suitable VBA type such as Currency is more appropriate, and check that its range and precision fit the problem. Do not assume that converting a number fixes every possible range or precision issue.

Passing text that cannot be interpreted as a number can also cause a type mismatch. If a macro reads worksheet data, check for errors and unexpected text before converting cell values or passing them to Abs.

Using absolute values with worksheet data

For a one-off result or a calculation that should update automatically, a worksheet formula is often the clearest option. If A2 contains -18.6, enter =ABS(A2) in B2; B2 returns 18.6. Fill the formula down to apply the same calculation to other rows. This leaves the original values intact and makes it easy to audit the result.

Absolute value is also helpful for the distance between two numbers. With 14 in A2 and 19 in B2, enter =ABS(A2-B2) in C2; the result is 5. Reversing the subtraction still returns 5 because ABS removes the sign. This is useful when you care about how far apart two values are, not which is greater.

Be precise about what that last calculation means. ABS(A2-B2) reports an unsigned difference; it does not tell you whether the first value is above or below the second. If direction matters—for example, whether actual sales exceeded a target—keep the signed calculation A2-B2 as well, or use a separate cell for it. Turning a signed variance into an absolute value can hide whether the result is favorable or unfavorable.

When to use a macro instead

Choose VBA when the absolute-value operation is one step in a larger automated task, such as processing many cells, validating imported information, or applying a repeatable business rule. Choose a worksheet formula when people need to inspect, fill, or recalculate results as the source values change. For a single calculation, VBA is usually unnecessary.

A VBA user-defined function can make the operation available in a cell, but the built-in worksheet ABS is normally more portable and straightforward. The custom function may be helpful if it represents a larger rule used across a workbook; merely renaming Abs does not add new functionality. Keep the original values in separate cells when you want an auditable record of what was entered.

On Windows and Mac desktop Excel, VBA can be used with macro-enabled workbooks. Save a workbook containing VBA as an Excel Macro-Enabled Workbook (.xlsm) if you need to preserve its code. Excel for the web does not run VBA, so a workbook intended for browser use should rely on worksheet formulas for this calculation.

Common mistakes and troubleshooting

  • Using the worksheet name in VBA: In ordinary VBA code, write Abs(value), not a worksheet formula beginning with =.
  • Expecting Abs to modify a variable: Assign the returned value if you want to keep it, such as result = Abs(number). The original variable is not changed by that call.
  • Removing a useful sign: Applying Abs to a profit variance, temperature change, or balance difference may erase information about direction. Confirm that only size matters.
  • Ignoring data types: A numeric value at the minimum boundary of an integer type can overflow. Use a suitable type and test boundary inputs.
  • Passing cell errors or text to a macro: Imported data may contain error values, blanks, or labels. Validate values before converting them to a numeric type.
  • Expecting Ctrl+Z to reverse a macro: Excel’s undo history does not generally reverse VBA actions. Save a copy before running code that changes data.

If a worksheet formula displays an error, check that the referenced cell contains a usable number and that the formula is spelled =ABS(reference). If a VBA line raises a type mismatch or overflow, inspect the input’s value and declared type, then test the lower and upper limits your data can reach.

Frequently Asked Questions

What is the absolute value function in Excel VBA?
It is the built-in Abs function. Use it as Abs(number); for example, Abs(-7) returns 7.

Is VBA Abs the same as Excel ABS?
They perform the same basic absolute-value operation. Use Abs in VBA code and ABS in a worksheet formula, such as =ABS(A2).

Can I use Abs directly in an Excel cell?
Use the worksheet function in a cell: =ABS(A2). The VBA function is used in VBA code, unless you define a VBA user-defined function and call that function from a cell.

Does Abs change the original number?
No. It returns an absolute-value result. Your code must assign that result or write it to a cell if you want to store it; a worksheet formula leaves the source cell unchanged.

Why does Abs sometimes cause an overflow error?
A signed numeric type may not be able to hold the positive counterpart of its minimum negative value. Use a wider type for the calculation and check the limits of the data being processed.

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.