Progress Bar VBA

[jazz2409]

Is it true that using progress bars while your macro is running makes it even slower?

I am looking at including a progress bar so the user is aware of what it's doing already during runtime.

[macropod]

Is it true that using progress bars while your macro is running makes it even slower?
Properly implemented, not so much that anyone would notice. In any event, the mere fact progress is being reported for code that takes a while reassures the user progress is occurring and can thereby make it seem quicker.

[jazz2409]

Properly implemented, not so much that anyone would notice. In any event, the mere fact progress is being reported for code that takes a while reassures the user progress is occurring and can thereby make it seem quicker.


Yes this is what I am thinking..

[Paul_Hossler]

Is it true that using progress bars while your macro is running makes it even slower?

I am looking at including a progress bar so the user is aware of what it's doing already during runtime.

1. I usually only update the progress bar when it changes by 1% by remembering the previous value

2. Or if I'm looping a lot of rows, I'll include something like




If r.row Mod 100 = 1 Then
...update progress bar
End if



There are a lot of progress bars floating around, but this is the one I use. It can be improved I know, but it might give you some ideas

The lines ...



m_NextX = x_Current


m_dPctDone = x_Current / m_TotalX
m_sPctDone = Format(m_dPctDone, "0%")

'if the displayed percent hasn't changed from the last value just exit
If m_sPctDone = m_sPctDoneOld Then Exit Function





... are the quick exit out of update if there's no change

[paulked]

I tend to use modeless userforms at certain milestones like the attached.

[paulked]

Very impressive Paul H. :bow: What impact does it have on the run-times?

[Paul_Hossler]

Depends on how much eye candy you turn on

In general, I'm sure there are some machine cycles consumed, but it 'feels' faster since the user can see something is happening. So in terms of wall clock time, I'd say it's imperceptible

The WaitTime parameters in Update and Done which are passed to Sleep can be set to 0 or low number




Sub test5()
Dim i As Long


Call progBarInit("Test Progress Bar", "Processing Your Data", 100000, False, 0, False, False)


For i = 1 To 100000
If progBarUpdate(CDbl(i), 0) Then Exit For
Next i
Call progBarDone("We're all DONE!!!!", 0)
End Sub

[paulked]

I like it, great job :thumb

[Paul_Hossler]

Too kind <blush>

If you're interested, I have a 'Status Message' similar to the progress bar, but it just displays a text update (words or %)

Button 6 and 7 has a little demo

BTW, I'm open (very) to suggestions or criticisms so feel free


My personal style is to try and make re-usable modules that I can just drop into new projects. I don't care too much about possible dead code usually, but I try to keep each toolbox module doing just one thing it's not much of an issue

I keep the latest (and hopefully greatest) version of the 'toolbox' modules in Library.XLSM with a version suffix (e.g. _06 or _01) so that if I am back working with a project from long ago, I know that I need to update module_something_02 in the old project with the newer module_something_07 from my Library


Much of this was based on code that I found on the web or collected over the years, but I don't have the original source

[jazz2409]

Hi Paul's :)
Is it worth adding a progress bar if the runtime can reach up to 3 minutes (I currently have 8000 rows of raw data that need to be calculated and then show into a table as in the one in the other thread)?
I only have two subs: The main sub (which does everything else) and the other sub for creating the table (as in the one in the other thread)

What I am thinking about doing is the progress bar will show a percentage as well as what the macro is currently doing (like setting up the weekly/monthly sheet, etx)

[Paul_Hossler]

Depends on how long the creating table takes

IMHO - I'd probably just do the progress bar for the 1 to 8000. If you only have (say) 500 rows then I don't think you'd need a progress indicator if it's only 4-5 seconds


What is the other thread?

[paulked]

@Paul H

Very nice too! I keep mine in Routines.xlam, it's good to be organised (but my wife will tell you I'm not great at that!)

@Jazz

I would say that anything over 20 seconds is a long time. Excel can appear to 'hang' (although it hasn't!) during long routines because Windows can't see what's going on under the bonnet (or hood in US!). Using a progress indicator can stop this happening, but not all the time!

[jazz2409]

Depends on how long the creating table takes

IMHO - I'd probably just do the progress bar for the 1 to 8000. If you only have (say) 500 rows then I don't think you'd need a progress indicator if it's only 4-5 seconds


What is the other thread?


@Jazz

I would say that anything over 20 seconds is a long time. Excel can appear to 'hang' (although it hasn't!) during long routines because Windows can't see what's going on under the bonnet (or hood in US!). Using a progress indicator can stop this happening, but not all the time!


This one: http://www.vbaexpress.com/forum/showthread.php?67224-Creating-a-single-Table-with-different-formulas-Excel-VBA

Before that will run I have another sub..
1) Gets data from Access db
2) Shows in one of my sheets in my Excel file
3) Sets up weekly and monthly sheets
4) Calculates weekly and monthly sheets
5) Sets up and calculates the main sheet (the link I added above)

