My team has had this strange error bugging us during development. From time to time when running VBA on an excel tool that my team is building, Excel will crash and the "Microsoft has stopped working" pop-up will appear and when the user selects the 'close the program' option another pop-up stating "Automation Error Exception Occurred" appears. Now here is where it gets weird:
It only happens on the machines running 2007, 2010 and 2013 machines never have this issue.
Only happens every couple weeks (every 3-5 versions of the tool). These same machines have been running and testing the tool all day every day up until the point it occurs.
There is no noticeable difference between the versions that work and those that don't from a code perspective.
The have been several instances where a machine will run the VBA code fine and suddenly the error will start occurring. Once, a 2007 machine encounters the error, that file will never work on that machine or any other 2007 machine again. BUT, if given a fresh copy of the same exact file, sometimes the problem will disappear.
after some online review, I have tracked the error down to three references being used: OLE Automation, Microsoft Office 12.0 Object Library, Microsoft CDO for Windows 200 Library. If any of these three are disabled in any combination the VBA will start running bug-free.
My questions are:
So after a lot of debugging we finally found the error. One of the programmers had a script that ran prior to the form loading in which he was pre-populating some of the listboxes in the form. Apparently because this SUB existed outside the form and/or because the form had not been initialized yet, this was what was causing excel to crash. Anyway, hope the answer helps anyone else out there who runs into the same thing.