Progress Bar VBA

[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


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