0 votes
in VBA by Beginner (8 points)

Hi,

 

I just used your code of progress bar.

https://wellsr.com/vba/2017/excel/beautiful-vba-progress-bar-with-step-by-step-instructions/

I think it is awesome, but I am facing some problems, it is making my code run lot lot slower than before.

This is because it reads and updates the bar each time it handles a row.

This is what I want but I'd like to know if ther is a way to update the bar on each row but without being refreshing the excel.

I tried to insert the screen update and display alerts like I always put, but it did not solve.

    Application.ScreenUpdating = False
    Application.DisplayAlerts = False

What else could I try. Can you help me please.

Thank you

1 Answer

+1 vote
by Skilled (582 points)
selected by
 
Best answer

Modify Step 2 of the tutorial as follows:

Const pctupdate = 0.05 'update bar in 5% increments (change as desired)
.
.
.
pctdone = i / lastrow
If (pctdone Mod pctupdate) = 0 Then
    With ufProgress
        .LabelCaption.Caption = "Processing Row " & i & " of " & lastrow
        .LabelProgress.Width = pctdone * (.FrameProgress.Width)
    End With
    DoEvents
End If

 

by Beginner (8 points)
It gave Runtime error 11 - Division by zero.

So instead of 0.05 I put 5 and still slow.

Also tried with 50, 100 and 1000. Same behaviour :S

Maybe is my code?
by Skilled (582 points)
Maybe is your code. Do you want to post it for review?
by Beginner (8 points)

Hi, good news and also bad news (the bad ones are for me :S)

So today I took another look to the code and somehow a genious idea came to my mind.

What if I change the posicion of the progress bar code. - no notorious change

Another look and better attention on how the code runs and noticed that it goes line by line (like I tried to define in beggining of the post). So what if I just sent the bar to read a code.

Genious!!! The progress bar works just fine and is fast.

The bad news, but for me lol

I have to adapt my code in order the progress bar goes along all the process.

I want the progress bar run while my code do:

  • Reorder columns
  • Text to columns
  • Prepare Pivot Table columns filter

Previous steps are done to a "Previous" sheet and then repeated for a "After" sheet.

Next a Pivot Table is generated.

All those steps are done in same sub, therefor are sequencial.

I want the progress bar to run during all those sequencial steps.

Can it be done? 

In a ruff way this is what I have and is not working well. It is slow as it reads line by line

Is there a way to send entire excel?

Sub UMTS_Monitor_CopyPaste_Generate()
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False

	Dim i As Long, lastrow As Long
	Dim pctdone As Single
	lastrow = Range("A" & Rows.Count).End(xlUp).Row

'*****************************************************************************************
'(Step 1) Display your Progress Bar
	ufProgress.LabelProgress.Width = 0
	ufProgress.Show
	For i = 1 To lastrow
'(Step 2) Periodically update progress bar
    pctdone = i / lastrow
    With ufProgress
        .LabelCaption.Caption = "Processing Row " & i & " of " & lastrow
        .LabelProgress.Width = pctdone * (.FrameProgress.Width)
    End With
    DoEvents
'*****************************************************************************************
 
    Sheets("Alarms_Before").Select
    Call UMTS_Monitor_CopyPaste_Reorder_Columns
    Call UMTS_Monitor_CopyPaste_Alarms_Before_SplitText
    Call UMTS_Monitor_CopyPaste_Pivot_Filters
    Call Delete_Double_Column_Name
    
    Sheets("Alarms_After").Select
    Call UMTS_Monitor_CopyPaste_Reorder_Columns
    Call UMTS_Monitor_CopyPaste_Alarms_After_SplitText
    Call UMTS_Monitor_CopyPaste_Pivot_Filters
    Call Delete_Double_Column_Name
    
    Range("E4").Select
    Call UMTS_Monitor_CopyPaste_Pivot_Table
    Range("B1").Select

