VBA应用程序或对象定义的错误结尾时
问题描述:
因此,我设置了一个自定义功能区,其中有两个按钮:一个用于运行模拟,另一个用于取消它。当模拟宏运行时,会出现一个进度条,直到完成。VBA应用程序或对象定义的错误结尾时
取消按钮运行一个只有End
的子版本。但问题是,当用户点击取消,错误出现,说:“应用程序定义或对象定义的错误”
基本上我试图让功能区中的按钮停止模拟宏,而不会引发错误。
我试过使用On Error
语句去到子结尾,但它仍然是相同的。下面是宏的代码:
Public Sub SimCallback(control As IRibbonControl)
Dim CurrentTrial As Long
Dim NumberOfTrials As Long
Dim StartTime As Double
Dim EndTime As Double
Dim SimBar As ProgressBar
Set SimBar = New ProgressBar
' Custom parameters for progress bar. References separate class
With SimBar
.Title = "Simulation Progress"
.StartColour = rgbGreen
.Top = Application.Top + 125
.Left = Application.Left + 25
End With
' Pre-sim actions
Worksheets("Histograms").EnableCalculation = False
Worksheets("Monte Carlo Results").EnableCalculation = False
StartTime = Timer
Worksheets("Monte Carlo Calculation").Range("U1:V10000").Clear
Let CurrentTrial = 1
NumberOfTrials = Worksheets("Monte Carlo Results").Range("M2").value
SimBar.TotalActions = NumberOfTrials
SimBar.ShowBar
' Loop repeats until selected number of trials have been performed.
' Values are stored in U and V columns on same sheet.
Do While CurrentTrial < NumberOfTrials
DoEvents
Worksheets("Monte Carlo Calculation").Calculate
For CurrentTrial = 1 To NumberOfTrials
Worksheets("Monte Carlo Calculation").Range("U" & CurrentTrial).value = Worksheets("Monte Carlo Calculation").Range("J2").value
Worksheets("Monte Carlo Calculation").Range("V" & CurrentTrial).value = Worksheets("Monte Carlo Calculation").Range("T2").value
SimBar.NextAction
Next CurrentTrial
CurrentTrial = CurrentTrial + 1
Loop
' Output range is copied to separate sheet, since referencing the original sheet slowed down the simulation time.
Worksheets("Monte Carlo Calculation").Range("U1:V10000").Copy Destination:=Worksheets("Histograms").Range("H1:J10000")
Worksheets("Histograms").EnableCalculation = True
Worksheets("Monte Carlo Results").EnableCalculation = True
' Display sim time and terminate progress bar
EndTime = Round(Timer - StartTime, 2)
SimBar.Terminate
MsgBox "Simulation Complete (" & EndTime & " seconds)", vbInformation
End Sub
答
在
可能性如下:
1)创建例如称为公共变量Running
:
Public Running As Boolean
在一个标准的代码模块的任何过程的外部声明。
2)在用于SimCallback
的代码,具有线
Running = True
朝向代码的顶部,并通过
Do While Running And CurrentTrial < NumberOfTrials
3取代
Do While CurrentTrial < NumberOfTrials
)在代码对于取消按钮,请将End
替换为
Running = False
这将允许清理行SimBar.Terminate
执行。
看到这里:https://*.com/questions/3979893/how-to-stop-vba-code-running –