Excel VBA: Convert a Variant to a String

VBA & macros · Updated November 21, 2026

In Excel VBA, use CStr to convert a Variant value to a String: for example, textValue = CStr(value). Check first for special values such as Null or an Excel error, because those cannot be converted by a plain CStr call.

What a Variant contains

A Variant is a flexible VBA data type: it can hold text, a number, a date, a Boolean value, an object reference, an array, or special states such as Empty and Null. That flexibility is useful when a procedure receives values from a worksheet, a control, or another routine without knowing their exact type in advance. It also means that “convert a Variant to a string” can involve more than changing a number into its printed digits.

Use a String variable when the next operation expects text, such as building a message, writing a text file, or comparing labels. The conversion produces a textual representation of the value; it does not preserve the original type. Once a number has been turned into text, for instance, VBA will not treat that string as a number in every later operation without converting it back.

Basic conversion with CStr

For ordinary scalar values, assign the result of CStr to a String variable. Declare both variables so the intended types are explicit:

Sub ConvertVariantExample()
    Dim sourceValue As Variant
    Dim textValue As String

    sourceValue = 125
    textValue = CStr(sourceValue)

    MsgBox textValue
End Sub

Here, sourceValue contains a numeric value, and textValue receives its string representation, "125". CStr is a VBA conversion function; it is not an Excel worksheet formula. Use it in VBA code, such as a procedure in the Visual Basic Editor, rather than entering it into a worksheet cell as though it were an Excel function.

The same basic pattern works for text, dates, and Boolean values when they are valid scalar values. If the Variant already contains a String, CStr returns a String representation of that text. Applying it to a value that is already text is usually harmless, but unnecessary.

Handle Null, Empty, and errors before converting

The most important conversion problem is that not every Variant contains a normal value. Null represents missing or unknown data, commonly returned by database-related code. Calling CStr on Null raises a run-time error. An Empty Variant, in contrast, represents an uninitialized value and converts to a zero-length string with CStr. An Excel cell containing an error value, such as #N/A, is another special case: do not pass it straight to CStr.

Decide what each special value should mean in your application. The following helper returns an empty string for Null and Empty, a fixed marker for an error, and a readable marker for objects and arrays. Those are policy choices, not automatic rules that fit every workbook:

Public Function VariantToText(ByVal value As Variant) As String
    ' Choose explicit text for special Variant contents.
    If IsError(value) Then
        VariantToText = "[Excel error]"
    ElseIf IsNull(value) Then
        VariantToText = ""
    ElseIf IsEmpty(value) Then
        VariantToText = ""
    ElseIf IsObject(value) Then
        VariantToText = "[Object: " & TypeName(value) & "]"
    ElseIf IsArray(value) Then
        VariantToText = "[Array]"
    Else
        VariantToText = CStr(value)
    End If
End Function

The order matters: test special contents before the final CStr call. Returning a marker such as [Excel error] avoids a conversion failure, but it discards the particular worksheet error type. If the exact error matters, keep the Variant value for later inspection or write separate logic to report the error category. Similarly, an object or array is not automatically a meaningful piece of text; a marker merely makes that limitation visible.

Convert worksheet cell values

A Range cell’s Value or Value2 property returns a Variant, making this conversion common when looping through cells. For example, with a value in A1, a basic assignment is:

Sub ReadCellAsText()
    Dim cellValue As Variant
    Dim cellText As String

    cellValue = ThisWorkbook.Worksheets("Sheet1").Range("A1").Value2

    If Not IsError(cellValue) And Not IsNull(cellValue) Then
        cellText = CStr(cellValue)
    Else
        cellText = ""
    End If

    MsgBox cellText
End Sub

This example treats both worksheet errors and Null as empty text. In normal worksheet cells, a truly blank cell commonly returns Empty rather than Null; CStr of Empty yields an empty string. The error check is still important because a cell containing #VALUE! or another Excel error produces an error Variant, not ordinary text.

For Excel automation, Value2 does not return VBA Date or Currency subtypes; dates and currency-formatted values are exposed as numeric values. Thus CStr on a date serial can return a number rather than the date as displayed in the cell. The Value property can return Date or Currency subtypes in relevant cases, but the displayed appearance is not guaranteed by either property. If the goal is specifically to capture what the user sees, a cell’s Text property returns its formatted display text. That display can depend on column width and may show hash marks when the column is too narrow, so it is usually unsuitable for reliable data processing.

Dates, numbers, and formatting choices

