Progress bar variable assigning

[kelly mort]

       Call IntProgressBar
        With Sheet2.Range("D7:D" & lr)
            Set rngFind = .Find(what:=mySearch, After:=Sheet2.Range("D" & lr), _
            LookIn:=xlValues, lookat:=xlWhole, searchdirection:=xlNext)
            If Not rngFind Is Nothing Then
                strFirstFind = rngFind.Address
                Do
                   ListBox1.lstView.AddItem Trim(rngFind.Offset(, -2).Text)
                        For i = 1 To 137
                            ListBox1.lstView.List(ListBox1.lstView.ListCount - 1, i) = _
                            Trim(rngFind.Offset(, i - 2).Text)
        
        ‘ This is where I placed the progress code before, and I ran for a long time
                        Next i
                    Set rngFind = .FindNext(rngFind)
                    
                    Dim currentProgress#, progressPercentage#, BarWidth&
    
                    currentProgress = i / 137
                    BarWidth = Progress.Border.Width * currentProgress
                    progressPercentage = Round(currentProgress * 100, 0)
        
                    Progress.Bar.Width = BarWidth
                    Progress.Text.Caption = progressPercentage & "% complete"
        
                    DoEvents
                Loop While Not rngFind Is Nothing And rngFind.Address <> strFirstFind
            End If
        End With
        Unload Progress

Hello,
I am here again – the challenge gets tougher and interesting. I am trying my skills on the progress bar and I got stacked with this one. I want to show the progress while each row of data is loaded or filtered. I don’t know what to write at where. Can someone pull me out?

Thanks in advance

 

[kelly mort]

Bump

Still need help with this. Can someone help?

 

[GWteB]

The best approach (imo) in such a case is to write code in seperate subs for each individual task. E.g. one sub for doing the search / loading / filtering (in a regular module) and one (or more) subs or functions for initializing and updating your progress bar (obviously in the module of a Userform). Those individual tasks can inform each other with the use of global variables, declared as Public in a regular module.

Splitting up your code in as small as possible subs or functions makes it more easy and more clear. In an other thread I've posted this example (post #29). Although it isn't "just a progress bar" (thus not on a modeless Userform), it could give you an impression how to handle things.

 

[GWteB]

btw for an explanation see post #35 of the same thread

 

[Yongle]

Another option - use StatusBar
- replace Application.Wait with valid code doing something

    Application.ScreenUpdating = False
   
    Application.StatusBar = "job 1"
        'code for job 1
            Application.Wait (Now + TimeValue("00:00:01"))
   
    Application.StatusBar = "job 2"
        'code for job 2
            Application.Wait (Now + TimeValue("00:00:01"))
   
    Application.StatusBar = "job 3"
        'code for job 3
            Application.Wait (Now + TimeValue("00:00:01"))
   
   
    Application.StatusBar = ""

    Application.ScreenUpdating = True

Note
- there are ways to make the StatusBar look like a progress bar

    With Application
    .ScreenUpdating = False
    
    .StatusBar = "|"
        .Wait (Now + TimeValue("00:00:01"))
    .StatusBar = "|||"
        .Wait (Now + TimeValue("00:00:01"))
    .StatusBar = "|||||"
        .Wait (Now + TimeValue("00:00:01"))
    .StatusBar = "||||||||"
        .Wait (Now + TimeValue("00:00:01"))
    .StatusBar = "|||||||||||"
        .Wait (Now + TimeValue("00:00:01"))
          
    .StatusBar = ""
    .ScreenUpdating = True
    End With

 

[kelly mort]

The best approach (imo) in such a case is to write code in seperate subs for each individual task. E.g. one sub for doing the search / loading / filtering (in a regular module) and one (or more) subs or functions for initializing and updating your progress bar (obviously in the module of a Userform). Those individual tasks can inform each other with the use of global variables, declared as Public in a regular module.

Splitting up your code in as small as possible subs or functions makes it more easy and more clear. In an other thread I've posted this example (post #29). Although it isn't "just a progress bar" (thus not on a modeless Userform), it could give you an impression how to handle things.

The best approach (imo) in such a case is to write code in seperate subs for each individual task. E.g. one sub for doing the search / loading / filtering (in a regular module) and one (or more) subs or functions for initializing and updating your progress bar (obviously in the module of a Userform). Those individual tasks can inform each other with the use of global variables, declared as Public in a regular module.

