I have a problem. I have VBA code that runs slowly when triggered by a worksheet

Question

I have a problem. I have VBA code that runs slowly when triggered by a worksheet change event. But runs perfectly fine & fast when run from the click of a button on that same sheet. Granted the code is complex there there's a custom dialog box, a bunch of loops, changing values across many sheets, copying & pasting... I mean a lot happens. The only difference is how the code is called.  

When it's started from a excel form button with the macro 'AfterNewDataSet' assigned, it takes 2 secs.

However, when started from inside the Worksheet_Change event, it takes 40secs.

Private Sub Worksheet_Change(ByVal Target As Range)
     If Target.Address = Range("Source").Address Then
          Call AfterNewDataSet
    End If
End Sub

ScreenUpdating, Calculation, EnableEvents are turned off at the beginning of AfterNewDataSet. I've even tried to use .OnTime from inside the Worksheet_Change event to trigger AfterNewDataSet to run after the Worksheet_Change event is over.
Any suggestion???

Explanation / Answer

It's pretty hard to hit a target blindfolded but I would suggest getting rid of any subjective references to Selection , ActiveSheet etc in the code for AfterNewDataSet. If need be, you could pass a handle to the worksheet along as a parameter when it is called from the Worksheet_Change event macro.

Another option would be to move the event, calculation and screen updating commands to the Worksheet_Change event macro. You do not have to remove them from AfterNewDataSet; just duplicate them so they are still there for the button press.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = Range("Source").Address Then
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
Call AfterNewDataSet
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
Application.ScreenUpdating = True
End If
End Sub

HTH :-)

Related Questions

Navigate

• Browse (All)
• Browse I
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.