VBA Application or object defined error when ending a sub

Question

So I've set up a custom ribbon that has two buttons: one for running a simulation, and another for cancelling it. When the simulation macro is running, a progress bar appears until it has finished.

The cancel button runs a sub that just has End. But the issue is that when the user clicks cancel, an error comes up that says "Application defined or object defined error"

Basically I'm trying to get a button in the ribbon that stops the simulation macro without throwing an error.

I've tried using an On Error statement to go to the end of the sub, but it still comes up the same. Here is the code for the macro:

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

See this: stackoverflow.com/questions/3979893/…

Kostas K.
– Kostas K.

2017-06-13 20:28:29 +00:00
Commented Jun 13, 2017 at 20:28 — Kostas K.
– Kostas K., Commented Jun 13, 2017 at 20:28

John Coleman · Accepted Answer · 2017-06-13 20:29:02Z

0

On possibility is as follows:

1) Create a public variable called e.g. Running:

Public Running As Boolean

declared in a standard code module outside of any procedure.

2) In the code for SimCallback, have the line

Running = True

towards the top of the code and replace

Do While CurrentTrial < NumberOfTrials

by

Do While Running And CurrentTrial < NumberOfTrials

3) In the code for the cancel button, replace End by

Running = False

This will allow the cleanup line SimBar.Terminate to execute.

answered Jun 13, 2017 at 20:29

John Coleman

52.1k7 gold badges59 silver badges127 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

VBA Application or object defined error when ending a sub

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related