Printable View
I have an Excel macro that works. Sometime, the macro creates a very large spreadsheet, i.e., multiple sheets with 500,000 rows each (each with not more than 13 columns, each of which has a single digit number). Typically, the next time the macro is run, Excel just shuts down. I feel like my code is okay because the last lines of the macro are all ‘Erase. Any suggestions as to what I may have missed?
It might be difficult to say what the issue is without seeing the code.
However, does this only happen if you run the macro, and then, without closing the Excel workbook(s) or Excel, re-run the macro again? In other words, does it ever happen when you start Excel and open the workbook and run the macro?
I could provide the code if necessary, but my gut tells me that this is something generic and not specific to the code. In answer to your question, this only happens when the macro is run again - I feel that the issue is somehow related to memory.
Is it an autoexecute-macro? As in: Is the macro executed, when you open the workbook, or do you have to trigger it manually to run?
And what's the size of the xlsm-file after successfully running the macro the first time?
The macro is manually run.
In terms of the file size: If the output has fewer than 500 rows, they are shown in the original file, but each row has fewer than 13 columns with a single digit number in each. As such, I doubt the file size changes much. If there are more than 500 rows, a new file is created which I doubt changes the size of the original file.
Errrrrr….. 500 rows vs. 500K rowsQuote:
Originally Posted by groston
what now?
If the output is small, less than 500 rows, I simply add them to the existing file. If the output is larger, I create a new file. The issue arises when the new file has multiple sheets with 500k rows per sheet.
In an attempt to figure out why Excel crashes, I just had a situation in which I suspected the next time the macro ran, Excel would crash. So, I opened the macro and put a break point after some of the basic steps of reading data from the sheet. Excel crashed before hitting the break point.
Just tried again, this time putting the break point on the first line after the DIM statements. Excel crashed again.
If this only ever happens when the macro is run a second time, then it could be a memory issue, especially if a break point just after the Dim section is not reached but Excel crashes instead.
Agreed - but is there anything I can do to alleviate this issue? I have release all the variables created, etc...Quote:
Originally Posted by jdc2000
Can you post your code and/or a sample workbook?
Are you releasing all your objects in your code? They are normally cleaned up automatically, however, I prefer to use Set [object var] = Nothing after they are closed (if needed) just the same.
Of course, to what extent this may be affecting your particular issue can't be known without (as AdorablePlum points out) seeing your code.
Do the Math.....Quote:
The issue arises when the new file has multiple sheets with 500k rows per sheet.
First you need Memory for Excel itself
Then you need Memory to LOAD that File
Then you need memory for all Variables you "Dim" before executing the first line
The code is used to find all possible combination of selected cells in the game Killer Sudoku. The numbers are read from the spreadsheet, the macros runs, then the possible combinations are shown.
Attachment 195053