Lost VBA when downgrading from Excel 2013 to Excel 2010

Question

I am on Windows 7. In the past I was using Excel 2003. I installed Office 2013 and found that my Office 2003 was intact and I could run both versions. I made updates to some worksheets using Excel 2013 and VBA code.

I was informed that I needed to use Office 2010 (I won't go into the reasons) so I uninstalled Office 2013 and installed Office 2010 (Corporate licenses). When I open the spreadsheets that I modified using Excel 2013 in 2010 I get a Microsoft Visual Basic for Applications message:

Class not registered. Looking for object with CLSID:(AC9F2F90-E877-11DE-9F68-00AA00574A4F)

When I hit Ok, I get the Excel message

Excel found unreadable content in name_of_spreadsheet.xls. Do you want to recover the content of this workbook?

If I select YES then I get a message that reads

Excel was able to open the file by repairing or removing the unreadable content. Lost Visual Basic project. Repairs were made to PivotTable report, One or more invalid conditional formats were removed from the workbook and Lost ActiveX controls.

Now the VBA code is not found. The workbook opens and looks normal, but buttons running VBA don't work, and when opening VBA (Alt+F11), I don't see any of the modules.

I tried running the FM20.dll using regsrv32.exe and I get a message which says:

The module fm20.dll failed to load. Make sure the binary is stored at the specified path or debug it to check for problems with the binary or dependent .DLL files. The specified module could not be found".

But I ran regsrv32 from the subdirectory the FM20.dll was in.

I presume my problems have to do with losing the Visual Basic project and/or ActiveX controls.

What do I need to do to be able to find the VBA code that was there before removing Excel 2013 and installing Excel 2010?

Answer 1

I had a similar problem. I used a different fm20.dll file from another computer that had not been "upgraded" to 2013 yet. Registration of fm20.dll went fine and my problem went away. It seems the fm20.dll file was corrupt. Any VBA that has Userforms seem to be affected. The Excel 2010 error that I recieved was: "The visual basic for applications (VBA) macros for this workbook are corrupted and has been deleted. The macros corruption most likely exists in the current file. To recover the macros, open up a backup copy of this file if you have one." A simple test of opening a VBA workbook with only code, which worked, and a simple file with a Userform which had one label on it with no code, which failed, led me to the fm20.dll file. I got the same message trying to register the fm20.dll file on the problem system, saying the file was bad. Copied a new fm20.dll from a good system, registration went fine, problem went away.

Answer 2

Office 2013 installs VBA 7.1. With it, an updated versions of FM20.dll - which in turn has a dependency on the VC10 redistributables - specifically msvcr100.dll. When VBA 7.1 is uninstalled - it does not remove FM20.dll - but the accompanying application most likely will remove the VC10 redist. When it does, FM20.dll is broken. It's not that it's corrupt - it's just incomplete. (I tracked this down using the Visual Studio tool, Depends.exe)

The problem is that reinstalling an application that uses VBA 6.x - won't overwrite this newer version of FM20.dll and so the file remains broken.

It seems like it's a bug in the VBA 7.1 installer that it doesn't clean up this file. You have to workaround choices. Delete FM20.dll and repair your older app - or install the VC10 redistributable.

Answer 3

This blogpost helped me in rectifying this error:
https://dynamicsgpland.blogspot.in/2009/11/vba-error-class-not-registered-looking.html

VBA Error: Class not registered. Looking for object with CLSID:{AC9F2F90-E877-11CE-9F68-00AA00574A4F}

I know, I know, it's such an obvious error, right? I mean, really, is there anybody left in the Dynamics GP world that wouldn't scoff at you for asking about this obvious class ID?

So I recently created a new development virtual server for a new project, something I've done dozens of times without issue. But for some reason, this time I ran into a few strange problems with Dynamics GP. One of them was this VBA error:

This was on a fresh Windows Server 2008 x64 virtual server, SQL Server 2005 SP3, and Dynamics GP 10 FP1, with SP4 installed. I did not have Visual Studio or Office installed, which I'm pretty sure is the reason for the error.

The error occurred whenever I tried to import a user form file into VBA, or whenever I tried to insert a new user form.

I later found that this error is apparently a variant of the one described in MBS KB Article 961568:

"Errors occurred during load" or when you import a package file that has a user form: Class {C62A69F0-16DC-11CE-9E98-00AA00574A4F} of control frmXXX was not a loaded control class.

At the time, I knew that this likely meant that a DLL wasn't registered on the server, but although those lovely GUIDs are a joy to see, they convey nothing to the average human as to which DLL is not registered.

As a shortcut, I switched over to a server that I knew did not have any problems with user forms in VBA and searched the registry for that GUID. That search told me that the problem was that the FM20.dll file was not registered.

After Googling for that file name, I learned that FM20.dll is a file needed to allow VBA to work with windows forms. This lovely KB article describes it's usage, but emphasizes that FM20.dll is "NOT redistributable" (they had to make the NOT all caps and bold for those of us who were unclear and confused about the lowercase non-bold version of the word 'not'), and that it can only be obtained by installing one of several Microsoft applications that happens to distribute it.

It must have taken a team of high priced lawyers thousands of dollars of billable hours to come up with that brilliant scheme that would surely prevent someone from copying a single DLL file and manually registering it. Looking to avoid the wrath of those same summer intern legal scholars, I did not simply copy the DLL from another machine and register it on my new server. Instead, I used the link in that same KB article to download the ever-so-popular "Microsoft ActiveX Control Pad", which installs the much coveted FM20.dll for me.

(Of course, the MBS KB article says to simply copy the FM20.dll file from an MS Office CD. Skirting the rules, are we, MBS?)

And, with that, the error went away and I was able to insert user forms and import form files into my VBA project.

One note--if you are working on Windows Server 2008 x64, the 32-bit file is installed in the C:\Windows\SysWOW64 directory.