vba 如何在 Excel 中创建状态对话框
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/51941/
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
How do I create a status dialog box in Excel
提问by JonnyGold
I have created a database report generator in Excel. I am trying to create a dialog box that displays status information as the program runs.
我在 Excel 中创建了一个数据库报告生成器。我正在尝试创建一个对话框，在程序运行时显示状态信息。
When I generate the report, although the dialog box appears, I cannot refresh/update the information it displays. Most of the time, the dialog box only partially appears. I have tried using the .repaint method, but I still get the same results. I only see the complete dialog box, after the report is generated.
当我生成报告时，虽然出现了对话框，但我无法刷新/更新它显示的信息。大多数情况下，该对话框仅部分显示。我曾尝试使用 .repaint 方法，但仍然得到相同的结果。在生成报告后，我只看到完整的对话框。
回答by Mark Biek
Try adding a DoEventscall in your loop. That should allow the form to repaint & accept other requests.
尝试在循环中添加DoEvents调用。这应该允许表单重新绘制并接受其他请求。
回答by Galwegian
I have used Excel's own status bar (in bottom left of window) to display progress information for a similar application I developed in the past.
我使用 Excel 自己的状态栏（在窗口的左下角）来显示我过去开发的类似应用程序的进度信息。
It works out very well if you just want to display textual updates on progress, and avoids the need for an updating dialog at all.
如果您只想显示进度中的文本更新，并且根本不需要更新对话框，它会非常有效。
Ok @JonnyGold, here's an example of the kind of thing I used...
好的@JonnyGold，这是我使用的那种东西的一个例子......
Sub StatusBarExample()
Application.ScreenUpdating = False
' turns off screen updating
Application.DisplayStatusBar = True
' makes sure that the statusbar is visible
Application.StatusBar = "Please wait while performing task 1..."
' add some code for task 1 that replaces the next sentence
Application.Wait Now + TimeValue("00:00:02")
Application.StatusBar = "Please wait while performing task 2..."
' add some code for task 2 that replaces the next sentence
Application.Wait Now + TimeValue("00:00:02")
Application.StatusBar = False
' gives control of the statusbar back to the programme
End Sub
Hope this helps!
希望这可以帮助！
回答by Robert Mearns
The code below works well when performing actions within Excel (XP or later).
在 Excel（XP 或更高版本）中执行操作时，下面的代码运行良好。
For actions that take place outside Excel, for example connecting to a database and retrieving data the best this offers is the opportunity to show dialogs before and after the action (e.g. "Getting data", "Got data")
对于在 Excel 之外发生的操作，例如连接到数据库和检索数据，这提供的最好的机会是在操作之前和之后显示对话框（例如“获取数据”、“获取数据”）
Create a form called "frmStatus", put a label on the form called "Label1".
创建一个名为"frmStatus"的表单，在名为"Label1"的表单上放置一个标签。
Set the form property 'ShowModal' = false, this allows the code to run while the form is displayed.
设置表单属性'ShowModal' = false，这允许在显示表单时运行代码。
Sub ShowForm_DoSomething()
Load frmStatus
frmStatus.Label1.Caption = "Starting"
frmStatus.Show
frmStatus.Repaint
'Load the form and set text
frmStatus.Label1.Caption = "Doing something"
frmStatus.Repaint
'code here to perform an action
frmStatus.Label1.Caption = "Doing something else"
frmStatus.Repaint
'code here to perform an action
frmStatus.Label1.Caption = "Finished"
frmStatus.Repaint
Application.Wait (Now + TimeValue("0:00:01"))
frmStatus.Hide
Unload frmStatus
'hide and unload the form
End Sub
回答by Richard Knott
Insert a blank sheet in your workbook Rename the Sheet eg. "information"
在您的工作簿中插入一张空白工作表 重命名工作表，例如。“信息”
Sheets("information").Select
Range("C3").Select
ActiveCell.FormulaR1C1 = "Updating Records"
Application.ScreenUpdating = False
Application.Wait Now + TimeValue("00:00:02")
Continue Macro
继续宏
Sheets("information").Select
Range("C3").Select
Application.ScreenUpdating = True
ActiveCell.FormulaR1C1 = "Preparing Information"
Application.ScreenUpdating = False
Application.Wait Now + TimeValue("00:00:02")
Continue Macro
继续宏
Etc Alternatively select a blank cell somewhere on the existing sheet instead of inserting a new sheet
等等 或者在现有工作表上的某处选择一个空白单元格而不是插入新工作表
Range("C3").Select
ActiveCell.FormulaR1C1 = "Updating Records"
Application.ScreenUpdating = False
Application.Wait Now + TimeValue("00:00:02")
Etc
等等
回答by Gulzar Nazim
The dialog box is also running on the same UI thread. So, it is too busy to repaint itself. Not sure if VBA has good multi-threading capabilities.
该对话框也在同一个 UI 线程上运行。所以，它太忙了，无法重新粉刷自己。不确定 VBA 是否具有良好的多线程功能。
