CODEWITHSUNDEEP
vbaexcelint
Victor Hugo
Victor Hugo

Reputation: 25

VBA Check if a decimal value has only 2 decimal points. Error using Int()

I'm using vba to write some check routines on a Excel file. One of them need to check if the values of some cells have only 2 or less decimal points. To do that, i write a function that basically have this code:

Function checkValues(sheet as Worksheet) as Boolean
    val = sheet.Cells(1,1).Value
    val = val * 100
    valInt = Int(val)

    if valInt <> val then
        checkValues = false
        Exit Function
    end if
    checkValues = true
End Function

The problem is that when i put the value of the Cell(1,1) equal to 1,12 this function returns False. When i use the Debug i see clearly that both valInt and val are equal to 112, but i don't know why the statement 'valInt <> val' gives me 'True'.

Anybody can help me?

ps: i already try to change to Fix() function and try a lot of others statements.

Thanks very much.

Upvotes: 2

Views: 2943

Answers (1)

YowE3K
YowE3K

Reputation: 23974

Double precision numbers cannot represent decimal values exactly (unless that decimal value can be expressed as the sum of some powers of 2, e.g. 0.75 is exact because it is 2^-1 + 2^-2). So use a variable type of Variant/Decimal rather than Variant/Double:

Function checkValues(Sheet As Worksheet) As Boolean
    Dim val1 As Variant ' Allows a subtype of Variant/Decimal
    Dim valInt As Long
    val1 = CDec(Sheet.Cells(1, 1).Value) * 100
    valInt = Int(val1)

    If valInt <> val1 Then
        checkValues = False
        Exit Function
    End If
    checkValues = True
End Function

Upvotes: 1

Related Questions