Computing Limitations for large VBA code

[schustda]

I have a VBA code that goes through a very large amount of data and unfortunately VBA will always end up crashing whenever I try to run it.

I wanted to see if anyone had any workarounds for this other than getting a higher powered computer. It doesn't really matter how long it takes so I was looking for a way to maybe slow down the program so it can get through all of the data.

My thinking was that I could start it after work and leave it running overnight. Unless anyone has any ideas, my next attempt will be putting a weight on the F8 key overnight to run through the program.

Thanks in advance

[Dave]

I'm fairly certain that a weight on the F8 isn't the answer. It's hard to say what's wrong but I'm pretty sure that the amount of data has nothing to do with your crash... it is the VBA code. I'm guessing you are using selections, not turning off the screen updating, not disabling events and/or not controlling your calculations when running your code. Trial the following. HTH. Dave

Sub test()
Application.EnableEvents = False
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
'your existing code without using selection
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub

[schustda]

Dave, I tried your code and it worked fine.


More information on my code is that works through a permutation and copy-pastes combinations that meet the correct criteria.

I ran my code again and after about 200 rows I got the error:

Run-time error '-2147417848 (80010108)':
Method 'Paste of object'_Worksheet' failed

Though it's worth noting I tried it on a coworker's higher powered computer and it got much further.

Maybe it's something with the copy/paste? Can put in a code to clear the cache every once in a while?

[SamT]

The way that code is written, I think the solution is to use the F5 key

[Dave]

Copy and paste can usually be replaced quite easily just by telling the data to go to a new place. That avoids overloading the clipboard which is actually your real problem. The clipboard crashes. U need to clear the clipboard following each paste if U want to keep your code and resolve this. Trial inserting this after each paste to start. It might work. Dave

Application.CutCopyMode = False

[Dave]

Hmmm...seems like previously there were more posts on this thread??????? Dave

[SamT]

See this thread (http://www.vbaexpress.com/forum/showthread.php?51063-any-web-attacks-targeting-the-forum)

[mikerickson]

Break the problem into smaller chunks.
Instead of


For i = 1 to 1000000
'do stuff
Next i
do it

For j = 1 to 1000
For i = 1 to 1000
'do stuff
Next i
ThisWorkbook.Save
Delay 1
Next jThe occasional Saving and the pausing of the code will help the system keep in synch with itself. (1000 is probably low, but the principle....)

[mikerickson]

Break the problem into smaller chunks.
Instead of


For i = 1 to 1000000
'do stuff
Next i
do it

For j = 1 to 1000
For i = 1 to 1000
'do stuff
Next i
ThisWorkbook.Save
Delay 1
DoEvents
Next jThe occasional Saving and the pausing of the code will help the system keep in synch with itself. (1000 is probably low, but the principle....)