Option Explicit

Function sbCollatz(s As String) As Long
'Calculates the Collatz length of a positive integer =
'returns count of iterations until result is 1.
'Excel is not the best tool to implement this but here we are:
'Source (EN): http://www.sulprobil.de/sbcollatz_en/
'Source (DE): http://www.berndplumhoff.de/sbcollatz_de/
'(C) (P) by Bernd Plumhoff 17-Jul-2022 PB V0.2
Dim b As Boolean, c As Integer
Dim i As Long, j As Long, k As Long, m As Long, n As Long, p As Long
n = Len(s)
m = n + 20 'We assume 20 additional digits will suffice
ReDim t(1 To m) As Integer
For i = 1 To n
    t(m - n + i) = Mid(s, i, 1)
Next i
b = False
For j = 1 To m - 1
    If t(j) <> 0 Then Exit For
Next j
k = 1
If j = m And t(m) < 2 Then
    t(m) = 1
    b = True
End If
Do While Not b
    k = k + 1
    Select Case t(m)
    Case 0, 2, 4, 6, 8
        'Divide by 2
        c = 0
        For j = 1 To m
            p = 5 * (t(j) Mod 2)
            t(j) = t(j) \ 2 + c
            c = p
        Next j
    Case 1, 3, 5, 7, 9
        'Multiply by 3 and add 1
        c = 1
        For j = m To 1 Step -1
            p = 3 * t(j) + c
            t(j) = p Mod 10
            c = p \ 10
        Next j
        Debug.Assert c = 0 'If we fail here the number of additional digits was too small
    Case Else
        Debug.Assert False
    End Select
    For j = 1 To m - 1
        If t(j) <> 0 Then Exit For
    Next j
    If j = m And t(m) = 1 Then b = True
    'If you like you can print out t() here
Loop
sbCollatz = k
End Function