Excel VBA Binary Strings: Text, Bytes, and Bits

VBA & macros · Updated November 21, 2026

In Excel VBA, a String is text, not a raw sequence of binary bytes. If you mean a string of 0s and 1s, VBA can build and store it as text; if you need actual file or network bytes, use a Byte array instead. The right choice depends on whether you are comparing text, displaying a number in base 2, or processing binary data.

What “binary string” can mean in VBA

The phrase has several meanings, and mixing them up is a common source of bugs. A binary-looking value such as "01001000" could be ordinary text, a representation of the number 72, or a sequence of eight individual bits. Separately, “binary” can describe how VBA compares two strings or how a file is read.

  • Bit text: characters such as 0 and 1 used to display a number in base 2. It is still a VBA String.
  • Binary comparison: a case-sensitive comparison of string characters, rather than a text comparison that treats some letter cases as equivalent.
  • Binary data: raw bytes, such as the contents of an image or a file. Use a Byte array for this, not a String.

Decide which meaning applies before choosing a VBA type. A String is convenient for readable labels and bit patterns. A Byte array is the appropriate representation when each value must be an exact byte from 0 through 255.

How VBA stores strings

In Windows Excel for Microsoft 365, VBA strings use Unicode text storage (BSTR). In practical terms, a VBA String holds characters, with a length that VBA tracks separately from a terminating character. It is not simply a fixed-width array of bytes. For example, "1010" contains four text characters; it does not automatically represent the numeric value ten or four packed bits.

A String can contain characters beyond ordinary English letters, and it can also contain a zero character. That does not make it a reliable container for arbitrary file bytes. Character encoding, conversion rules, and functions that expect text can change or interpret the contents. Some external APIs also treat a zero character as the end of text, even though VBA can track a longer string.

Use a Byte array when exact byte values matter. Each Byte holds one value from 0 to 255, so a sequence such as 72, 73, 0, 255 remains unambiguous. This distinction is important for files, encryption routines, compressed data, and communication protocols.

Binary comparison is about matching text

VBA’s binary comparison mode does not convert text into binary digits. It controls how text comparisons are performed. In a binary comparison, uppercase and lowercase letters are different: "A" is not equal to "a". The comparison follows character values rather than applying the case-insensitive behavior associated with a text comparison.

For a comparison that clearly states its mode, use StrComp. With leftText and rightText in the procedure, the result is -1 when the first string sorts before the second, 0 when they match under the chosen comparison, and 1 when the first sorts after the second:

Sub CompareTextBinary()
    Dim result As Long

    ' Compare case-sensitively using binary comparison.
    result = StrComp("A", "a", vbBinaryCompare)
    Debug.Print result
End Sub

The exact displayed result for this example is nonzero; the important point is that the strings do not match in binary comparison. For case-insensitive comparisons, use vbTextCompare where appropriate, remembering that text comparison follows locale-sensitive text rules. The module-level statement Option Compare Binary is the default when no Option Compare statement is present, but an explicit comparison argument makes the intention easier to see in a particular line of code.

Creating a string of 0s and 1s

If you want to display a nonnegative integer as a base-2 string, build a String from its remainders when divided by two. The function below accepts a VBA Long from 0 through 2,147,483,647. It returns "0" for zero and does not add leading zeroes for other values. Put it in a standard VBA module; it can be called from another procedure or used as a worksheet function.

Public Function ToBinaryText(ByVal value As Long) As String
    Dim result As String
    Dim remainder As Long

    ' This version represents nonnegative Long values only.
    If value < 0 Then
        Err.Raise vbObjectError + 1000, "ToBinaryText", _
            "Use a nonnegative number."
    End If

    If value = 0 Then
        ToBinaryText = "0"
        Exit Function
    End If

    Do While value > 0
        remainder = value Mod 2
        If remainder = 0 Then
            result = "0" & result
        Else
            result = "1" & result
        End If
        value = value \ 2
    Loop

    ToBinaryText = result
End Function

For example, calling ToBinaryText(13) returns "1101", because 13 is 8 + 4 + 1. The output is text, which means leading zeroes can be added later for a fixed-width display without changing the underlying number. This function deliberately rejects negative numbers: a negative integer’s binary representation depends on the chosen bit width and signed-number convention, so presenting it as an unspecified sequence would be misleading.

Use a Byte array for actual binary data

A Byte array is the natural VBA structure for binary payloads. This small procedure assigns four explicit byte values and prints them in the Immediate window, which you can display in the Visual Basic Editor with Ctrl+G on Windows:

