Force a screen update in Excel VBA

by

Add a DoEvents function inside the loop, see below.

You may also want to ensure that the Status bar is visible to the user and reset it when your code completes.

Sub ProgressMeter()

Dim booStatusBarState As Boolean
Dim iMax As Integer
Dim i As Integer

iMax = 10000

    Application.ScreenUpdating = False
''//Turn off screen updating

    booStatusBarState = Application.DisplayStatusBar
''//Get the statusbar display setting

    Application.DisplayStatusBar = True
''//Make sure that the statusbar is visible

    For i = 1 To iMax ''// imax is usually 30 or so
        fractionDone = CDbl(i) / CDbl(iMax)
        Application.StatusBar = Format(fractionDone, "0%") & " done..."
        ''// or, alternatively:
        ''// statusRange.value = Format(fractionDone, "0%") & " done..."
        ''// Some code.......

        DoEvents
        ''//Yield Control

    Next i

    Application.DisplayStatusBar = booStatusBarState
''//Reset Status bar display setting

    Application.StatusBar = False
''//Return control of the Status bar to Excel

    Application.ScreenUpdating = True
''//Turn on screen updating

End Sub

More Related Contents:

  1. to make this code simpler
  2. VBA code takes long time to execute in excel
  3. Excel VBA deleting rows in a for loop misses rows
  4. How To Speed Up VBA Code
  5. Execute a command in command prompt using excel VBA
  6. “Unable to get the VLookup property of the WorksheetFunction Class” error [duplicate]
  7. Copy data from another Workbook through VBA
  8. VBA : save a file with UTF-8 without BOM
  9. Excel VBA Load worksheet in Function
  10. Searching an excel with two search words (multiple lookup) using vba macro [closed]
  11. Excel VBA – Combine rows with duplicate values in one cell and merge values in other cell
  12. Load csv file into a VBA array rather than Excel Sheet
  13. Insert line break in wrapped cell via code
  14. Change color of certain characters in a cell
  15. Want VBA in excel to read very large CSV and create output file of a small subset of the CSV
  16. Making VBA Form TextBox accept Numbers only (including +, – and .)
  17. Inserting an Online Picture to Excel with VBA
  18. Change length of For loop while in the loop
  19. VBA – Run Time Error 1004 ‘Application Defined or Object Defined Error’
  20. Consolidating worksheets into one
  21. VBA Macro crashes after 32000 rows
  22. How to create dynamic variable names VBA
  23. How to break a long string into multiple lines
  24. Read VBA macros (or vbaProject.bin) of an Excel file without opening it in MS Excel [closed]
  25. simple vba code gives me run time error 91 object variable or with block not set
  26. How do I open a file if I only know part of the file name?
  27. VBA recognizing workbook by partial name
  28. How do I get a list of unique values from a range in Excel VBA?
  29. How to get selected value in multicolumn listbox
  30. Query my excel worksheet with VBA

Leave a Comment