Excel VBA Macro: Progress Bar While Macro is Running (UserForm)
Vložit
- čas přidán 2. 08. 2024
- Excel VBA Macro: Progress Bar While Macro is Running (UserForm). In this video, we go over how to create a status bar that serves as a progress indicator for any users running your macro. The progress bar we make is created with a UserForm that updates as the macro continues to run. Each update shows an increase in bar width and percentage. We also briefly go over the importance of DoEvents and vbModeless
Data used in this video:
simplemaps.com/data/world-cities
Code (CZcams doesn't allow brackets; so LT and GT are used for less than and greater than, respectively):
Sub delete_rows()
Dim count As Integer
Dim start As Integer
Dim i As Integer
count = ActiveSheet.Cells(Rows.count, "A").End(xlUp).row
i = 2
OpenStatusBar
Do While i LT= count
If Cells(i, 9) = "" Then
Rows(i).EntireRow.Delete
i = i - 1
End If
DoEvents
Call RunStatusBar(i, count)
i = i + 1
count = ActiveSheet.Cells(Rows.count, "A").End(xlUp).row
Loop
Unload StatusBar
End Sub
Sub OpenStatusBar()
With StatusBar
.Bar.Width = 0
.Frame.Caption = "0% Complete"
.Show vbModeless
End With
End Sub
Sub RunStatusBar(row As Integer, total As Integer)
With StatusBar
.Bar.Width = 246 * (row / total)
.Frame.Caption = Round((row / total) * 100, 0) & "% Complete"
End With
End Sub
#ExcelVBA #ExcelMacro
Great explanation, really understood everything pretty easily.. better than other tutorials on progressbar in vba
Appreciate the feedback!
Thanks so much, I was getting quite frustrated as I found lots of examples online but not one explained exactly how to incorporate my macro; after watching your video it worked first time and is absolutely perfect!!!
Awesome! So glad to hear that!!
Mate that looks so good, am definitely gonna use this technique on my macros going forward. Great video!
Thanks a lot!
Excelente, desde Perú un fuerte abrazo, con esto concluyo mi proyecto !
Thank you very much. Simple and effective.
Best tubewise in both structure and explaining after viewing all others!! Applause applause!! And no dll needed!!
Thank you so much!!
@@greggowaffles omg haven't we been busy!! 😆 What took you so long!! No doubt the best I tested almost them all including some I believe from india!! LoL,🤩 great job simply!!!
Lol I’ve been super busy at work!! I need to make more time for videos. From India? Nice!!! Thanks again!!
Amazing, I am delighted!
Glad to hear that!!
Thank you. Great video.
No problem! Thank you
Great video. Thank you for sharing.
Thanks for watching!
super video! you helped a log. God bless you :)
excellent. Works perfectly. Thank you very much.
No problem. Thank you for watching!
Thanks for sharing 👍
😎👍
Awesome 👍🏽
Thanks for the visit!
Great Video
Thanks!
Great video! Works perfectly for me. While I watched the first time, was wondering why you didn't dim row and total (I'm not a programmer). I tried on my code using the exact same code you share and the Sub RunStatusBar did not work, but it was just a matter of using the variables I've already used instead of row and total and worked perfectly. Thanks!
Hey, man! Great job, thank you so much!
Appreciate the feedback! Thanks for watching!!
@@greggowaffles I have just done and it worked perfectly!!!
😎😎
Thanks very much for the tutorial. I have a personal macro file where I have a code to give me a list of sheets' name. Since it's in my personal macro file, I can run that code in any active workbook and it'll give me the list of sheets' name in that workbook. Is it possible to create a progress bar for that code? Just like a global code, can there be a global progress bar which is attached to the code and will run on any workbook the moment I run the code?
Thank you so much
You are very welcome!
I use for next in my code, where i place the "doevents" ?
It worked bro thanks :))))
Glad to hear that!! No prob 😎
As i am new to the coding world i am facing some difficulties to place a progressbar while macro runs ( progressbar for add, delete, update and search from userform to sheet). could you please help me with that if i send you my file?
مرسی
Is it possible to run the status bar in the video without looping command?
Do you know the impact on computational time something like this has on the program?
Ya, kinda wish he would have run it with and without the bar, on a long data set, just to see if it was appreciable.
I'm currently running a macro that looks like it is headed towards a 45 minute completion time.
That's slow enough that a status bar would be nice, hence why I am here, but not nice enough that I'd want one if it's going to turn it into an hour long completion lol
I’ve been looking for this for a while.... amazing video! For my macro I have to track progress of several Queries refresh, is there a way to track that in vba? Thx
thanks! can you be more specific? im not quite sure what youre asking
@@greggowaffles I think he means - for example let's say you have a few external data connections that query a sharepoint list or another excel file. The WB has a button that triggers a refresh of the data queries, let's say just one in total for this case. Is there a way within visual basic to display the progress, or at least get a confirmation that the query has completed its refresh successfully?
Why the "row" and "total" variables are not defined and the macro works? How the macro understands what they are standing for?
They are being defined when I write "row as Integer" and "total as Integer" at the top of the sub
Hey this is Awesome!! Could you possibly post the code? Thanks great videos.
Thanks a lot! I'm out of town and away from my code right now but I can send it when I get back on Wednesday
Please sir I want this file
just added the code in the description
great code and video! thank you!
but please post the code here!! that makes everything easier for all of us
thanks! your wish is my command!! just added the code to the description 😉 i'll make sure to add the code going forward and will go back and the code to my existing videos as well
greggowaffles, you saved me with this ".Show vbModeless"!!! Thx a lot
No prob! So glad to hear that!!