Performing Tokenized Replacements in a String in Access 2010

Performing Tokenized Replacements in a String in Access 2010

Office 2010

Office Visual How To

Summary:  Microsoft Access 2010, along with Visual Basic for Applications (VBA), makes it easy to perform a tokenized replacement in a string.

Last modified: March 09, 2015

Applies to: Access 2010 | Access Services | Office 2010 | VBA

Published:  June 2011

Provided by:  Ken Getz, MCW Technologies, LLC


In this article, you learn the steps of using Visual Basic for Applications (VBA) to perform tokenized replacements in a string. Looking for tokens such as %1, %2, and %3, the code replaces values in an ordered list of values in a string. This technique makes it easy to construct a calculated string without having to perform string concatenation.

Code It

With a bit of effort, the Replace function built into VBA makes it easy to create a replacement system in which the code replaces numbered placeholders in a string with ordered values in a list. The code looks for tokens such as %1 and %2 and replaces each token with the parameter in the same ordinal position. (The first parameter replaces %1, the second parameter replaces %2, and so on.)

Set up the Code

To use the sample code provided with this article in your own applications, you must start by either adding a module to your database or finding an existing module into which to place the code. (Put these procedures in a standard module, not a class module, to get the most use from them.) To create a new module, in Access 2010, click the Create tab, and then in the Macros & Code group, click Module. This action opens the VBA editor with the new module selected. To use an existing module, in the Macros & Code group, click Visual Basic to load the VBA editor, and then select the module. Either way, copy the code from the sample database into the selected module. (If you prefer, you can also only import the module, TokenReplaceCode, from the sample database into your own database.)

Using the Sample Code

The sample module includes a single function, TokenReplace, which accepts two parameters:

  • strIn contains the incoming template string, which should include tokens such as %1 and %2.

  • varItems contains an array of strings that the function inserts into the template string, replacing %1 with the first string, %2 with the second, and so on.

The function returns the new string, with the tokens replaced by the strings from the string array.

Using either the sample database or your own database that has the code inserted, you can interact with the function by using the Immediate window. Press Ctrl+G to display the Immediate window, and type the following line of code. Press Enter to display the function results in the Immediate window.

? TokenReplace("This %1 a ""%2"" of %3 this works.", "is", "test", "how")

This test returns the following string:

This is a "test" of how this works.

The function replaced the %1 token with the word "is", %2 with the word "test", and %3 with the word "how". Obviously, this is a simple example—you are more likely to use the TokenReplace function when you have some data that you want to insert into a string without using string concatenation. Try the following example in the Immediate window. (Type the entire text before pressing Enter; the line break is for readability only.)

? TokenReplace("Order %1 was placed on %2. Order %1 will ship on %3.",
  12345, #12/13/2012#, #12/14/2012#)

After you press Enter, the Immediate window displays the following output.

Order 12345 was placed on 12/13/2012. Order 12345 will ship on 12/14/2012.

As you can see, the function handles converting data into strings, and also handles repeated use of tokens within the same string template.

Examining the Sample Code

The code in the TokenReplace function is actually very simple. The function accepts two parameters: a string, and an array declared by using the ParamArray keyword. This keyword lets you specify as many parameters as you want when you call the function, and because of this, the ParamArray keyword can be applied only to the final parameter in a list of parameters. The first parameter contains the template string, and the rest of the parameters contain the data to be replaced into the template. The function returns the newly modified string.

Public Function TokenReplace(ByVal strIn As String, _
 ParamArray varItems() As Variant) As String

The function should not encounter errors. However, if it does, it includes standard error handling, which ignores the error and returns the current status of the modified string.

On Error GoTo HandleErr
    ' Code removed here...
    ' Return the modified string.
    TokenReplace = strIn
    Exit Function
    ' If any error occurs, simply return the
    ' string as it currently exists.
    Select Case Err.Number
        Case Else
            ' MsgBox "Error: " & Err.Description & _
            '  " (" & Err.Number & ")"
    End Select
Resume ExitHere

In the previous code example, the code that performs the work was removed. That code resembles the following.

Dim strReplace As String
Dim strItem As String
Dim intI As Integer

For intI = LBound(varItems) To UBound(varItems)
    strReplace = "%" & (intI + 1)
    strItem = varItems(intI)
    strIn = Replace(strIn, strReplace, strItem)
Next intI

The code starts by creating three variables that contain temporary values.

Dim strReplace As String
Dim strItem As String
Dim intI As Integer

Next, the code loops through each array of replacement strings.

For intI = LBound(varItems) To UBound(varItems)
  ' Code removed here…
Next intI

Because the LBound function returns a 0-based array limit, the code must add 1 to the intI value to use tokens starting with %1. The code inside the loop takes three actions. It starts by creating the replacement token.

strReplace = "%" & (intI + 1)

Next, the code retrieves the item to be put into the template.

strItem = varItems(intI)

Finally, the code inside the loop replaces the token (%1, %2, and so on) with the associated replacement string.

strIn = Replace(strIn, strReplace, strItem)

The code loops through all the values in the ParamArray input parameter, replacing the associated token with the value from the array.

Read It

The function described here works not only in Microsoft Access 2010, but also in any modern VBA host product. This includes the products in Microsoft Office 2003 through Office 2010. You might wonder what happens if you have a different number of replacement strings than replacement tokens—the function handles this situation reasonably. If there are more replacement values than tokens, the replacements are ignored. If there are more tokens than replacement values, the tokens remain in the output string. (Try calling TokenReplace with a template string that includes tokens, and supply no values as replacements. The result is the template string.)

We recommend taking the time to examine all the VBA string-related functions. They are surprisingly flexible and handle many situations you might not expect.

See It

Watch the video

Watch video

Length: 07:49

Click to grab code

Grab the Code

Explore It


About the Author

Ken Getz is a senior consultant with MCW Technologies and splits his time between programming, writing, and training. He specializes in tools and applications written in Visual Studio and Visual Basic. Ken is the coauthor of several best-selling books. They include Access 2002 Developer's Handbook with Paul Litwin and Mike Gunderloy, Visual Basic Language Developer's Handbook with Mike Gilbert, and VBA Developer's Handbook with Mike Gilbert (Sybex). He co-wrote several training courses for Application Developer's Training Company (, including seminars for Visual Basic .NET, ASP.NET, Access 2000 and 97, and Visual Basic 6 and 5. He has also recorded video training for AppDev, covering Visual Basic, ASP.NET, Visual Basic 6, and Access 2000 and 97. Ken is a frequent speaker at technical conferences and has spoken often at Microsoft's Tech·Ed conference. Ken is also a technical editor for Access-VB-SQL Advisor magazine and a columnist for Informant Publications' asp.netPRO magazine.

© 2015 Microsoft