Want to become an expert in VBA? So this is the right place for you. This blog mainly focus on teaching how to apply Visual Basic for Microsoft Excel. So improve the functionality of your excel workbooks with the aid of this blog. Also ask any questions you have regarding MS Excel and applying VBA. We are happy to assist you.

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

Popular Posts

Contact Form

Name

Email *

Message *