O'Reilly logo

Writing Word Macros, Second Edition by Steven Roman PhD

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

VBA String Functions

Here are a handful of useful functions that apply to strings (both constants and variables):

The Len function

The Len function returns the length of a string; that is, the number of characters in the string. Thus, the code:

Len("January Invoice")

returns the number 15.

The UCase and LCase functions

These functions return an all uppercase or all lowercase version of the string argument. The syntax is:

UCase(string)
LCase(string)

For instance:

MsgBox UCase("Donna")

will display the string “DONNA”.

The Left, Right, and Mid functions

These functions return a portion of a string. In particular:

Left(string, number)

returns the leftmost number characters in string and:

Right(string, number)

returns the rightmost number characters in string. For instance:

MsgBox Right("Donna Smith", 5)

displays the string “Smith”.

The syntax for Mid is:

Mid(string, start, length)

This function returns the first length number of characters of string, starting at character number start. For instance:

Mid("Library.doc",9,3)

returns the string “doc”. If the length parameter is missing, as in:

Mid("Library.doc",9)

the function will return the rest of the string, starting at start.

The Instr function

The syntax for this very useful function is:

Instr(Start, StringToSearch, StringToFind)

The return value is the position, starting at Start, of the first occurrence of StringToFind within StringToSearch. If Start is missing, then the function starts searching at the beginning of StringToSearch. For instance:

MsgBox ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required