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