How would you approach the next step of my base64 to binary conversion? (VBA)

[thegreatescape]

I am writing a base64 encoder to learn more deeply VBA. The first step I am taking is to convert a decimal to binary. Here is what I have done so far (below). I have spent some time trying different approaches but my mental powers fail me. So hopefully I can learn from skilled members here as to how they would continue from my base code below. Thank you so much.

Private Sub numToBinary()




Dim goal As Integer


goal = 19


base64Array = Array(64, 32, 16, 8, 4, 2, 1)



'pseudo: recursively total each element of the array checking each calculation if goal is reached, once hit, map which elements were used in the calculation
'to binary, the goal above is 19, so the result would be 0010011

'the getArrayIndex() function I wrote anticipating I would need to get the index from the base64array during the calculation.



End Sub



'function to return the index of an element in an array
Private Function getArrayIndex(Arr, value) As Integer


Dim i As Integer
For i = LBound(Arr) To UBound(Arr)
          If value = Arr(i) Then
            getArrayIndex = i
            Exit For
          End If
Next i


End Function

 

[Eric W]

Consider:

Sub Dec2Binary()
Dim goal As Integer, result As String

' Method 1
    goal = 19   
    While goal > 0
        result = goal Mod 2 & result
        goal = goal \ 2
    Wend
    Debug.Print result
    
' Method 2
    goal = 19
    Debug.Print WorksheetFunction.Dec2Bin(goal, 7)
        
' Method 3
    goal = 19
    Debug.Print WorksheetFunction.Base(goal, 2)
        
End Sub

Personally, if I were to convert a decimal number to binary, I'd use the continued division method (Method 1). No array needed, no helper function needed.

But if you really want to use the power of Excel, use one of the built-in functions (methods 2 and 3) to do it and save yourself the coding altogether. If you have a newer version of Excel with BASE, use that, since it handles larger numbers.

Hope this helps!

 

[thegreatescape]

Consider:

Sub Dec2Binary()
Dim goal As Integer, result As String

' Method 1
    goal = 19   
    While goal > 0
        result = goal Mod 2 & result
        goal = goal \ 2
    Wend
    Debug.Print result
    
' Method 2
    goal = 19
    Debug.Print WorksheetFunction.Dec2Bin(goal, 7)
        
' Method 3
    goal = 19
    Debug.Print WorksheetFunction.Base(goal, 2)
        
End Sub

Personally, if I were to convert a decimal number to binary, I'd use the continued division method (Method 1). No array needed, no helper function needed.

But if you really want to use the power of Excel, use one of the built-in functions (methods 2 and 3) to do it and save yourself the coding altogether. If you have a newer version of Excel with BASE, use that, since it handles larger numbers.

Hope this helps!

Eric, thanks very much for giving some options there, I didn't realise that vba had an in built function already, but method one is definately something I will work on to fully understand as I can see it being really useful in potential other situations.

Thanks again.
steve

 

[Eric W]

Method 1 also works for any other base. Just change the 2s to whatever base you need.

Glad to help!

 

[Rick Rothstein]

You might find this generalized converter to be of some interest. It could be easily expanded to Base 64 provided you can define the 64 Base 64 digits, but it will easily handle Decimal to Base 2 or Base 2 to Decimal to quite a large number (decimal value up to a maximum of 79228162514264337593543950335). This is the link to my mini-blog article...

<!-- title / author block --> [h=3]Convert a (Possibly) Very Large Positive Decimal Number to Any Base (Up To 36)[/h]