Introduction | Example | Tutorial | Applications
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.
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 spreadsheets with our free VBA Developer's Guide 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 Guide and a bunch of pre-built macros so you can master file I/O, arrays, strings and more.
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 Guide and a bunch of pre-built macros so you can master file I/O, arrays, strings and more.
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(, calls the function by passing it a variable with a Double data type,
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( is allowed, as well.
If the conversion to a string succeeds, your value will be stored in
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
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
Our subscribers love getting VBA tips, techniques and tactics like this in their inbox. To see why, subscribe using the form below.