Excel-VBA Solutions: How to Check If a Value Has Decimal Places

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

Pages

How to Check If a Value Has Decimal Places