How to Keep the SW Prefix in Word VBA Formatting Searches
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.
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.
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.
Press Alt + F11 in your word processor to open the Visual Basic for Applications (VBA) editor.
In the Project Explorer, find and open the module and subroutine containing your search and replace formatting logic.
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.
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.
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. Enable Developer Tools: Open WPS Writer, navigate to the Developer tab on the ribbon, and click on 'Macro Security' to enable macros.
- 2. Access the VBA Editor: Click 'Visual Basic' in the Developer tab or press Alt + F11 to launch the macro editor.
- 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.

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.




