VBA Excel String Variables: Types, Examples, and Tips

VBA & macros · Updated November 21, 2026

In Excel VBA, declare a string variable with Dim name As String; it stores text such as names, codes, or messages. Use a regular variable-length String for most tasks, and convert values from worksheet cells deliberately when you need predictable text.

What a VBA string variable is

A variable is a named place for a value while a procedure runs. A VBA String holds text, including letters, numbers stored as text, punctuation, and spaces. For example, customerName might hold "Avery Chen", while invoiceCode might hold "004281". The latter is text, so its leading zeros remain intact.

VBA strings are Unicode text, which lets them represent characters from many writing systems. A string is not the same thing as a number: if a variable contains "42", VBA may convert it in some expressions, but it remains text until it is converted or used in a numeric context. This distinction matters for identifiers, ZIP codes, product codes, and phone numbers, where arithmetic is not intended.

To create a variable, put a declaration inside a procedure or at module level. The common procedure-level pattern is:

Sub ShowGreeting()
    Dim greeting As String ' Holds the message for this procedure.
    greeting = "Hello from Excel VBA"
    MsgBox greeting
End Sub

In the Visual Basic Editor, Dim introduces the variable and As String gives it an explicit type. In the VBA editor, Option Explicit at the top of a module forces variables to be declared before use. It helps catch spelling mistakes such as typing greetng instead of greeting. Before running a macro, save a copy of the workbook: VBA changes cannot be reversed with Ctrl+Z.

Declaring strings, scope, and initial values

A string declared inside a procedure is normally local to that procedure. It is created when the procedure runs and is not a persistent store for workbook data. A module-level declaration can make a variable available to procedures in that module; broader scope choices should be used only when procedures genuinely need to share state. For ordinary text processing, local variables are easier to reason about.

Use a separate declaration for each variable, or specify the type for every variable in a comma-separated declaration. In VBA, Dim firstName, lastName As String does not make both variables strings: only lastName is explicitly a String, while firstName is a Variant. Prefer clear declarations such as:

  • Dim firstName As String
  • Dim lastName As String
  • Dim message As String

A newly declared variable-length String starts as an empty string, represented explicitly by vbNullString or by "". vbNullString is a useful VBA constant when you mean no text; it is not the same data type as the special Null value. Empty text and a cell that has never been populated can have different meanings in an application, so decide whether your code should preserve or normalize that distinction.

String variables versus worksheet cells

A cell is part of the workbook; a string variable is held in memory while VBA runs. Reading a cell into a variable does not automatically make every cell value text. For example, a cell containing a number may be returned as a numeric Variant, and a date may be returned as a serial number when read through Value2. If the destination is a String, VBA converts the value, and the result can depend on the value and regional settings.

When you need text, use an explicit conversion such as CStr after checking for values that cannot safely be converted. The example reads A1 from Sheet1 and treats a genuinely empty cell as empty text:

Save a copy of the workbook before running this macro; macro actions cannot be undone with Ctrl+Z.

Sub ReadCellAsText()
    Dim rawValue As Variant ' A cell can return values other than text.
    Dim textValue As String

    rawValue = ThisWorkbook.Worksheets("Sheet1").Range("A1").Value2
    If IsError(rawValue) Then
        MsgBox "A1 contains an error value."
        Exit Sub
    End If

    If IsEmpty(rawValue) Then
        textValue = vbNullString
    Else
        textValue = CStr(rawValue)
    End If

    Debug.Print textValue
End Sub

Value2 avoids the Currency and Date subtypes used by Range.Value, but it does not force a cell’s contents to become text. If A1 contains a worksheet error such as #N/A, CStr is not a safe way to handle it; the example checks with IsError first. When working with Variants from sources other than a cell, also consider IsNull before conversion. For large ranges, reading a range into a Variant array and processing values in memory can avoid repeated worksheet access.

Converting values to and from strings

CStr(expression) converts a value to a String when a valid conversion exists. The conversion can reflect the computer’s regional settings: decimal separators and date representations may differ between locales. If text is intended to be a stable identifier, it is usually better to preserve it as text at the source than to convert a number back into text and expect the original formatting to reappear.

For instance, if Excel stores 0007 as the number 7, converting that numeric value to text produces a representation of 7, not the original four-character code. Formatting the cell to display leading zeros does not necessarily change its underlying value. To retain the code, store or import it as text, or construct the desired text using a deliberate formatting rule.

VBA’s Val function can read a numeric prefix from text, but it is not a universal substitute for validating user input or using a locale-aware conversion. Use a suitable conversion routine for the actual data and reject unexpected input rather than assuming every string is a number. Likewise, IsNumeric can help test whether an expression is recognized as numeric, but passing that test does not decide whether the value is appropriate for a particular business rule.

Joining, measuring, and editing strings

Use the ampersand operator (&) to join text. It communicates concatenation clearly and avoids the ambiguity of the plus sign, which VBA can use for numeric addition or attempt to interpret through implicit conversions. For example, fullName = firstName & " " & lastName joins two names with one space. If an operand might be Null or another unexpected value, handle that condition before joining.

Common built-in tools include Len for a string’s length, Left$ and Right$ for characters at either end, Mid$ for a section, InStr to search for text, Replace to substitute text, and Trim$ to remove leading and trailing ordinary spaces. The dollar-sign forms of several functions return a String rather than a Variant. LenB measures byte length, not the number of visible characters, so it is usually not the right test for a text limit.

