VBA - Decimal to Binary Function - Output seems to be character limited

[Dim Me as xlNoob]

Hi all,

I have written a function which is able to convert decimal fractions up to 999,999,999.999999 into a binary fraction. It seems to work pretty well but I have an issue with the output I'm getting being limited to 54 characters in total. Have I limited myself with the way I've handled the variables or something? Also it doesn't accept integers over 999,999,999 is this a limitation that I've somehow coded into the function?

Function DECTOBIN(DecIn As Double, Optional FractionLen As Long = 20) As String
    Dim intDecIn As Long, fDecIn As Double, Bin As Long, n As Long
    
    DECTOBIN = ""
    intDecIn = Int(DecIn)
    fDecIn = DecIn - intDecIn
    
    For Bin = 0 To intDecIn
        If 2 ^ Bin - 1 >= DecIn Then Exit For
    Next Bin
    
    For n = Bin To 1 Step -1
        If intDecIn < 2 ^ (n - 1) Then DECTOBIN = DECTOBIN & "0"
        If intDecIn >= 2 ^ (n - 1) Then
            DECTOBIN = DECTOBIN & "1"
            intDecIn = intDecIn - 2 ^ (n - 1)
        End If
    Next n
    
    If DECTOBIN = "" Then DECTOBIN = "0"
    
    If fDecIn > 0 Then
        DECTOBIN = DECTOBIN & "."
        Do
            fDecIn = fDecIn * 2
            DECTOBIN = DECTOBIN & Int(fDecIn)
            fDecIn = fDecIn - Int(fDecIn)
            counter = counter + 1
        Loop Until fDecIn = 0 Or counter = FractionLen
    End If
End Function

It seems to handle smaller numbers with longer fraction parts like 10.999999999999 but still limited to 54 characters. But when I get to 999,999,999.999999 it won't let me add any more decimal places, excel just automatically cuts anything off after 6 decimal places.

Overall, the precision is pretty good but I just want to know if I'm somehow limiting the function or if it is excel.

Additionally, if anyone has a good idea on how to identify when the fractional part of the number starts repeating (remembering it could be repeating 1 or 2 digits, or it could be repeating 5, or 10 digits. I didn't really have any ideas on how I could get the code to identify this and exit the loop when it happens.

All comments/feedback welcome!

 

[Eric W]

I don't see any problems with your code, I think you're just bumping against the limitations of Excel. For example, Excel supports about 15-digit precision for numbers, plus an exponent. So if you have 9 integer digits, that only leaves room for 6 decimal places. And your limitation of 999,999,999 is really 2,147,483,647 (or 2^31 - 1). If you really want to get fancy, you could code your own numeric routines, using either strings or byte arrays to hold your numbers, and you can get whatever precision you want.

Scroll down to "Calculation Specifications and limits" in this link:

https://support.office.com/en-us/ar...d-limits-1672b34d-7043-467e-8e27-269d656771c3

As far as looking for when the decimal starts to repeat, you might be out of luck. Partly because of the way Excel stores numbers. It stores numbers in a binary representation, and just displays them as decimal. If you put in .3 for example, and then look at positions far to the right of the decimal, you'll see rounding, so there's no real way to know if the number is accurate or rounded. Even if you could expect accurate numeric representation, because the number of repeating digits could be as many as the denominator of the fraction, and if you're entering the numbers as decimals, that could be the equivalent of 10^n.

And I'm sure you're aware that you can use your same algorithm to convert to other bases as well. Something like this:

Function DecToBaseX(DecIn As Double, Base As Long, Optional FractionLen As Long = 20) As String
Dim whole As Long, frac As Double

    whole = Int(DecIn)
    DecToBaseX = IIf(whole = 0, "0.", ".")
    
    While whole > 0
        DecToBaseX = whole Mod Base & DecToBaseX
        whole = whole \ Base
    Wend
    
    frac = DecIn - Int(DecIn)
    While frac > 0 And FractionLen > 0
        frac = frac * Base
        DecToBaseX = DecToBaseX & Int(frac)
        frac = frac - Int(frac)
        FractionLen = FractionLen - 1
    Wend
    
    If Right(DecToBaseX, 1) = "." Then DecToBaseX = Replace(DecToBaseX, ".", "")
    
End Function

Bases over 10 would require a minor tweak though.

 

[Dim Me as xlNoob]

That's a cool function, I hadn't considered other bases as of yet, but you really cleaned up the first half of my code.

Thanks for all the info I really appreciate it. I have no need to get any better precision than this get's for me, but I'm interested in looking into your suggestion of custom numeric routines. I've never been down that path. Thanks for the insight.