Why is vba code much faster when started from the immediate window RRS feed

  • Question

  • When I run a vba macro in Excel, writing let say 100,000 cells, it takes about 10 seconds to finish. When I run the same macro directly form the immediate window, it finishes in a split second.

    Suggestions like application.calculationmode=manual and screenupdating = false I have tried. The problem is not in the coding. It has something to do with the settings of Excel or Windows. I have tried to change the processor schedule in Systems, but no luck!

    I hop anybody recoqnizes this problem and has an answer.


    • Moved by Steve Fan Monday, April 17, 2017 4:43 AM
    Friday, April 14, 2017 8:59 AM

All replies

  • Hi Snarf70,

    It seems very interesting issue.  But I cannot understand the meaning "run the same macro directly form the immediate window".
    What does it mean "directly from the immediate window"?  I will be happy if you will insert a screen shot of it.
    Friday, April 14, 2017 11:47 AM
  • Hi,

    Since this issue is more related to VBA macro, I'll move it to a more appropriate forum:

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.


    Steve Fan

    Please remember to mark the replies as answers if they helped.

    If you have feedback for TechNet Subscriber Support, contact

    Monday, April 17, 2017 4:42 AM
  • I created a test macro to write to 100,000 cells in Excel 2013. It ran in 2s. Running the same from the immediate menu gave exactly the same result. I suspect that when you run from the immediate window you have not passed a correct parameter so the macro errors out?

    Rod Gill
    Author of the one and only Project VBA Book and VBA developer.

    Tuesday, April 18, 2017 4:43 AM