VBA Errors in Excel 2016

[automationamateur]

I was recently upgraded from Excel 2013 (32 Bit) to Excel 2016 (64 Bit) on windows 7 enterprise OS. Macros I have been using for years have all the sudden stopped working and erroring out on the oddest code. Any help is greatly appreciated, I'm not sure what I am missing. Thanks!

This existing code that is extremely simple and has worked for years now creates error, "Run-time error '-2147417848 (80010108)':Method 'Delete' of object' _Worksheet' failed"

Sheets("Sample Sheet").Delete

When I run macro recorder to accomplish the same task Excel 2016 now creates this:

Sheets("Sample Sheet").Select
ActiveWindow.SelectedSheets.Delete

 

[JLGWhiz]

Sheets("Sample Sheet").Delete

Without seeing the rest of the code, I'm guessing that you need a workbook reference to qualify the sheet.

 

[automationamateur]

Thanks for your response. Even if this is my only line of code it works in 2013 but not in 2016? I guess my question is more 'what changed' between versions? Am I going to have to rewrite every stored macro I have because of compatibility issues? Why would I now need a "workbook reference to qualify the sheet" in 2016 but not in 2013. Thanks for the help.

Without seeing the rest of the code, I'm guessing that you need a workbook reference to qualify the sheet.

 

[JLGWhiz]

I don't believe there is much difference in compatibility. It all depends on the how the code is structured, its environment and how external references are handled. It is helpful if the applicable code is posted for review and analysis to resolve this type of issue. If you have an enormous amount of code, just post the declarations and then the part with the issue. It is difficult to analyze a cause from a general description of an issue. For one thing, the error number indicates that it is generated external to Excel since in excel it would normally be a 1004 run time error. But it is difficult to say without seeing the code.

 

[automationamateur]

The only code there is - is what I previously posted. that single line of code works with excel 2013 but not with 2016 all else equal.

 

[JoeMo]

What happens if you try this:
1. Remove or comment out the code that errors.
2. Edit the recorded macro, by removing the line:
ActiveWindow.SelectedSheets.Delete
and changing the ".Select" to ".Delete" on the line:
Sheets("Sample Sheet").Select
3. Ensure that a sheet named "Sample Sheet" is present and run the edited, recorded macro.

 

[automationamateur]

JoeMo, That works, code runs fine. Any idea as to why it works since it ends up being the exact same code as was originally there, causing the error?

 

[JoeMo]

JoeMo, That works, code runs fine. Any idea as to why it works since it ends up being the exact same code as was originally there, causing the error?

Most likely, the code that errors spells "Sample Sheet" differently than what is on the tab. Perhaps there are extra spaces in the code or the tab.

 

[automationamateur]

Most likely, the code that errors spells "Sample Sheet" differently than what is on the tab. Perhaps there are extra spaces in the code or the tab.

Shoot, I thought you were on to something. Unfortunately, that is not it. It is the same workbook, code, etc. Literally nothing has changed except switching from excel 2013 to 2016. Any other takers?

 

[JoeMo]

Shoot, I thought you were on to something. Unfortunately, that is not it. It is the same workbook, code, etc. Literally nothing has changed except switching from excel 2013 to 2016. Any other takers?

Copy the sheet name from your errant code and paste it to a cell (let's say A1 for example). Then copy the sheet tab name from a sheet that can't be deleted from the 2013 code and paste it to another cell (say B1 for this example). Now in C1 enter: =EXACT(A1,B1)

What do you get? If FALSE, then in D1 enter: =LEN(A1) and copy it to E1. Does D1 differ from E1?