MS Word has a Replace() VBA function?

Apr 13, 2009 by Tony Chung in Technology

It astounds me that a company as large as Microsoft can’t develop better help systems to accompany their products. While most people complain that Microsoft merely releases lousy products, I am one of the faithful who believe that the products are fine, but badly, poorly, confusingly, terribly supported.

Case in point: MS Word’s string Replace() function, not discussed in any official VBA reference.

As part of my work, I get the opportunity to hack macros in Visual BASIC for Applications (VBA) to automate tedious Word processes. I’ve also had the pleasure of working with the slightly different VBA implementations for Excel and Access. After all, it’s just another programming language, right?

What upsets me is that when I compare Microsft’s documentation to looking up solutions to web development problems using on online references for PHP, HTML and CSS (and now JavaScript), and W3Schools, Microsoft sells developers short. The articles are highly detailed theoretical concepts lacking any useful substance. I know this explanation sounds vague. I’m just trying to get this off my chest so I can get back to work.

Replace the facts, ma’am

Take, for example, the relatively simple concept of replacing strings within strings. Every programming language provides a function to replace strings simply and easily. However, if you read only the Word documentation, you would get the idea that to perform any string replacement functions you need to involve the complex and oft complicated Find object.

Press <F1> to call for help within the VB project window

Press to call for help within the VB project window

Previous web searches showed me that Word 2003 had a Replace() function that is perfect for this cause. I archive this solution here for future reference.

How to use Replace()

Here’s an example of a simple script that replaces spaces with underscores. Ideally, we’d like to use a regular expression (RegExp) to remove non-alphanumeric characters, but for this demo we’ll just write a function:

Function replaceSpace(sText As String)
    replaceSpace = Replace(sText, " ", "_")
End Function

Test this function with a small Sub to feed it some text. In the absence of RegExp handling, if we know in advance the characters we need to filter out we could join the results of multiple Replace() calls and return the final version. This is the output from the Simple version.

Simple script to Replace() spaces with underscores

Simple script to Replace() spaces with underscores

If I learn anything about RegExp in the next little while I’ll update this post.