Why is my SheetCalculate routine being activated so many times in Excel VBA?

I'm using this code to turn the value of B17 into F7's value whenever F7 is changed. The code below is working, but when I debug it line by line, it seems like this subroutine runs 10 times or so before it exits. Why is that? Will that slow down the macro?

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
    If Range("F7").Value <> Range("B17").Value Then
        Range("B17").Value = Range("F7").Value
    End If
    Exit Sub
End Sub

Open in new window

shampouyaAsked:
Who is Participating?
 
byundtCommented:
If the user is changing the value in cell F7 (rather than a formula), I think you would get better results using a Worksheet_Change event sub. Such a sub will run much less often than a Workbook_SheetCalculate event sub. You could even trap changes only in cells F7 and B17 like this:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim targ As Range
With Me
    Set targ = Union(.Range("F7"), .Range("B17"))
    Set targ = Intersect(targ, Target)
    If Not targ Is Nothing Then
        If .Range("F7").Value <> .Range("B17").Value Then
            Application.EnableEvents = False
            .Range("B17").Value = .Range("F7").Value
            Application.EnableEvents = True
        End If
        MsgBox .Name & " Ran"    'This statement used only for testing
        'Exit Sub       'This statement not required in code as shown
    End If
End With
End Sub

Open in new window

0
 
byundtCommented:
Changing the value on the worksheet triggers calculations on your worksheet and hence the Workbook_SheetCalculate sub is called several times in a row. Try turning events off when you change the value:
Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
    If Range("F7").Value <> Range("B17").Value Then
        Application.EnableEvents = False
        Range("B17").Value = Range("F7").Value
        Application.EnableEvents = True
    End If
    'MsgBox "Ran"      'This statement included only for testing
    'Exit Sub        'This statement not required in sample code
End Sub

Open in new window

0
 
byundtCommented:
Also, you do not specify which worksheet the values of F7 and B17 are being changed on. If you intend the changes to be made only on the active worksheet, then the code is probably OK--but it would be better practice to specify the worksheet.
Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
With Sh
    If .Range("F7").Value <> .Range("B17").Value Then
        Application.EnableEvents = False
        .Range("B17").Value = .Range("F7").Value
        Application.EnableEvents = True
    End If
    MsgBox "Ran"    'This statement used only for testing
    'Exit Sub       'This statement not required in code as shown
End With
End Sub

Open in new window


If you have more than one worksheet, each of them can potentially trigger the code to run--ten worksheets equals ten calls to the sub. This will happen if the worksheets contain one or more formulas using a volatile function like RAND, TODAY, NOW, INDIRECT, OFFSET or CELL.

You can get around the latter problem by using a Worksheet_Calculate sub in the code pane for each worksheet that needs to respond. You do this instead of the Workbook_SheetCalculate sub in ThisWorkbook code pane.
Private Sub Worksheet_Calculate()
With Me
    If .Range("F7").Value <> .Range("B17").Value Then
        Application.EnableEvents = False
        .Range("B17").Value = .Range("F7").Value
        Application.EnableEvents = True
    End If
    MsgBox "Ran"    'This statement used only for testing
    'Exit Sub       'This statement not required in code as shown
End With
End Sub

Open in new window

0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
byundtCommented:
Finally, because the Workbook_SheetCalculate or Worksheet_Calculate events occur so often when a workbook is being used, I prefer to avoid using them unless absolutely necessary. Quite often, there is another way to accomplish your end goal that does not get triggered so often.

Why do you think that you need a Workbook_SheetCalculate event sub?
0
 
shampouyaAuthor Commented:
I chose them because it seemed to be the easiest way to get cell B17 to mimic the value of F7 without the use of formulas. I want the user to see B17 change instantly as soon as they change the value of F7, and I want to do it without formulas.

I tried this below and the looping still continued, unless I removed the Application.EnableEvents = True line, which could be dangerous, I would guess.

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)

    If Range("F7").Value <> Range("B17").Value And ActiveCell.Address = "$F$7" Then
        Application.EnableEvents = False
        ActiveSheet.Range("B17").Value = ActiveSheet.Range("F7").Value
        Application.EnableEvents = True
    End If

End Sub

Open in new window

0
 
byundtCommented:
Failing to turn application events back on would mean that your workbook would no longer respond to events. I should think that would qualify as "dangerous."

I found the MsgBox insightful in figuring out what might cause your problem. Please try the following code and let me know how it responds:
Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
With Sh
    If .Range("F7").Value <> .Range("B17").Value Then
        Application.EnableEvents = False
        .Range("B17").Value = .Range("F7").Value
        Application.EnableEvents = True
    End If
    MsgBox .Name & " Ran"    'This statement used only for testing
    'Exit Sub       'This statement not required in code as shown
End With
End Sub

Open in new window

0
 
Rob HensonFinance AnalystCommented:
In order for the Worksheet_Calculate event to trigger, I assume you have Calculation switched to Automatic. Therefore, the change in one cell will be seen just as quickly (if not quicker) in the other cell as the sheet calculates a formula linking the two. The difference between the two will probably be fractions of a second unless the sheet is particularly heavy with complex formulas and takes a while to calculate. If that is the case, then maybe the Worksheet_Calculate event reacts at the start of the calculate event and therefore the change in cells occurs before the calculation finishes.

Alternatively, are you wanting the user to be able to use either cell as an input? If you are then you will need each cell to be a hard-coded value and have them linked by VBA as you have done but you have to capture which was changed so that you then change the other.

Thanks
Rob H
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.