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, while Abs(12.5) also returns 12.5; the function removes the negative sign without changing the number’s magnitude.
Syntax and what ABS returns
The VBA syntax is Abs(number), where number is a numeric expression, variable, or value that VBA can treat as numeric. The result is zero or a positive number. The function does not edit a cell or change the variable supplied to it; it returns a value that your code can assign, compare, display, or use in a calculation.
For example, if a variable named difference contains -8, Abs(difference) evaluates to 8, but difference remains -8 unless your code assigns the result back to it. This distinction matters when you need to preserve the direction of a change as well as measure its size.
VBA includes Abs as a built-in function, so you do not need to enable a reference or install an add-in. It is available in ordinary VBA code in Excel, including procedures, functions, and loops.
How to use Abs in a VBA macro
A simple procedure can calculate an absolute value and place it in another worksheet cell. The example assumes A1 contains a number and writes its absolute value to B1. It reads the source value as a Double, which is useful for values that may include decimal places.
Save a copy of the workbook before running a macro. VBA macros cannot be undone with Ctrl+Z, so a backup is especially important if your code writes to cells or changes other workbook contents.
Sub WriteAbsoluteValue()
Dim sourceValue As Double
' Read the number from A1.
sourceValue = CDbl(Cells(1, 1).Value)
' Write its absolute value to B1.
Cells(1, 2).Value = Abs(sourceValue)
End Sub
In Excel for Microsoft 365 on Windows, open the VBA editor with Alt+F11, or choose Developer > Visual Basic. On a Mac, use Tools > Macro > Visual Basic Editor; Option+F11 may also work depending on the keyboard and function-key settings. If the Developer tab is not visible, you can enable it in Excel’s ribbon settings. The workbook must be saved in a macro-enabled format, such as .xlsm, to retain VBA code.
Using Abs for differences and tolerances
A common reason to use Abs is to compare two values without caring which one is larger. Subtracting one value from another gives a signed difference: the result is negative when the first value is smaller. Applying Abs turns that result into the distance between the two values.
For example, suppose the measured value is 98.7 and the target is 100. The signed difference, measured minus target, is -1.3; its absolute value is 1.3. That tells you how far the measurement is from the target, but not whether it is above or below it. Keep the signed difference too if direction matters.
In a worksheet, with the measured value in A2 and the target in B2, enter =ABS(A2-B2) in C2. If A2 is 98.7 and B2 is 100, C2 returns 1.3. This is Excel’s worksheet function, not VBA code; it uses the same name and purpose. In VBA, the corresponding expression is Abs(Cells(2, 1).Value - Cells(2, 2).Value).
For a tolerance check, compare the absolute difference with a limit. If a difference of up to 0.5 is acceptable, the condition is true when Abs(measured - target) <= 0.5. This pattern is useful for measurements, reconciliations, and tests where small deviations in either direction count equally. Choose the tolerance to fit the data and its units; Abs does not decide what counts as acceptable.
Processing a column of worksheet values
When a column contains signed numbers and you want their magnitudes in another column, a loop can apply Abs one row at a time. This example assumes numeric values are in A2:A6 and writes results to B2:B6. It deliberately leaves the source values unchanged.
Save a copy before running this macro, since the changes made by VBA cannot be reversed with Ctrl+Z.
Sub FillAbsoluteValues()
Dim rowNum As Long
Dim sourceValue As Double
' Process rows 2 through 6.
For rowNum = 2 To 6
sourceValue = CDbl(Cells(rowNum, 1).Value)
Cells(rowNum, 2).Value = Abs(sourceValue)
Next rowNum
End Sub
If A2:A6 contains -4, 0, 2.5, -7, and 10, respectively, the corresponding results in B2:B6 are 4, 0, 2.5, 7, and 10. The example assumes every source cell contains a value that can be converted to a number. In a real workbook, decide how the macro should handle blanks, text, or error values rather than assuming the whole range is clean.
For a one-off worksheet calculation, a cell formula such as =ABS(A2) may be simpler than a macro. With -4 in A2, it returns 4. A formula updates when its referenced value changes; the macro above writes a value, so its result will not recalculate automatically after the source is edited.
Abs compared with related VBA functions
Abs and Sgn answer different questions. Abs returns a number’s magnitude. Sgn returns -1 for a negative number, 0 for zero, and 1 for a positive number, indicating its sign rather than its size. Use Sgn when you need to branch on direction, and Abs when you need the distance from zero.
Neither function rounds a value. For instance, applying Abs to -3.14159 gives 3.14159, not 3.14. If you need a rounded result, use an appropriate rounding operation separately and be clear about whether rounding should happen before or after other calculations. Taking an absolute value is also not the same as changing a negative number to positive conditionally: positive inputs stay positive, and zero stays zero.
For a difference between two numbers, subtraction establishes direction and Abs removes it. For a sign-sensitive rule, retain the original subtraction. A useful approach is to keep both values in named variables, such as signedDifference and absoluteDifference, so later code makes clear whether it is testing direction or size.
Data types, blanks, and common errors
VBA arithmetic is affected by the data type and range of the value being processed. A Double is a practical choice for many calculations involving decimal values. If you use a narrower integer type, a value may exceed that type’s range, and the calculation can raise an overflow error. Applying Abs does not make an otherwise unrepresentable result safe. For robust code, choose a type that can hold the input and its positive magnitude.
Cells often contain more than ordinary numbers. A blank cell, text such as a label, or an Excel error value can behave differently from a numeric cell, especially when code converts a cell value with CDbl. Conversion can fail for unsuitable content, and an error value cannot be treated as a normal number. If a range is not guaranteed to be numeric, inspect each cell before conversion and decide whether to skip it, report it, or handle it another way.
- Check whether the source cell contains an error before performing numeric calculations.
- Decide how blanks and text should be handled instead of silently treating all input as valid.
- Use a suitable variable type and account for values near its limits.
- Keep the original signed value if later steps need to know whether it was positive or negative.
VBA’s Option Explicit setting is also helpful: it requires variables to be declared and can catch misspelled names before they cause confusing results. It does not validate worksheet data, but it reduces a separate class of coding mistakes.
VBA Abs versus Excel’s worksheet ABS
Excel has an ABS worksheet function as well as VBA’s built-in Abs. They perform the same basic mathematical operation, but you use them in different places. Enter =ABS(A1) in a worksheet cell to calculate from A1 and keep the result linked to the source. Write Abs(variable) in a VBA procedure when your code needs the result as part of its logic or needs to place it somewhere.
In VBA, WorksheetFunction.Abs is another way to call the worksheet function through Excel’s object model. For ordinary absolute-value calculations in VBA, the built-in Abs is shorter and direct. Do not confuse either version with a command that changes a cell’s stored number: the function only returns a result. Your code or a worksheet formula determines where that result goes.
Excel for the web can calculate worksheet formulas such as =ABS(A1), but it cannot run VBA macros. To execute VBA, open the workbook in the desktop version of Excel. On Windows and Mac desktop versions, the details of opening the editor and enabling macro execution differ, but the basic VBA Abs syntax is the same.
Frequently Asked Questions
Does VBA Abs change the original variable?
No. Abs(value) returns a result and leaves the variable unchanged. Assign the result back to that variable only if you intentionally want to replace its signed value.
What does Abs return for zero?
It returns zero. Zero has no negative magnitude, so applying Abs to zero does not change it.
Can I use Abs directly on a worksheet cell in VBA?
Yes. You can pass a cell’s value to Abs, such as Abs(Cells(1, 1).Value), when the cell contains a suitable numeric value. Check for blanks, text, or errors if the input is not controlled.
Is VBA Abs the same as the Excel ABS formula?
They calculate absolute value, but one is used in VBA code and the other is entered as a worksheet formula. Use the worksheet formula for a live cell result and VBA when a macro needs the value during its execution.
Why does my Abs calculation cause an error?
Common causes include nonnumeric or error-valued input, a failed conversion, or a result outside the range of the chosen data type. Check the input and variable type, and test edge cases before using the calculation across a large range.
Related
- Excel Table Structured References: Rules, Examples & Tips
- Excel Take Data From a Table: Methods and Examples
- Excel Table Data Filtering: How to Narrow Rows
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA: Match a Trendline Label to Its Line ColorYes. In Excel VBA, set the trendline label’s font color to the trendline’s line color: use Trendline.DataLabel.Font.Color and…
- Excel VBA Range Using Cells: Syntax, Examples, and TipsIn Excel VBA, use Cells(row, column) to identify a cell by its row and column numbers, and pass…
- Excel VBA Format Date Values: Examples and FixesTo format a date in an Excel cell with VBA, set the cell’s NumberFormat property, for example: Range("A1").NumberFormat…
- Excel VBA Boolean: True, False, and How to Use ThemYes—Excel VBA has a built-in Boolean data type for values that are either True or False. Declare a…
- Excel VBA: How to Close a UserForm After a Button ClickTo close an Excel VBA UserForm when a button is clicked, put Me.Hide in that button’s Click event…
- Excel VBA InputBox Range Selection: How to Use Type 8To let someone select cells in Excel VBA, use Application.InputBox with Type:=8; it returns the selection as a…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.