what is the best easy way to show a progress bar while this code is running

I had this question after viewing VBA modification instead of too many OR function, how to use array {2011, 2012, 2013}.

Sub macro3()
intYearCol = Range("dataYear").Column
intMonthCol = Range("dataMonth").Column
intProductCol = Range("dataPRODUCT").Column
intAmountCol = Range("dataAMOUNT").Column
Set ShD = Sheets("Data")
For Each C In Range(ShD.Range("a2"), ShD.Range("a" & Rows.Count).End(xlUp))
    If (ShD.Cells(C.Row, intYearCol).Value >= 2011 Or ShD.Cells(C.Row, intYearCol).Value <= 2013) _
            And ShD.Cells(C.Row, intMonthCol).Value < 111 And _
            ShD.Cells(C.Row, intProductCol).Value Like "[5-7]*" Then
        mySum = mySum + ShD.Cells(C.Row, intAmountCol).Value
    End If
Next
Sheets("Main").Range("B3") = mySum
End Sub

Open in new window


how can i add a progress bar for this code. i want the progress bar to show the progress when the code is running.

thanks.
LVL 6
FloraAsked:
Who is Participating?
 
NorieVBA ExpertCommented:
Perhaps something like this?
Sub MacroAll()
Dim Main As Worksheet

    Set Main = Sheets("Main")
    Set ShD = Sheets("Data")

    intYearCol = Range("dataYear").Column
    intMonthCol = Range("dataMonth").Column
    intProductCol = Range("dataPRODUCT").Column
    intAmountCol = Range("dataAMOUNT").Column

    For Each cl In Range(Main.Range("B3"), Main.Range("K14"))
        mySum = 0
        Application.StatusBar = "Processing month " & Main.Cells(cl.Row, 1).Value & " for year " & Main.Cells(2, cl.Column).Value
        For Each c In Range(ShD.Range("a2"), ShD.Range("a" & Rows.Count).End(xlUp))

            If ShD.Cells(c.Row, intYearCol).Value = (Main.Cells(2, cl.Column).Value2 * 1) And ShD.Cells(c.Row, intMonthCol).Value = (Main.Range("A" & cl.Row).Value2 * 1) _
               And ShD.Cells(c.Row, intMonthCol).Value <> 111 And _
               ShD.Cells(c.Row, intProductCol).Value Like "[5-7]*" Then
                mySum = mySum + ShD.Cells(c.Row, intAmountCol).Value
            End If
        Next
        cl.Value = mySum
    Next

    Application.StatusBar = False

End Sub

Open in new window

1
 
NorieVBA ExpertCommented:
Flora

Instead of a progress bar, which can sometimes actually slow things down, why not use the status bar to inform the user of the progress?

Sub macro3()
    intYearCol = Range("dataYear").Column
    intMonthCol = Range("dataMonth").Column
    intProductCol = Range("dataPRODUCT").Column
    intAmountCol = Range("dataAMOUNT").Column

    Set ShD = Sheets("Data")

    For Each C In Range(ShD.Range("a2"), ShD.Range("a" & Rows.Count).End(xlUp))

       Application.StatusBar = "Processing row " & C.Row

        If (ShD.Cells(C.Row, intYearCol).Value >= 2011 Or ShD.Cells(C.Row, intYearCol).Value <= 2013) _
                And ShD.Cells(C.Row, intMonthCol).Value < 111 And _
                ShD.Cells(C.Row, intProductCol).Value Like "[5-7]*" Then
            mySum = mySum + ShD.Cells(C.Row, intAmountCol).Value
            
        End If
    Next

    Sheets("Main").Range("B3") = mySum

End Sub

Open in new window


Perhaps not as visually appealing as a nice progress bar but it could do the job.:)
0
 
FloraAuthor Commented:
thanks Norie.  you hit the nail on the head.

it worked for my macro. but what if i have two for loops?  where should i place Application.StatusBar = "Processing row " & C.Row

Sub MacroAll()
Dim Main As Worksheet
intYearCol = Range("dataYear").Column
intMonthCol = Range("dataMonth").Column
intProductCol = Range("dataPRODUCT").Column
intAmountCol = Range("dataAMOUNT").Column
Set Main = Sheets("Main")
Set ShD = Sheets("Data")

For Each cl In Range(Main.Range("B3"), Main.Range("K14"))
mySum = 0
    For Each c In Range(ShD.Range("a2"), ShD.Range("a" & Rows.Count).End(xlUp))
        If ShD.Cells(c.Row, intYearCol).Value = (Main.Cells(2, cl.Column).Value2 * 1) And ShD.Cells(c.Row, intMonthCol).Value = (Main.Range("A" & cl.Row).Value2 * 1) _
                And ShD.Cells(c.Row, intMonthCol).Value <> 111 And _
                ShD.Cells(c.Row, intProductCol).Value Like "[5-7]*" Then
            mySum = mySum + ShD.Cells(c.Row, intAmountCol).Value
        End If
    Next
