In VBA, String is the data type used to store text, such as names, messages, codes, and text read from cells. Declare a variable with Dim name As String; its length can change as you assign different text to it.
What String means in VBA
A String is a sequence of characters. It can hold letters, digits, spaces, punctuation, and other text characters. For example, "A17" is a string even though it contains digits: VBA treats it as text when it is enclosed in quotation marks and assigned as a string. This distinction matters when you compare values, perform arithmetic, or write data to a worksheet.
VBA has several built-in data types for different kinds of values. Use String for text, Long for many whole-number calculations, Double for decimal values, and Boolean for True or False values. Choosing an appropriate type makes code easier to understand and helps avoid unintended conversions.
String is a data type, not a function. VBA also has a function named String (and its string-returning form, String$) that creates a repeated character sequence. The similar names can be confusing, but the type declares what a variable can store, while the function constructs a value.
Declaring variable-length and fixed-length strings
The common declaration is Dim message As String. This creates a variable-length string: the amount of text it contains can vary as the program runs. You do not need to specify a maximum size for ordinary string variables. Their practical limit is constrained by available memory and VBA’s implementation limits, rather than by a length you set in the declaration.
VBA also supports fixed-length strings, written with an asterisk and a length:
Dim code As String * 8
This variable is eight characters long. If you assign fewer characters, VBA pads the value with trailing spaces; if you assign more, the value is truncated to the declared length. That behavior can be useful when working with legacy file formats or records with fixed-width fields, but it is usually inconvenient for everyday text processing. A variable-length string is generally the better default.
Pay attention to where As String appears when declaring multiple variables. In Dim firstName, lastName As String, only lastName is a String; firstName is a Variant. Declare each type explicitly, for example:
Dim firstName As String
Dim lastName As String
Assigning and combining text
Put literal text inside straight double quotation marks, then assign it with =. Use the ampersand operator, &, to join pieces of text. It is the clearest choice for string concatenation because it tells VBA that the operation is text joining, rather than leaving the meaning of + open to numeric interpretation or type coercion.
Before running a macro, save a copy of the workbook: VBA macros cannot be undone with Ctrl+Z. This small example shows declarations, assignment, concatenation, and the length of the resulting text:
Sub BuildMessage()
' Create and inspect a short text value.
Dim greeting As String
Dim userName As String
Dim message As String
greeting = "Hello, "
userName = "Mina"
message = greeting & userName & "!"
Debug.Print message
Debug.Print Len(message)
End Sub
The Immediate window displays Hello, Mina! and then 12, because the exclamation mark, comma, and space all count as characters. In the Visual Basic Editor, open the Immediate window with Ctrl+G on Windows; the Mac shortcut and editor behavior can vary by Excel version. On Mac, use the Visual Basic Editor’s View menu to show the Immediate window if needed.
For line breaks, use constants such as vbCrLf for a carriage return and line feed, or vbLf for a line feed. vbTab inserts a tab character. These constants are often more readable than embedding control characters in a text literal.
Useful string functions and operations
VBA provides functions for measuring, searching, extracting, and cleaning text. Many have both a Variant-returning name and a version ending in $, such as Left and Left$. The dollar-suffixed form returns a String and can make the intended result type explicit. Common tools include:
Len(text)returns the number of characters in a string.Left$(text, count)andRight$(text, count)return characters from the beginning or end.Mid$(text, start, count)extracts part of a string. Its starting position is one-based, so the first character is position 1.InStr(start, text, searchText)finds a substring and returns its position, or zero when it is not found.Trim$(text)removes leading and trailing ordinary spaces;LCase$andUCase$change letter case.Replace(text, find, replacement)substitutes matching text within a string.
For example, if a code is "AB-204", searching for "-" with InStr returns 3. That position can help locate a separator before extracting the parts around it. Remember that a missing match returns zero, not an error, so test the result before using it as an extraction position.
The function String$(5, "*") returns five asterisks: "*****". That is the function, not a way to declare a String variable. Functions such as Format$ can also produce text for display, but formatted output is still text; it should not be mistaken for a numeric value suitable for arithmetic.
Converting values to and from strings
VBA often converts values when an expression combines different types, but relying on implicit conversion can make code difficult to predict. Use CStr(value) when you deliberately want a value represented as text. For instance, a number converted to a string can be joined with a label. The result may reflect regional settings for decimal separators or dates, so it is not always a stable format for data exchanged between systems.
Conversion in the other direction needs equal care. A string such as "42" may be converted to a number in a numeric expression, but text such as "42 items" is not a complete numeric value. Val reads a numeric prefix and stops at the first character it cannot interpret, which can give surprising results; it is not a general-purpose validation routine. For reliable numeric input, check that the text has the expected format and use an appropriate conversion function.
Strings can also be empty or contain only spaces, which are different conditions. A zero-length string, "", has Len equal to zero. A string containing three spaces has a length of three, even though it may look blank in a cell or message. Use Trim$ when surrounding spaces should be ignored, but do not trim automatically if spaces are meaningful data.
Strings, Variants, Null, and worksheet values
A variable declared as String is not the same as a Variant containing text. A Variant can hold values of several types, including text, numbers, dates, errors, and special states such as Empty or Null. This flexibility is useful when receiving values from Excel, but it means code should check what it received before converting or manipulating it.
Excel cell values are commonly returned to VBA as Variants. A cell may contain text, a number, a date serial, a formula error, or nothing at all. Assigning such a value directly to a String may trigger conversion, but an error value or Null can cause a runtime error. Check for an error before calling CStr, and handle Null if your data source can supply it. For a range with mixed types, keep the value in a Variant until you have established what it represents.
In Excel VBA, Range.Value2 is often useful for reading the underlying value without the Currency and Date subtypes returned by some uses of Value. It does not turn every cell into text. A numeric cell remains numeric, and a date is commonly represented by its serial number. Range.Text, by contrast, returns the displayed text, which can depend on number formatting and column width; it may show a shortened display such as number signs. Choose the property according to whether your code needs the underlying value or what is visibly displayed.
How VBA compares strings
String equality and ordering depend on the comparison mode. By default, VBA uses binary comparison, which distinguishes uppercase and lowercase letters according to character codes. A module can instead specify Option Compare Text at the top, before procedures, to make ordinary string comparisons case-insensitive according to the system’s text-comparison rules. Because this changes behavior across that module, state it deliberately rather than assuming comparisons ignore case.
For a comparison where you want to name the mode at the point of use, consider StrComp. It can compare two strings using binary or text comparison and returns a result indicating whether the first is less than, equal to, or greater than the second. This is helpful when a procedure needs a particular comparison rule without relying on the module’s default. For predictable matching, also decide whether leading spaces, trailing spaces, punctuation, or accents should count as differences.
Common mistakes and practical safeguards
- Confusing text digits with numbers:
"0081"is text when stored as a string, preserving its leading zeros. Converting it to a number changes its meaning for identifiers such as postal codes or product codes. - Using
+to join text: Prefer&. If a value is not actually text, convert it deliberately or handle its type before concatenation. - Forgetting fixed-string padding: Trailing spaces in fixed-length strings can affect comparisons and output. Use
Trim$only if those spaces are not part of the required data. - Assuming blank-looking means empty: A cell can contain spaces, a formula returning an empty string, or a truly empty value. Test the condition that matches the task rather than relying on appearance.
- Ignoring errors from worksheet cells: A cell containing
#N/Ais an error value, not the text"#N/A". Check it withIsErrorbefore attempting a string conversion. - Expecting formatting to change the stored value: A cell displaying
0012may hold the number 12 with a number format. If the leading zeros are part of an identifier, store or construct the identifier as text.
These distinctions are especially important in import and export macros. Before changing a column to text or converting it, determine whether the values are prose, identifiers, dates, or quantities. A type choice that is correct for a person’s name may be wrong for a numeric amount or a code whose zeros must be preserved.
Frequently Asked Questions
What is the difference between String and String$ in VBA?
String is the data type used in a declaration such as Dim title As String. String$ is a function that creates a string containing a specified number of repeated characters, such as five hyphens. The shared name does not make them interchangeable.
Can a VBA String contain numbers?
Yes. A String can contain digit characters, as in "2026", but those characters are text rather than a numeric value. Convert the string to a suitable numeric type before arithmetic, and keep it as text when digits are identifiers or must retain leading zeros.
How do I check whether a VBA string is empty?
For a String variable, compare it with "" or check whether Len(variable) equals zero. If spaces should count as empty for your task, trim first or test the trimmed value; a string of spaces is not technically a zero-length string.
What is the maximum length of a VBA String?
A variable-length String does not have a small fixed limit specified in its declaration. Its usable size depends on VBA’s limits and the memory available to the process. Fixed-length strings, written with a declared length such as String * 8, have a specific size and are intended for cases that require fixed-width text.
Why does a VBA string comparison treat uppercase and lowercase differently?
VBA’s default comparison is binary, so case can matter. A module-level Option Compare Text changes ordinary comparisons to text comparison, while StrComp lets code request a comparison mode for a particular comparison. Choose the behavior that fits the data rather than assuming all comparisons are case-insensitive.
Related
- Hide a Column in Power BI: Steps and Important Limits
- Bulls Eye 1-2-3 Data Sheet: Specs, Uses and Safety
- Y Balance Score: How to Calculate and Interpret It
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA: Sum Cells by Fill ColorIf by “add cells” you mean total their numbers, a VBA macro can sum values in a range…
- Excel VBA: Find the Last Row in a Column with DataIn Excel VBA, the usual way to find the last row with data in column A is ws.Cells(ws.Rows.Count,…
- Excel VBA AutoFilter: How to Apply Filters with CodeIn Excel VBA, apply AutoFilter to a range that includes its header row, then set the field number…
- Excel VBA to List File Names in All Subfolders AutomaticallyTo list file names from a folder and all its subfolders in Excel for Microsoft 365 on Windows,…
- Excel VBA Copy Paste Special: Values, Formats, and MoreIn Excel VBA, copy a range and call PasteSpecial on the destination, specifying what to paste. For example,…
- Excel VBA: Highlight Cells in an Array LoopTo highlight worksheet cells while looping through an array in Excel VBA, read a range into an array,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.