The CStr VBA Function: How to Convert Different Expressions to Strings

Visual Basic for Applications or VBA is simple yet powerful programming language. It is valuable for creating macros that automate routine task in the MS Office suite of products. VBA contains a host of functions to manipulate strings and numbers. These are useful for creating simple as well as complex programs which can be reused. Strings are just an array of characters. The CStr() function is an extremely valuable feature of VBA. It allows you to convert any kind of expression into string format.  In this intermediate level tutorial, we walk you through the CStr VBA function.  We assume that you are familiar with the basics of Excel and VBA. However, if you want to brush up your knowledge on MS Excel VBA we suggest that you go through this introductory course to Excel VBA Macros.

What are Strings?

Strings are basically text. They are stored and treated as character arrays. You can perform several operations on strings such as calculating string length, reversing a string, comparing strings, string concatenation, finding and replacing characters in a string and more. Most programming language come inbuilt with useful and necessary string functions.  If you would like to look up more information on strings, you should take up this basic C programming course. Since C is one of the most popular programming languages, and also the precursor to many newer programming languages, strings are explained in great details in most C tutorials and courses.

What is Cstr() Function?

CStr()Function converts an expression into a string. Its syntax is as follows:

CStr(expression)

• Boolean– If the expression is Boolean then the Cstr() function returns a string containing a true or false.
• Date– This function returns a string which contains a date in the short-date format.
• Null– If the expression is null, a run-time error will occur.
• Empty– If the expression is empty, then the function will return an empty string (“”).
• Error– Will return a string that contains the word “error” along with an error code.
• Other numeric– The function will return a string that contains the number.

Here is an example to demonstrate how CStr() function works:

Sub Example()

    Dim DateHired As Date

    DateHired = #1/4/2014#

    ActiveCell = CStr(DateHired)

End Sub

Cstr() function will accept expressions of any data type. Here it converts the date value into a string value.

It is a fact that some programmers do not declare their variables until they are needed during the flow of the code.  It is a good programming practice declare all the program variables at the beginning of the program.

Example 1: Program to Search for a String in an Array

Dim Str_Array() As String = {"green", "blue", "yellow"}

Dim search_str As String = "blue"

Dim index as Long

For index = LBound(Str_Array) To UBound(Str_Array)

If (String.Compare(Str_Array(index),searchItem)=0) Then

MsgBox("Found: " + search_str + " at Index: " + CStr(index))

Next

In this program, we declare a variable to be of data type string. Lbound() function determines the beginning of the array while Ubound() function determines the end of the array. We loop through the array from the beginning to the end. In each iteration we compare the array element with the search item. If it evaluates to true, then message box displays the item along with the index value in the array.

You can look up some more VBA macro programming examples in this excellent VBA course.

Example 2: Program to Search for an Integer in an Array

Dim Int_Array() As Integer = {10, 28, 12}

Dim Int_Item as Integer = 12

Dim index as Long

For index = LBound(Int_Array) To UBound(Int_Array)

If (Int_Array(index) = Int_Item) Then

MsgBox("Found: " + Cstr(Int_Item) + " at Index: " + CStr(index))

Next

The above program searches for an Integer in the array. On success, it returns the integer value as string using CStr().

Example 3 Program to Count Vowels in a String

Dim New_letters(RichTextBox1.Text.Length) As Char

New_letters = RichTextBox1.Text.ToUpper.ToCharArray()

Dim count = 0

For Each letter In New_letters

Select Case letter

Case "A", "E", "I", "O", "U"

count += 1

End Select

Next

Label1.Text = "Vowel Count: " + CStr(count)

In this program, the function creates an array of character name letters. Then it copies the text from Richtextbox and converts it into uppercase. In the For loop we check whether each letter is a vowel. If they are then we increment the count variable by 1. In the end, count variable will contain the number of vowel occurrences.

Example 4: Program to Convert a Decimal Number into a Binary Number

Sub Conv2Bin()

Dim New_str As String

Dim int1 As Long

Int1 = Application.InputBox( Prompt:="Type the number you wish to convert and click OK.", _

Title:="Convert to Binary", Type:=1)

New_str = CStr(int1)

b = CBin(int1)

MsgBox "You entered " & New_str & "." & Chr(13) & Chr(13) _

& "Its binary value is " & Chr(13) & b

End Sub

Function CBin(Number1 As Long) As String

Dim NewTemp As Variant

NewTemp = 1

Do Until NewTemp > Number1

NewTemp = NewTemp * 2

Loop

Do Until NewTemp < 1

If Number1 >= NewTemp Then

CBin = CBin + "1"

Number1 = Number1 - NewTemp

Else

CBin = CBin + "0"

End If

NewTemp = NewTemp / 2

Loop

CBin = CStr(Val(CBin))

End Function

In this program we accept an integer value. We create a function CBin() and pass the decimal number as parameter. The function converts the decimal number into binary number. Finally we display the binary number. We initialize NewTemp to 1. Then we check whether it is greater than the integer parameter passed. If not in a loop we multiply it by 2 until it becomes greater than the decimal passed in CBin(0 function. We then use the NewTemp variable in order to append the binary digits “1” and “0” to CBin. We pass CBin string to VBA Val function which returns the numbers found in the string. In the end we get a binary equivalent of the decimal number.

If you want to learn more about VBA arrays, we recommend you to go through this specialized VBA course.

Example 5: Program to Remove Duplicate Array Rows

Sub remove_Duplicates()

Dim newArray(5) As String

Dim myCol As Collection

Dim i As Long

Set myCol = New Collection

newArray(0) = "bbb"

newArray(1) = "bbb"

newArray(2) = "ccc"

newArray(3) = "ddd"

newArray(4) = "ddd"

On Error Resume Next

For i = LBound(newArray) To UBound(newArray)

myCol.Add 0, CStr(strArray(i))

If Err Then

newArray(i) = Empty

dup = dup + 1

Err.Clear

ElseIf dup Then

newArray(i - dup) = newArray(i)

newArray(i) = Empty

End If

Next

For i = LBound(newArray) To UBound(newArray)

Debug.Print newArray(i)

Next

Initially we declare the variables. Then we add duplicate data to our array. Next we determine the beginning and end of the array. We loop through every element of the array to remove any duplicates.

As with any other major programming language, string functions form an integral and important part of VBA. Go through the examples and create your own unique code. Practice makes perfect and so is the case with mastering programming language. Hope this basic tutorial on VBA CStr was useful and informative. To advance your knowledge of using strings in your VBA programs we recommend you take this ultimate VBA course.