CODEWITHSUNDEEP
excelvba
Indra
Indra

Reputation: 73

Count decimal places by Excel VBA

In my excel, there are different types of decimal nos and decimal places are different also.

Eg. 112.33, 112.356, 145.1, 25.01, 27.001

I need to know the count of cells which has 2 digit decimal places, Eg - 25.01 - that should be one of them. I need this code for excel VBA

Upvotes: 0

Views: 20226

Answers (4)

Geoff Griswald
Geoff Griswald

Reputation: 1093

Improving on the very handy function submitted by MatthewHagemann.

This version;

  • Doesn't error when the user passes more than one cell as a range to the function

  • Works regardless of whether the number in the cell is stored as Text or as a Number

VBA:

Function CountDecimalPlaces(InputCell As Range) As Integer
'Counts the number of decimal places in a cell
Dim StringConvert As String

If InputCell.Cells.Count > 1 Then
    CountDecimalPlaces = 0
    Exit Function
End If

StringConvert = InputCell.Value

If InStr(1, StringConvert, ".") = 0 Then
    CountDecimalPlaces = 0
Else
    CountDecimalPlaces = Len(StringConvert) - InStr(1, StringConvert, ".")
End If

End Function

Upvotes: 1

MatthewHagemann
MatthewHagemann

Reputation: 1195

Function GetNumberDecimalPlaces(theCell As Range) As Integer

Dim periodPlace as integer, stringLength as integer

periodPlace = InStr(1, theCell.Text, ".")

If periodPlace = 0 Then
    GetNumberDecimalPlaces = 0
Else
    stringLength = Len(theCell.Text)

    GetNumberDecimalPlaces = stringLength - periodPlace
End If

End Function

Upvotes: 1

Rene
Rene

Reputation: 76

Alex's answer can be extended for the case where decimal character (period or comma) is unknown. This may be so if numbers are taken from an Excel sheet and regional settings are unknown in advance. Another extension is the handling of (integer) numbers that lack decimals.

Dim iDigits As Integer
Dim vNumber As Variant

vNumber = Excel.Application.ActiveCell.value

If VBA.IsNumeric(vNumber) And Not VBA.IsEmpty(vNumber) Then
    iDigits = Len(vNumber) - Len(VBA.Fix(vNumber))

    ' correct for decimal character if iDigits > 0
    iDigits = IIf(iDigits > 0, iDigits - 1, 0)
Else
    iDigits = 0
End If

Upvotes: 1

Alexander Bell
Alexander Bell

Reputation: 7918

You can use VBA technique as shown in the following example:

Dim digit As Integer
Dim num As Double
num = 123.456
digit = Len(CStr(num)) - InStr(CStr(num), ".")

where digit is the number of decimal places.

pertinent to your first sample:

digit = Len(CStr(112.33)) - InStr(CStr(112.33), ".")

Rgds,

Upvotes: 5

Related Questions