How Can I Keep the Screen From Updating While Running an Excel Script?

ScriptingGuy1

Hey, Scripting Guy! I seem to recall that there’s a way to “freeze” the display in Microsoft Excel any time you carry out a lengthy operation; that way the user doesn’t see anything change until the operation is complete. Do you have any idea how I can do that?

— IW

Hey, IW. Do we have any idea of how you can do that? Oh, IW, you’ve cut us to the quick; after all, we’re the Scripting Guys, for Pete’s sake! How can you even ask if we have any idea of how you can do that? To tell you the truth, IW, you’ve hurt our feelings – oh, look: you made Dean cry. It’s OK, Dean; we’re sure IW didn’t mean that the way it sounded. Come on now; Scripting Guys don’t cry.

Sorry, folks; trying to get Dean calmed down could take awhile. In the meantime, do we have any idea of how you can freeze the display in Microsoft Excel any time you carry out a lengthy operation? Well, now that you mention it, no, we don’t. But we can look it up:

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
Set objWorkbook = objExcel.Workbooks.Add()
Set objWorksheet = objWorkbook.Worksheets(1)
objExcel.ScreenUpdating = False
For i = 1 to 100
    For j = 1 to 100
      objExcel.Cells(i,j) = i * j
    Next
Next
objExcel.ScreenUpdating = True

What we have here is a script that starts Excel and then fills a big range of cells (row 1, column 1 through row 100, column 100) with the product of the row number and the column number. You say that sounds boring, that nobody wants to sit there and watch all those numbers get added to an Excel spreadsheet? We couldn’t agree with you more; that’s why we’ve “frozen” the screen so that you don’t see each number get tediously added to the worksheet. Instead you just sit quietly for half a minute or so and then, like magic, watch as the fully-updated spreadsheet springs to life.

Oh for the love of – no, Peter, they didn’t say that you were boring; they said that Excel was boring. No, Peter, not on your sleeve; you’ll have to borrow the handkerchief from Dean as soon as he’s finished with it. Good heavens.

Kind of makes you wonder if maybe the Scripting Guys are a little too sensitive, doesn’t it?

Now, where we were? Oh, right: the script. As you can see, we start things out in fairly typical fashion, at least when it comes to scripts that use Microsoft Excel. After creating an instance of the Excel.Application object we set the Visible property to True; that gives us a running instance of Excel that we can see on screen. Next we use the Add method to add a new workbook to our instance of Excel, then use this line of code to bind to the first worksheet in that workbook:

Set objWorksheet = objWorkbook.Worksheets(1)

We’re now ready to start adding numbers to the worksheet, something we’ll do using this block of code (which we won’t bother explaining):

For i = 1 to 100
    For j = 1 to 100
      objExcel.Cells(i,j) = i * j
    Next
Next

Of course, as we’ve already decided, we aren’t interested in watching all these numbers get added to the worksheet. That’s why, just before we launch into the preceding block of code, we set Excel’s ScreenUpdating property to False:

objExcel.ScreenUpdating = False

What’s going to happen when we set ScreenUpdating to False? Well, depending on how you want to look at it, nothing. Our script will go ahead and run its calculations and add the results to the designated cells in the spreadsheet; however, you won’t see any of that happen. Instead, Excel will just sit there, seemingly frozen. As soon as we’ve finished adding values to the spreadsheet, however, we’ll set ScreenUpdating back to True:

objExcel.ScreenUpdating = True

At that point you’ll see everything that took place while the screen was frozen. In other words, you’ll go directly from a spreadsheet that has no data on it to a spreadsheet that has data in row 1, column 1 through row 100, column 100.

Give it a try and you’ll see what we mean.

Incidentally, temporarily disabling screen updating will also make your scripts run a tad bit faster. For example, on our test computer – with screen updating disabled – it took 17 seconds for this script to complete. With screen updating enabled, it took 13 seconds. Not a huge savings, but a savings nonetheless.

At any rate, we – hold on a second. Well, if we do take you out for ice cream will you two stop pouting?

Sorry, we have to go. We’ll see everyone tomorrow.

ScriptingGuy1

Follow