VBA & Macro Problems

Fix Excel VBA InStr Cannot Find Text in Worksheet Names

Amos GikundaAmos Gikunda Oct 15, 2026 869 views

Question details

The user is experiencing an issue where the Excel VBA InStr function fails to locate specific text strings within dynamically generated or existing worksheet names.

How to Fix VBA InStr Cannot Find Text in Excel Worksheet Names
Product
Excel VBA
Device & OS
not provided
Scenario
Searching for a specific string within worksheet names using the InStr function in a VBA macro.
Observed behavior
The InStr function returns 0 (not found) even when the worksheet appears to exist, usually due to subtle naming discrepancies like hyphens versus underscores, or hidden spaces.
Before you start

Before debugging your macro, ensure you have enabled the Developer tab in your spreadsheet software and have access to the Visual Basic Editor (VBE) to inspect your code.

Solution 1Recommended

Verify and Correct Exact String Matching

Ensure the search string used in the InStr function matches the actual worksheet name exactly, paying close attention to hyphens and underscores.

The InStr function is highly literal. A common pitfall is using a hyphen in the VBA code (e.g., VOL-WTS-AREA) while the actual sheet name uses an underscore (e.g., VOL_WTS_AREA).

1
Open the VBA Editor

Press ALT + F11 on your keyboard to open the Visual Basic Editor.

2
Locate the InStr function

Find the module and the specific line of code containing your InStr check, such as InStr(1, ws.Name, "VOL-WTS-AREA").

3
Inspect the actual tab name

Go back to your workbook and carefully inspect the spelling and punctuation of the worksheet tab in question.

4
Update the VBA string

Correct the string in your VBA code so that the underscores, hyphens, and characters perfectly match the worksheet tab name.

Verify and Correct Exact String Matching
Pro Tip: Copy the worksheet name directly from the tab by double-clicking it and paste it into your VBA code to avoid manual typing errors.
Advanced Macro Support

Debug Macros and VBA Code Easily in WPS Spreadsheet

WPS Office provides robust support for VBA macros, allowing you to seamlessly run, debug, and edit your scripts. You can easily fix string matching issues and manage worksheet names with a familiar developer interface.

  1. 1. Open your workbook: Open your macro-enabled workbook (.xlsm) in WPS Spreadsheet.
  2. 2. Access the Developer Tab: Navigate to the 'Developer' tab on the top ribbon menu.
  3. 3. Open Visual Basic Editor: Click 'Visual Basic' to open the built-in VBA Editor.
  4. 4. Fix the string mismatch: Locate your InStr code and modify the string to match the exact worksheet name.
  5. 5. Save and run: Save your changes and run the macro directly within WPS Spreadsheet.
Fully compatible with Microsoft Excel VBA syntax and macro scriptsBuilt-in Visual Basic Editor for easy macro debuggingLightweight and fast execution for heavy data processingFree to download with comprehensive spreadsheet features
microsoft office alternative - wps office

Frequently Asked Questions

Why does VBA InStr return 0 even when the text is in the worksheet name?

This typically happens due to exact match failures. Common culprits include case sensitivity, trailing spaces, or substituting underscores for hyphens (e.g., VOL_WTS vs VOL-WTS).

How do I make the InStr function case-insensitive?

You can make InStr case-insensitive by adding vbTextCompare as the fourth argument in the function, for example: InStr(1, ws.Name, "Text", vbTextCompare).

Can compiler errors or missing references cause InStr to fail?

Yes, if your VBA project has missing references or compiler errors, built-in string functions like InStr might fail to compile or execute properly. You can check the 'References' under the 'Tools' menu in the VBA editor to clear missing items.

How can I check for hidden characters in my Excel worksheet names?

You can use the Len() function in VBA to check the character count of the worksheet name and compare it to the visible text length, or use Debug.Print with framing characters (like asterisks) to spot trailing spaces in the Immediate Window.