VBA String Functions – A Primer

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.

1. Concatenation

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]

2. Formatting

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 ] )

For example:

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.

This course on VBA for Excel can show you other kinds of format manipulations in VBA.

3. Substrings

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

Left(“String”, n)

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".

Learn more about other VBA functions and macros with this course.

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

Replace(source_string,find_string,replacement_string).

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.