Learn about VBA String Operators
In VBA, the primary string operator is the concatenation operator (&), which is used to join two or more strings together. There is also the + operator, which can also be used for concatenation but is less common due to potential ambiguity when dealing with numeric and string types.
VBA String Operators:
- Concatenation using &: Joins two strings together.
- Concatenation using +: Also joins strings, but care should be taken as it can lead to type conversion issues if mixed with numeric types.
Key Difference Between & and + for Strings:
- & (Preferred): Always treats the operands as strings, making it a safer choice when concatenating.
- +: Might cause errors or type conversion issues if one of the operands is numeric, as + is also the addition operator.
For string operations like finding length, extracting substrings, and replacing content, VBA provides built-in functions rather than operators, such as Len(), Mid(), and Replace()
Following are String methods that are supported in VBA. Please click on each one of the methods to know in detail.
- LCase : This function convert into lower case
Ex.
Private Sub Lcase_demo()
cag = “Anuj Kumar”
msgbox(“Line 1 : ” & LCase(cag))
cag = “MS office”
msgbox(“Line 2 : ” & LCase(cag))
cag = “microtech”
msgbox(“Line 3 : ” & LCase(cag))
End Sub
The result will come as follow
Line 1 : anuj kumar
Line 2 : ms office
Line 3: microtech
- UCase : converting the entered string into UPPER case letters
Example :
Private Sub UCase_demo()
var = ” Anuj Kumar ”
msgbox(“Line 1 : ” & UCase(var))
var = “MS office ”
msgbox(“Line 2 : ” & UCase(var))
var = ” microtech ”
msgbox(“Line 3 : ” & UCase(var))
End Sub
The result will come as follow
Line 1 : ANUJ KUMAR
Line 2 : MS OFFICE
Line 3: MICROTECH
- Left: The Left function returns a specified number of characters from the left side of the given input string
Example:
Private Sub Constant_demo_Click()
Dim cag as Variant
cag = “Anuj Kumar”
msgbox(“Line 1 : ” & Left(cag,2))
cag = “Sanjay Kumar”
msgbox(“Line 2 : ” & Left(cag,5))
cag = “Vijay Kumar Jain”
msgbox(“Line 3 : ” & Left(cag,9))
End Sub
The result will come as follow
Line 1 : An
Line 2 : Sanja
Line 3: Vijay Kum
