Astra

Excel VBA Abs Function (Get Absolute Value of a Range)

Estimated reading: 4 minutes

When you want to work with only non-negative numbers or remove the negative sign from numbers, Excel VBA offers you the Abs function. You can get the absolute value for a cell but not for a range with the Abs function. So, in this article, we will show you how to get the absolute value of a range by using the Excel VBA Abs function.

Excel VBA Abs Function

Purpose

To return the non-negative or absolute value of a number.

Syntax

Abs(Number)

Arguments

ArgumentsExplanation
NumberThe number that we want to convert in absolute form

Example#1: Get Absolute Values of Numbers in a Range

Here, below, is the following data containing numbers, we will apply the VBA Abs function to get the absolute values of the numbers.

Get Absolute Values of Numbers in a Range
  • Copy the following VBA code and paste it into a Module. To run the code press F5.
Sub VBA_Abs_Function()
'Declare the variables
Dim xCell As Range
'Apply For Loop in a Range with Abs function
    For Each xCell In Range("B4:B9")
        xCell.Offset(0, 1) = Abs(xCell.Value)
    Next xCell
End Sub
VB
VBA Code with Abs Function for a Range with For Loop
  • So, you will get the following output with the absolute values.
Get Absolute Values with VBA Abs Function

Example#2: Show the Absolute Value of a Number in a Message Box

Apart from the data set, you can show the result in a VBA MsgBox by applying the following Code.

Sub Abs_Function_MsgBox()
MsgBox Abs(-99)
End Sub
VB
Abs Function in a MsgBox
  • Your VBA MsgBox will show the absolute value of -99.
VBA MsgBox

Example#3: Find the Closest Value of a Number

You can find the closest value of a number in an array. We have some array values with 1.4, 2.9, 2.97, 6.2, and 7.3. From the number, we want to find the closest value of 5. Apply the following VBA code to get the closest match.

Sub Find_Closest_Number()
'Declare the variables
Dim xNumber
Dim item_List
Dim closestVal As Double
Dim xDif As Double
Dim min_Dif As Double
'Set a starting value for minimum difference
min_Dif = 10
'Insert the numbers list in Array
xNumber = Array(1.4, 2.9, 2.97, 6.2, 7.3)
'Apply For Loop for each number
    For Each item_List In xNumber
    'Set Condition to find the absolute difference from 5
        xDif = Abs(item_List - 5)
      'Apply If condition to find the minimum difference
        If xDif < min_Dif Then
            min_Dif = xDif
            closestVal = item_List
        End If
    Next item_List
'Show the result in a message box
    MsgBox "The closest value: " & closestVal
End Sub
VB
VBA Code to Find the Closest Match
  • The VBA MsgBox will appear with 6.2 as it is the closest match among the array numbers.
VBA MsgBox

Things to Keep in Mind

  • The VBA Abs function cannot perform for a range value. It only can work will a single-cell reference. So, you have to apply the Abs function in between loops.

Frequently Asked Questions

Q: What is the data type of the argument for the VBA Abs function?

A: Any numeric data type, including Integer, Long, Single, Double, Currency, or Decimal, may be used as the argument.

Q: What kind of data does the VBA Abs function return?

A: The data type of the argument matches the return type exactly.

Q: Can the VBA Abs function be used with non-numeric values?

A: No, you can only use numeric values with the VBA Abs function. You will encounter a runtime error if you attempt to use it with a non-numeric value.

Conclusion

From the above article, you have learned how to get the absolute value of a range with Excel VBA. If you have any additional questions, kindly leave a comment for us. In response, you will provide solutions from the Solved Excel research wing. Stay safe and get connected with us! 

4 thoughts on “Excel VBA Abs Function (Get Absolute Value of a Range)”

  1. Thanks for our personal marvelous posting! I actually
    enjoyed reading it, you might be a great author.
    I will remember to bookmark ylur blog and will often come back
    down the road. I wsnt to encourage you to ultimately continue your reat writing, have a nice day!

    Feel free to visit my page – vavada.webgarden.Com

Leave a Reply

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

Share this Doc
Jump to Topics
SOLVED EXCEL
Scroll to Top