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.
Recommended Articles
Empower your team. Lead the industry.
Get a subscription to a library of online courses and digital learning tools for your organization with Udemy Business.