How to Calculate Absolute Values in VBA (With Example)


The absolute value of a number represents the distance between the number and zero.

To calculate absolute values in VBA, you can use the Abs function.

Here is one common way to use this function in practice:

Sub FindAbsoluteValue()

    Dim i As Integer
    
    For i = 2 To 10
        Range("B" & i) = Abs(Range("A" & i))
    Next i
      
End Sub

This particular example calculates the absolute value of each cell in the range A2:A10 and displays them in the range B2:B10.

The following example shows how to calculate absolute values in VBA in practice.

Example: How to Calculate Absolute Values in VBA

Suppose we have the following list of values in Excel:

Suppose we would like to calculate the absolute value of each cell in column A and display them in column B.

We can create the following macro to do so:

Sub FindAbsoluteValue()

    Dim i As Integer
    
    For i = 2 To 10
        Range("B" & i) = Abs(Range("A" & i))
    Next i
      
End Sub

When we run this macro, we receive the following output:

calculate absolute value in VBA

Column B now displays the absolute value of each value in column A.

Notice that each of the positive values in column A remained positive in column B.

Also notice that each of the negative values in column A were converted to positive in column B.

Note: You can find the complete documentation for the VBA Abs function here.

Additional Resources

The following tutorials explain how to perform other common tasks in VBA:

VBA: How to Calculate Average Value of Range
VBA: How to Sum Values in Range
VBA: How to Count Number of Rows in Range

Leave a Reply

Your email address will not be published. Required fields are marked *