2

The issue:

Similar to this issue but unable to find a solution I am trying to figure out how to fix this bug:

Viewing an object in Locals or Watch window causes excel to crash

The custom class is as wrapper for ADO execution allowing us to use a separate class to maintain our SQL code and execute it within the wrapper to keep error handling for MS Access ADO execution in one neat place.

This has been great for logging and debugging purposes until a few days ago I noticed trying to expand the node for the custom class in the Locals or Watch windows in the VBA editor cause Excel to silent crash after a 5 second hang and restart with the file recovery window.

Unfortunately the code is company sensitive so I may not be able to paste any in but if there are any ideas as to what might cause it I may be able to find the source and report back.

Has anyone else experienced this issue?

Update: New symptom - During debugging and comparing the text between an older build and the new one I found that the Locals window goes empty (clears of all objects) just after the hang stops and before the crash occurs.

Fixes I have tried:

  • Rebuilding VBA project and fixing all known compile errors (to eliminate simple vba errors)
  • Exporting all classes and modules - reimporting into new workbook (to eliminate the workbook file being corrupt)
  • Copying class and module text into notepad and clearing all formatting - pasting into new workbook (in an attempt to remove corrupt class data)
  • Disabling any reference to other custom classes to narrow down to a single class.

I appreciate your help and hope my issue can shed some light on it for others.

Community
  • 1
  • 1
Tom 'Blue' Piddock
  • 2,131
  • 1
  • 21
  • 36
  • Might it be that you are expanding an array of objects when this happens? And that this array is too big to be held in the PC's memory? To many elements in the node maybe? Could you reduce the amount of elements in the node and retry? – K_B Mar 20 '13 at 11:50
  • @K_B I'll take a look into the memory but there is no particular array being expanded, only the base of the custom class itself which doesn't directly expose the internal properties/variables. I'll try it out as a simple instantiation to see if it is any arrays filling up. – Tom 'Blue' Piddock Mar 20 '13 at 17:08
  • If you try to watch your properties individually, do they all work? I think VBA's exception handling is a bit flakey in the Watch window. – grahamj42 Mar 23 '13 at 19:33
  • All watches and locals work except the root of the custom class I mentioned. I have tried doing a diff compare on previous builds to narrow down anything sucking up memory but the garbage collection is pretty good and doesn't seem to do badly. It's been bugging me for a while now. – Tom 'Blue' Piddock Mar 25 '13 at 08:55
  • I had a similar issue. It would crash if I e.g. had a public property of type Double(), which should return a private variable, which I mistakenly had declared as type Double, i.e. not an array. – Morten May 02 '13 at 12:50

1 Answers1

2

You can try below things. I hope any one of them work for you. If it still it does not works i request you to simulate the similar issue for demo and share the code.

  • Possible issues with add-ins. Try to remove your addins (uninstall the com addins) and run the code and check if you are still having the issue.Disabling add-ins is a good test because it let you narrow the choice of which add-in is causing the crash.

  • Check whether your antivirus software is up to date or is conflicting with Excel. If your antivirus software is not up to date, Excel may not function correctly.

  • Kindly run the macro on single instance of excel and check if you are still having the issue. By single instance i mean when you go to task manager processes tab where only one image name with excel.exe should appear.

  • Verify/install the latest window updates

  • Try to run the same macro on other pc or other version of excel

  • Use mztools andin and review your code