Excel/VBA Automation Errors due to Office Service Pack 3.0 caused by Forms

Question

After installing all latest windows updates my Excel VBA code is showing an automation error at the very start of the first module. It contains some forms as well as numerous modules of VBA code. However, when removing the forms (and associated code) everything is fine.

The same thing happened about a year ago after some other windows updates. Back then deleting the .exd files did the trick, but the current updates (including Service Pack 3) seem to be different.

What is going on and how can I get the forms to work? All the code is unchanged and has survived all previous windows/office updates. Running Windows 7 (same happening on Vista machine) and Office 2007.

Answer 1

I used to see this a lot with Visual Basic and Crystal Reports; either due to a bad deployment or DLL Hell.

So, my guess is that it's not the code but perhaps some 3rd party controls (OCXs, DLLs) you're using.

Check your References (In VBA IDE, click Tools and References) and to see where each DLL is pointing.

See if any of the dates on the libraries are different than a working machine and you may find the culprit.

---

Edit

The OP found the problem by following the steps above.

The fix for this particular issue is to unregister mscomctl.ocx:

regsvr32 /u mscomctl.ocx 

Then register it:

regsvr32 mscomctl.ocx

Answer 2

Same issue here, problem completely solved after registering mscomctl.ocx!

for Windows 7 or 2008 - remember to run cmd.exe "as administrator", and then:

for 32bit Windows systems: regsvr32 c:\windows\system32\mscomctl.ocx

for 64bit Windows systems: regsvr32 c:\windows\syswow64\mscomctl.ocx