Convert string to binary in excel vba

Question

This is my code where I try to convert string to binary but its not working.Can please somebody help me? I have been trying to solve this for past 5 days!! Thank you in advance!

  Sub Button1_Click()
          Dim strText As String
           strText = ActiveWorkbook.Sheets("Sheet2").Range("A1")

           Debug.Print strText
           Dim n, val As Integer
           n = Len(strText)

           Debug.Print n

           Dim str As String
            str = Mid(strText, 1, 1)
           Debug.Print str
           For i = 1 To n
           'val = CInt(strarr(i))
           val = Asc(str)

           Debug.Print val
           Dim bin As String
           Dim modval As Integer
           bin = ""

           While val > 0
           modval = val - (2 * (val \ 2))

           If modval = 1 Then
           bin = bin & "1"
           val = 0
           Else
           bin = bin & "0"
           val = 0
           End If
           Wend

           bin = StrReverse(bin)
           Debug.Print bin
           Next i
           Debug.Print i
            ActiveWorkbook.Sheets("Sheet2").Range("A2") = bin
        End Sub

Please be more specific. What is it doing that is incorrect? Does it error? If so, on which line does it error? — Scott Craner, Dec 05 '19 at 23:11
@ScottCraner It doesnt give any error but also doesnt give output. output is always 0 for whatever input.I want something like if input = "hey" then output will be output = "01101000 01100101 01111001" — maleficent, Dec 05 '19 at 23:12
you keep resetting `bin` and `val` inside the loop. that is why you get nothing. — Scott Craner, Dec 05 '19 at 23:21

score 1 · Accepted Answer · answered Dec 06 '19 at 00:37

1

Sub Button1_Click()
    Dim fullstr As String
    fullstr = ActiveWorkbook.Sheets("Sheet2").Range("A1")

    Dim bin As String
    bin = ""

    Dim j As Long
    For j = 1 To Len(fullstr)
        Dim str As String
        str = Mid$(fullstr, j, 1)

        Dim z As Double
        z = Asc(str)

        Dim i As Long
        For i = 7 To 0 Step -1
            Dim y As Double
            y = (2 ^ i)

            bin = bin & Int(((z / y) - Int(((z / y) / 2)) * 2))
        Next i
        bin = bin & " "
    Next j

    ActiveWorkbook.Sheets("Sheet2").Range("A2") = bin
End Sub

answered Dec 06 '19 at 00:37

Scott Craner

148,073
10
49
81

Thanks a lot for the code. Is there a way to do the reverse? I mean from binary to string – YasserKhalil Dec 06 '19 at 02:39
That is a new question. – Scott Craner Dec 06 '19 at 02:42
Thanks a lot. I could found a clue at this [LINK](https://stackoverflow.com/questions/26624736/convert-binary-to-string) – YasserKhalil Dec 06 '19 at 02:50
Thank you so much!! – maleficent Dec 06 '19 at 18:04

Convert string to binary in excel vba

1 Answers1