Display Alerts = False End Sub Public Sub Stroboscope On() Application. Calculation = xl Calculation Automatic Application. Turned it back off for that connection and the workbook no longer flashes.The status bar will show the message “Macro running” as the code is running, and once the code is done, it will show “Ready” which is one of the default Excel messages. Display Status Bar = True With Worksheets(“Sheet1”) lrow = . Option Explicit Sub macro1() Dim i As Long, lrow As Long Application. You can ask the code to display a status message on the status bar so that the user is informed about the current status of the code. Here is a simple code which gives a message box showing the values in column A starting from row 2 to the last row.
Certain vba macros / codes take a long time to run or execute the actions required. Sometimes, there could be one or more macros which cover multiple processes and this status bar will be useful to know at which process, the macro has reached.If you are at an office or shared network, you can ask the network administrator to run a scan across the network looking for misconfigured or infected devices.I want to start off this post by thanking everyone who sent in their examples in response to my January request.If you think I missed an important concept for how to optimize Excel VBA performance, or if you’ve got a valuable comment or link to share, please feel free to post here so everyone can benefit. Turn Off Everything But the Essentials While Your Code is Running This optimization explicitly turns off Excel functionality you don’t need to happen (over and over and over) while your code runs.Note that in the code sample below we grab the current state of these properties, turn them off, and then restore them at the end of code execution. Display Page Breaks = False ‘note this is a sheet-level setting ‘: This setting tells Excel to not redraw the screen while False.If you have turned off screen updating using the line Application. Screen Updating = False at the beginning of your code, then if any user runs the code, they will not know what is going on and think that the computer system is not responding. It is incredibly helpful to be able to look at what you all are doing with Excel!Not only did I see a huge variety in how Excel is being used, you also pointed out various tips and tricks for writing fast VBA code in Excel.One reason this helps is that if you’re updating (via VBA) several different ranges with new values, or copy / pasting from several ranges to create a consolidated table of data, you likely do not want to have Excel taking time and resources to recalculate formulas, display paste progress, or even redraw the grid, especially after every single operation (even more so if your code uses loops). The benefit here is that you probably don’t need Excel using up resources trying to draw the screen since it’s changing faster than the user can perceive.Just one recalculation and one redraw at the end of your code execution is enough to get the workbook current with all your changes. Enable Events display Page Break State = Active Sheet. Since it requires lots of resources to draw the screen so frequently, just turn off drawing the screen until the end of your code execution.