What happens when you close a workbook with code running?

1
votes

I have a workbook on a network containing worksheet data and housing a generous amount of data, VBA code, forms etc. I developed a custom ribbon available via an add-in that opens this file and executes a macro when a button on the ribbon is clicked.

  1. When the ribbon button is clicked, it executes a callback within the add-in that houses the custom ribbon XML file.
  2. Code within the callback uses Application.Run() to open and execute the much larger network file that holds the worksheet data and macros needed for a lengthy automated process.
  3. If a user cancels operation via the "Cancel" button on one of several forms (or if code successfully completes execution), via Application.Run it passes code execution back to another procedure in the add-in housing the custom ribbon.
  4. This "EndProcedures()" macro in the add-in closes the network file.

The issue I am having is that code execution stops (as if an "End" command had been encountered) as soon as I close the network file which houses the vast majority of the VBA code. Since the user has the ability to cancel operation in the middle of the macro, the procedure call to the "EndProcedures()" macro in the ribbon workbook (the one that code execution began from) is something like #8 in the call stack, with #2-7 being procedures residing in the workbook that is being closed by #8. Since I want to halt code execution and the next line in the "EndProcedures()" macro is simply the global "End" command anyway, this isn't necessarily an issue. However, for the last couple of days I have been wrestling with some cryptic errors that I am thinking may be tied to the fact that I am closing a workbook while it has code running in the call stack. I have been getting "Out of Memory" errors in the VBA editor (though strangely I haven't seen them when the VBA editor is not open), and my thought is that this is due to either:

  1. The code window for the network file simply being open in the VBA editor after the file itself is closed by other VBA code, OR
  2. Perhaps actual memory issues due to object references not being cleared because the file was unloaded, rather than an "End Sub" or "End" line being encountered.

Though I use custom objects extensively in the macros housed in the network file, I am very careful about memory usage with them (which shouldn't even be that much) and don't think that this code is the source of the "Out of memory" errors. The question for now is whether or not closing a workbook with VBA code while code is running in the workbook being closed can cause "Out of memory" errors, and if so what can be done so that once macro execution ends users don't have my macro workbook open to wreak havoc with. Will I really have to code proper "Exit Sub" lines all the way out of the call stack?

This is my first post to StackOverflow, even though I probably learned 50% of what I can do in VBA from here, so please bear with me on this one.

1
excelvba
VBA objects die with the application hosting them unless you are using unmanaged resources. Period. What you can leak are Application instances, especially if you are starting an instance on another server. That would be the first place I'd start looking. – Comintern
@Comintern I only have one instance of Excel running, just with more than one workbook open (using 2010 by the way). Don't think that is the issue, but thanks for the input. – jemmell

1 Answers

0
votes

After a few weeks of continuing to play with this, it doesn't appear that exiting code execution in the middle of a call stack involving multiple workbooks/modules was the source of the "Out of Memory" issues that were encountered. Though I haven't been able to pinpoint which edits actually addressed the "Out of Memory" errors, at least the issue has been resolved to my satisfaction.

P.S. - I answered my own question because it was no longer an issue, not because I necessarily pinpointed a resolution. As this was my first post to StackOverflow, if this was not the proper action to take please let me know.