VBA Excel Application Screenupdating

VBA Lesson 13: VBA for Excel for the Application. This is an excerpt from Pierre Leclerc (www.excel-vba.com). Application is a VBA object, IT IS EXCEL.   VBALesson13:VBAforExcelfortheApplication ThisisanexcerptfromPierreLeclerc(www.excel-vba.com) Applicationisa VBAobject,ITISEXCEL.Forexample:Application.QuitwillcloseExcelalltogether. Exercise1a Step1:OpenanewworkbookinExcelandusetheALT/F11keystogotothevisualbasiceditor(VBE). Step2:Copythefollowingmacrointhecodewindow ofanysheet.Asyoucanread,youareaskingExceltocloseitself. SubtestLesson13a1()     Application.Quit EndSub Step3:Asyouhavelearnedinlesson7,gotoExcelandrunthemacro fromthemenubar(Excelbefore 2007)ortheribbon(Excelsince 2007). Step4:Youwillbeaskedifyouwanttosavetheworkbook.Answer"No"andExcelwillcloseitself. Exercise1b Ifyoudonotwanttobebotheredbythealerttosaveyourworkbookyouwilladdalineofcodetothesmallmacro:ActiveWorkbook.Saved=True Step1:OpenanewworkbookinExcelandusetheALT/F11keystogotothevisualbasiceditor(VBE). Step2:Copythefollowingmacrointhecodewindow ofanysheet.Asyoucanread,youareaskingExceltocloseitselfbutsaying firstthattheworkbookhasalreadybeensaved. SubtestLesson13a1()     ActiveWorkbook.Saved=True     Application.Quit EndSub Step3:RunthemacrofromExcelasyoudidwiththepreviousone. Excelwilljustcloseitselfwithoutaskingyouanything. TherearemanyotherwordsthatyoucanuseincombinationwithApplication.Amongthem,twoimportantwordsare: ScreenUpdating(Application.ScreenUpdating)WhenyoudonotwanttoseeyourscreenfollowtheactionsofyourVBAprocedure(macro),youstartandendyourcodewiththefollowingsentences:Application.ScreenUpdating=False Thenattheend:Application.ScreenUpdating=True Exercise Step1:OpenanewworkbookinExcelandusetheALT/F11keystogotothevisualbasiceditor(VBE). Step2:Copythefollowingmacrointhecodewindow ofanysheet.Asyoucanread:startingincellA1avalueof"99"willbeenteredintheselectedcellthenthecursorwillmoveonecelldowntoenter"99",repeattheprocessuntiltherownumberoftheselectedcellis3000andcomebacktocellA1. SubtestLesson13b1()     Range("A1").Select     DoUntilSelection.Row=3000         Selection.Value=99         Selection.Offset(1,0).Select     Loop     Range("A1").Select EndSub Step3:RunthemacrofromExcelasyoudidwiththepreviousone. Step4:Removeallthe"99"fromthecells Step5:Copythefollowingmacrointhecodewindowofa newworkbookandrunit.Twolinesofcodehavebeenaddedtothepreviousmacrotopreventallthestepsoftheactiontobeseenonthescreen. SubtestLesson13b2()     Application.ScreenUpdating=False     Range("A1").Select     DoUntilSelection.Row=3000         Selection.Value=99         Selection.Offset(1,0).Select     Loop     Range("A1").Select     Application.ScreenUpdating=True EndSub Step6:RunthemacrofromExcelasyoudidwiththepreviousone.Youwillseeablanksheet,nomovementwhatsoeverandthenasheetwherecellsA1toA3000areequalto"99". Sometimesyouortheusersmightwanttoseetheaction.Someothertimesyouortheuserdonotwanttoseetheaction.Itisuptoyoutousethesentenceornot. Youcanevenusethepairofsentences(asbelow)anywherewithinalongmacrotorefreshthescreenatsignificantpointsintheprocess.WiththepairofsentencesyoucallforarefreshmentwithApplication.ScreenUpdating=True andtheninterrupttherefreshmentprocessuntilthenextrefreshmentwithApplication.ScreenUpdating=False.BeforetheendofthemacroyouwilluseafinalApplication.ScreenUpdating=True. Thepairofrefreshingsentences: Application.ScreenUpdating=True Application.ScreenUpdating=False Step7:Closetheworkbookwithoutsavinganything  Goto thenextlesson Lesson14 :VBAforExcelforWorkbooks  Backhome