带有用户窗体的 Excel VBA 进度条

Question

我正在尝试学习如何在用户窗体中使用进度条。

我的代码的问题是它在运行循环后显示进度条；它应该在显示进度条的同时运行循环，而不是像 10%...20%...30%...40%......100%。

谁能帮我修复我的代码以实现这一目标？

'-----Below is loop-----------------------------------------------------
Sub looprange()
   Dim r As Range
'----------loop thru 0 to 9 ---------------
   For Each r In Sheet6.Range("j2", Range("j" & Rows.Count).End(xlUp))

      Sheet6.Range("i2").Value = r.Value

      ActiveWindow.ScrollRow = 11
      Application.CutCopyMode = False

      Call print_jpeg

   Next r

   MsgBox "done"

End Sub

--

'--------Below is vba code in userform :------------
Private Sub UserForm_Activate()

    Dim remainder As Long
    Dim i As Long, j As Long

    Call looprange

    remainder = 0

    For i = 1 To 200

        UserForm1.Label2.Width = UserForm1.Label2.Width + 1

        If i Mod 2 = 0 Then
            remainder = remainder + 1
            UserForm1.Caption = remainder & ” % complete”
            UserForm1.Label2.Caption = remainder & “%”
        End If

        For j = 1 To 600

            DoEvents

        Next j

    Next i

    MsgBox “Loading of program complete.”

    Unload UserForm1

End Sub

Answer 1

我相信标准 VBA 中不包含真正的状态栏(没有引用)，但是您可以滥用标签控件。请注意，此方法以性能为代价换取用户的清晰度(用户可以看到应用程序正在运行，但它比没有状态栏的同一个应用程序慢)

只需添加三个标签，一个用于状态文本，一个用于实际移动条，一个用于完整条边框。并格式化它们以适合您的应用程序(见下图):

下面的代码:

Private Sub cbStart_Click()
 one_percent_bar_width = lLoadBar.Width / 100 'width of one percent of the total loading bar
 max_numbers = 1000 'only for demo purpose

 Me.lLoadingBar.Visible = True
 For i = 0 To max_numbers 'your loop here

     'your code here

   percentage = i / max_numbers * 100 'calculation of progress, replace i and max_numbers to fit your loop
   Me.lStatus.Caption = percentage & "% complete" 'status percentage text
   Me.lLoadingBar.Width = percentage * one_percent_bar_width 'width of actual blue bar
   DoEvents 'slows down code but only way to make the bar visibly move, tradeoff between user clarity and speed
 Next i 'edit to fit your loop
 Me.lStatus.Caption = "Complete!" 'adjust status to whatever you want
End Sub

Private Sub UserForm_Initialize()
 Me.lLoadingBar.Visible = False 'hide the small blue bar
 Me.lStatus.Caption = "Progress not started" 'adjust status to whatever you want
End Sub