Old files - Excel Macros stopped working - new ones fine

Hi folks

I've got a strange problem with some existing files that really has me stumped.

Office 2010 (32 bit), Windows 7 (64 bit)

I was given a new PC by the company yesterday. The machine was a clean install. Since then, some macros have stopped working in my existing files. The problem is specific to this machine.

Symptoms in some old files only:
1. Some events are no longer working on some old files. Worksheet_Activate triggers, bur Worksheet_Change does not.
2. ActiveX buttons can be depressed, but macros don't run. (Buttons can't be depressed for the *.EXD problem).
3. If I go to the VBA Editor, I can run macros using F5, but even the simplest commands (such as Sheets("Sheet1").Select) gives an Application Error (32808). I see similar behaviour if I use the Immediate window to execute a command.

The above applies to several files that have been developed over the course of several months/years. A new sheet I started 2 weeks ago seems to work with no problems. If I create a new file, it also works with no issues. I did wonder if the Dec 2014 ActiveX issue was related, but the usual fix of deleting all *.EXD files did nothing for me.

What I have tried so far:
- Reboot PC
- Delete all *.EXD files.
- Delete all temporary files in Windows\Temp
- Remove some References
- Take a version of the files dating back a week or so.

All the files are fairly complicated, capable of e-mailing, and similar functions. They have References to MSForms, Outlook, etc.

I have yet to determine a pattern (I'll update here if I find one).

Does anyone have any ideas of what else I could try?

Thanks in advance.

Best regards, Rob.

Hi Bob+,

Since you ruled out the Dec 14th update, here are a few more things you can try, in order of simplicity:

a. Missing Reference: Find a computer that the file runs on. In the VBA references (Tools > References) make sure that all the resources that are checked on the other computer, are also checked on your computer.

b. Corrupt sheet:
Make sure you have a backup copy of your file.
(1) Make a copy of the offending sheet
(2) Delete the original offending Sheet
(3) Rename the copy back to the name of the original

c. Corrupt sheet, tedious and time consuming fix:
Make sure you have a backup copy of your file.
(1) Delete all Active X controls
(2) Make a copy of the offending sheet
(3) Delete the original offending Sheet
(4) Rename the copy back to the name of the original
(5) Add Active X controls to the sheet

Lewis

Hi Lewis

Apologies for the replying sooner, I have been on holiday.

Thanks for the response. I checked your suggestions. It wasn't a missing reference. I don't think it was a corrupt sheet, as it was working on other PCs, and it was only when I changed my laptop that the issue arose.

However, I have managed to fix it. My reasoning was that, if I can create working macros on this PC, then I'll try recreating the macros in those old files.

The steps I took:

• Save a backup copy!
• Open the VB Editor.
• Export all modules e.g. Module1.bas.
• Delete all modules.
• For each sheet containing macros, create a blank text file. I used the sheet name for each text file to track what code should go where.
• Copy/paste the code from each sheet in to the appropriate text file. DO NOT delete the sheets or ActiveX controls from the workbook.
• Delete the code from each worksheet.
• Do the same from ThisWorkbook if you have any macros in there.
• Save the file. Close Excel.
• Reopen the file.
• Copy/Paste the code back in to each sheet from the text files.
• Re-import the modules.
• Save the file.

Following the above, I had the inability to press buttons in the file. This is the consistent with the *.EXD issue introduced in Dec 2014 by an MS update. To resolve this, delete all EXD files by following the instructions here:
https://support.microsoft.com/en-us/kb/3025036/en-us

The above solved it for me and the file works on other PCs. I made no changes to the workbook (no need to delete sheets, etc). I realise how weird this sounds, and I can't think of why this would solve it, but it worked on both the files I was having problems with.

Best regards, Rob.

Hi Rob,

Thanks for sharing, and I hope you enjoyed your holiday.

I'm glad you solved the problem. Since you didn't delete or change any sheets, it certainly doesn't seem like an Active X problem. I have seen Active X problems exhibit similar behavior in the past, especially when there is a leftover unused Active X control that is no longer visible.

My speculation is that there must be an internal Excel binary software flag related to macros that was corrupt. By removing all macros, the flag got cleared.

As an aside if you have too much time. Would a similar technique work, that instead of deleting all Macros:
a. Save the file as type .xlsx
b. Save the .xlsx file as .xlsm
c. Replace the macros

Lewis