vba keep info that macro is running in Excel

Q196. I have a macro that takes a lot of time for execution - how can I keep the user informed that the macro is running?

For example, I have a macro that takes couple of minutes to execute as it processes voluminous information - sometime it is not clear during waiting if the macro is actually running. Is ther a way for me to get status?

To do it in Excel, here is the answer:

  1. Option Explicit
  2. Sub SubRoutineSample()
  3. Dim i As Long
  4. For i= 2 to 20000
  5. Application.StatusBar = "Processing Record " & i
  6. ' complex
  7. ' macro
  8. ' function
  9. ' goes here
  10. Next i
  11. Application.StatusBar = False
  12. End Sub

Description:

a) Line 5 - When execution happens, StatusBar at the bottom left of Excel window is updated with details of record being processed. This information helps ascertain the processing status of macro.

excel vba keep info that macro is running

b) Line 11 - To return control of the status bar back to Excel, set its value to FALSE.

 

You can find similar Excel Questions and Answer hereunder

1) How can I get the count of number of series in a Chart using VBA?

2) How can I dynamically add a hyperlink using VBA?

3) How can I turn off Alerts using VBA?

4) How can I set Page orientation, Zoom % , Title Rows and footer using VBA?

5) I have a complex WorkSheet with lot of fields and data - How can I quickly find the cells that have formulas?

6) How can I loop through all ActiveX checkboxes in WorkSheet and set them to Unchecked status?

7) Vba delete entire row if contains certain text in Excel

8) How can I activate a routine when there is a change in value of a cell?

9) How to disable ability to insert Rows and Columns in Excel (using VBA)?

10) How can I sort data using VBA?

11) How do I copy a Table from one location to another and retain all formulas, formats and columnwidths?

12) How can I get users to select a file for processing using my macro?

13) How can I extract file name from a full path including folder path and file name?

14) How to change desktop background in Excel

15) How can I get input from user through a prompt and assign user's input to a cell?