CODEWITHSUNDEEP
vbaexcel
user2156096
user2156096

Reputation: 43

Excel freezes during execution

When I execute my code (which takes 3-4min. to execute) in Excel, and I click on something in excel or in my userform, it freezes, gets white, almost crashes... When the execution is ready everything is fine again.

Of course it is normal that I can't work in excel during execution, but how can I avoid the "crashing" of excel?

Upvotes: 1

Views: 12478

Answers (4)

Charlie Hill
Charlie Hill

Reputation: 143

Do you have a Loop in the code?

If so, add the following line just after you initialize the loop and it should help with your problem!

DoEvents

Upvotes: 5

pn7a
pn7a

Reputation: 492

After you make sure there are no infinite loops making excel unresponsive try optimizing your code.

I found this a very interesting read on vba-excel code optimization and some of the practices actually had an impact on a big and time consuming module I wrote. Hope it helps.

Upvotes: 0

Jon Crowell
Jon Crowell

Reputation: 22320

The best way to prevent these kinds of issues is to benchmark your code to determine what is taking so long. If your code is taking over 3 minutes to run, you almost certainly have room for optimization.

Here are the steps you need to take:

  1. Set multiple breakpoints within the code that is executing.
  2. Time how long it takes to get from one breakpoint to the next.
  3. Once you've identified the culprit(s), refactor.
  4. If you aren't able to improve performance, post the inefficient code and ask for help.

A notorious slow-performing anti-pattern to look out for is selecting things during a loop.

Upvotes: 2

StoriKnow
StoriKnow

Reputation: 5866

What you need to do is create a custom UserForm that will overlay the Excel UI, like a Progress Bar. You'll also want to look into ways to force your form to stay ontop, much like a modal form. You can't use modal form's because they cease execution.

Upvotes: 0

Related Questions