Excel Vba Progress Bar When You Dont Know How Long Out Will Take
Introduction | Design Progress Bar | Add together Progress Bar Macros | Brandish Progress Bar | Final Thoughts
Introduction
Make your macros stand out by creating this cute VBA Progress Bar to illustrate the progress. This tutorial walks you through how to make the sleek UserForm and how to implement it in your next project!
Inclusion of a VBA progress bar is an incredibly useful feature when you accept long macros that take several minutes to complete. Without a periodic status update, your users volition wonder if the macro is however running. Follow this step-by-step guide to learn how to comprise a progress bar into your own spreadsheet.
To go you motivated about what's in store, I'm going to go ahead and testify yous the final design of my VBA progress bar:
Design Progress Bar
Adding your UserForm
The first thing we want to do is create a UserForm and add the elements necessary to plow it into a progress bar. If yous're new to VBA, follow these steps to add a UserForm:
- Open up the VBA Editor (Alt+F11)
- Insert a UserForm
- Right-click the Project Explorer Pane
- Hover over Insert
- Click Userform
Setting the Progress Bar Properties
Once you accept an empty UserForm, go ahead and press F4 to bring upward the Properties Window.
- Change the (Name) of the UserForm to
ufProgress . - Change the ShowModal property to Fake. Setting this property to Faux guarantees other macros will continue to run while the UserForm is open. Check out this tutorial to learn more about the ShowModal holding.
- Adjust the size of your UserForm to something visually pleasing for you. I like to set the Top property of my Progress Bars to
110 and the Width belongings to240 . - If you'd like, you tin can go ahead and update the Caption holding of the UserForm to something like
Running... . This step isn't necessary if y'all want to hibernate the title bar altogether. I'll show you how to practise that later in this tutorial!
Hither'south what my Progress Bar looks like afterward completing these steps:
Designing the UserForm
At present that you have the UserForm configured the fashion we desire information technology, yous tin can brainstorm designing it. There are a few elements we must have for the VBA progress bar to role properly. Follow these steps to brand sure each element is included:
Insert Label
- Insert a characterization in the upper left of your UserForm. This will be used later to display the text indicating the status of your macro.
- Make sure the label is wide plenty for any text you may brandish later. I fix my Width belongings to
174 , simply there'due south nothing magical about that number. - Change the (Name) of the label to
LabelCaption . - Alter the Explanation property to an empty cord (no text).
At this point, your Progress Bar should wait something like this:
Insert Frame
The adjacent stride is to insert a Frame on your UserForm. The frame is the item highlighted in the Toolbox screenshot below:
Yous want to insert the frame then it's roughly centered on your UserForm, both horizontally and vertically, with a trivial margin on each side. It should exist positioned below the label you previously added. At this point, your UserForm will expect something similar this:
To make the frame brainstorm to resemble a progress bar, you'll need to change a few properties. Follow these steps to configure the frame properties:
- Select the frame and change the (Name) property to
FrameProgress - Change the Caption property to an empty cord (no text)
- Change the SpecialEffect belongings to
2 - fmSpecialEffectSunken
At this point, y'all tin start to run into the outline of your progress bar. The progress bar is finally starting to have shape and volition await something similar this:
Insert Another Characterization
Next, nosotros want to insert another characterization, but this characterization is never going to have any text. This label is going to grow equally your macro progresses to make full the frame you just created. Stay with me for a infinitesimal to learn what I mean.
- Insert a label Within the frame y'all just created, and change the height so information technology fits nicely right up against the elevation and lesser of your frame.
- Position the label so the left edge of the label touches the left edge of the frame. The width of the label doesn't thing at this point. Information technology will look similar this:
- Change the (Proper name) property to
LabelProgress - Change the Caption property to an empty cord (no text)
- Alter the BackColor property to
blue , or whatever color you prefer. - Change the SpecialEffect belongings to
1 -fmSpecialEffectRaised .
Yous are done designing your VBA Progress Bar! The UserForm volition look something like this:
Once yous get to this bespeak, you're fix to add the macros to your Progress Bar so it behaves the way we desire information technology to.
Add together Progress Bar Macros
When your progress bar pops up, yous don't desire the ugly title bar with the red X to show up, correct? I didn't think so.
Hide Title Bar
To remove the title bar, I'1000 going to borrow a macro from my Remove Window Edge tutorial. If the person using the spreadsheet you're creating is a Mac user, this macro won't piece of work, but I'll show you lot how to prevent it from crashing on them later.
- Insert a Standard Module
- Right-click the Project Explorer Pane
- Hover over Insert
- Click Module
- Change the (Proper noun) property of the Module to
HideTitleBar - Paste the following macro into the new module you just created:
'Place IN STANDARD MODULE Selection Explicit Option Private Module Public Const GWL_STYLE = - 16 Public Const WS_CAPTION = & HC00000 #If VBA7 And then Public Declare PtrSafe Part GetWindowLong _ Lib "user32" Allonym "GetWindowLongA" ( _ ByVal hWnd As Long , _ ByVal nIndex As Long ) Equally Long Public Declare PtrSafe Function SetWindowLong _ Lib "user32" Alias "SetWindowLongA" ( _ ByVal hWnd As Long , _ ByVal nIndex As Long , _ ByVal dwNewLong Every bit Long ) As Long Public Declare PtrSafe Part DrawMenuBar _ Lib "user32" ( _ ByVal hWnd Equally Long ) As Long Public Declare PtrSafe Function FindWindowA _ Lib "user32" ( ByVal lpClassName As String , _ ByVal lpWindowName Every bit String ) As Long # Else Public Declare Function GetWindowLong _ Lib "user32" Alias "GetWindowLongA" ( _ ByVal hWnd As Long , _ ByVal nIndex As Long ) Equally Long Public Declare Function SetWindowLong _ Lib "user32" Allonym "SetWindowLongA" ( _ ByVal hWnd Equally Long , _ ByVal nIndex Every bit Long , _ ByVal dwNewLong Equally Long ) Equally Long Public Declare Part DrawMenuBar _ Lib "user32" ( _ ByVal hWnd As Long ) As Long Public Declare Function FindWindowA _ Lib "user32" ( ByVal lpClassName As String , _ ByVal lpWindowName As String ) As Long #End If Sub HideTitleBar ( frm Every bit Object ) Dim lngWindow Equally Long Dim lFrmHdl As Long lFrmHdl = FindWindowA ( vbNullString , frm . Caption ) lngWindow = GetWindowLong ( lFrmHdl , GWL_STYLE ) lngWindow = lngWindow And ( Not WS_CAPTION ) Call SetWindowLong ( lFrmHdl , GWL_STYLE , lngWindow ) Call DrawMenuBar ( lFrmHdl ) End Sub Make powerful macros with our free VBA Developer Kit
Tutorials similar this can be complicated. That's why we created our costless VBA Developer Kit and our Big Volume of Excel VBA Macros to supplement this tutorial. Grab them below and you lot'll be writing powerful macros in no time.
Add Macro to UserForm
Once you do this, get dorsum to your ufProgress form and follow these steps:
- Right click anywhere on your ufProgress form and select View Code
- Clear out any macros that may appear on the form
- Paste the post-obit macro:
'PLACE IN YOUR USERFORM Lawmaking Private Sub UserForm_Initialize () #If IsMac = False So 'hide the title bar if you're working on a windows machine. Otherwise, simply display information technology as you lot normally would Me . Height = Me . Superlative - 10 HideTitleBar . HideTitleBar Me #Cease If Finish Sub The IsMac check in this routine is what prevents the macro from crashing for users running Excel on a Mac. Instead of the sleek UserForm without a title bar, they'll see the title bar. In other words, their Progress Bars won't be as pretty as you lot Windows users, but that'south okay, right!? ;)
Display Progress Bar
At present that you're done designing your progress bar and configuring the macros, information technology'south time to effigy out how to incorporate the progress bar into your existing macro.
This is where you may take to adapt the solution I provide into your ain awarding. This might require you to remember outside the box a bit, only I'm confident you tin can do that!
The all-time way to show you how to include the progress bar in your macro is to give you a demonstration and and so walk you through the of import pieces.
In this example, I loop through all the rows in my spreadsheet and update my progress bar after each row. Here's the sample macro (pay attention to the comment cards):
'PLACE IN A STANDARD MODULE Sub LoopThroughRows () Dim i As Long , lastrow As Long Dim pctdone As Single lastrow = Range ( "A" & Rows . Count ). End ( xlUp ). Row '(Footstep 1) Brandish your Progress Bar ufProgress . LabelProgress . Width = 0 ufProgress . Show For i = one 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 '-------------------------------------- 'the residual of your macro goes below here ' ' '-------------------------------------- '(Stride 3) Close the progress bar when you lot're done If i = lastrow So Unload ufProgress Side by side i End Sub This sample has three of import steps. These 3 steps are common whether or not your macro uses a For loop similar the ane above.
Step 1 - Display your Progress Bar
At some point in your macro, you want to make your progress bar appear on your screen. Chances are, you want to exercise this right after your macro starts to run. To do that, y'all want to make sure the width of your progress bar is set to 0 at the beggining and then bear witness your UserForm. All you lot have to practise is add the following lines to your macro, like I did in the example in a higher place:
ufProgress . LabelProgress . Width = 0 ufProgress . Bear witness Step two - Periodically Update Progress Bar
At some indicate, you desire to update the length of your progress bar to indicate how far along your macro is. That's the point of a progress bar, right? In my example, I used a formula to calculate what percentange of the way through the macro I was, based on which row I was processing. Inside the loop, I included the following code:
pctdone = i / lastrow With ufProgress . LabelCaption . Caption = "Processing Row " & i & " of " & lastrow . LabelProgress . Width = pctdone * (. FrameProgress . Width ) Finish With DoEvents Each time I get to a new row, I update the
This is a great manner to signal progress if your macro consists of a loop where y'all know the starting position and yous know the ending position. Examples where a solution like the one in a higher place volition work are when yous're processing each line of data in a spreadsheet, or looping through files in a binder.
Step 3 - Close the Progress Bar
When your macro is nearing completion, make sure you close your Progress Bar. In my example, I closed the VBA Progress Bar UserForm when I got to the concluding row. I did that via this line of lawmaking:
If i = lastrow Then Unload ufProgress Final VBA Progress Bar
When you put it all together, you'll get a cute VBA progress bar that looks like this:
What if I don't have a loop?
The approach I demonstrated in the previous section is great for loops, but information technology's not very useful if your macro isn't constructed with a primary loop. What if y'all don't have a loop?
The important part is, you need to follow the same 3 steps.
You don't demand a formula to do this. You could manually update the progress bar at various points in your macro. Take a wait at this sit-in, where I include the aforementioned 3 steps but they're manually sprinkled into my existing macro.:
'PLACE IN STANDARD MODULE Sub DemoMacro () '(Step one) Display your Progress Bar ufProgress . LabelProgress . Width = 0 ufProgress . Testify FractionComplete ( 0 ) '(Step 2) '-------------------------------------- 'A portion of your macro goes here '-------------------------------------- FractionComplete ( 0.25 ) '(Step two) '-------------------------------------- 'A portion of your macro goes hither '-------------------------------------- FractionComplete ( 0.5 ) '(Step ii) '-------------------------------------- 'A portion of your macro goes hither '-------------------------------------- FractionComplete ( 0.75 ) '(Step 2) '-------------------------------------- 'A portion of your macro goes here '-------------------------------------- FractionComplete ( i ) '(Step ii) '(Step 3) Close the progress bar when you're done Unload ufProgress Terminate Sub Sub FractionComplete ( pctdone Every bit Single ) With ufProgress . LabelCaption . Caption = pctdone * 100 & "% Consummate" . LabelProgress . Width = pctdone * (. FrameProgress . Width ) End With DoEvents Cease Sub In this case, I spaced out my macro so I could provide a status update at guess 1/4 intervals. How you separate your macro isn't important. Equally long equally you're periodically updating your
What if my progress bar isn't updating?
If yous accept a complicated macro that uses a bunch of resource, you may discover that your progress bar won't update or it'll show up white. To fix the progress bar, add the following code right below the Cease With statement in Step 2 of the progress bar.
This short piece of lawmaking forces VBA to redraw your progress bar so it's updated each time the UserForm changes.
Final Thoughts
Y'all have successfully created an incredible progress bar using VBA that your users will surely be thankful for! Go creative and alter your explanation text to specifically friction match what your macro is doing at any given point if you'd like. When I need to mass produce PDF invoices, I've included a progress bar similar this one to keep me updated on the condition. Y'all tin can do the same!
That's all for this tutorial. When you're prepare to take your VBA to the next level, subscribe using the form below.
Ready to do more than with VBA?
We put together a behemothic PDF with over 300 pre-built macros and we want you to have it for costless. Enter your email accost below and nosotros'll ship you a copy along with our VBA Programmer Kit, loaded with VBA tips, tricks and shortcuts.
Source: https://wellsr.com/vba/2017/excel/beautiful-vba-progress-bar-with-step-by-step-instructions/
0 Response to "Excel Vba Progress Bar When You Dont Know How Long Out Will Take"
Post a Comment