• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to show progress bar while running the macro

ThrottleWorks

ThrottleWorks

Excel Ninja
Is it possible to show a progress bar while running the macro, so that the user will know macro is stil working


I have used screen updating false to redue the time, but the macro still takes 30 minutes to run


I want to use the progess bar so that user will be aware that macro is running & should not be stopped abruptly


Can anyone help me in this please
 
Good day sachinbizboy


Put this in the search box "progress bar while running the macro" and this link pops up


http://chandoo.org/forums/topic/need-a-fix-to-enable-this-marco-in-my-existing-workbook-6?message=spammed
 
Hi sachinbizboy,


You can also see the below link where I answered to the similar kind of a question earlier.


http://chandoo.org/forums/topic/is-it-possible-1


Please download the file and let us know if this is what something that you are looking for.


Kaushik
 
Hi !   Must see this link :  http://www.andypope.info/vba/pmeter.htm


Another way is to use a Windows API …


But a progress bar could slow down your program, for example, I have a process which takes 18s to run with it

but takes only 3s without !


Maybe you could use the status bar with a % value of progression …
 
Hi Sachin ,


All of those who have posted earlier might certainly be of help ; my take on your problem is :


Either you have a typographical error , and 30 minutes is actually 30 seconds ; in this case just ignore my post.


If however , you really mean 30 minutes , then you need something more than a progress bar. 50 years back , batch processing programs running on punched cards probably took 30 minutes to run ; I can't imagine anything that nowadays can take 30 minutes to run , unless you are trying to predict the weather !


Even reducing the run time from 30 minutes to 20 minutes will probably be of more help to the user than a progress bar.


Narayan
 
Hi Narayan Sir,


You are correct, this is the main concern. Thanks for highlighting.

The macro actually takes 50 minutes ! to run.

I am trying to reduce the running time but I have to be extremely careful & sure while doing changes.


The person who has designed the macro has not put any comment in the module.

For Each loop is used extensively & sometimes the formulas are written in the code & sometimes they are on the worksheet.


I am trying understand all the code & then starting the changes.

Sir what will be your advice to me to reduce the running time.

P.S. - The macro file is almost 2 MB
 
I forgot to mention one more thing, whenever I try to do any changes in the file, the file gets hang or takes eternity to save the changes, this is the main deterent in changing the macro.


Sir could you please advice me how do I identify what is casuing this.
 
Hi Sachin ,


If you can just blank out all the data in the file , and send me the file ( so that the following are available : sheet tabs , named ranges , and of course the macro ) , I can try and see if I can do anything to help out.


My email is narayank1026[at]gmail[dot]com


Narayan
 
Would it be possible to at least post the code, if not in portions? All your For..each loops are probably the main culprit. Other "quick hits"


ScreenUpdating turned off?

Calculations turned off if making lots of changes to sheet?

Access objects directly rather than doing lots of selections?

Cell values stored in variables rather than repetively reading from workbook?

Examples:

Code: 
Sub BadCode()
For Each c In Range("A2:A10")
c.Select
c.Copy
c.Offset(0, 1).Select
ActiveSheet.Paste
c.Offset(0, 2) = c.Value * 2
Next c
End Sub

Sub BetterCode()
Dim myValue As Double
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

For Each c In Range("A2:A10")
myValue = c.Value
c.Offset(0, 1) = myValue
c.Offset(0, 2) = myValue * 2
Next c

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub
 
Last edited:
Good day Luke Sir, thanks a lot for the help.

You are right, I will post the codes, infact I should have done it by now, but somehow could not do it becasue of some reasons.


Screen Updating is off.

I have a doubt, if I put calculation on manual, will it still give me correct results.


You are absolutetly correct, I think my Loop is a BadCode, I need to changes it to BetterCode Courtesy Luke Sir.


Thanks a lot once again.
 
You must log in or register to reply here.
Back
Top