For example, if a source value may have extra spaces, assign a cleaned copy with cleanName = Trim$(rawName). This removes spaces at the ends, not spaces between words; "Avery Chen" stays as written. Do not trim automatically when leading or trailing spaces are meaningful, such as in data prepared for a fixed-width file.

Comparing strings and handling case

String comparisons are affected by the module’s comparison setting. VBA’s default Option Compare Binary compares character codes, so uppercase and lowercase letters are treated as different. Option Compare Text makes comparisons case-insensitive according to text-comparison rules. Put an Option Compare statement at the top of a module, before procedures, and choose a setting intentionally rather than relying on an assumption.

For a comparison whose behavior should be clear at the point of use, call StrComp with vbBinaryCompare or vbTextCompare. It returns zero when the strings compare as equal, a negative value when the first sorts before the second, and a positive value when it sorts after the second. This is often clearer for a one-off case-insensitive check than changing a module-wide setting.

Trim and compare user input only according to the task. A confirmation word such as “yes” may reasonably ignore case and surrounding spaces, while a password, case-sensitive code, or exact imported identifier may require an exact comparison. Also distinguish a text comparison in VBA from Excel’s worksheet comparison behavior; they are related tasks but do not automatically share every setting.

Fixed-length and variable-length String types

The usual declaration, Dim note As String, creates a variable-length string. Its content can grow or shrink as assignments change, subject to available memory and the limits of the operation involved. It is the appropriate choice for most names, messages, filenames, and cell text.

VBA also supports fixed-length strings, declared with a length, such as Dim code As String * 8. An assigned value shorter than the declared length is padded with trailing spaces; a longer value is truncated to the declared length. This behavior can be useful when a format specifically requires fields of a set width, but it can surprise ordinary worksheet code: comparisons and output may include padding, and excess input can be lost.

Do not choose a fixed-length string merely because a worksheet column appears to have a consistent number of characters. Data may later exceed that width, and Excel cells can hold much longer text than a small fixed field. The worksheet’s cell-content limit is 32,767 characters; that is a separate Excel limit, not a recommendation to allocate strings of that size. Choose a fixed length only when an external format or interface specifies it.

Common errors and practical safeguards

Many string bugs come from implicit conversion, unexpected whitespace, or confusing an empty string with a missing or invalid value. Declaring variables explicitly, checking cell errors, and choosing the desired comparison mode prevent a large share of these problems. Other useful checks include:

  • Unexpected leading zeros: preserve identifiers as text before Excel interprets them as numbers.
  • “Type mismatch” errors: inspect Variants for worksheet errors or Null before converting them with CStr.
  • Text that looks equal but is not: check for leading, trailing, or nonbreaking spaces. Trim$ removes ordinary spaces, not every invisible character.
  • Truncated values: check for fixed-length declarations or downstream limits before assigning longer text.
  • Case-sensitive results: check Option Compare or supply an explicit comparison argument to StrComp.

When imported data contains nonbreaking spaces, tabs, or line breaks, inspect the actual characters rather than assuming Trim$ will remove all of them. VBA constants such as vbTab, vbCr, and vbLf can help identify or replace specific whitespace. Make replacements targeted: removing every space from a name, for example, would alter valid text.

Building text in a macro

For a few pieces of text, repeated use of & is straightforward. If a loop builds a very large result one fragment at a time, repeated concatenation can become inefficient. A common alternative is to put fragments into a String array and combine them with Join. This also makes the separator, such as a line break, explicit.

The example below joins three labels with line breaks. Save a copy before running any macro because VBA changes cannot be undone with Ctrl+Z.

Sub BuildMessage()
    Dim parts(0 To 2) As String ' Stores each line separately.
    Dim message As String

    parts(0) = "Order received"
    parts(1) = "Status: processing"
    parts(2) = "Please keep this reference"
    message = Join(parts, vbCrLf)

    MsgBox message
End Sub

In Excel for Microsoft 365 on Windows, press Alt+F11 to open the Visual Basic Editor; on Mac, Option+F11 may require the Fn key, depending on the keyboard. Insert a standard module in the editor to add a procedure. Excel for the web can open and edit workbooks containing VBA, but it cannot create, run, or edit VBA macros; use the desktop app for those tasks. Enable macros only in workbooks you trust.

Frequently Asked Questions

How do I declare a string variable in Excel VBA?
Use a declaration such as Dim customerName As String inside a procedure. Add Option Explicit at the top of the module to require variable declarations and catch misspellings.

What is the difference between String and Variant in VBA?
A String is specifically for text. A Variant can hold different kinds of values, including text, numbers, dates, Empty, or Error values. Use a Variant when the incoming value can vary, such as a worksheet value you need to inspect, then convert it to String when appropriate.

How do I put a cell value into a string variable?
Read the cell into a Variant, check whether it contains an error or is empty, and then use CStr for a value that can be converted. Reading with Value2 does not guarantee that the result is text, and numeric values will not retain display formatting such as leading zeros.

How do I compare strings without regard to capitalization?
Use StrComp(firstText, secondText, vbTextCompare) = 0 for an explicit case-insensitive comparison. You can also use Option Compare Text at module level when that comparison rule should apply throughout the module.

Why does a fixed-length string contain extra spaces?
A declaration such as Dim code As String * 8 pads shorter assigned values with spaces to fill the specified length. Use a regular variable-length String unless a fixed-width format specifically requires padding.

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.