CODEWITHSUNDEEP
vbaexcel
Guruprasad J Rao
Guruprasad J Rao

Reputation: 29683

Get the length of decimal places

I have an Excel sheet where I have several columns and one column is Amount where I've to validate each cell and check whether it's length after decimal point is greater than 2 and if yes then throw error.

Public Function CheckLength(value As String) As Integer
    Dim n As Double
    Dim itg As Integer
    Dim dcm As Double
    n = value 'Say Value is 50.01 here 
    itg = Int(n) 'Will be 50
    dcm = Split(n - itg, ".")(1) 'But n-itg will yield something and dcm`s value will be 
    '1000001 some strange value where as it has to be `01` or say whatever comes after decimal part
    CheckLength = Len(dcm)
End Function

Upvotes: 1

Views: 1675

Answers (2)

SierraOscar
SierraOscar

Reputation: 17637

If you're actually checking numbers then this will work:

Function CheckLength(value As Double) As Integer
    If InStr(CStr(value), ".") Then
        CheckLength = Len(Split(CStr(value), ".")(1))
    Else
       CheckLength = 0
    End If
End Function

It converts the number to a string, splits it using "." as the delimiter and then returns the length of the second item in the array returned (which is anything after the ".") 

Const myNumber As Double = 50.13245
Debug.Print CheckLength(myNumber) '// Returns 5

'// Split 50.13245 on "." returns an array with 2 parts:
'//    (0) = "50"
'//    (1) = "13245"
'//    Length of (1) '13245' = 5

Upvotes: 1

moffeltje
moffeltje

Reputation: 4658

You could do this:

Public Function CheckLength(value As String) As Integer
    Dim n As Double
    Dim itg As Integer
    Dim dcm As Double
    n = value
    itg = Int(n)
    dcm = Len(CStr(n)) - InStr(CStr(n), ".")

    CheckLength = dcm
End Function

Note: if there is no "." in n, it will return the total length (because it will be Len(CStr(n)) - 0) so you could check if the string contains a "." before, or you can check if dcm is the same as Len(CStr(n)) and then return 0.

Upvotes: 1

Related Questions