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

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

Select allOpen in new window

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

Select allOpen 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

Select allOpen in new window

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?

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

Select allOpen in new window

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

Select allOpen in new window

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