1

This is something I have read a lot myself but couldn't find the solution. I have a program of about 10,000 lines where I have a procedure attach to a particular command button. This calculates some complex equations.

I have started the code as usual with Application.ScreenUpdating = False and turned it to True just before ending of the main procedure. Yet just the upper part of my Excel Sheet (near the Menu Bar) keeps flickering about 10 second until the result appears. I tried disabling events, as well as turning the calculations to manual, but nothing helps. Even tried mentioning screen updating to false at the beginning of sub procedures related to main procedure.

Any suggestion regarding this will be really appreciated. Thank you!

Athrika
  • 63
  • 7
  • Is there event code that is running for worksheet events? Like `Worksheet_Change` or `Worksheeet_Activate` or anything like that? – Soulfire Aug 12 '15 at 15:17
  • No not really. Just very simple loops and variables without any event. – Athrika Aug 13 '15 at 05:42

1 Answers1

1

Try adding this at the start of the sub:

Application.EnableEvents = False
Application.DisplayStatusBar = False
Application.Calculation = xlCalculationManual

And this just before the end:

Application.EnableEvents = True
Application.DisplayStatusBar = True
Application.Calculation = xlCalculationAutomatic

That should help. Note though, my screen also flickers sometimes when running complex macros (Excel looks like it's not responding, but it's working in the background).

BruceWayne
  • 22,923
  • 15
  • 65
  • 110
  • Thank you for your response. It seems like it got a shade better but yet the screen flickers. The result comes ofcourse every time. For executing small calculations this doesn't happen though. I am not a profi yet in VBA. May be my coding might not be that efficient. Thank you once again nevertheless. – Athrika Aug 13 '15 at 05:40
  • If your code isn't too long, you can post it to see if there's any suggestions to be made. Are you using `.Select` or `.Activate` a lot? – BruceWayne Aug 13 '15 at 14:11
  • Yes I have used in during the plot. But even if I comment out the plot sub procedure, the screen flickers during the calculation. And the calulation is quite clean. It is complicated with loops but yet with simple logically. Unfortunately I can't upload the entire code. Its about 10.000 lines and in collaboration to confidential work. – Athrika Aug 14 '15 at 05:41
  • @Athrika - Ah, well it might just be due to the size/complexity of the macro. If you let it run, does it successfully finish correctly? – BruceWayne Aug 14 '15 at 14:49
  • yes with datas that are small..it doesn't create any problem (no flickering atall), and with big data it takes like 5 seconds, but the code is entirely executed every time. – Athrika Aug 15 '15 at 05:02
  • @Athrika - that's what happens with some of mine too. I guess Excel can't be open and running a macro without looking like it's going to crash, or something. If you do find a solution though, let us know! – BruceWayne Aug 15 '15 at 20:23