Quick Jump
Introduction | Example | Tutorial | Applications | Comments

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

Get wellsrPRO for Free

Get these macros delivered right to your spreadsheet for free

Access tutorials and import my macros without ever leaving Excel

Show me - It's free

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.

VBA CStr convert number to string
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.

VBA CStr convert date to string

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!

VBA CStr convert date to formatted string

VBA CStr convert date to formatted string

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

Comments

Follow me on Google+ for more great VBA content - and an occassional non-VBA post. Share this article with others and send me a message if you have ideas for exciting VBA articles.


Get wellsrPRO for Free

Get these macros delivered right to your spreadsheet for free

Access tutorials and import my macros without ever leaving Excel

Show me - It's free

About Ryan Wells


Ryan Wells

Ryan Wells is a Nuclear Engineer and professional VBA Developer. He is the lead developer of several VBA applications, including PDF VBA - a leading Excel Add-in for exporting Excel Objects, like charts and tables, to PDFs. Discover more of his popular Excel Add-ins, including Mouse To Macro and CF Shapes, at his dedicated Excel Add-ins page.