In our last post we learned how to calculate absolute value using Excel worksheet function. Today let’s learn how to calculate absolute value in VBA.
Here is our sample data set. As you can see we have few companies and change of their stock values.
Positive values are in green. Negative values are shown in red. If there is no change then values are in black. Assume we need to calculate the absolute value of change of the each company. We can use following vba function for that.
Abs (Number)
Number should be any numeric expression. So if we want to find the absolute value of change of “General Electric Company” then we can find it by
Abs(Range("B2").Value)
As we need to find absolute value of several cell values we can use For loop as follows.
|
Sub CalculateAbsValue() Dim i As Integer For i = 2 To 8 Range("C" & i).Value = Abs(Range("B" & i).Value) Next i End Sub |
After you run above macro you will get following result.
And this post explains how to use worksheet ABS function.
How to Use Excel ABS Function