By default max is set to 100 but you can change this e.g. Initializes the variables needed to run the Progress Bar and allows you to set the title of the User Form and the max i.e. Initialize (title As String, Optional max As Long = 100)– needs to be run first.Now a quick explanation of the VBA Functions defined above: Public Function Floor(ByVal x As Double, Optional ByVal Factor As Double = 1) As Double LTimePassed.Caption = "" & tlTotalHour & " hours, " & tlTotalMin & " minutes, " & tlTotal & " seconds" LTimeLeft.Caption = "" & tlHour & " hours, " & tlMin & " minutes" LProgress.Caption = "" & CLng(CDbl(progress) / maxProgress * 100) & "%" TlSec = (tlSec / progress) * (maxProgress - progress) TlSec = Second(tl) + Minute(tl) * 60 + Hour(tl) * 3600 LBar.Width = CLng(CDbl(progress) / maxProgress * maxWidth) If progress > maxProgress Then progress = maxProgress Public Sub AddProgress(Optional inc As Long = 1)ĭim tl As Double, tlMin As Long, tlSec As Long, tlHour As Long, tlTotal As Long, tlTotalSec, tlTotalMin, tlTotalHour MaxProgress = max: maxWidth = lBar.Width: lBar.Width = 0 Public Sub Initialize(title As String, Optional max As Long = 100) Next copy past the code below: Dim progress As Double, maxProgress As Double, maxWidth As Long, startTime As Double Right click on your new User Form and click View Code. Now we need the logic that will allow you to configure and run the Progress Bar. Next add 3 VBA Labels to the User Form and change some selected properties per the image below: Click on the link in case you want a tutorial on how to create these. Easy and straightforward.īelow find a quick tutorial of how to create your own VBA Progress Bar User Form! Create a customer User Formįirst you need to create a customer VBA User Form. The easiest approach to animating an Excel is to create a simple VBA UserForm with the use of a label control which width you can manipulate to show the current progress. Calculating this is best done by approximating the time it took to run Animated VBA Progress Bar Example Users especially wanted to know how much execution time was left – whether they should grab a coffee or stay and wait for the macro to finish. How much execution time was left (estimation).The current progress of the computations.This is where the VBA Progress bar can aid you.įor one of my older projects I needed a VBA Progress Bar that would show: In such cases it is important to notify the end users of the progress of your macros/calculations so they can switch to other activities. This causes many issues, especially for the end users who usually do not know how long processing the calculations/macros will take. Sometimes there are very large and complex solutions built in Excel (which is a mistake mind you), where calculations or macro executions can take minutes or even hours.
A VBA Progress Bar can be used to manage your users anxiety about the execution time of your VBA Macro.
0 kommentar(er)
