vba string functionsVisual 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 ( 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.

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 (“String”, n)
Right (“Good Morning”, 4)    returns “ning”.
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(“String”, n)

where n is the specified number of characters. Here’s an example

Left(“Good Morning”, 4) returns “Good”
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(“Good Morning”) returns “good morning”
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(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(1, “Good Morning”,”Morning”) returns the integer 6.

List of other VBA string functions

Here we have put together some of the other most commonly used VBA string functions. Take a look.

Ltrim(“    Good Morning”) returns “Good Morning”
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.

Excel VBA students also learn

Empower your team. Lead the industry.

Get a subscription to a library of online courses and digital learning tools for your organization with Udemy for Business.

Request a demo