Fix Excel VBA Type Mismatch with the Worksheets Collection in WPS Office
Question details
The user needs to resolve a VBA runtime type mismatch that occurs when assigning ActiveWorkbook.Worksheets to a variable declared as a Worksheets object, because the property actually returns a Sheets collection.
- Product
- WPS Spreadsheets VBA (Excel-compatible macro editor)
- Device & OS
- not provided
- Scenario
- A user is writing or debugging an Excel-compatible VBA macro in WPS Spreadsheets and assigns ActiveWorkbook.Worksheets to a typed variable.
- Observed behavior
- The macro raises the runtime error 'Type mismatch' because the Worksheets property returns a Sheets collection, not a Worksheets collection.
Open WPS Spreadsheets, press Alt+F11 to open the VBA editor, and locate the procedure where the type mismatch occurs. Note the exact Dim and Set statements before editing so you can verify the change.
Declare the Variable as Sheets Before Assigning ActiveWorkbook.Worksheets
Use this solution when you want the declared variable to match the Sheets collection returned by the Worksheets property.
The Excel VBA Worksheets property is named Worksheets but returns a Sheets collection. A Sheets collection can contain worksheets and chart sheets, so declaring the variable as Sheets matches the object type that the property actually returns.
In WPS Spreadsheets, press Alt+F11 to open the VBA editor.
Find the Dim statement that currently declares the variable as Worksheets, for example Dim wkshts As Worksheets.
Replace the declaration with Dim wkshts As Sheets.
Keep or add the assignment Set wkshts = ActiveWorkbook.Worksheets.
Press F5 or run the macro to confirm that the type mismatch error is resolved.
Declare the Variable as Object When the Returned Type Is Uncertain
Use this solution when you are not sure which collection type a property returns or when the macro targets different Excel-compatible hosts.
Fix the Worksheets Collection Type Mismatch Directly in WPS Spreadsheets
WPS Spreadsheets includes a Microsoft Excel-compatible VBA editor. You can open your macro-enabled workbook, update the mismatched declaration to Sheets or Object, and run the macro without switching to another Office suite.
- 1. Download WPS Office: Go to the official WPS Office website and download the free Windows version, then install it.
- 2. Open the macro-enabled workbook: Launch WPS Spreadsheets and open the .xlsm or Excel-compatible workbook that contains the VBA macro.
- 3. Open the VBA editor: Press Alt+F11 to open the built-in VBA editor.
- 4. Update the declaration: Find the mismatched variable and change Dim wkshts As Worksheets to Dim wkshts As Sheets or Dim wkshts As Object.
- 5. Run the macro: Run the macro from the VBA editor or from the WPS Spreadsheets Developer tab to confirm the error is gone.
Frequently Asked Questions
Why does ActiveWorkbook.Worksheets cause a VBA type mismatch?
The Worksheets property returns a Sheets collection, not a Worksheets collection. Assigning it to a variable declared as Worksheets triggers a type mismatch. Declare the variable as Sheets or Object.
Can I still use Dim wkshts As Worksheets if I only need worksheets?
No. Even if you only need worksheets, the returned collection is a Sheets collection, so the variable should be declared as Sheets. You can then access individual Worksheet objects from that collection.
Does this fix also work in WPS Spreadsheets?
Yes. Open the VBA editor with Alt+F11 in WPS Spreadsheets and apply the same declaration change to fix the error in Excel-compatible macros.
What is the difference between Sheets and Worksheets in VBA?
A Sheets collection can contain worksheets and chart sheets, while a Worksheets collection contains only worksheets. ActiveWorkbook.Worksheets returns a Sheets collection despite its name.
