Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel,microsoft.public.excel.programming
|
|||
|
|||
Excel cannot complete this task......
On Mar 22, 11:14 am, Boblink wrote ....
Anne, thank you for your link. You are practical and pragmatic there. Boblink I have virtually the identical affliction. It's been a problem for a long time, and cost countless money and loss of lives while MS has waxed impotently about it. I hope the Excel development team sleeps well at night while this problem has existed for years. In my case the crash happens at different times. I can open the file and run the macro and have it crash the app. when 25% done; I can then do EXACTLY the same thing and have it make it 55% of the way through. I also have 1 Gig memory, which I am certain is not stressed outside of Excel; and the .XLS is a single MEG. I thank the people who offer well-meant responses of "you're short on memory. Try closing Outlook." That's simply not the issue here. Successive *pastes* appears to be the crux of the problem. One tactical response is to add Application.CutCopyMode = False after pastes. This may help others; it didn't solve things for me, but it apparently transformed the crashes to new mutations and new timings. In the many hours I've bled researching this, over and over I hear that pastes are flaky. I have had vomitously choppy "success" from subdivide/conquer. To illustrate: I have 1650 total values to calculate. 550 are displayed on each page. There are 11 columns of 50 values each. If I divide the problem into 33 tasks and manually run the macro 33 times, maybe 7 times out of 10 I can make it to the goal line, whereas I crash 100% of the time if I run a single loop. Imagine the conversations I've had with the company CEO. He says, "What do you mean, you simpleton? You can't write a loop? Isn't that what I learned to do in BASIC 35 years ago??" Divide and conquer is the only spitball I've found against this Goliath. |
#2
Posted to microsoft.public.excel,microsoft.public.excel.programming
|
|||
|
|||
Excel cannot complete this task......
On Mar 23, 8:56 am, "AmendConstitution_ArnoldForPresident"
wrote: On Mar 22, 11:14 am, Boblink wrote .... Anne, thank you for your link. You are practical and pragmatic there. Boblink I have virtually the identical affliction. It's been a problem for a long time, and cost countless money and loss of lives while MS has waxed impotently about it. I hope the Excel development team sleeps well at night while this problem has existed for years. In my case the crash happens at different times. I can open the file and run the macro and have it crash the app. when 25% done; I can then do EXACTLY the same thing and have it make it 55% of the way through. I also have 1 Gig memory, which I am certain is not stressed outside of Excel; and the .XLS is a single MEG. I thank the people who offer well-meant responses of "you're short on memory. Try closing Outlook." That's simply not the issue here. Successive *pastes* appears to be the crux of the problem. One tactical response is to add Application.CutCopyMode = False after pastes. This may help others; it didn't solve things for me, but it apparently transformed the crashes to new mutations and new timings. In the many hours I've bled researching this, over and over I hear that pastes are flaky. I have had vomitously choppy "success" from subdivide/conquer. To illustrate: I have 1650 total values to calculate. 550 are displayed on each page. There are 11 columns of 50 values each. If I divide the problem into 33 tasks and manually run the macro 33 times, maybe 7 times out of 10 I can make it to the goal line, whereas I crash 100% of the time if I run a single loop. Imagine the conversations I've had with the company CEO. He says, "What do you mean, you simpleton? You can't write a loop? Isn't that what I learned to do in BASIC 35 years ago??" Divide and conquer is the only spitball I've found against this Goliath. I'm a little interested in what is going on here because I almost don't believe you when you infer that "Excel can't do it." For example, I have a macro that calculates well over 2,500 formulas and keeps track of over another 60,000 other formulas (composed of 24 columns and 2,500 rows) which are all created via a macro. When I run my macro, I don't crash. I'm just trying to say that I'm interested in your problem. Do you have any posts of your macro? Matt |
#3
Posted to microsoft.public.excel,microsoft.public.excel.programming
|
|||
|
|||
Excel cannot complete this task......
The code is a little unwieldy to completely post, but here's a taste
If gbDirtySheet Then cmdDoAccessQuery_Click Application.Goto Reference:="SummaryDur1to25" Selection.Copy Application.Goto Reference:=sResultStartPoint ActiveCell.Offset(0, i - 1).Range("A1").Select Selection.PasteSpecial Paste:=xlPasteValues Application.CutCopyMode = False 'hopefully corrects automation bug in Excel Application.Goto Reference:="SummaryDur26to50" Selection.Copy Application.Goto Reference:=sResultStartPoint ActiveCell.Offset(12 * 25, i - 1).Range("A1").Select Selection.PasteSpecial Paste:=xlPasteValues Application.CutCopyMode = False 'hopefully corrects automation bug in Excel This never makes it through the 550 times I request it. Sure, it's easy to write a billion operations, and it runs sweet. Sub foo() Dim i As Long For i = 1 To 1000000000: If i Mod 10000000 = 0 Then Debug.Print i Next End Sub But I don't think capacity or limits is the issue here... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel cannot complete this task with available resources | Excel Worksheet Functions | |||
Excel cannot complete this task... | Excel Programming | |||
Excel cannot complete this task with available resoucres | Excel Discussion (Misc queries) | |||
Excel cannot complete this task with available resources. | Excel Discussion (Misc queries) | |||
Excel cannot complete this task | Excel Discussion (Misc queries) |