Splitting up your code in as small as possible subs or functions makes it more easy and more clear. In an other thread I've posted this example (post #29). Although it isn't "just a progress bar" (thus not on a modeless Userform), it could give you an impression how to handle things.

If I understand what you are saying correctly, it means what I am asking for is very possible.

But my issue here is that I don't have that level of knowledge to be able use those separate subs and functions as you are suggesting.

I will love it, if you can get that done for me.

Thanks

 

[kelly mort]

@Yongle,

Can you show me, in the code I posted, where to place those lines of yours?

 

[Yongle]

Firstly run this code so that you can see the StatusBar updating
- the String function repeats character | the number of times determined by Round(i / 10, 0)

Sub TestStatusBar()
    Application.ScreenUpdating = False
    Dim i As Long, j As Long
    For i = 1 To 137
        For j = 1 To 10
            Debug.Print i       '(slows the code to allow statusbar update to be visible)
        Next j
        Application.StatusBar = String(Round(i / 10, 0), "|")
    Next
    Application.StatusBar = ""
End Sub

Choosing where to place it in your code depends on what you are trying to demonstrate and what is slowing your code down
- here is one option for you to test

    Application.ScreenUpdating = False             'HERE

    With Sheet2.Range("D7:D" & lr)
        Set rngFind = .Find(what:=mySearch, After:=Sheet2.Range("D" & lr), LookIn:=xlValues, lookat:=xlWhole, searchdirection:=xlNext)
        If Not rngFind Is Nothing Then
            strFirstFind = rngFind.Address
            Do
                ListBox1.lstView.AddItem Trim(rngFind.Offset(, -2).Text)
                For i = 1 To 137
                    ListBox1.lstView.List(ListBox1.lstView.ListCount - 1, i) = Trim(rngFind.Offset(, i - 2).Text)
                    Application.StatusBar = String(Round(i / 10, 0), "|")                  'HERE
                Next i
                Set rngFind = .FindNext(rngFind)
                         
                DoEvents
            Loop While Not rngFind Is Nothing And rngFind.Address <> strFirstFind
        End If
    End With

    Application.StatusBar = ""                 'HERE

 

[GWteB]

If I understand what you are saying correctly, it means what I am asking for is very possible.

Yes it is!

But my issue here is that I don't have that level of knowledge to be able use those separate subs and functions as you are suggesting.

I'm afraid that there's no way to circumvent the hassle of those seperate subs and functions.

I will love it, if you can get that done for me.

I can imagine, but I can't take over your project. You better take a look at this blog: a step-by-step-guide by Ryan Wells
On his website AJP Excel Information Andy Pope provides a few examples for download.

 

[kelly mort]

Firstly run this code so that you can see the StatusBar updating

Cool, I am playing around it and it is nice.

Meanwhile, I was able to use a count function to count for the MySearch variable and using that to get the job done.

Call IntProgressBar
        With Sheet2.Range("D7:D" & lr)
            Set rngFind = .Find(what:=mySearch, After:=Sheet2.Range("D" & lr), _
            LookIn:=xlValues, lookat:=xlWhole, searchdirection:=xlNext)
            
Dim e&, TotCount&
TotCount = Application. CountIf(Sheet2.Range("D7:D" & lr), mySearch)

If Not rngFind Is Nothing Then
                strFirstFind = rngFind.Address
                Do
                  e = e + 1
                   ListBox1.lstView.AddItem Trim(rngFind.Offset(, -2).Text)
                        For i = 1 To 137
                            ListBox1.lstView.List(ListBox1.lstView.ListCount - 1, i) = _
                            Trim(rngFind.Offset(, i - 2).Text)
                        Next i
                    Set rngFind = .FindNext(rngFind)
                    
                    Dim currentProgress#, progressPercentage#, BarWidth&
    
                    currentProgress = e / TotCount
                    BarWidth = Progress.Border.Width * currentProgress
                    progressPercentage = Round(currentProgress * 100, 0)
        
                    Progress.Bar.Width = BarWidth
                    Progress.Text.Caption = progressPercentage & "% complete"
        
                    DoEvents
                Loop While Not rngFind Is Nothing And rngFind.Address <> strFirstFind
            End If
        End With
        Unload Progress