Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Progress Bar
Hi -
I have a macro that takes quite some time. There are several loops that run inside of it to delete or consolidate rows (which can be up to 40,000 rows). The macro can take about 20 seconds to 1.5 minutes. I would like to create a Progress Bar to let the user know that how long to expect (and that the macro is actually running...). I have viewed the website http://www.enhanceddatasystems.com/E...rogressBar.htm , but I do quite understand how to incorporate this into my macro. If anyone has any suggestions, I would really appreciate it. Thank you in advance. -- Jeff |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Progress Bar
On Apr 4, 3:53*pm, Jeff wrote:
Hi - I have a macro that takes quite some time. *There are several loops that run inside of it to delete or consolidate rows (which can be up to 40,000 rows). * The macro can take about 20 seconds to 1.5 minutes. *I would like to create a Progress Bar to let the user know that how long to expect (and that the macro is actually running...). *I have viewed the websitehttp://www.enhanceddatasystems.com/ED/Pages/ExcelProgressBar.htm, but I do quite understand how to incorporate this into my macro. If anyone has any suggestions, I would really appreciate it. * Thank you in advance. -- Jeff Hi Jeff, If you can place your loop code, or at least an abbreviated version it would probably make it easier to suggest the best method. Also, have you considered just using a statusbar notification? Even 1.5min isn't rediculously long, and as a user as long as I had some sort of indication that things were still ticknig along I would be happy. I'd be reluctant to slow things down too much more by adding even more code such as a form with a progressbar etc. Different if it was a 2 hour peocess... then the overhead of the progressbar would be negligible in effect compared to the overall run if you know what I mean. Here is some code for a StatusBar progress message: Sub StatusText() Dim i As Long, Target As Long Target = 1000000 For i = 1 To Target If i * 100 Mod Target = 0 Then Application.StatusBar = i * 100 _ / Target & "% Complete" End If DoEvents Next Beep Application.StatusBar = False End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Progress Bar
Hi -
the status bar would work fine too. Where would I put this in my code so that it is some-what accurate? Thanks! -- Jeff "Ivyleaf" wrote: On Apr 4, 3:53 pm, Jeff wrote: Hi - I have a macro that takes quite some time. There are several loops that run inside of it to delete or consolidate rows (which can be up to 40,000 rows). The macro can take about 20 seconds to 1.5 minutes. I would like to create a Progress Bar to let the user know that how long to expect (and that the macro is actually running...). I have viewed the websitehttp://www.enhanceddatasystems.com/ED/Pages/ExcelProgressBar.htm, but I do quite understand how to incorporate this into my macro. If anyone has any suggestions, I would really appreciate it. Thank you in advance. -- Jeff Hi Jeff, If you can place your loop code, or at least an abbreviated version it would probably make it easier to suggest the best method. Also, have you considered just using a statusbar notification? Even 1.5min isn't rediculously long, and as a user as long as I had some sort of indication that things were still ticknig along I would be happy. I'd be reluctant to slow things down too much more by adding even more code such as a form with a progressbar etc. Different if it was a 2 hour peocess... then the overhead of the progressbar would be negligible in effect compared to the overall run if you know what I mean. Here is some code for a StatusBar progress message: Sub StatusText() Dim i As Long, Target As Long Target = 1000000 For i = 1 To Target If i * 100 Mod Target = 0 Then Application.StatusBar = i * 100 _ / Target & "% Complete" End If DoEvents Next Beep Application.StatusBar = False End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Progress Bar
On Apr 5, 2:29*am, Jeff wrote:
Hi - the status bar would work fine too. *Where would I put this in my code so that it is some-what accurate? Thanks! -- Jeff "Ivyleaf" wrote: On Apr 4, 3:53 pm, Jeff wrote: Hi - I have a macro that takes quite some time. *There are several loops that run inside of it to delete or consolidate rows (which can be up to 40,000 rows). * The macro can take about 20 seconds to 1.5 minutes. *I would like to create a Progress Bar to let the user know that how long to expect (and that the macro is actually running...). *I have viewed the websitehttp://www.enhanceddatasystems.com/ED/Pages/ExcelProgressBar.htm, but I do quite understand how to incorporate this into my macro. If anyone has any suggestions, I would really appreciate it. * Thank you in advance. -- Jeff Hi Jeff, If you can place your loop code, or at least an abbreviated version it would probably make it easier to suggest the best method. Also, have you considered just using a statusbar notification? Even 1.5min isn't rediculously long, and as a user as long as I had some sort of indication that things were still ticknig along I would be happy. I'd be reluctant to slow things down too much more by adding even more code such as a form with a progressbar etc. Different if it was a 2 hour peocess... then the overhead of the progressbar would be negligible in effect compared to the overall run if you know what I mean. Here is some code for a StatusBar progress message: Sub StatusText() * * Dim i As Long, Target As Long * * Target = 1000000 * * For i = 1 To Target * * * * If i * 100 Mod Target = 0 Then * * * * * * Application.StatusBar = i * 100 _ * * * * * * * * * / Target & "% Complete" * * * * End If * * * * DoEvents * * Next * * Beep * * Application.StatusBar = False End Sub- Hide quoted text - - Show quoted text - Hi Jeff, If you only had one loop, I could answer that easily. Since you say you have several though, it is a bit tricky to say without seeing the structure. The idea behind the code that I posted is that 'Target' is the total number of iterations of the loop. 'i' (the counter) is the current loop number and therefore i * 100 Mod Target will only equal 0 every 1% (if that makes sense). If you changed that line to i * 10 Mod Target, the statusbar would update in 10% intervals. If you have multiple loops, it gets harder since you have to try and work out what portion of the whole process each loop is and break it down that way. This could be easy or impossible. The other option that could be more feasible would be to have multiple status segments. For example, in the first loop you could have the status bar read: "Loading data - 10%" through to 100%, then in your next loop start from zero again but have it read "Analysing Stuff - 10%" through to 100% again and so on. At least this way the count will be accurate and the user will still know that at least something is happening. If your loops are complex and you have tried to segment the time you run a good risk of reverting to the old Windows 95 progress bar syndrome where it used to fly up to 99% in chunks, then seemingly sit there forever while you held your breath and hoped it was still going :). If you're still doubtful, post your code if you can and we'll try to suss it out. Cheers, Ivan. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Progress Bar | Excel Discussion (Misc queries) | |||
Progress Bar | Excel Worksheet Functions | |||
NEED Help - How to capture the Event's progress in Progress Bar | Excel Programming | |||
Progress Bar | Excel Programming | |||
Progress Bar | Excel Programming |