cl.Value = mySum
Next

End Sub

Open in new window


also at the end the status bar remain with a number shall i put Application.StatusBar = ""   or Application.StatusBar = False? at the end of the code?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
NorieVBA ExpertCommented:
To 'reset' the status bar use this just before End Sub.
Application.StatusBar = False

Open in new window

As for having two loops, you could still have Application.StatusBar... in the same place but you could adjust the message to reflect what you are actually doing.

What exactly is the code doing?

Do you have years in row 2 on the sheet 'Main'?
0
 
FloraAuthor Commented:
Thanks Norie.

here is the complete workbook with its code.

problem is, when i put the that status bar there, the next loop keeps repeating it. the large count which takes longer than the actual timing of the macro.
EE.xlsb
0
 
NorieVBA ExpertCommented:
Well your inner loop is running over the same rows multiple times, so to make the status bar idea work we would need to adjust what appears in it.

Let's say you were doing some sort of calculation/processing involving the year, then we could adjust it to show something like this.

Processing row X for year Y.

Is that the kind of thing you would be looking for?
0
 
FloraAuthor Commented:
yes.  thanks.  anything that would show something in status bar. until the macro is finished running.  

Application.StatusBar = "Processing row " & C.Row  worked for the first code i posted. but only it became repetitive with i had the second loop. i posted the workbook.
0
 
FloraAuthor Commented:
perfect!   thanks a million Norie.
0
 
Aleksandr M.EconomistCommented:
Flora, possibly you'd better look towards the way of optimizing your code rather than implementing progress bars? You could significantly decrease the time by means of making a number of calls from VBA to worksheet object not so frequent. I've rewritten your code a bit and substituted direct references to a worksheet with references to a virtual array. Give it a try and you will see the difference.
P.S. I left Application.Statusbar changes but they are really hardly visible, so they can be commented in the future
Option Base 1

Public Enum DATA_STRUCTURE
    YEAR_FIELD = 1
    MONTH_FIELD = 2
    PRODUCT_FIELD = 3
    AMOUNT_FIELD = 4
End Enum

Public Type DATE_RANGE
    START_YEAR  As Integer
    END_YEAR    As Integer
End Type

Sub MacroAll()
Const SH_MAIN_NAME      As String = "Main"
Const SH_DATA_NAME      As String = "Data"
Const OUTPUT_RNG_ADR    As String = "B3:K14"

Dim Years       As DATE_RANGE
Dim vSourceData As Variant
Dim vOutputData As Variant
Dim rngOutput   As Range
Dim lRow        As Long
Dim j           As Long
Dim iYear       As Integer
Dim iMonth      As Integer
Dim mySum       As Double

'//Setting years (IMPORTANT ASSUMPTION: YEARS HAVE TO GO ONE AFTER ANOTHER IN ASC ORDER)
With Sheets(SH_MAIN_NAME).Range(OUTPUT_RNG_ADR)
    Years.START_YEAR = CInt(.Resize(1, 1).Offset(-1, 0).Value)
    Years.END_YEAR = CInt(.Resize(1, 1).Offset(-1, .Columns.Count - 1).Value)
End With

'//Defining source range
With Sheets(SH_DATA_NAME)
    lRow = .Cells(.Rows.Count, 1).End(xlUp).Row
    vSourceData = .Range(.Cells(2, DATA_STRUCTURE.YEAR_FIELD), .Cells(lRow, DATA_STRUCTURE.AMOUNT_FIELD)).Value
End With

'//Initiating output array
ReDim vOutputData(12, Years.END_YEAR - Years.START_YEAR + 1)

For iYear = Years.START_YEAR To Years.END_YEAR
    Application.StatusBar = "Processing " & iYear & " year... "
    For iMonth = 1 To 12
        
        mySum = 0
        
        For j = LBound(vSourceData) To UBound(vSourceData)
            If vSourceData(j, DATA_STRUCTURE.YEAR_FIELD) = iYear Then
                If vSourceData(j, DATA_STRUCTURE.MONTH_FIELD) = iMonth Then
                    If Left$(vSourceData(j, DATA_STRUCTURE.PRODUCT_FIELD), 1) Like "[5-7]" Then
                        mySum = mySum + vSourceData(j, DATA_STRUCTURE.AMOUNT_FIELD)
                    End If
                End If
            End If
        Next j
        
        vOutputData(iMonth, iYear - Years.START_YEAR + 1) = mySum
        
    Next iMonth
Next iYear

'//Writing results onto a sheet
Set rngOutput = Sheets(SH_MAIN_NAME).Range(OUTPUT_RNG_ADR)
rngOutput.Value = vOutputData

Application.StatusBar = vbNullString
End Sub

Open in new window

4
 
FloraAuthor Commented:
@Aleksandr M  

You are amazing!    

thank you very much.


i was just wondering, how can i replace these four items in your code with my actual Named Ranges.

since i already closed this question. i am opening a new question so that you get the marks.  thanks a million.

