I've been developing an application in VBA to screen scrape a PCOMM Workstation Emulator for an IBM
AS/400. The application stores the data in an array, then uses ADO to insert the code into the workbook, and pull the data
back into a userform for manipulation. During development I wasn't having any errors until I received an 'Out of Memory'
error which I attributed to the copious amount of controls that the userform was generating (controls are added
programmatically as needed based on records retrieved from the Excel file). Therefore, I changed the userform exit method
from Me.Hide to Unload Me. However, since then I have been experiencing issues with Excel crashing while the code was
executing. The crashes are intermittent, and much more rare when stepping through the code, making it hard to identify the
line of code causing the crash. Furthermore, the crashes sometimes occurred during the screen scrape and sometimes during the
creation of the userform. I would sometimes receive an 'Automation Error' pop up, but it didn't highlight a specific line or
provide much help. Most recently the crashes have started occurring after I close Excel and the window disappears, but before
the task ends inside the task manager. Another interesting note is that if I run the add-in several times from the custom
menu bar I have created multiple instances of the workbook show up in the VBE. I thought this might be related to the ADO
query, but I'm not sure? At one point I was stepping through the code and it crashed on a reference to a function stored in
my add-in module. The function sorted the array alphabetically. I thought maybe the call to that function was causing the
crashes, so I rewrote the code inside my macro and removed the reference.
Troubleshooting steps so far:
-Reset all variables at the end of subroutines to free up memory.
-Defined each variable type, reducing the amount of variants to the bare minimum.
-Exported/Imported all modules and userforms to a blank workbook.
-Removed unneccesary PCOMM references
-Excluded code from add-in module (re-wrote code directly into the macro's modules)
-Copied/Pasted all code from modules and userform into a blank workbook.
-Tried both Me.Hide and Unload Me to hide the userforms.
-Tried moving all of the code from the four separate modules into one module.
-Tried stepping through the code (this rarely throws any errors for me)
-Tried removing all 'Call' functions; replaced with the function name and arguments
-Tried adding Option Explicit to the beginning of each module
-Tried running the macro and several machines
-Tried running automatic updates to get new office patches
-Tried updating functions to pass arguments ByVal and defined argument data types and function return value data types
-Tried commenting out various pieces of code in my main sub routine to identify what point errors started occurring.
-Tried cussing at it until it worked.
References I am using:
-Visual Basic For Applications
-Microsoft Excel 12.0 Object Library
-Microsoft Office 12.0 Object Library
-Microsoft Forms 2.0 Object Library
-Microsoft ActiveX Data Objects 2.8 Library
-Microsoft ActiveX Data Objects Recordset 2.8 Library
-PCOMM autECLOIA Automation Object 1.0 Library
-PCOMM autECLPS Automation Object 1.0 Library
-PCOMM autECLSession Automation Object 1.0 Library
-BCUtilities (Excel Add-in containing frequently used code)
A concern that I have now is that after all of my
troubleshooting I've fixed one crashing error, but gained another. As I said, the crashes seem to have started occurring more
after I close Excel than during the actual execution of the code, but being intermittent it is hard to judge. I also have to
wait for a user to become available to run another test to see how their machine is responding after my troubleshooting
I hate to post the code because there's a lot of it, and also because I have some security concerns, but
if it is necessary I can probably do it (as a whole, in chunks, or as an attachment, I'm not sure what the preferred method
So, I suppose this is what I'm curious about:
-Why would multiple workbooks with the same name be opening when I rerun the macro? Could this cause Excel to crash when it
-Is there some way to retrieve information about the crash after it occurs? I've tried looking at the report and the event
log, but they're Greek to me.
-Can 'Out of Memory' errors cause corruption, and could I be inadvertently carrying that corruption to my new workbooks? Or
perhaps my add-in was corrupted?
-Are there any known issues with the references I have that could cause an instability?
-Any other ideas?
Thanks in advance for any assistance. Please let me know if I can provide more information.
Windows XP Professional SP2
Microsoft Office 2007 and 2003
Dell Optiplex 745