Return to VBA Code Examples

VBA Wildcards

This tutorial will demonstrate how to use wildcards in VBA.

Wildcards are used in all programming languages and database application like SQL Server. A wildcard can be defined as a symbol that is used to replace one or more characters in a text string.   For example this text string – “mo*” – will find the words mom, mouse, moose, mommy etc; while this text string “mo?” will only find the word mom as the wildcard ? replaces only one character.

We use wildcards with the Like Operator which is an easier alternative to VBA Regex.

Using the Asterix (*) Wildcard in VBA

The Asterix wildcard replaces one or more characters in a VBA string.

Lets look at the following range of cells in Excel:

VBAWildcard Range

By using an Asterix wildcard in our VBA code, we can find all the Firstnames that begin with “M” and change the color of the text to red.

We have therefore looped through the range and found all the first names that begin with the letter M as our wildcard string is “M*

The result of running the code above is shown below.

VBAWildcard Range Red

 

If we were to use the wildcard string “Ma*” – then only the first names in B3 and B4 would change.

Using the Question Mark (?) Wildcard in VBA

The question mark will replace a single character in a VBA string.

Consider the following data:

VBAWildcard Range QMark

 

We can use the wildcard string “?im” to find any first names that end in “im”

The result of running this code is shown below:

VBAWildcard Range QMark Result

Using [char list]as a Wildcard

The example above can be modified slightly to allow us to use the question mark, in addition to a character list of allowed characters.  The wildcard string can therefore be amended to “?[e-i]m” where the first character can be anything, the second character has to be a character between e and i and the last letter has to be the character “m”.  Only 3 characters are allowed.

The result of this code would be:

VBAWildcard Range CharString

Using the hash (#) Wildcard in VBA

The hash (#) wildcard replaces a single digit in a VBA string.   We can match between 0 to 9.

The code above will loop through all the cells in the Range (“B3:E8”) and will change the color of the text in a cell to RED if a double-digit number is found in that cell.

VBAWildcard Range Numbers

In the example below, the code will only change the number if the last number is a 9.

VBAWildcard Range SpecificNumber

 

VBA Coding Made Easy

Stop searching for VBA code online. Learn more about AutoMacro - A VBA Code Builder that allows beginners to code procedures from scratch with minimal coding knowledge and with many time-saving features for all users! vba save as


Learn More!