De-basing numbers

[wolfvanzandt]

So, if you want to go in the other direction and convert a decimal number to a number in a different base system, here's how you can do it.

Function Dec2Basen(arr1 as double, arr2 as integer)
'REM This function converts a base-10 number to a number in any base.
Dim I as integer, BigPower as integer, Pwr as integer
Dim Quo as double, Hl as integer, Remainder as double
Dim OutString as string, FinalStr as string

'REM First, determine the first power of the base larger than the
'base-10 number.
BigPower=0
Do until arr2^BigPower>arr1
    BigPower=BigPower+1
Loop
'Now, step powers back to -3. Start with BigPower-1 to find the first digit to
'the left.
If arr1>0 then
    Quo=Int(arr1)/(arr2^(BigPower-1))
    Hl=Int(Quo)
    Remainder=Quo-Int(Quo)
    Remainder=Remainder*(arr2^(BigPower-1))
else
    Quo=arr1/(arr2^(BigPower-1))
    Hl=Int(Quo)
    Remainder=Quo-Int(Quo)
end if
    if Hl>9 then
        OutString=ValueAlpha(Hl-9)
    else
        OutString=Str(Hl)
    End if
Pwr=BigPower-2
'Now, cycle until Pwr=-3 finding each digit in turn
do until Pwr=-3
    'Find
    If Pwr >-1 then
        Quo=Int(Remainder)/(arr2^Pwr)
        Hl=Int(Quo)
        Remainder=Quo-Int(Quo)
        Remainder=Remainder*Hl
    else
        If pwr=-1 then
            Quo=(arr1-Int(arr1))/(arr2^Pwr)
            Hl=Int(Quo)
            Remainder=(Quo-Int(Quo))/10
            OutString=OutString+"."
        Else
            Quo=Remainder/(arr2^Pwr)
            Hl=Int(Quo)
            Remainder=Quo-Int(Quo)          
        End if
    end if
    if Hl>9 then
        OutString=OutString+ValueAlpha(Hl-9)
    else
        OutString=OutString+Str(Hl)
    End if
    Pwr=Pwr-1
Loop
FinalStr=""
For I=1 to Len(OutString)
    If Mid(OutString,I,1)<>" " then
        FinalStr=FinalStr+Mid(OutString,I,1)
    End If
Next I
Dec2Basen=FinalStr
End Function

Again, if you work in another base, you pretty much have to play with strings because spreadsheet arithmetic is based on decimal math. You see most of the logic structures in this code - For...Next loops, If....Then.....Elseif......Else....End If branches, and also one we didn't use the last time, Do loops.

Do loops are more flexible than For....Next loops, and they are a little more confusing. The beginning statement is a Do statement; the end is a Loop statement. Either of them can have an Until or While provision and all four variations produce different outcomes.

"While" instructs the program to keep doing the code between the Do and Loop statements while a certain provision is true. If it senses that the provision is true on a Do statement, it will jump out of the loop before it runs through the process again. To make it run one more time, you can place the While condition on the Loop statement.

Using "Until", you keep the loop going until some provision is true. At that point, the looping stops. I will usually get the logic wrong the first time around and have to "adjust" which statement the provision is attached to during the debugging process. Let's look at one of the Do loops.

This is the part that figures out which digit to use. It goes until a power of -3 is reached (otherwise, on a repeating decimal like 0.3333.... or a nonrepeating, infinite decimal like pi, it would go on forever. Infinite loops are not your friend. Three decimal places are okay for government work, anyway.

do until Pwr=-3                                                So, this loop is going to repeat until the Pwr variable, the variable that senses
    'Find                                                            which decimal place the code is working on, reaches -3. The Pwr variable is
    If Pwr >-1 then                                            decremented every loop on the next to last statement below.
        Quo=Int(Remainder)/(arr2^Pwr)
        Hl=Int(Quo)                                              This loop contains two If.....Then structures inside it - two are nested.
        Remainder=Quo-Int(Quo)
        Remainder=Remainder*Hl
    else
        If pwr=-1 then
            Quo=(arr1-Int(arr1))/(arr2^Pwr)
            Hl=Int(Quo)
            Remainder=(Quo-Int(Quo))/10
            OutString=OutString+"."
        Else
            Quo=Remainder/(arr2^Pwr)
            Hl=Int(Quo)
            Remainder=Quo-Int(Quo)          
        End if
    end if
    if Hl>9 then
        OutString=OutString+ValueAlpha(Hl-9)
    else
        OutString=OutString+Str(Hl)
    End if
    Pwr=Pwr-1                                          This is where the Pwr variable is decremented and the Do statement senses whether it has
Loop                                                        reached -3 yet. When it does, the loop ends right there and processing jumps to the next
                                                                statement after the Loop statement.