VBA & Macro Problems

How to Keep the SW Prefix in Word VBA Formatting Searches

Maira MehtabMaira Mehtab Sep 27, 2026 873 views

Question details

The user needs to modify a Word VBA macro so that it retains the 'SW' prefix when searching for and formatting dollar amounts.

Product
Word
Device & OS
not provided
Scenario
Using a VBA macro with regular expressions (wildcards) to find and format dollar amounts preceded by the specific string 'SW'.
Observed behavior
The VBA macro removes the 'SW' prefix during the replacement operation because the regular expression grouping does not include the prefix in the replacement match.
Before you start

Ensure you have access to the Developer tab in your word processor and that your VBA editor is open to the module containing the regular expression macro.

Solution 1Recommended

Update the Regular Expression Grouping in VBA

Adjust the parentheses in your macro's search text to include the 'SW' prefix inside the capture group, ensuring it is retained during the replacement.

By default, if a prefix is left outside the capture group parentheses in your `.Text` assignment, the replacement operation will overwrite and drop it. Moving the prefix inside the parentheses ensures the entire string is captured and kept intact during the formatting process.

1
Open the VBA Editor

Press Alt + F11 in your word processor to open the Visual Basic for Applications (VBA) editor.

2
Locate the macro code

In the Project Explorer, find and open the module and subroutine containing your search and replace formatting logic.

3
Modify the .Text property

Change the search text string from .Text = "SW ($[0-9,]{1,})" to .Text = "(SW $[0-9,]{1,})". This places the 'SW' part inside the capturing group.

4
Run the macro

Save your code and press F5 to run the macro. The 'SW' prefix will now remain intact alongside the formatted dollar amount in your document.

Understanding VBA Wildcard Syntax: Parentheses in VBA wildcard searches dictate which parts of the matched string can be referenced or kept during replacement. Everything inside the parentheses is treated as a single block that can be preserved.
Advanced VBA Support

Format Documents Automatically with WPS Writer Macros

WPS Office fully supports VBA macros, allowing you to seamlessly run, edit, and create complex regular expression searches just like in Microsoft Word.

  1. 1. Enable Developer Tools: Open WPS Writer, navigate to the Developer tab on the ribbon, and click on 'Macro Security' to enable macros.
  2. 2. Access the VBA Editor: Click 'Visual Basic' in the Developer tab or press Alt + F11 to launch the macro editor.
  3. 3. Edit and Execute: Paste your updated regular expression macro code into a new or existing module, then run it to format your document seamlessly.
Fully compatible with Microsoft Word VBA scripts and syntax, including wildcard searches.Lightweight installation with fast execution of complex document formatting tasks.Built-in Developer tools for debugging and editing macros easily.
microsoft office alternative - wps office

Frequently Asked Questions

Why does my VBA macro delete text during a replace operation?

If you use wildcards (regular expressions) in Word VBA, any text matched by the search property but not included in the replacement string or capture groups will be overwritten and deleted. Ensuring all necessary text is inside the capture group parentheses prevents this.

What does the {1,} mean in the VBA search string?

The {1,} quantifier in VBA wildcard searches indicates that the preceding character or range (in this case, numbers and commas [0-9,]) must appear at least once, up to an unlimited number of times.

How do I reference a captured group in a VBA replacement?

In the .Replacement.Text property, you can reference captured groups using a backslash followed by the group number, such as \1 for the first set of parentheses defined in your search text.