Use VBA CStr to Convert Number to String

Introduction - CStr

Learn how to use the VBA CStr function to convert a number to a string. CStr can also be used to convert a date to a string using VBA.

This is Part 2 of a new tutorial series showing you how to convert between VBA data types.
Part 1: Convert string to integer
Part 3: Convert string to date

Example - CStr

Convert a Number to a String

Sub DemoCStr()
'Convert a data type to a string
Dim dPrice As Double, str1 As String
dPrice = 19.99
str1 = ConvertToString(dPrice)
MsgBox str1, , "Successful Conversion"
End Sub

Function ConvertToString(v1 As Variant) As String
On Error GoTo 100:
 ConvertToString = CStr(v1)
 Exit Function
100:
 MsgBox "Failed to convert """ & v1 & """ to a string.", , "Aborting - Failed Conversion"
 End
End Function

Make powerful macros with our free VBA Developer Kit

It’s easy to copy and paste a macro like this, but it’s harder make one on your own. To help you make macros like this, we built a free VBA Developer Kit and wrote the Big Book of Excel VBA Macros full of hundreds of pre-built macros to help you master file I/O, arrays, strings and more - grab your free copy below.

Tutorial - CStr

The ConvertToString function does all the work in this VBA macro. Simply call the function and pass it an expression or data type, such as a boolean, a double, an integer or a date. In my CStr example, str1 = ConvertToString(dPrice), calls the function by passing it a variable with a Double data type, dPrice.

I chose to pass a variable directly to the ConvertToString function, but you could also pass a value stored in a cell. For example, str1 = ConvertToString(Range("A1")) is allowed, as well.

If the conversion to a string succeeds, your value will be stored in str1 as a string, as illustrated by the following Watch window.

Successful conversion from double to string

If the conversion fails, a message box will appear letting you know it failed and your macro execution will abort. CStr accepts most expressions so, unlike CInt, there are very few ways to cause CStr to fail. With that said, one surefire way to make it fail is to apply CStr to an array. This will cause a “Type mismatch” error.

VBA Convert Date to String

The VBA CStr Function can do more than just convert numbers to strings. It can also convert dates to strings so you can manipulate them with string manipulation functions (Mid, strConv, Split, InStr, UCase, etc.). Note: Many string manipulation functions can be used on dates even without converting them to strings.

Sub ConvertDateToString()
'Convert a date to a string
Dim dChristmas As Date, str1 As String
dChristmas = "December 25, 2015 15:00"
str1 = ConvertToString(dChristmas)
MsgBox str1, , "Successful Conversion"
End Sub

Function ConvertToString(v1 As Variant) As String
On Error GoTo 100:
 ConvertToString = CStr(v1)
 Exit Function
100:
 MsgBox "Failed to convert """ & v1 & """ to a string.", , "Aborting - Failed Conversion"
 End
End Function

Notice that although the variable dChristmas was written in Long Date format with 24-hour time, the post-conversion is stored in Short Date format with 12-hour time. This is based on the default format specified via your run-time locale.

Have no fear! You can force the string to be stored in whatever format you like using the Format function. For example, change the call statement to

str1 = Format(ConvertToString(dChristmas), "[$-409]mmmm d, yyyy h:mm;@")

and the resulting string will be stored in a new date format!

Application Ideas - CStr

If your familiar with wellsr.com articles, you’ll recall several tutorials where I reference the CStr function. Many native VBA functions require string inputs, so you’ll regularly find yourself needing to convert numbers to strings. One such function is the VBA shell function. In order to close an open application, you’ll need to convert the process ID to a string.

You now now how to convert numbers to strings and dates to strings using VBA, but don’t stop there! Play around by converting other data types to string, including booleans.

The CStr Function is the second featured VBA data type conversion in this series. Read more data type conversion articles by visiting

• VBA CInt
• VBA CDate

Our subscribers love getting VBA tips, techniques and tactics like this in their inbox. To see why, subscribe using the form below.