\n| \n Note<\/b>. Admittedly, the Scripting Guy who writes this column came close after the University of Washington football team lost in overtime for the second straight week. But he didn\u2019t actually cry; he just had something in his eye.<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n Sorry, folks; trying to get Dean calmed down could take awhile. In the meantime, do<\/i> 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\u2019t. But we can look it up:<\/p>\nSet objExcel = CreateObject(\"Excel.Application\")\nobjExcel.Visible = True\nSet objWorkbook = objExcel.Workbooks.Add()\nSet objWorksheet = objWorkbook.Worksheets(1)\nobjExcel.ScreenUpdating = False\nFor i = 1 to 100\n For j = 1 to 100\n objExcel.Cells(i,j) = i * j\n Next\nNext\nobjExcel.ScreenUpdating = True\n<\/pre>\nWhat 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\u2019t agree with you more; that\u2019s why we\u2019ve \u201cfrozen\u201d the screen so that you don\u2019t 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.<\/p>\n Oh for the love of \u2013 no, Peter, they didn\u2019t say that you<\/i> were boring; they said that Excel<\/i> was boring. No, Peter, not on your sleeve; you\u2019ll have to borrow the handkerchief from Dean as soon as he\u2019s finished with it. Good heavens.<\/p>\n Kind of makes you wonder if maybe the Scripting Guys are a little too<\/i> sensitive, doesn\u2019t it? <\/p>\n 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<\/b> object we set the Visible<\/b> property to True; that gives us a running instance of Excel that we can see on screen. Next we use the Add<\/b> 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:<\/p>\nSet objWorksheet = objWorkbook.Worksheets(1)\n<\/pre>\nWe\u2019re now ready to start adding numbers to the worksheet, something we\u2019ll do using this block of code (which we won\u2019t bother explaining):<\/p>\n For i = 1 to 100\n For j = 1 to 100\n objExcel.Cells(i,j) = i * j\n Next\nNext\n<\/pre>\nOf course, as we\u2019ve already decided, we aren\u2019t interested in watching all these numbers get added to the worksheet. That\u2019s why, just before we launch into the preceding block of code, we set Excel\u2019s ScreenUpdating<\/b> property to False:<\/p>\nobjExcel.ScreenUpdating = False\n<\/pre>\nWhat\u2019s 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\u2019t see any of that happen. Instead, Excel will just sit there, seemingly frozen. As soon as we\u2019ve finished adding values to the spreadsheet, however, we\u2019ll set ScreenUpdating back to True:<\/p>\n objExcel.ScreenUpdating = True\n<\/pre>\nAt that point you\u2019ll see everything that took place while the screen was frozen. In other words, you\u2019ll 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. <\/p>\n Give it a try and you\u2019ll see what we mean.<\/p>\n Incidentally, temporarily disabling screen updating will also make your scripts run a tad bit faster. For example, on our test computer \u2013 with screen updating disabled \u2013 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.<\/p>\n At any rate, we \u2013 hold on a second. Well, if we do<\/i> take you out for ice cream will you two stop pouting?<\/p>\n Sorry, we have to go. We\u2019ll see everyone tomorrow.<\/p>\n","protected":false},"excerpt":{"rendered":" Hey, Scripting Guy! I seem to recall that there\u2019s a way to \u201cfreeze\u201d the display in Microsoft Excel any time you carry out a lengthy operation; that way the user doesn\u2019t see anything change until the operation is complete. Do you have any idea how I can do that? — IW Hey, IW. Do we […]<\/p>\n","protected":false},"author":595,"featured_media":87096,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[1],"tags":[711,48,49,3,5],"class_list":["post-66133","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-scripting","tag-excel-application","tag-microsoft-excel","tag-office","tag-scripting-guy","tag-vbscript"],"acf":[],"blog_post_summary":" Hey, Scripting Guy! I seem to recall that there\u2019s a way to \u201cfreeze\u201d the display in Microsoft Excel any time you carry out a lengthy operation; that way the user doesn\u2019t see anything change until the operation is complete. Do you have any idea how I can do that? — IW Hey, IW. Do we […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/66133","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/users\/595"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/comments?post=66133"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/66133\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media\/87096"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media?parent=66133"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/categories?post=66133"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/tags?post=66133"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}} |
![\"Hey,](\"https:\/\/devblogs.microsoft.com\/wp-content\/uploads\/sites\/29\/2019\/02\/q-for-powertip.jpg\" "\\"Hey,")
<\/p>\n![\"Spacer\"](\"https:\/\/devblogs.microsoft.com\/scripting\/wp-content\/uploads\/sites\/29\/2019\/05\/spacer.gif\")
![\"Hey,](\"https:\/\/devblogs.microsoft.com\/wp-content\/uploads\/sites\/29\/2019\/02\/a-for-powertip.jpg\" "\\"Hey,")
![\"Script](\"http:\/\/img.microsoft.com\/library\/media\/1033\/technet\/images\/scriptcenter\/ad.jpg\" "\\"Script")
<\/a><\/p>\n