String is the data type indicated to store text in VBA.
Dim VarText As String 'Declaration of a variable of the data type String
To store text information in a String variable, the content must be enclosed in double quotation marks (" "):
Dim VarText As String VarText = "Some Name"
Although it is possible to store numeric information in text format, they will not behave in the same way as in numeric format. This will modify and limit their acceptable operators.
Dim VarText As String Dim VarNum As Single VarText = "6" + "3" MsgBox VarText 'This will result in "63" in text format VarNum = 6 + 3 MsgBox VarNum 'This will result in 9 VarText = "6" / "3" 'This will generate an error: this operator does not exist for String VarNum = 6 / 3 'This will make the division normally
The following are the main predefined functions for manipulating Strings in VBA.
The functions UCase and LCase make a string in uppercase and lowercase letters, respectively.
|UCase||Makes a string in uppercase letters||
|LCase||Makes a string in lowercase letters||
UCase and LCase are important functions for data standardization, since not all of your sources will be in the same case (uppercase or lowercase).
Another function to change the text case is the StrConv. (which can work as UCase, LCase and can also make the first letters uppercase.
When using StrConv(string, conversion) it is necessary to include the desired conversion type as an argument.
|StrConv(string,1)||vbUpperCase||Converts everything to uppercase|
|StrConv(string,2)||vbLowerCase||Converts everything to lowercase|
|StrConv(string,3)||vbProperCase||Converts the first letters to uppercase and the others for lowercase|
Here is an example of StrConv using vbProperCase:
The LTrim, RTrim and Trim functions are used to remove spaces at both ends of a String:
|LTrim||Removes the spaces in the left end||
Text Example ...
|RTrim||Removes the spaces in the right end||
|Trim||Removes the spaces in both ends||
The "..." was inserted after the variable to show the reduction of spaces.
vbCrLf is a function to add a line ("Enter") to the String.
Len is a function that returns the total number of characters in a String.
All characters are counted, including whitespace characters.
Len can be used as a simple way to validate information.
Sub orderNumber Dim onumber As String onumber = Inputbox("What is your order number?") If Len(onumber) = 10 Then 'All numbers must have 10 characters MsgBox "We will process your request shortly." Else MsgBox "Invalid code. Please check the inserted number" End If End Sub
The InStr and InStrRev functions search within a String a given occurrence and return (counting from left to right in number of characters) the position it appears.
|InStr||Search the occurrence position from left to right||
|InStrRev||Search the occurrence position from right to left||
These functions can be very useful when used together with Left and Right, as we will see later.
In both cases, InStr and InStrRev return:
- The position number, counting from the beginning of the String, that is, counting from left to right.
- The position of the first occurrence within the String,(InStr the left most, InStrRev the right most), starting from left to right.
If the returned value is 0, it means that the searched occurrence was not found within the String.
The Left, Right and Mid functions are used to extract parts of a line of text (String).
MsgBox Left("Text example", 3) 'Retornará "Tex" MsgBox Right("Text example", 3) 'Retornará "ple" MsgBox Mid("Text example", 3, 4) 'Retornará "xt e"
Left, Right and Mid functions (such as Len, InStr and InStrRev) can be used in conjunction with other functions for better results.
|Left||Extracts characters to the left of a given position||
|Right||Extracts characters to the right of a given position||
|Mid||Extracts characters from within the string, between given positions||
In the arguments of the Mid function the initial position and the number of characters to be extracted must be indicated.
The Replace function is used to swap parts of the text with a desired one.
Dim Text As String Text = "Always, Always study VBA" MsgBox Replace(Text, "Always", "Super")
It is possible to convert a data with type String to numeric with the help of functions:
Dim VarInt As Integer VarInt = CInt("34") 'Converts "34" to the integer 34 MsgBox VarInt+3 'Returns 37
|CByte||Converts a String to type Byte|
|CInt||Converts a String to type Integer|
|CLng||Converts a String to type Long|
|CSng||Converts a String to type Single|
|CDbl||Converts a String to type Double|
|CCur||Converts a String to type Currency|
|CDec||Converts a String to type Decimal|
You can convert a number to a String using CStr
VarText = CStr(33) 'Converts 33 to "33" MsgBox VarText & "a" 'Returns "33a"
SuperExcelVBA.com is learning website. Examples might be simplified to improve reading and basic understanding. Tutorials, references, and examples are constantly reviewed to avoid errors, but we cannot warrant full correctness of all content. All Rights Reserved.
Excel ® is a registered trademark of the Microsoft Corporation.
© 2023 SuperExcelVBA | ABOUT