replacing DATA_STRUCTURE.MONTH_FIELD  with  Named Range  "DataMonth"
replacing  DATA_STRUCTURE.AMOUNT_FIELD with  Named Range  "DataAMOUNT"
replacing  DATA_STRUCTURE.PRODUCT_FIELD with  Named Range  "DataPRODUCT"
replacing DATA_STRUCTURE.YEAR_FIELD  with  Named Range "DataYear"


edit:  opened question link https://www.experts-exchange.com/questions/29069762/a-small-modification-to-the-code-by-helped-byAleksandr-M.html
0
 
Aleksandr M.EconomistCommented:
DATA_STRUCTURE is just giving us an understanding what is the order of columns in the source data range, but do not represent ranges themselves. If you wish to address columns of your named ranges then you can convert them into usual variables or create a new type variable instead of Enum.
Something like:
Private type STRUCTURE
   MONTH_FIELD as integer
   AMOUNT_FIELD as integer
   .....
end type

Open in new window

And then in your Subroutine you should declare an instance of this type (for ex. Dim DATA_STRUCTURE as STRUCTURE) and assign column numbers as you did it in your initial code for intYearCol, intMonthCol variables similar to this:
    DATA_STRUCTURE.YEAR_FIELD = Range("dataYear").Column
    DATA_STRUCTURE.MONTH_FIELD = Range("dataMonth").Column
    ....

Open in new window

BUT be aware of the thing which I wrote you about at the top of the post - the code I provided assumes columns to be in a range 1 to 4. And If you put one of your named ranges in column "G" for example, then you will get an error.
If you wish not to use Enums or custom types at all then you would need to make more alterations to the code. In this case I would propose to Declare 4 variants and populate them with data from named ranges, which you wrote about. But this would require more changes to the code as i've stated already.
0
 
FloraAuthor Commented:
Thanks very much.


i could not make the enum work, but instead i just replaced with declaring 4 variant as you recommended and it worked perfectly.

i am amazed with the speed. it is 100 times faster than the old code.

here is the complete code.

Option Base 1

Public Type DATE_RANGE
    START_YEAR  As Integer
    END_YEAR    As Integer
End Type


Sub MacroAll()
Const SH_MAIN_NAME      As String = "Main"
Const SH_DATA_NAME      As String = "Data"
Const OUTPUT_RNG_ADR    As String = "B3:K14"

Dim Years       As DATE_RANGE
Dim vSourceData As Variant
Dim vOutputData As Variant
Dim rngOutput   As Range
Dim lRow        As Long
Dim j           As Long
Dim iYear       As Integer
Dim iMonth      As Integer
Dim mySum       As Double


    intYearCol = Range("dataYear").Column
    intMonthCol = Range("dataMonth").Column
    intProductCol = Range("dataPRODUCT").Column
    intAmountCol = Range("dataAMOUNT").Column

'//Setting years (IMPORTANT ASSUMPTION: YEARS HAVE TO GO ONE AFTER ANOTHER IN ASC ORDER)
With Sheets(SH_MAIN_NAME).Range(OUTPUT_RNG_ADR)
    Years.START_YEAR = CInt(.Resize(1, 1).Offset(-1, 0).Value)
    Years.END_YEAR = CInt(.Resize(1, 1).Offset(-1, .Columns.Count - 1).Value)
End With

'//Defining source range
With Sheets(SH_DATA_NAME)
    lRow = .Cells(.Rows.Count, 1).End(xlUp).Row
    vSourceData = .Range(.Cells(2, intYearCol), .Cells(lRow, intAmountCol)).Value
End With

'//Initiating output array
ReDim vOutputData(12, Years.END_YEAR - Years.START_YEAR + 1)

For iYear = Years.START_YEAR To Years.END_YEAR
    Application.StatusBar = "Processing " & iYear & " year... "
    For iMonth = 1 To 12
        
        mySum = 0
        
        For j = LBound(vSourceData) To UBound(vSourceData)
            If vSourceData(j, intYearCol) = iYear Then
                If vSourceData(j, intMonthCol) = iMonth Then
                    If Left$(vSourceData(j, intProductCol), 1) Like "[5-7]" Then
                        mySum = mySum + vSourceData(j, intAmountCol)
                    End If
                End If
            End If
        Next j
        
        vOutputData(iMonth, iYear - Years.START_YEAR + 1) = mySum
        
    Next iMonth
Next iYear

'//Writing results onto a sheet
Set rngOutput = Sheets(SH_MAIN_NAME).Range(OUTPUT_RNG_ADR)
rngOutput.Value = vOutputData

Application.StatusBar = vbNullString

End Sub

Open in new window

0
 
Aleksandr M.EconomistCommented:
i could not make the enum work, but instead i just replaced with declaring 4 variant as you recommended and it worked perfectly
I'm glad you understood the idea ) Have a nice day
0
 
FloraAuthor Commented:
thanks very much Aleksandr.

i very much appreciated your help.  

Have a great day! too.

best regards,
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.