Resolved: Method ‘Sheets’ of Object ‘_Global’ Failed

We have a complex macro enabled spreadsheet. Today we got a ticket from the business user that Excel is throwing error:

“Run-time 1004 error: Method ‘Sheets’ of object ‘_Global’ failed.”.

I could not find an answer even after searching online for hours. None of the online discussion was giving the root cause of the error. Everyone were discussing how to change the code to fix the error.

The spreadsheet was working fine for couple of years but it suddenly stopped working. I suspected that it wasn’t the code but it was something else. I needed the root cause of this error.

So What is “Sheets” of Object “_Global”?

Finally, I found the root cause of this error at this link. Author discusses that Sheets is shortcut of Application.ActiveWorkbook.Sheets.

Now the error makes sense. In simple words, this error is trying to say that “Sheets” is trying to resolve to “Application.ActiveWorkbook.Sheets” but it encountered “Nothing” in the object chain.

So either “Application = Nothing” or “Application.ActiveWorkbook = Nothing”. As “Application” would always be available, the main culprit for this error is “Application.ActiveWorkbook”. Value of “Application.ActiveWorkbook” is “Nothing” and hence method “Application.ActiveWorkbook.Sheets” is not available which is the root cause of this error.

Why Application.ActiveWorkbook Becomes Nothing

Application.ActiveWorkbook represents currently open, visible and focused Excel spreadsheet.

Here are few reasons why Application.ActiveWorkbook becomes Nothing and hence it throws an error in VBA code.

• Excel file (i.e. active workbook) is in PROTECTED VIEW mode.
• Excel file (i.e. active workbook) is hidden.
• Excel file (i.e. active workbook) looses focus.

3 Solutions to Resolve This Issue

You may encounter one of the above issues. Depending on your issue, try following solutions depending on your situation.

• If Excel file is in PROTECTED VIEW, make sure to make it editable.
• If the Excel file is not visible, go to View > Windows > Unhide and make sure to unhide your Excel file.
• If for any reason your Excel file looses focus, make sure it is visible and the focus is on your current Excel file.

Solution For My Issue

In my case, VBA code was executing upon opening the file. I temporarily commented out the code which was throwing an error. Once the code executed, it still did not show the Excel file.

In Developer Mode, I could see the file is open but it was not visible. I figure out that somehow Excel file was accidentally hidden by the user.

I went to View Ribbon > Window > Unhide and unhide the Excel spreadsheet.

Once the file is visible, it successfully executed the code.