Menu
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.
225
The best way to deal with the automation error is to use the “On Error Resume Next” statement that moves to the next line of the code irrespective of the error. You can also use the Err object to get information about the source and nature of the error.
To resolve this problem, modify the code so each call to an Excel object, method, or property is qualified with the appropriate object variable. The author of the post was getting the error because he was not using the Access object to open and close the database.
The solution though was pretty easy: Save the file as an "Excel Binary Workbook". Once you do that, the problem goes away for everyone using the workbook.
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
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
36
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