'*****************************************************************************************
'(Step 3) Close the progress bar when you're done
    If i = lastrow Then Unload ufProgress
	Next i
'*****************************************************************************************

    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
End Sub

 

 

by Skilled (582 points)

Try this:

Sub UMTS_Monitor_CopyPaste_Generate()
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Dim i
'Number of Call statements
    Const lastCall = 9 '(change if appropriate)
    i = 0
'*****************************************************************************************
'(Step 1) Display your Progress Bar
    Load ufProgress
    ufProgress.LabelProgress.Width = 0
    ufProgress.Show
'*****************************************************************************************
    Sheets("Alarms_Before").Select
    UpdateProgressBar i, lastCall '(Step 2) Periodically update progress bar
    Call UMTS_Monitor_CopyPaste_Reorder_Columns
    UpdateProgressBar i, lastCall '(Step 2) Periodically update progress bar
    Call UMTS_Monitor_CopyPaste_Alarms_Before_SplitText
    UpdateProgressBar i, lastCall '(Step 2) Periodically update progress bar
    Call UMTS_Monitor_CopyPaste_Pivot_Filters
    UpdateProgressBar i, lastCall '(Step 2) Periodically update progress bar
    Call Delete_Double_Column_Name
'*****************************************************************************************
    Sheets("Alarms_After").Select
    UpdateProgressBar i, lastCall '(Step 2) Periodically update progress bar
    Call UMTS_Monitor_CopyPaste_Reorder_Columns
    UpdateProgressBar i, lastCall '(Step 2) Periodically update progress bar
    Call UMTS_Monitor_CopyPaste_Alarms_After_SplitText
    UpdateProgressBar i, lastCall '(Step 2) Periodically update progress bar
    Call UMTS_Monitor_CopyPaste_Pivot_Filters
    UpdateProgressBar i, lastCall '(Step 2) Periodically update progress bar
    Call Delete_Double_Column_Name
'*****************************************************************************************
    Range("E4").Select
    UpdateProgressBar i, lastCall '(Step 2) Periodically update progress bar
    Call UMTS_Monitor_CopyPaste_Pivot_Table
    Range("B1").Select
'*****************************************************************************************
'(Step 3) Close the progress bar when you're done
    Unload ufProgress
'*****************************************************************************************
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
End Sub

Sub UpdateProgressBar(i, lastCall)
    i = i + 1
    With ufProgress
        .LabelCaption.Caption = "Processing " & i & " of " & lastCall
        .LabelProgress.Width = (.FrameProgress.Width * i) / lastCall
    End With
    DoEvents
End Sub

 

by Beginner (8 points)

That just worked like a charm.

I understood well the code and changed a bit as I have a couple more function calls in it.

Now code runs fast and progress bar is update accordingly.

Still see all macro actions on background.

I am searching all around but only tips are to ScreenUpdating and DisplayAlerts to False

But not working here.

Thank you a lot for you help JWoolley your awesome

by Skilled (582 points)
+1
If you are satisfied, please Vote my original Answer (click Up-Arrow next to Answer).

Welcome to wellsr Q&A
wellsr Q&A is the VBA and Python programming community that rewards you for learning how to code.

Getting Started
Register
VBA Cheat Sheets (On Sale Now)

Earn free prizes for asking VBA and Python questions and for answering questions asked by others in our community.

Looking for something else? Hire our professional VBA Help, instead.

What makes us different?
Our points system rewards you with a chance for free gifts based on the quality of your questions and answers. All you have to do is post and you could get rewarded, like these members:

ParserMonster $25 Amazon Gift Card
Hightree $10 Amazon Gift Card
Thales1 $10 Amazon Gift Card
runfunke $10 Amazon Gift Card
coolag $10 Amazon Gift Card
Siew Hun $10 Amazon Gift Card

So, why don't you join us? It really is an encouraging way to motivate members in our VBA and Python community.

Register

For more programming tips visit the VBA Tutorials Blog and the Python Tutorials Blog.

...