我可以在子例程运行时单击 MS Access 表单上的按钮吗?

Question

我有一个进度条，一个很小的弹出表单，链接到一个耗时的子例程的进度。

我正在尝试在进度条上放置一个取消按钮。当子例程在后台运行时，我无法单击进度条窗体中的任何内容。

有没有办法在子例程进行时单击不同表单上的按钮？

Answer 1

是的，这是可能的。使用DoEvents告诉 VBA 继续泵送/处理 Windows 消息;结果可能不像真正的异步代码那样响应迅速，但应该足以启用单击 [取消] 按钮并处理取消。

---

本文中的代码(免责声明：我写的)最初是为 Excel 编写的，并使用UserForm(当主机为 Access 时隐藏在 VBE 中，但 Access VBA 项目绝对可以包含和使用UserForm模块)。

您需要删除特定于 Excel 的位，如下所示QualifyMacroName：

Private Function QualifyMacroName(ByVal book As Workbook, ByVal procedure As String) As String
QualifyMacroName = "'" & book.FullName & "'!" & procedure
End Function

然后修改Create工厂方法以需要instance参数，如下所示：

Public Function Create(ByVal procedure As String, ByVal instance As Object, Optional ByVal initialLabelValue As String, Optional ByVal initialCaptionValue As String, Optional ByVal completedSleepMilliseconds As Long = 1000, Optional canCancel As Boolean = False) As ProgressIndicator
Dim result As ProgressIndicator
Set result = New ProgressIndicator
result.Cancellable = canCancel
result.SleepMilliseconds = completedSleepMilliseconds
If Not instance Is Nothing Then
Set result.OwnerInstance = instance
Else
Err.Raise 5, TypeName(Me), "Invalid argument: 'instance' must be a valid object reference."
End If
result.ProcedureName = procedure
If initialLabelValue <> vbNullString Then result.ProgressView.ProgressLabel = initialLabelValue
If initialCaptionValue <> vbNullString Then result.ProgressView.Caption = initialCaptionValue
Set Create = result
End Function

编译后，您可以通过注册执行实际工作的 worker 方法来使用该ProgressIndicator，如下所示：

With ProgressIndicator.Create("Run", New MyLongRunningMacro, canCancel:=True)
.Execute
End With

其中MyLongRunningMacro是一个类模块，具有可能如下所示的Run方法：

Public Sub Run(ByVal progress As ProgressIndicator)
Dim thingsDone As Long
For Each thing In ThingsToDo
Application.Run thing
thingsDone = thingsDone + 1
progress.UpdatePercent thingsDone / ThingsToDo.Count
If ShouldCancel(progress) Then
' user confirmed they want to cancel the whole thing.
' perform any clean-up or rollback here
Exit Sub
End If
Next
End Sub
Private Function ShouldCancel(ByVal progress As ProgressIndicator) As Boolean
If progress.IsCancelRequested Then
If MsgBox("Cancel this operation?", vbYesNo) = vbYes Then
ShouldCancel = True
Else
progress.AbortCancellation
End If
End If
End Function

例如，其中ThingsToDo可能是要执行的宏的集合。使用循环报告进度百分比更容易，但是虽然它也可以处理一系列操作，但干净地处理取消有点困难：

Public Sub Run(ByVal progress As ProgressIndicator)
Dim thingsDone As Long
DoThingOne
If Not UpdateAndContinue(progress, 0.33) Then Exit Sub
DoThingTwo
If Not UpdateAndContinue(progress, 0.66) Then Exit Sub
DoThingThree
If Not UpdateAndContinue(progress, 1) Then Exit Sub
End Sub
Private Function UpdateAndContinue(ByVal progress As ProgressIndicator, ByVal percentCompleted As Double) As Boolean
progress.UpdatePercent percentCompleted
If ShouldCancel(progress) Then 
' user confirmed they want to cancel the whole thing.
' perform any clean-up or rollback here
Exit Function
Else 
UpdateAndContinue = True
End If
End Function