How to find the recurring period string of a recurring decimal using VBA

[Juggler_IN]

I want to find the recurring period string of a recurring decimal.

That is, for example:
1/3 = 0.(3) = 0.333333... with a period "3" of length 1.
1/7 = 0.(142857) = 0.142857142857... with a period "142857" of length 6.
1/15 = 0.0(6) = 0.066666... with a period "6" of length 1.

So, if my input string is x=0.142857142857142857142, the code should output 142857.

I have attempted a VBA function with a reference Java code at Periods and the code is:

Function findSequence(x As String) As String

    Dim n As Long, i As Long, j As Long, k As Long

    n = Len(x)

    For i = 1 To n - 1

        For j = i To n - 1

            k = j Mod i: If k = 0 Then k = 1 Else k = k

            If Mid(x, k, 1) <> Mid(x, j, 1) Then
                i = j
            Else
            End If

        Next j

        findSequence = Mid(x, 1, i + 1) ' (The issue appears to be at this line.)
        GoTo x1

    Next i

    findSequence = "Impossible"
    GoTo x1

x1:
    Exit Function

End Function

But, this code is not giving the required output. Any suggestions?

 

[Gokhan Aycan]

Hey, from what I have read I don't think this is exactly what you want but maybe you will like it. I used the second approach from How do you calculate how many decimal places there are before the repeating digits, given a fraction that expands to a repeating decimal?. You can only use integer values, whether string or long type.

You need this simple class: cRational

Public numerator As Long, numMultiplier As Long
Public divisor As Long, divMultiplier As Long
Public remainder As Long
Public approximate As String
Public index As Long

Public Function FindSequence(ByVal numerator As Variant, ByVal divisor As Variant, Optional ByVal NoDecimals As Long = 99) As String
    
    On Error GoTo errHandler
    
    numerator = CLng(numerator)
    divisor = CLng(divisor)
    
    On Error GoTo -1
    
    Dim i As Long
    Dim approximate As String, numInteger As Long
    Dim ratio As cRational, coll As Collection
    
    Set coll = New Collection
    
    approximate = ""
    NoDecimals = NoDecimals + 1
        
    If numerator >= divisor Then
        numInteger = numerator \ divisor
        numerator = numerator - divisor * numInteger
    End If
    
    For i = 1 To NoDecimals
        Set ratio = New cRational
        
        ratio.index = i
        ratio.numerator = numerator
        ratio.divisor = divisor
        ratio.numMultiplier = 10
        
        ratio.remainder = ratio.numMultiplier * numerator Mod divisor
        ratio.divMultiplier = (ratio.numMultiplier * numerator - ratio.remainder) / divisor
        
        approximate = approximate & ratio.divMultiplier
        ratio.approximate = approximate
        
        On Error Resume Next
        
        coll.Add ratio, CStr(numerator)
        
        If Err Then
            FindSequence = coll(coll.Count).approximate
            Dim temp As String
            temp = "(" & Mid(FindSequence, coll(CStr(numerator)).index) & ")"
            FindSequence = numInteger & "." & Left(FindSequence, coll(CStr(numerator)).index - 1) & temp
            If Right(FindSequence, 3) = "(0)" Then FindSequence = "Not Repeating"
            Exit For
        End If
        
        On Error GoTo -1
        
        numerator = ratio.remainder
        
        Set ratio = Nothing
    Next i
    
    If i = NoDecimals + 1 Then FindSequence = "Couldn't find within " & NoDecimals - 1 & " decimal places."
    
    Exit Function
    
errHandler:
    
    FindSequence = "Parameter is not an integer."
    
End Function

 

[jdellasala]

I want to find the recurring period string of a recurring decimal.

That is, for example:
1/3 = 0.(3) = 0.333333... with a period "3" of length 1.
1/7 = 0.(142857) = 0.142857142857... with a period "142857" of length 6.
1/15 = 0.0(6) = 0.066666... with a period "6" of length 1.

So, if my input string is x=0.142857142857142857142, the code should output 142857.

I have attempted a VBA function with a reference Java code at Periods and the code is:

Function findSequence(x As String) As String

    Dim n As Long, i As Long, j As Long, k As Long

    n = Len(x)

    For i = 1 To n - 1

        For j = i To n - 1

            k = j Mod i: If k = 0 Then k = 1 Else k = k

            If Mid(x, k, 1) <> Mid(x, j, 1) Then
                i = j
            Else
            End If

        Next j

        findSequence = Mid(x, 1, i + 1) ' (The issue appears to be at this line.)
        GoTo x1

    Next i

    findSequence = "Impossible"
    GoTo x1

x1:
    Exit Function

End Function

But, this code is not giving the required output. Any suggestions?

You do know that there are FUNCTIONS that can alleviate the issues you are having if you use them. Like ROUND, ROUNDDOWN, ROUNDUP, CEILING, FLOOR, ABS, INT, MOD.... ROUNDUP and ROUNDDOWN may not be available in Excel 2003, but I bet the others are!

 

[Juggler_IN]

?Gokhan Aycan; Thanks for the function code. It helps.