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
– Scott Craner, Commented Dec 5, 2019 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
– maleficent, Commented Dec 5, 2019 at 23:12
you keep resetting bin and val inside the loop. that is why you get nothing. — Scott Craner
– Scott Craner, Commented Dec 5, 2019 at 23:21

Scott Craner · Accepted Answer · 2019-12-06 00:37:49Z

2

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 6, 2019 at 0:37

Scott Craner

153k10 gold badges52 silver badges88 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

YasserKhalil Over a year ago

Thanks a lot for the code. Is there a way to do the reverse? I mean from binary to string

Scott Craner Over a year ago

That is a new question.

YasserKhalil Over a year ago

Thanks a lot. I could found a clue at this LINK

maleficent Over a year ago

Thank you so much!!

Collectives™ on Stack Overflow

Convert string to binary in excel vba

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related