Visual Basic for Applications or VBA is an event-driven programming language. It is used to automate tasks in the Microsoft Office suite of products – especially repetitive tasks. It enables programmers use this tool to write effective and efficient macros. VBA contains a host of useful functions, on top of those present in Excel or the other MS Office products.
Today, in this beginner’s level tutorial we’ll walk you through the basics of string usage in VBA, and then take a look at the important VBA string functions. If you’re new to VBA, you should first take this introductory course to using macros with VBA.
What is a String
A string, in VBA, is a type of data variable which can consist of text, numerical values, date and time and alphanumeric characters. Strings are a frequently used to store all kinds of data and are important part of VBA programs. There are five basic kinds of string operations which are commonly used in VBA. Let’s take a closer look at them.
The Ampersand (&) sign concatenates two strings into a new string. For instance “Welcome”&”Home” yields the string “WelcomeHome”. The syntax for it is
string1 & string2 [& string3 & string_n]
There are bunch of formatting related functions you can use to represent the string in whichever way you need. A few such commonly used functions are:
- Format()- This function formats the string according to the format you’ve chosen from a predefined set. They syntax is
Format ( expression, [ format ] )
Format(Now(), “Long Time”) will display the current system time in the long time format.
Format(Now(), “Long Date”) will display the current system date in the long date format.
- FormatCurrency() – This formats an expression as a currency value.
- FormatDateTime() – This function returns a formatted date/time value.
- FormatNumber() – You can use this function to get a formatted number expression.
- FormatPercent() – Use this function to get a formatted percent expression.
As the name suggests, a substring function divides a string into different parts, per the specified criteria. Here are the functions which fall under this category.
- Right()- Breaks out a substring from the right side of the main string. The second argument is the specified number of characters. The syntax is
Right (“String”, n)
Right (“Good Morning”, 4) returns “ning”.
- Mid()- This function extracts a substring which contains a specified number of characters, from the specified position. The syntax is
Mid(String, position, n)
where position is the starting position of the string from which the substring is to be taken and n is the specified number of characters. Here’s an example
Mid(“Good Morning”, 7, 5) returns "ornin"
- Left()- Obtains a specified number of characters from the left side of a string. The syntax is
where n is the specified number of characters. Here’s an example
Left(“Good Morning”, 4) returns “Good”
- Split()- It splits the original string into substrings. The syntax is
Split (string, Delimiter, limit)
where string is the input string to be separated. Delimiter character separates the string into parts. The default delimiter is the space character ” “. Limit is the maximum number of substrings. Let’s see an example
Split ("This is a test string", " ") yields the following substrings "This" "is" "a" "test" "string".
4. Conversion Functions
These functions convert a string’s case – from uppercase to lowercase and vice versa.
- LCase()- Converts a string or character to lowercase.
LCase(“Good Morning”) returns “good morning”
- UCase()- COnverts the string to uppercase.
UCase(“Good Morning”) returns “GOOD MORNING”.
5. Find and Replace
These functions come in handy to search for certain substrings, and perhaps replacing them with specified strings.
- Replace() – Use this function to replace a substring with another substring. The syntax is
Here source_string is the source string, that you want to search in. find_string is the string to be searched for in the source string.
replacement_string will replace find in source string. For example:
Replace("wonderful", "der", "bat") will return "wonbatful"
- InStr()- Use this function to get the start position of the first occurrence of a string in another. For example:
Instr(1, “Good Morning”,”Morning”) returns the integer 6.
- InStrRev()- This function is similar to the previous one. Only difference is that it starts from the right side of the string.
List of other VBA string functions
Here we have put together some of the other most commonly used VBA string functions. Take a look.
- Asc , AscW()– This function is used to get an integer value which represents the ASCII code corresponding to a character.
- Chr , ChrW()– This function returns a character corresponding to the ASCII code passed as parameter.
- Filter()– Use this function to get a zero-based array which contains a subset of a string array.
- Join()- This function is used to join substrings together.
- Len()– Returns the length of a string, including the empty spaces.
- LSet– This function aligns the string to the left.
- Ltrim- Use this function to remove leading spaces. For example
Ltrim(“ Good Morning”) returns “Good Morning”
- RTrim()- This is similar to the Ltrim function and lets you remove trailing spaces.
- Trim()- This function is a super-set of the previous two. It removes both leading and trailing spaces from a string.
- RSet– This function align the string to the right.
- Space() – This function is used to put spaces in a string.
- StrComp()– This compares two strings and lets you know whether they are identical or different.
- StrConv()-This function converts the string as specified by the user.
- StrReverse()- This function reverses a string in place. For example
StrReverse("Hello") will return "olleH"
Hope this tutorial helped you learn more about VBA string functions. Do try them out on your own to get a better grip on them. Note that string manipulation is just one part of VBA. You can learn more about other VBA macros and functions with this ultimate VBA course.