How to Fix VBA InStr Returning Zero for Quotation Marks in Word
Question details
The user needs to find out why the VBA InStr function fails to locate quotation marks or apostrophes in a Word document.
- Product
- Word
- Device & OS
- not provided
- Scenario
- Running a VBA macro loop using the InStr function to search for specific punctuation marks like quotation marks, apostrophes, commas, and grave accents.
- Observed behavior
- The InStr function returns zero (character not found) even though the targeted punctuation marks are visibly present in the document text.
Ensure you understand how your word processor automatically formats typed text, as standard punctuation is often replaced with stylized alternatives instantly.
Update VBA Code to Check for Smart (Curly) Quotes
Modify your InStr function to search for both straight and curly punctuation character codes to account for Word's automatic formatting.
By default, word processors automatically convert straight quotes and apostrophes into smart (curly) quotes. The standard Chr(34) and Chr(39) only represent straight quotes, which causes the InStr function to fail when searching formatted text.
To fix this, you must expand your macro to look for character codes 145 through 148.
Press Alt + F11 on your keyboard to open the Visual Basic for Applications editor and locate your macro script.
Find the line of code where you are searching for Chr(34) (straight double quote) or Chr(39) (straight apostrophe).
Update your code to also search for left and right curly double quotes using Chr(147) and Chr(148).
Add search conditions for curly single quotes (apostrophes) by including Chr(145) and Chr(146) in your logic.
Run your macro again to verify that the InStr function now successfully returns the character positions for all punctuation types.
Disable Smart Quotes AutoFormat Setting
Prevent the software from converting straight quotes to curly quotes in the future so your standard macro code works without modification.
Write and Run VBA Macros Easily with WPS Office
WPS Office offers robust support for VBA macros, allowing you to automate tasks and run your customized scripts seamlessly. If you are experiencing formatting inconsistencies, writing and debugging your macros in WPS Writer is an excellent choice.
- 1. Enable Developer Tools: Open WPS Writer, navigate to the Options menu, and ensure the Developer Tools tab is enabled on your ribbon.
- 2. Access the VBA Editor: Click on the Developer tab and select 'Visual Basic' or press Alt + F11 to open the integrated VBA editor.
- 3. Write Your Macro: Insert a new module and paste your InStr macro code, ensuring you account for both straight and curly quote Chr codes.
- 4. Run and Test: Click the Run button or press F5 to execute your macro and verify it processes the document's punctuation correctly.
Frequently Asked Questions
What are the VBA character codes for curly quotation marks?
In VBA, the left single curly quote is Chr(145), the right single curly quote (apostrophe) is Chr(146), the left double curly quote is Chr(147), and the right double curly quote is Chr(148).
Why does Word change my straight quotes to curly quotes automatically?
Word includes an AutoFormat As You Type feature designed to make documents look more professional. It automatically replaces standard keyboard straight quotes with typographically correct curly (smart) quotes as you type.
How can I replace all curly quotes with straight quotes using VBA?
You can use the Document.Content.Find.Execute method in your VBA script to search for Chr(146) or Chr(148) and replace them with Chr(39) or Chr(34). This ensures uniformity across your document before running other InStr searches.
Does the InStr function distinguish between different types of apostrophes?
Yes, the InStr function performs exact character matching. It treats a straight apostrophe (Chr(39)) and a smart apostrophe (Chr(146)) as entirely different characters, causing your search to return zero if the wrong type is targeted.
