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

Write better macros in half the time
I see people struggling with Excel every day and I want to help. That's why I'm giving away my personal macro library for free. This powerful gift lets you automatically import all my macros directly into your spreadsheet with just one click.

Show me how I can improve my VBA skills for 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

Our subscribers love getting VBA tips, techniques and tactics like this in their inbox. To see why they like it, subscribe using the form below. Then, if you’re ready to start writing your macros faster, without having to search online for answers, check out our best-selling VBA Cheat Sheets for some great automation tips.


Write better macros in half the time
I see people struggling with Excel every day and I want to help. That's why I'm giving away my personal macro library for free. This powerful gift lets you automatically import all my macros directly into your spreadsheet with just one click.

Show me how I can improve my VBA skills for free