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

Juggler_IN

Active Member
Joined
Nov 19, 2014
Messages
347
Office Version
  1. 2003 or older
Platform
  1. Windows
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:

VBA Code:
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?
 
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.

1671281825630.png


You need this simple class: cRational
VBA Code:
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

VBA Code:
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
 
Upvote 0

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
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:

VBA Code:
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!
 
Upvote 0

Forum statistics

Threads
1,212,933
Messages
6,110,751
Members
448,295
Latest member
Uzair Tahir Khan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top