VBA CSTR

What Is Excel VBA CSTR Function?

VBA CSTR is a data type conversion function to convert any value to string data in VBA. CSTR stands for “Convert to String (STR).”

For example, if the variable value is integer or long, we can use the VBA CSTR function to convert it to the String data type. For instance, “543” is a number that can be converted to String using the CSTR; similarly, “15-05-2023” can be converted to String using the VBA CSTR function.

For example, look at the following VBA code. The variable “V1” data type is “Date” in the first line of the code. Next, we have assigned the date value “2023-05-01” to the variable “V1”. Next, we convert the date to String using the “CSTR” function and display it.

Code:

Sub CSTR_Intro()
Dim V1 As Date
V1 = “2023-05-01”
MsgBox CStr(V1)
End Sub

What Does CSTR Function Do in VBA?

While working with VBA, we may get multiple data types and need to convert one data type to another. Hence, VBA has many data type conversion functions, and CSTR is one of them.

The VBA CSTR function converts the value supplied to the function into a string data type, which can be passed on to other operations wherever a string data value is required.

Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)

If you want to learn Excel and VBA professionally, then ​Excel VBA All in One Courses Bundle​ (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

VBA CSTR Syntax

Below is the syntax of the Excel VBA CSTR function.

CStr(Expression) As String

The syntax for the VBA CSTR function has only one argument, which is mandatory. We must provide the value we want to convert to string data type.

The input to this argument could be a direct or a variable value or a cell reference. The standard data type values that are converted using the CSTR are Integer, Boolean, and Date data type values.

How to Use VBA CSTR Function?

Let us show you a brief example of using the VBA CSTR function. For example, look at the following value we have in Excel cell. We have a date value in cell A2. Let’s convert this to a string, as shown below.

Step 1: Start the sub-procedure by naming the macro in the Visual Basic editor window.

Step 2: First, let us test the value’s data type in cell A2. Use the VBA TYPENAME function for this.

Step 3: Now, let’s provide the A2 cell value y using the RANGE object and the value property of the Range object as follows.

Step 4: To see the result of this function, wrap it with the MSGBOX.

Step 5: Now, execute the code by pressing the F5 key. We will get the following result.

Since the value in the A2 cell is a date, the TYPENAME function has returned the data type as “Date”. Now, let’s convert this into a string data type.

Step 6: Define a variable to store the value of the cell A2. The value in cell A2 is a date; hence, we have assigned the data type as “date.”

Step 7: Define another variable to convert the value assigned to the first variable, i.e., MyValue.

Step 8: For the first variable, “MyValue” assign the value of the cell A2 using the range object as follows.

Step 9: Next, for the second variable, “MyConvertedValue,” we must assign the string converted value. So, enter the CSTR function.

Step 10: For the argument, enter the first variable MyValue which has the assigned value of the cell A2.

Step 11: Now, let us check the new data type of the value in cell A2 using the TYPENAME function.

Code:

Sub CSTR_Example()

Dim MyValue As Date
Dim MyConvertedValue As String

MyValue = Range(“A2”).Value

MyConvertedValue = CStr(MyValue)

MsgBox TypeName(MyConvertedValue)

End Sub

Execute the code, and we will get the following result in a message box.

Now the data type of the cell A2 value has been converted from date to string.

Examples

Example #1: Convert Integer Value to String

For example, look at the following code.

Part 1: First, we have defined the variable “Value_1” and assigned the data type “integer” to it. It means the defined variable can hold only integer values.

Part 2: Now, we have assigned the integer value to the defined variable in Part 1.

Part 3:

• To check the data type of the variable Value_1, we are using the VBA “TypeName” function and showing the result in a message box.
• Execute the code by pressing the shortcut key “F5,” we will see the following result in a message box.

Since the data assigned to the variable is an integer, the data type function “TypeName” shows the data type as “Integer.”

However, we can change this to a string data type using the CSTR function. To convert the value to String data type, use the VBA CSTR function.

Code:

Sub CSTR_Example1()
Dim Value_1 As Integer
Value_1 = 500
MsgBox TypeName(CStr(Value_1))
End Sub

Inside the TypeName function, we have used the VBA CSTR function, which will convert the variable “Value_1” to String data type.

Execute the code by pressing the F5 key, and we will see the data type as “String” in the message box.

This way, we can use the VBA CSTR function to convert any data type to String data type.

Example #2: Convert Date Data Type to String Data Type

 We often work with dates, which is one of the data types available in VBA. For example, look at the following VBA code.

Code:

Sub CSTR_Example2()
Dim MyDate As Date
MyDate = “2023-05-06”
MsgBox MyDate
End Sub

The variable “MyDate” has “Date” as the data type, and in the following line, we have assigned the date value to this variable, i.e., MyDate = “2023-05-06.”

Now in the message box, we are showcasing the value assigned to the variable “MyDate.” We get the following result.

Currently, the value shown in the message box is a date data type. However, by using the VBA CSTR we can convert the date data type to String data type.

The following code will convert the date data type value to String data type using the CSTR conversion function.

Code:

Sub CSTR_Example2()
Dim MyDate As Date
MyDate = “2023-05-06”
Dim Converted_Date As String
Converted_Date = CStr(MyDate)
MsgBox Converted_Date
End Sub

Compared to the previous code, we have defined an additional variable, “Converted_Date,” with the string data type. Next, we have used the VBA CSTR function to convert the first variable value from date to String.

Now, in the message box, we will see the following value.

The value in the message box is similar to the previous one, but this value has been converted from date to string because of the VBA CSTR conversion function.

VBA CSTR Format Date: The result of the date value seen in the message box is a date format depending on your system. For example, the date result shown in the message box above is of the “dd-mm-yyyy” format. However, we can convert this to any date format using the VBA CSTR FORMAT Date.

The following code will convert the date value from the date data type to the string data type along with the specified date format in excel.

Code:

Sub CSTR_Example2()
Dim MyDate As Date
MyDate = “2023-05-06”
Dim Converted_Date As String
Converted_Date = CStr(Format(MyDate, “yyyy-mmm-dd”))
MsgBox Converted_Date
End Sub

Inside the VBA CSTR function, we have used the VBA FORMAT function to format the date to “yyyy-mmm-dd.”

Now the result looks like this.

Example #3: VBA CSTR Boolean Value

Usually, Boolean values contain only TRUE and FALSE. Therefore, these two values are treated as Boolean data types. However, using the VBA CSTR function, we can convert the Boolean value to a string data type.

For example, look at the following code.

Sub CSTR_Example3()
Dim B1_Value As Boolean
Dim B2_Value As Boolean
B1_Value = True
B2_Value = False
MsgBox B1_Value & vbNewLine & B2_Value
End Sub

Two variables, “B1_Value” and “B2_Value,” have the data type “Boolean” and are assigned two Boolean values, “True” and “False,” respectively.

In the message box, we show the assigned Boolean values as follows.

Now, we can convert them to string by applying the VBA CSTR function as follows.

Sub CSTR_Example3()
Dim B1_Value As Boolean
Dim B2_Value As Boolean
B1_Value = True
B2_Value = False
MsgBox CStr(B1_Value) & vbNewLine & CStr(B2_Value)
End Sub

The result shown in the message box remains the same, i.e., TRUE and FALSE, but the data type of these two values no longer be a Boolean because of the VBA CSTR function used to convert them to String data type.

Important Things To Note

• VBA CSTR is a function available only in VBA. It is not a part of the worksheet function library.
• We must assign the value the VBA CSTR function returns to another variable; otherwise, it will throw an error.
• VBA CSTR Array must be used carefully to avoid confusion and errors.
• Any numerical value assigned to the string data type variable will only be treated as a string value.

Frequently Asked Questions (FAQs)

Recommended Articles

Guide to VBA CSTR. Here we explain how to use CSTR function to convert any value to string data in Excel VBA with examples. You may learn more from the following articles –

• VBA Format
• VBA ReDim
• VBA CLng