Performing Tokenized Replacements in a String in Access 2010
Summary: Microsoft Access 2010, along with Visual Basic for Applications (VBA), makes it easy to perform a tokenized replacement in a string.
Last modified: September 12, 2012
Applies to: Access 2010 | Access Services | Office 2010 | VBA
Published: June 2011
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.
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:
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.
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... ExitHere: ' Return the modified string. TokenReplace = strIn Exit Function HandleErr: ' 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.
Next, the code loops through each array of replacement strings.
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.
Next, the code retrieves the item to be put into the template.
Finally, the code inside the loop replaces the token (%1, %2, and so on) with the associated replacement string.
The code loops through all the values in the ParamArray input parameter, replacing the associated token with the value from the array.
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.
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 (www.appdev.com), 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.