Old XLSM files crashing Excel; looking for help to troubleshoot, please

[EdNerd]

This is a large corporate outfit. I will never talk to the upper echelon IT people who push out security and program updates and expect us to just go with their flow. I'm a self-taught coder, gleaning stuff out of books and from forums like this one. Mebbe 15 years ago, with XL2003 (remember toolbars and buttons??), I created a macro-laden Excel file to help our local work section track workflow in various projects. (Note: I'm a tech writer, not a computer programmer. I've had one computer class in beginning Access database.) Over time, I've added a few bits and pieces of code to correct issues ("gee - I never expected someone to try to use it like that!"). But essentially it's still the same template. The file doesn't reach out to the internet for anything, but it does occasionally want to touch another local office network server - which may have changed addresses over the years.

Now, we are using Microsoft Office 365. And corporate - both global and local - IT dept keeps pushing security patches and other updates without any path for feedback for "how does this affect you?" And one by one our workflow tracker files are biting the dust. When we try to open them, Excel closes - not just that file, but any and every Excel file open on your machine just disappears. No error messages or pop-ups - just instantly gone. No rhyme or reason as to which one will die - no one user, or server location, or project demand set in common. Occasionally someone gets an error message about a corrupt file that needs repair, but not very often.

I can open the file in Protected view (hold Shift, right-click file and select Open in Protected view), and get the yellow banner on top with the button to Enable Editing. As soon as I click that, the file closes, even when holding Shift to prevent the auto-open macros from running. While in Protected view, I tried to Save As a non-macro file, but that command is disabled in Protected view. As soon as I selected Enable Saving, the file closed. I even tried the ZIP file trick, which worked to let me view the components. And I tried deleting all code in the VBA module and restoring the file - no go. At this point, my only solution is to try to slurp all the data off into a non-macro version of the workflow tracker, and then maybe add macro code back in one tidbit at a time to see where it chokes. I'm kinda suspecting it has something to do with the legacy Sharing mode and the changes to the Comments.

I've never had to deal with an issue on this scale. Our local IT people are good with network and Windows issues. But their answer to Excel macros issues is to delete and reinstall Office - all of it! If anyone has any suggestion to help me troubleshoot this, I'd really appreciate some help.

~~ Ed

 

[jkpieterse]

Happy to try to help, ping me through my website (email at the bottom of each page) Home of JKP Application Development Services, Excel consultant

 

[rlv01]

A couple of things:

IT dept keeps pushing security patches and other updates without any path for feedback for "how does this affect you?"

I'm quite familiar with the large corporate environment and I can tell you that little meaningful help will be coming from that quarter since they mainly regard 'amateur' VBA programmers as a nuisance and wish they would all go away. Your best bet is to learn how to adapt and fix/harden your code to limit the damage from things pushed from IT that might break it. Your first priority is to export all your code so you don't lose it.

then maybe add macro code back in one tidbit at a time to see where it chokes.

Based on where you are now, that is not an unreasonable approach. In this situation I like to comment out all my code an then add things (Subs, Functions, etc) one thing at a time until I find the one that is causing the problem. Then dive into that one with the debugger.

Lastly, take @jkpieterse up on his offer. The dude is an excel legend.

 

[EdNerd]

Happy to try to help, ping me through my website (email at the bottom of each page) Home of JKP Application Development Services, Excel consultant

Thank you so much for the kind offer. You are one of people I have learned so much from over the years as you have shared over the internet. Unfortunately, as much as I would love to take you up on your offer, I am unable to retain your services. If you want to PM me, I'd be happy to explain the situation.

 

[EdNerd]

So we tried Excel's "Open and Repair" function. And it seemed to work okay. After saving, the files open without issue. And it looks like all the4 macros are still there, and I'm hoping all the formulas still work. On one file all the cell and worksheet protection was removed from several - but not all - protected sheets. But at least we are mostly functional again. I have breathing room to explore and fix more better!

 

[jkpieterse]

I'd start with checking things like styles and named ranges for unexpected items.

 

[EdNerd]

I'd start with checking things like styles and named ranges for unexpected items.

I don't use any styles. But I do have a lot of named ranges. And users do make copies of the workflow sheets into the same file, which creates duplicates of some names. But all those are scoped to worksheet only, not to the entire workbook. Which creates the question: if I have several ranges with the same name, but each has a unique worksheet address, would that confuse Excel??

 

[jkpieterse]

You may not use styles (they are great!) , but have a look at them anyway. They pile up due to copying and pasting from other workbooks.
Duplicate local named ranges are fine. As long as you don't also have global copies of them too

 

[EdNerd]

I ran a function to list out all the styles, and found a name that's not native to the original file - "Linked Cell". Then I found a function on jpk-ads.com biggrin to locate a cell containing a certain style - but it didn't score a hit anywhere. There's no broken links to data sources. I can't find a formula looking for external information. Next up is to pick apart all the code in the forms: those are all old, and there's probably some deprecated functions hiding in there.

 

[jkpieterse]

My flexfind may help to find external links