The Abs function is a math function in VBA (Visual Basic for Applications) that returns an absolute value for a number. If you insert a negative number into the function, the Abs function will return a positive number. So this function omits the negative sign from a number and returns a positive number. In this article, we will guide you to use the Abs function in Excel VBA with 9 relevant examples.
Download Practice Workbook
You can download the Excel file from the following link and practice along with it.
9 Examples of Using Abs Function in Excel VBA
Example 1: Get the Absolute Value in a Worksheet Cell Using the Abs Function in Excel VBA
In this example, I will show you to get the absolute value for a negative number within an Excel worksheet cell. I have a negative value in cell B5 and using the Abs function in Excel VBA, I will get the absolute value in cell C5.
So let’s get started.
❶ First of all, press the ALT + F11 keys to open the VBA editor.
❷ After that, go to the Insert menu from the main ribbon. Then select Module.
❸ Now copy the following code and paste it to the VBA editor in Excel.
Sub GetAbsolute()
Range("C5").Value = Abs(Range("B5"))
End Sub
❹ Save the VBA code using CTRL + S.
❺ Now press F5 to run the VBA code or press ALT + F8 to open the Macro window.
❻ Select the GetAbsolute function and hit the Run command.
As a result, you will get the absolute value in cell C5 for the number -5 in cell B5.
Read More: How to Use VBA Val Function in Excel (7 Examples)
Example 2: Input a Number Directly into the Abs Function in Excel VBA
We can input a number directly into the Abs function to get the absolute value of it. To do so, use the following steps.
❶ Open the VBA editor by pressing ALT + F11 keys.
❷ Open a new Module from the Insert menu.
❸ Copy the following code of the Abs function and paste it to the VBA editor.
Sub InsertInsideFunction()
MsgBox Abs(-17)
End Sub
❹ To run the code hit the Run Sub button or press the F5 key.
Finally, you will get the result in a Message Box like this:
Read More: How to Use VBA IsNumeric Function (9 Examples)
Example 3: Get the Absolute Values for a Range of Cells Using the Abs Function Excel VBA
In this section, we will calculate the absolute value for a list of numbers using the Abs function in Excel VBA.
We have a list of numbers in cells B5 to B9. We can get the absolute value for all of them using the Abs function.
To do that,
❶ Press ALT+F11 to open the VBA editor.
❷ Create a new Module from the Insert menu.
❸ Use the following code of the Abs function in the VBA editor.
Sub AbsRange()
Dim x As Range
For Each x In Range("B5:B9")
x.Offset(0, 1) = Abs(x.Value)
Next x
End Sub
❹ Paste and Save the code in the Excel VBA editor.
❺ Press the F5 key.
This will run the code and you will get all the numbers without the negative sign.
Read More: How to Return a Value in VBA Function (Both Array and Non-Array Values)
Example 4: Get the Absolute Value for a Negative Number in a Message Box Using the Abs Function in Excel VBA
We can insert a negative number in the VBA code and get the absolute value of it with just a keystroke.
To do that follow the steps below.
❶ First of all, press ALT+F11 to open the VBA editor.
❷ Now create a new Module from the Insert menu.
❸ Then use the following code of the Abs function in the VBA editor.
Sub AbsMsgBox()
Dim x As Integer
Dim y As Variant
x = -9
y = Abs(x)
MsgBox "Absolute Value of -9 is : " & y
End Sub
❹ Paste and Save the code in the Excel VBA editor.
❺ Finally hit the F5 key to run the code.
Then you will get the absolute value of -9 is 9 appears in a message box like this:
Read More: How to Use MsgBox Function in Excel VBA (A Complete Guideline)
Example 5: Calculate the Absolute Value for a Positive Number in a Message Box Using the Abs Function in Excel VBA
To calculate the absolute value of a positive number, 144 for instance, follow the steps below:
❶ You can hit ALT+F11 to open the VBA editor.
❷ Go to Insert > Module.
❸ Use the following code of the Abs function in the VBA editor.
Sub AbsMsgBox()
Dim x As Integer
Dim y As Variant
x = 144
y = Abs(x)
MsgBox "Absolute Value of 144 is : " & y
End Sub
❹ Paste and Save the code in the Excel VBA editor.
❺ To run the code, press the F5 key.
Then you will get the absolute value in the following message box.
Read More: Excel Formula to Generate Random Number (5 examples)
Similar Readings:
- How to Unhide Top Rows in Excel (7 Methods)
- Excel Date Picker for Entire Column
- How to Use VBA Len Function in Excel (4 Examples)
- Use IsNull Function in Excel VBA (5 Examples)
- How to Use VBA RTrim Function (5 Suitable Examples)
Example 6: Return an Absolute Value for Zero (0) in a Message Box Using the Abs Function in Excel VBA
To compute the absolute value of zero (0) using the Abs function in Excel VBA, follow the steps below:
❶ To open the VBA editor press ALT+F11.
❷ Now create a new Module from the Insert menu.
❸ Copy the following code of the Abs function in the VBA editor.
Sub AbsMsgBox()
Dim x As Integer
Dim y As Variant
x = 0
y = Abs(x)
MsgBox "Absolute Value of 0 is : " & y
End Sub
❹ Paste and Save the code in the Excel VBA editor.
❺ Finally hit the F5 key to run the above VBA code of the Abs function.
Then you will get the result in the following message box.
Related Content: How to Use VBA Space Function in Excel (3 Examples)
Example 7: Get the Absolute Value of Text String in a Message Box Using the Abs Function in Excel VBA
Let’s see how the Abs function handles a text of string in Excel VBA. To do that, follow the steps below:
❶ Press ALT+F11 to open the VBA editor.
❷ Go to Insert > Module to create a new module for the VBA code.
❸ Copy the following code of the Abs function in the VBA editor.
Sub AbsMsgBox()
Dim x As Integer
Dim y As Variant
x = "This is a Text"
y = Abs(x)
MsgBox "Absolute Value of 'This is a Text' is : " & y
End Sub
❹ Paste and Save the code in the Excel VBA editor.
❺ Press the F5 button to execute the VBA code.
You will see a text message returning “Run-time error ‘13: Type mismatch”. This means the Abs function can’t return an absolute value for a text of string.
Read More: How to Use VBA Str Function in Excel (4 Examples)
Example 8: Return the Absolute Value for a Null Value in a Message Box Using the Abs Function in Excel VBA
To return the absolute value for a null number, follow the steps below:
❶ Hit the ALT+F11 keys to open the VBA editor.
❷ Now create a new Module from the Insert menu.
❸ Copy the following code of the Abs function in the VBA editor.
Sub AbsMsgBox()
Dim x
Dim y As Variant
x = Null
y = Abs(x)
MsgBox "Absolute Value of 'Null' is : " & y
End Sub
❹ Paste and Save the code in the Excel VBA editor.
❺ Now you have to press the F5 key to execute the above VBA code.
After running the code, you will see that the absolute value for a null value is also null.
Related Content: How to Use Concatenate in Excel VBA (4 Methods)
Example 9: Find the Nearest Value to a Number Using the Abs Function in Excel
In this example, I will show you how to find the nearest value to the number 15 using the Abs function in Excel.
We have an array of numbers: 11.3, 43.21, 7.5, 8.44, 7.9, 44.55, 14
So let’s see what number from the above array is the nearest to 15.
To do that,
❶ Press ALT+F11 to open the VBA editor.
❷ Go to Insert > Module to create a new module for the VBA code.
❸ Copy the following code of the Abs function in the VBA editor.
Sub FindNearest()
Dim x
Dim y
Dim z As Double
Dim m As Double
Dim n As Double
n = 55
x = Array(11.3, 43.21, 7.5, 8.44, 7.9, 44.55, 14)
For Each y In x
m = Abs(y - 15)
If m < n Then
n = m
z = y
End If
Next y
MsgBox "The value near to 15 is " & z
End Sub
❹ Paste and Save the code in the Excel VBA editor.
❺ Press the F5 button to execute the VBA code.
After executing the above function, you will see that the nearest number to 15 from the above array is 14.
Read More: How to Use VBA TimeValue Function (6 Relevant Examples)
Things to Remember
- Press ALT + F11 to open the VBA editor.
- Use ALT + F8 keys to open the Macro.
- F5 is the hotkey to run VBA codes.
- To get the absolute value of a number, you have to insert the number or a cell address into the VBA code.
Conclusion
To sum up, we have discussed 9 relevant examples regarding the usage of the Abs function in Excel. You are recommended to download the practice workbook attached along with this article and practice all the methods with that. And don’t hesitate to ask any questions in the comment section below. We will try to respond to all the relevant queries asap. And please visit our website Exceldemy to explore more.