VBA function to find number of decimal places

[chirp]

Hey,

I need a function to find the number of decimal places of a certain number (in this specific case doubles)

The first solution would be something like this:

'returns the number of decimal places within a double
Public Function getDecPlaces(inputNum As Double) As Long
Dim ndx As Long
ndx = InStr(1, inputNum, ".")
If ndx > 0 Then
    getDecPlaces = Len$(CStr(inputNum)) - ndx
End If
End Function

But i feel there is likely a much better way of doing this...any help would be appreciated!!

 

[Sektor]

Function AfterPoint(str As String) As String
    With CreateObject("VBScript.RegExp")
        .Pattern = "\d\.(\d+)"
        AfterPoint = .Execute(str)(0).Submatches(0)
    End With
End Function

 

[mikerickson]

Function CountDecimalPlaces(aNumber As Double) As Long

    CountDecimalPlaces = Application.Max(Len(CStr(aNumber)) - Len(CStr(Int(aNumber))) - 1, 0)

End Function

or

CountDecimalPlaces = Len(CStr(aNumber)) - Len(CStr(Int(aNumber))) + CLng(Len(CStr(aNumber)) <> Len(CStr(Int(aNumber))))

 

[Sektor]

Oops. Here's correction:

Function AfterPoint(str As String) [B]As Integer[/B]
    With CreateObject("VBScript.RegExp")
        .Pattern = "\.(\d+)"
        AfterPoint = [B]Len([/B].Execute(str)(0).Submatches(0)[B])[/B]
    End With
End Function

 

[Rick Rothstein]

Okay, here is my attempt at a UDF (user defined function)...

Function AfterPoint(S As String) As Long
  AfterPoint = Len(Split(S & ".", ".")(1))
End Function

 

[chirp]

hey, thanks for the replies.

By "better" i really meant "faster", and so i did a rough speed comparison of all the below (and fast or not i appreciate the effort!).

Input: 29121.00113342 (relatively invariant with different inputs)

Times for 100,000 trials:
DecimalCount (Rick) (using cstr(double) as input)=~.63 seconds
DecimalCount (Rick) (with string as input)=~.52 seconds
Original=~.285 seconds (if you move the if to one line)
CountDecimalPlaces (mike)=~.285 seconds
AfterPoint (use late binding and reg exp are slow so about .8s/1000)

Rick, i like your solution for the simplicity, should note just to add an "On Error Resume Next" or check for a decimal as it errors on integer input.

Mike, i found yours to be equal to the original (timewise) by explicitly setting the lengths of each conversion as below, and really liked the -1 if true trick. I need to think about that property more as it can come in useful

Function CountDecimalPlaces(aNumber As Double) As Long
Dim len1 As Long, len2 As Long
    len1 = Len(CStr(aNumber))
    len2 = Len(CStr(Int(aNumber)))
    CountDecimalPlaces = len1 - len2 - CLng(len1 <> len2)
End Function

i am really not trying to make this a competition between the original function and the others, i just have large sets of data that i need to return the max precision, and this is a step i thought could be trimmed down a little. that said the original takes about half the time with integer input, which is a real benefit compared to these proposed solutions.

If converting to a string is the only way of doing this then these are probably close to the fastest way this could be done.

Thanks!

 

[Sektor]

Make RegExp early binding.
1. Make reference: Tools -> References -> Microsoft VBScript Regular Expressions. 5.5.
2. Create following code in appropriate modules.
3. Close and open workbook so that re variable would be hooked up.

In ThisWorkbook module:

Private Sub Workbook_Open()
    Set re = New RegExp
    re.Pattern = "\.(\d+)"
End Sub

In standard module:

Public re As RegExp

Function AfterPoint(str As String) As Integer
    AfterPoint = Len(re.Execute(str)(0).Submatches(0))
End Function

 

[chirp]

Yeah, if you use early binding the regEx method will come down to the same order of magnitude as the others (and is a "neater" way of doing this). However i tend to avoid using explicit references where possible just for ease of "distribution".

and the results from before were a little misleading as if i was to use this method for an array of values i would only have to create one object

 

[mikerickson]

How about this

Function CountOfD(aString As String) As Long
    Const DecimalPoint As String = "."
    CountOfD = Len(Mid(aString, InStr(1, aString & DecimalPoint, DecimalPoint) + 1))
End Function

 

[Rick Rothstein]

Rick, i like your solution for the simplicity, should note just to add an "On Error Resume Next" or check for a decimal as it errors on integer input.

You must have looked at my UDF before I changed it... I concatenated a decimal point onto the passed in argument to control the error (doing it that way keeps the function "simple").