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.
Purpose
To return the non-negative or absolute value of a number.
Syntax
Abs(Number)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.
- 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- So, you will get the following output with the absolute values.
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- Your VBA MsgBox will show the absolute value of -99.
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- The VBA MsgBox will appear with 6.2 as it is the closest match among the array numbers.
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!