So far I have 8000 rows (and counting! :rotlaugh:) of raw data that will be calculated into the weekly and monthly sheets. I optimized my codes by removing all select, copy, activate, etc. Initially the entire thing runs for over 5 or 6 minutes, now max so far is 2 minutes but could run a little longer in the coming days/weeks as not every department in our company has submitted their raw data.

So far I have:

8000 rows of raw data
332 sub categories
10 weeks
3 MTD dates

[Paul_Hossler]

OK -- I was following your other thread, but didn't have anything to contribute

Attach your latest, greatest, and finalest version here (after you load the Access db) and include the steps to run to the 8000 rows part

[jazz2409]

Hi Paul, I am not sure how to go about doing that because those are all client names and details that I can't load here.. Basically I get the weighted average for each week and month. The criteria would be category, sub category, and date. I do sumproduct to compute all those.

[Paul_Hossler]

Well, what I do if I have a long running macro process with different steps I'll just do a series of text statusing messages ("Reading data", "Sorting Data", Deleting Old Records", etc.)

But if I have a long process (like reading and processing lots of records, I'll do a progress bar

[jazz2409]

What do you mean text statusing? You mean like in Excel's actual status bar?

I tried to incorporate your progress bar from one of your posts here in this thread. But it just froze and it wasn't showing anything. Maybe I should just give up on the idea of adding one.

[paulked]

Have another look at mine, so simple and ideal for what you want to do.

[jazz2409]

Have another look at mine, so simple and ideal for what you want to do.

Hi Paul, I am actually planning to use yours instead. Hopefully it's not gonna go blank on me during runtime :D

[paulked]

It shouldn't if you remember the DoEvents to make sure it updates :wink:

[Paul_Hossler]

What do you mean text statusing? You mean like in Excel's actual status bar?

I tried to incorporate your progress bar from one of your posts here in this thread. But it just froze and it wasn't showing anything. Maybe I should just give up on the idea of adding one.

Well, I have DoEvents in incorportated into my modules in PB_Demo_1.xlsm modules in Post #9 and none of them freeze. Do you have events turned off? Did you call the 3 Subs like in the Usage:?


' ver 06 4/24/2020' rewrite
'
' Requires
' ufStatusMessage
'
'Usage
'Sub test()
'
' Call msgStatusInit("Starting up", "Testing Status Message")
' Call msgStatusUpdate("Now doing 1 " & ".......", "Step 1 of 3", 1000)
' Call msgStatusUpdate("Now doing 2 " & ".......", "Step 2 of 3", 1000)
' Call msgStatusUpdate("Now doing 3 " & ".......", "Step 3 of 3", 1000)
' Call msgStatusDone("Yea!!! All Done", 2000)
'
'End Sub
'
'
'Sub test2()
' Dim i As Long
'
' Call msgNumberInit("Now Processing", 200, "Testing Percent Status Message")
' For i = 1 To 200
' Call msgNumberUpdate(i, 200)
' Next i
' Call msgNumberDone("Finished!!", 1000)
'End Sub


Not Excel's status bar. Button6 displays a text message in a UserForm box with status information, Button7 displays a text message with percent complete

[jazz2409]

Hi Paul H, Here's a sample workbook. I could not include all the data because it would be too difficult to change things

[Paul_Hossler]

I added some text progress messages to the macro that the button runs

There's an awful lot of calculation going on

Do you need to use VBA to insert formulas which have to be calculated, or can you just use the data in the macro and put the result n the worksheet?

[jazz2409]

Hmmmm I believe I need the formula because the data are daily and I need to compute the weekly and monthly.. :(
but then I convert all of them to values only, I just forgot to add the sub :D


Is it possible to get the same results if I use the data in the macro and put the result n the worksheet?

[Paul_Hossler]

I'd say so, if the user doesn't change the inputs, then the macro can recalc the dependent data

There's a hybrid approach - use a macro to compute 'static' parameters and have formulas in the WS to use any dependent data that the user might change

For (made up) example

1. Load raw data
2. Process data with macro to add any derived values as values not formulas (e.g. add column for MM/YY and fill it in the macro from a Date as a Value).
3. Make a pivot table


These are very simple examples, no IF's for example



Option Explicit


'math way
Sub DoData()
Dim rData As Range
Dim r As Long, c As Long
Dim x As Double

Set rData = Worksheets("Macro").Cells(1, 1).CurrentRegion


For r = 2 To rData.Rows.Count
x = 0#
For c = 1 To rData.Columns.Count - 1
x = x + rData.Cells(r, c).Value
Next c

rData.Cells(r, rData.Columns.Count).Value = x
Next r


End Sub


'WS formula way
Sub DoData1()
Dim rData As Range, rTemp As Range
Dim r As Long, c As Long
Dim x As Double

Set rData = Worksheets("Macro").Cells(1, 1).CurrentRegion


For r = 2 To rData.Rows.Count
Set rTemp = Range(rData.Cells(r, 1), rData.Cells(r, rData.Columns.Count - 1))
rData.Cells(r, rData.Columns.Count).Value = Application.WorksheetFunction.Sum(rTemp)
Next r
End Sub