Sub ShowByteValues()
    Dim payload(0 To 3) As Byte
    Dim index As Long

    ' Store exact byte values, including zero and the maximum byte.
    payload(0) = 72
    payload(1) = 73
    payload(2) = 0
    payload(3) = 255

    For index = LBound(payload) To UBound(payload)
        Debug.Print payload(index)
    Next index
End Sub

The printed values are 72, 73, 0, and 255. No character encoding is needed to preserve them. For binary files, VBA’s Open ... For Binary and Get/Put statements can read and write byte data; choose the array size and file position carefully, and close the file handle when finished. A file’s bytes may encode text, but they may instead represent an image, compressed content, or another format. Do not assume that every byte sequence is readable text.

Before running a macro that changes a workbook or writes a file, save a separate copy and check its target path. Macro actions cannot be reversed with Ctrl+Z. The example above only prints values, but the precaution matters when adapting code for real files.

Converting text to bytes and choosing an encoding

Text becomes bytes through an encoding such as UTF-8 or a Windows code page. The encoding determines how characters map to byte sequences; different encodings can produce different bytes for the same visible text. This is why a String-to-byte conversion is not just a change of variable type.

VBA’s StrConv(text, vbFromUnicode) converts Unicode text to the computer’s current ANSI code page. It can be suitable for restricted cases such as ASCII-only text, but it is not a general UTF-8 conversion and may not preserve characters that the active code page cannot represent. The reverse option, vbUnicode, converts from the system code page to Unicode; it cannot infer the correct encoding of arbitrary bytes.

When a file or service specifies UTF-8, use a conversion method that explicitly produces UTF-8 bytes rather than assuming the system code page matches. If a protocol specifies a byte order mark, a particular encoding, or exact byte positions, follow that specification. For simple labels or a visible bit pattern, stay with a String and avoid byte conversion altogether.

Common mistakes and debugging checks

Many errors arise because a value looks like binary data but is actually text. A worksheet cell containing 0101 may be interpreted as a number and lose its leading zero. A VBA String preserves the characters as text, while numeric conversion treats them as a number written in decimal unless code explicitly parses them as base 2.

  • Do not confuse characters with bits. The String "11111111" has eight characters; it is not automatically one byte with value 255.
  • Do not use a String as a universal byte container. Character conversion can change values, especially outside basic ASCII.
  • Do not assume “binary comparison” means numeric comparison. It is a text-comparison mode, not a base-2 conversion.
  • Check leading zeroes. Bit strings are identifiers or formatted representations in many tasks, so store them as text when their width matters.
  • Check byte bounds. A Byte cannot hold a negative number or a value above 255. Use a wider numeric type for calculations, then validate before assigning bytes.

When a result is unexpected, inspect the data type and the values at each stage. For text, examine its length and characters. For binary data, print each byte as a number or as two hexadecimal digits. Hex is often easier to scan than a long run of bits, but it is still only a display format; the underlying data remains bytes.

Which representation should you choose?

Choose based on what the value means and what the next operation requires. A display string, a comparison, and a file payload are different jobs even if they all involve the word “binary.”

  • Use String for ordinary text, labels, and a readable sequence of 0s and 1s.
  • Use StrComp with vbBinaryCompare when a text match must be case-sensitive and explicit.
  • Use a Byte() array for exact byte values, binary file contents, or data passed to byte-oriented code.
  • Specify an encoding when converting text to bytes; do not rely on an unstated default for data that must travel between systems.

These types are not interchangeable. Converting between them is a deliberate step with rules, and those rules should match the source format. In Excel, worksheet cells are also not a substitute for arbitrary binary storage: use cells for values and text intended for a spreadsheet, and use file or byte-handling code for binary payloads.

Frequently Asked Questions

Does VBA have a binary string data type?
No. VBA has the String type for text and the Byte type for individual bytes. A sequence of 0s and 1s can be stored in a String, but it remains text rather than a special binary-string type.

How do I convert a number to a binary string in Excel VBA?
Use a routine that repeatedly divides a nonnegative number by two and collects the remainders. The ToBinaryText function above returns "1101" for 13. Decide separately whether you need fixed-width output with leading zeroes.

What does vbBinaryCompare do?
It tells VBA to compare strings using binary comparison rules, so uppercase and lowercase versions such as "A" and "a" are different. It does not convert a string to binary digits or compare numbers in base 2.

Can I store a binary file in a VBA String?
That is not a safe general approach. Use a Byte array for exact file bytes. A String is designed for text, and converting bytes through a character encoding can alter or misinterpret arbitrary binary content.

Does StrConv(text, vbFromUnicode) create UTF-8?
No. It converts Unicode text to the active Windows ANSI code page. Use an explicitly UTF-8-capable conversion approach when a file or service requires UTF-8, and verify the encoding expected by that destination.

Related

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.