In my last post I introduced you a useful VBA function call Fix. Today I’m going to show you how you can use that function to check whether a value has decimal places or not. In other words how to check whether a value is an integer/long or not.
I will explain this using an example. Let’s consider this array.
Dim SampleValues(4) As Double
SampleValues(0) = 5
SampleValues(1) = 5.95
SampleValues(2) = 20
SampleValues(3) = -13.7
SampleValues(4) = -2
So now let’s try to find out whether each value in this array has decimal place or not. We can do it like this.
|
Sub CheckForDecimalPlaces() Dim SampleValues(4) As Double Dim i As Long SampleValues(0) = 5 SampleValues(1) = 5.95 SampleValues(2) = 20 SampleValues(3) = -13.7 SampleValues(4) = -2 For i = LBound(SampleValues) To UBound(SampleValues) If Fix(SampleValues(i)) = SampleValues(i) Then Debug.Print SampleValues(i) & " - No decimal places" Else Debug.Print SampleValues(i) & " - has decimal places" End If Next i End Sub |
In above subroutine Fix function is used with an if statement. Let’s consider the following line.
| If Fix(SampleValues(i)) = SampleValues(i) Then |
So if we take the value 5 then Fix(5) equals to 5. Then 5=5. So above if statement becomes true. If we take the value 5.95 then Fix(5.95) equals to 5. But 5 is not equals to 5.95. So in that case above if statement becomes false.
That's how you can use Fix function to check whether a value has decimal places or not. So If we run above code we will get following result in the immediate window.
5 - No decimal places
5.95 - has decimal places
20 - No decimal places
-13.7 - has decimal places
-2 - No decimal places