CStr converts a value to text; it does not provide a stable, custom formatting contract. Numeric and date text can reflect regional settings, so the decimal separator and date order may differ between Windows configurations. A date can appear in a familiar local format for one user and a different format for another. If the text is intended for display in a particular format, use VBA’s Format function with an explicit format string, for example Format$(someDate, "yyyy-mm-dd"). Format strings and locale-sensitive details still deserve care when data must travel between systems.

For example, if a Variant contains a numeric value and you want two decimal places for a report, a format such as Format$(amount, "0.00") makes the desired decimal places explicit. That is formatting, not a general-purpose replacement for CStr: the output is still a String, and the format may round or omit information according to its pattern. Keep the underlying numeric value for calculations, and create formatted text only at the point where a display or export needs it.

VBA also has Str, but it is not usually the best substitute for CStr. Str is designed for numeric values and can add a leading space for positive numbers; its number formatting behavior also differs. For ordinary Variant-to-string conversion, CStr is clearer. For a controlled number or date representation, choose Format with an appropriate format string instead.

Choose a conversion based on the task

Before converting, identify what the resulting string is for. A value suitable for a message box might not be suitable for a CSV export, a database key, or a comparison. In particular, converting values with CStr does not guarantee that distinct original values remain distinguishable: formatting can hide precision, and dates can be rendered in different regional forms.

  • Readable text for a message: use CStr for ordinary scalar values, and decide how Null and errors should be described.
  • Consistent display: use Format or Format$ with an intentional pattern for dates and numbers.
  • Text exactly as shown in a cell: consider the Range.Text property, while accounting for column width and display formatting.
  • Data that must remain numeric or date-like: keep it in an appropriate typed variable instead of converting it prematurely.
  • Objects and arrays: define what information should be represented. CStr does not serialize their contents into a useful string automatically.

Common mistakes and troubleshooting

  • “Type mismatch” or an error during conversion: check whether the Variant contains Null, an Error value, an object, or an array before calling CStr. Use IsNull, IsError, IsObject, and IsArray as appropriate.
  • A date turns into a serial number: check whether the value came from Value2. Use an explicit date format for output, or choose the Range property based on whether you need the underlying value or displayed text.
  • A number uses an unexpected decimal or date separator: remember that CStr is not a fixed, locale-independent serialization format. Apply a deliberate format or a separately designed export convention.
  • Leading zeros disappear: if a cell stores 00123 as a number, the original leading zeros are not part of that numeric value. Store identifiers as text or apply a specific display format before converting.
  • A cell looks blank but the result is not empty: a formula may return an empty string, or formatting may hide a stored value. Inspect the cell’s underlying value and formula rather than relying only on its appearance.

When debugging, inspect the value’s subtype with TypeName(value) or VarType(value) before converting it. This can reveal why two values that look alike behave differently—for example, a String containing digits versus a numeric Variant. Avoid using a conversion error handler as a substitute for deciding how special values should be treated; explicit checks make the code’s behavior easier to understand.

Run and protect VBA code

In Excel for Microsoft 365 on Windows, open the Visual Basic Editor with Alt+F11, insert or select a standard module, and place a procedure there. On a Mac, the Visual Basic Editor shortcut is commonly Option+F11; on some keyboards you may also need the Fn key. The Developer tab may need to be enabled to access Visual Basic from the ribbon. Excel for the web can open workbooks containing macros but does not run or edit VBA macros; use the desktop application.

Save a copy of the workbook before running a macro. VBA changes cannot be undone with Ctrl+Z (or Command+Z on Mac), so test code on a copy, especially if it loops through cells or writes results back to a worksheet. A simple conversion assigned to a variable does not alter a cell, but a larger procedure may make changes that are difficult to reverse.

Frequently Asked Questions

What is the VBA function to convert a Variant to a String?
Use CStr(value) for ordinary scalar values. Check for Null and Error values first, since those require explicit handling.

Does CStr convert Empty to an empty string?
Yes. An Empty Variant converts to a zero-length String with CStr. This differs from Null, which causes an error if passed directly to CStr.

How do I convert an Excel error value to text?
Test the Variant with IsError before conversion and choose a representation, such as a marker like [Excel error]. Do not call CStr directly on the error value.

Why does CStr return a number instead of a date?
A cell read through Value2 supplies dates as numeric serial values. Format the value as a date for readable output, or use the cell’s Text property if you specifically need its displayed text.

Should I use CStr or Format?
Use CStr for a straightforward conversion of a normal value. Use Format or Format$ when the output needs a chosen number or date pattern; both approaches produce text, but formatting lets you specify its appearance.

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.