VBA & Macro Problems

How to Fix the Excel VBA Variable Not Set Error with Array Variables

WPS Content Manager Oct 7, 2026 869 views

Question details

The user needs to resolve an 'Object variable not set' error in Excel VBA that triggers when looping through an array of worksheet objects.

How to Fix the Excel VBA Variable Not Set Error with Array Variables

Product: Microsoft Excel
Device & OS: not provided
Scenario: Iterating through multiple worksheet objects stored in a VBA Array using a 'For Each' loop.
Observed behavior: The macro throws an error because the loop variable is declared as a Worksheet instead of a Variant, or because the worksheet variables placed into the array have not been properly initialized using the 'Set' keyword.

Before you start

Before modifying your VBA script, verify that all worksheet objects referenced in your code actually exist in the active workbook and are correctly spelled.

Solution 1Recommended

Declare the Loop Variable as Variant and Initialize Objects

Fix the error by declaring your 'For Each' loop variable as a Variant and ensuring all objects in the array are instantiated via the 'Set' command.

In Excel VBA, the Array() function inherently returns a Variant containing an array, rather than a strongly typed array (like an array of Worksheets). Therefore, any 'For Each' loop that iterates through this array must use a Variant loop variable.

Additionally, an 'Object variable not set' error (Run-time error 91) often occurs if the variables inside the Array function have not been assigned to an actual object before the loop runs.

Open the VBA Editor

Press Alt + F11 on your keyboard to open the Visual Basic for Applications (VBA) editor.

Locate the affected loop

Find the module and macro containing the 'For Each' loop where the variable not set error occurs.

Change the loop variable type

Modify the declaration of your loop variable to a Variant. For example, change 'Dim ws As Worksheet' to 'Dim ws As Variant'.

Initialize worksheet variables

Before the loop executes, ensure every worksheet variable is initialized. Add lines like: 'Set dropWorksheet = ThisWorkbook.Sheets("Drop")' for each variable in the array.

Execute the updated loop

Format your loop correctly using: 'For Each ws In Array(dropWorksheet, crewWorksheet, supplyWorksheet)'. Run the code to verify the error is resolved.

Declare the Loop Variable as Variant and Initialize Objects

Check all referenced variables: If you use variables such as 'new_dropWorksheet' inside the loop, verify they are also declared and assigned prior to being referenced.

Seamless VBA Support

Use WPS Spreadsheet to Edit and Run VBA Macros

WPS Office provides excellent built-in support for VBA macros, allowing you to easily write, edit, and debug your automated Excel tasks in a familiar and lightweight environment.

1. Open your macro-enabled workbook: Launch WPS Spreadsheet and open your .xlsm file.
2. Enable macros: Click the 'Enable Macros' prompt that appears at the top of the workspace.
3. Open the Developer tab: Navigate to the Developer tab on the top ribbon and click the 'VBA Editor' button.
4. Debug and fix variables: Update your Worksheet loop variables to Variant, apply your 'Set' statements, and press F5 to execute the code smoothly.

Fully compatible with Microsoft Excel .xlsm and .xlsb macro formatsBuilt-in VBA editor makes it easy to debug object variable errorsFree and lightweight alternative to heavy Microsoft Office installations

microsoft office alternative - wps office

Frequently Asked Questions

Why does the Array function in VBA return a Variant?

The VBA Array() function is designed to hold elements of diverse data types. To accommodate this flexibility, it inherently returns a Variant containing an array instead of a specific, strongly-typed array.

What does 'Object variable or With block variable not set' mean?

This is Run-time error 91. It means your code is trying to interact with an object variable that hasn't been instantiated yet, or you forgot to use the 'Set' keyword when assigning an object reference to the variable.

How do I properly assign a Worksheet to a variable in VBA?

You must use the 'Set' keyword for object variables. For example, write 'Set mySheet = ThisWorkbook.Sheets("Data")' instead of just 'mySheet = ThisWorkbook.Sheets("Data")'.

Can I use a Worksheet variable instead of Variant for arrays?

If you use the Array() function, you must use a Variant loop variable. However, if you explicitly declare a worksheet array (e.g., 'Dim mySheets(1 To 3) As Worksheet'), you can loop through it using a Worksheet variable.

Excel & Data Problems