ME: How to create a Progress Bar in VBA?

ChatGPT (Source: chat.openai.com):
Creating a progress bar in VBA involves using UserForms, which are dialog boxes that allow you to create custom interfaces. Here’s a step-by-step guide on how to create a simple progress bar in VBA:

Step 1: Open the Visual Basic for Applications (VBA) editor:

• In Excel, press “ALT + F11” to open the VBA editor.

Step 2: Insert a UserForm:

• In the VBA editor, click on “Insert” in the menu and choose “UserForm” to add a new UserForm to your project.

Step 3: Add Controls to the UserForm:

• In the toolbox (if not visible, press “CTRL + T” to show it), select “ProgressBar” and draw it on the UserForm.
• Add a Label to display the progress percentage (optional but useful for user feedback).

Step 4: Set Properties for the ProgressBar:

• Select the ProgressBar control on the UserForm and set its “Min” and “Max” properties to 0 and 100, respectively.
• You can also set other properties like the “Caption,” “BorderStyle,” etc., according to your preference.

Step 5: Add VBA Code for the Progress Bar:

• Double-click on the UserForm to open the code window for the form.
• In the code window, paste the following code:

Option Explicit

Private Sub UserForm_Activate()
    ' Initialize the progress bar
    ProgressBar1.Value = 0
End Sub

Public Sub UpdateProgressBar(ByVal percentage As Double)
    ' Update the progress bar with the given percentage
    ProgressBar1.Value = percentage
    Label1.Caption = "Progress: " & Format(percentage, "0.0") & "%"
    DoEvents ' Allow the UserForm to refresh
End Sub

Step 6: Use the Progress Bar in Your VBA Code:

• Now you can use the progress bar in your VBA code. For example, if you have a loop and want to update the progress bar during its execution, you can do something like this:

Sub ExampleUsingProgressBar()
    Dim i As Long
    Dim totalIterations As Long
    
    totalIterations = 1000 ' Set the total number of iterations
    
    ' Show the progress bar form
    UserForm1.Show vbModeless
    
    For i = 1 To totalIterations
        ' Your code here (perform the task)
        ' ...
        
        ' Update the progress bar after each iteration
        UserForm1.UpdateProgressBar i / totalIterations * 100
    Next i
    
    ' Close the progress bar form when the task is done
    Unload UserForm1
End Sub

Step 7: Close the VBA editor and run the macro:

• Close the VBA editor and run the macro containing the code shown in “ExampleUsingProgressBar.”

This will display the UserForm with the progress bar updating as the loop progresses, giving you a simple progress indication. Note that you can customize the progress bar appearance and behavior according to your requirements.

Source: chat.openai.com

How to show a macro processing using a progress bar?

Would like to present list of movies with the highest score in history wordlwide, by gross income in US Dolars.
I found some information on the internet and focused on one list with 1000 positions, including year of production (release). Downloaded the list directly to Excel Worksheet and named it as “Source”. Then I wrote a small macro to insert first 500 records in the second Sheet, named “Data”. Here’s the code for this process:

For x = 2 To 501
  sData.Cells(x, 1).Value = x - 1
  sData.Cells(x, 2).Value = sSource.Cells(x, 2).Value
  sData.Cells(x, 3).Value = Right(sSource.Cells(x, 3).Value, 13)
  sData.Cells(x, 4).Value = sSource.Cells(x, 4).Value
      
    For y = 1 To 4
      sData.Cells(1, y).Value = sSource.Cells(1, y).Value
    Next y

Next x

To higliht sort of information, I decided to colored :
– pink: the best sellers with income > 2 billion dolars
– blue: all next position, contained in top 10
– brown: next values to 20
– dark grey: all the rest.

Then I created a user form for progress bar. Inserted a simple UserForm without changing it Name, only Caption:

put a Frame inside the UserForm:

and a Labels:

1) one for progress bar, where I picked a color (pink):

2)and one with a description “Data upload progress” in Label’s Caption.

The final result:

There will be no code in the UserForm, but we need to go back to our Module and add some code lines:

For modular variables:

Dim PctDone As Single
Dim ProgressIndicator As UserForm1

A procedure for progress bar update:

Sub UpdateProgress(pct)
    With ProgressIndicator
  
        .FrameProgress.Caption = Format(pct, "0%")
        .LabelProgress.Width = pct * (.FrameProgress.Width - 10)
    
    End With
    
    DoEvents

End Sub

Inside main procedure :

ProgressIndicator.Show vbModeless

If TypeName(ActiveSheet) <> "Worksheet" Then
    Unload ProgressIndicator
    Exit Sub
End If

    For x = 2 To 501
'...'
        
        PctDone = x / 501
        Call UpdateProgress(PctDone)
    
    Next x

How it works:

Check the whole code and try it by your own: