vba 运行宏时的进度条
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13133684/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Progress bar while running macro
提问by Cprog
This is a macro that works on all the files of my workbook and executes the macro wrapon all the sheets if the sheet is visible. I wanted to show a progress bar to show the progress while macro is running..
这是一个适用于我的工作簿的所有文件的宏 ，如果工作表可见，则在所有工作表上执行宏换行。我想在宏运行时显示一个进度条来显示进度..
Sub execute()
Application.ScreenUpdating = False
Application.Cursor = xlWait
' makes sure that the statusbar is visible
Application.DisplayStatusBar = True
'add your message to status bar
Application.StatusBar = "Formatting Report..."
userform1.show
Call Delete_EmptySheets
Dim WS_Count As Integer
Dim i As Worksheet
' Set WS_Count equal to the number of worksheets in the active
' workbook.
WS_Count = ActiveWorkbook.Worksheets.Count
' Begin the loop.
For Each i In Worksheets
If Not i.Visible = xlSheetVeryHidden Then
i.Select
Call wrap
End If
Next i
Application.Cursor = xlDefault
' gives control of the statusbar back to the programme
Application.StatusBar = False
Application.ScreenUpdating = True
End Sub
For the same I have used a userform with a label but it executes only before or after the macro execute
同样，我使用了带有标签的用户表单，但它仅在宏执行之前或之后执行
Private Sub UserForm_Activate()
Call ShowProgressBarWithoutPercentage
End Sub
Sub ShowProgressBarWithoutPercentage()
Dim Percent As Integer
Dim PercentComplete As Single
Dim MaxRow, MaxCol As Integer
Dim iRow, iCol As Integer
MaxRow = 500
MaxCol = 500
Percent = 0
'Initially Set the width of the Label as Zero
UserForm1.Label1.Width = 0
For iRow = 1 To MaxRow
For iCol = 1 To MaxCol
Worksheets("Sheet1").Cells(iRow, iCol).Value = iRow * iCol
Next
PercentComplete = iRow / MaxRow
UserForm1.Label1.Width = PercentComplete * UserForm1.Width
Next
Unload UserForm1
End Sub
Can someone show a method to show the progressbar when macro is running in background?
有人可以展示一种在后台运行宏时显示进度条的方法吗？
回答by Jamie Bull
The problem could be your Application.ScreenUpdating = False. You could update the screen periodically, but that might negate the benefits of having it set to Falsein the first place. The status bar still updates though, so you could write something like the following to the status bar.
问题可能出在您的Application.ScreenUpdating = False. 您可以定期更新屏幕，但这可能会抵消将其设置为False第一位的好处。状态栏仍然会更新，因此您可以在状态栏中写入类似以下内容。
0% |
10% ||||||
And update that as the macro runs.
并在宏运行时更新它。
25% ||||||||||||||
...
50% ||||||||||||||||||||||||||||
...
100% ||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Here's an example:
下面是一个例子：
Sub StatusBarPercent(Percent As Double)
Dim i As Long
Dim Status As String
Percent = Percent * 100
Status = "Formatting Report... " & Percent & "% "
For i = 0 To Percent
Status = Status & "|"
Next
Application.StatusBar = Status
End Sub
