Как я могу запускать код VBA каждый раз, когда значение ячейки изменяется по формуле?

Question

Я хотел бы знать, как я могу запустить код VBA каждый раз, когда ячейка получает значение изменяется формулой? Мне удалось запустить код, когда ячейка получает значение, измененное пользователем, но это не работает

Answer 1

Если у меня есть формула в ячейке A1 (например, = B1 * C1), и я хочу запускать некоторый код VBA каждый раз, когда изменяется A1 из-за обновлений в ячейке B1 или C1, тогда я могу использовать следующее:

Private Sub Worksheet_Calculate()
    Dim target As Range
    Set target = Range("A1")

    If Not Intersect(target, Range("A1")) Is Nothing Then
    //Run my VBA code
    End If
End Sub

---

Обновить

Насколько я знаю проблема с Worksheet_Calculate является то, что он запускается для всех ячеек, содержащих формулы в электронной таблице, и вы не можете определить, какая ячейка была пересчитана (т.е. Worksheet_Calculate не обеспечивает Target объект)

Чтобы обойти это, если у вас есть набор формул в столбце А, и вы хотите определить, какая из них обновлена, и добавить комментарий к этой конкретной ячейке, то я думаю, что следующий код достигнет этого:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim updatedCell As Range
    Set updatedCell = Range(Target.Dependents.Address)

    If Not Intersect(updatedCell, Range("A:A")) Is Nothing Then
       updatedCell.AddComment ("My Comments")
    End If

End Sub

Чтобы объяснить, для обновления формулы одна из входных ячеек в эту формулу должна измениться, например, если формула в A1 является =B1 * C1 тогда либо B1 или же C1 должен измениться, чтобы обновить A1.

Мы можем использовать Worksheet_Change событие, чтобы обнаружить изменение ячейки на листе и затем использовать функцию аудита Excel для отслеживания зависимостей, например, ячейка A1 зависит от обоих B1 а также C1 и, в этом случае, код Target.Dependents.Address вернется $A$1 за любое изменение B1 или же C1,

Учитывая это, все, что нам теперь нужно сделать, это проверить, находится ли зависимый адрес в столбце A (используя Intersect). Если он находится в столбце А, мы можем добавить комментарии в соответствующую ячейку.

Обратите внимание, что это работает только для добавления комментариев только один раз в ячейку. Если вы хотите продолжить перезаписывать комментарии в одной и той же ячейке, вам нужно будет изменить код, чтобы сначала проверить наличие комментариев, а затем удалить, если необходимо.

Answer 2

Код, который вы использовали, не работает, потому что смена ячейки - это не ячейка с формулой, а продажа... меняется:)

Вот что вы должны добавить в модуль рабочего листа:

(Дата обновления: строка "Set rDependents = Target.Dependents" выдаст сообщение об ошибке, если нет иждивенцев. Это обновление позаботится об этом.)

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rDependents As Range

    On Error Resume Next
    Set rDependents = Target.Dependents
    If Err.Number > 0 Then
        Exit Sub
    End If
    ' If the cell with the formula is "F160", for example...
    If Not Application.Intersect(rDependents, Range("F160")) Is Nothing Then
        Call abc
    End If
End Sub

Private Sub abc()
    MsgBox """abc()"" is running now"
End Sub

Вы можете расширить это, если есть много зависимых ячеек, устанавливая массив адресов ячеек. Затем вы должны проверить для каждого адреса в массиве (вы можете использовать любую циклическую структуру для этого) и запустить нужную подпрограмму, соответствующую измененной ячейке (используйте SELECT CASE...) для этого.

Answer 3

Вот еще один способ использования классов. Класс может хранить начальное значение ячейки и адрес ячейки. При вычислении события он сравнивает текущее значение адреса с сохраненным начальным значением. Приведенный ниже пример предназначен для прослушивания только одной ячейки ("A2"), но вы можете инициировать прослушивание большего количества ячеек в модуле или изменить класс для работы с более широкими диапазонами.

Модуль класса называется "Class1":

Public WithEvents MySheet As Worksheet
Public MyRange As Range
Public MyIniVal As Variant

Public Sub Initialize_MySheet(Sh As Worksheet, Ran As Range)
    Set MySheet = Sh
    Set MyRange = Ran
    MyIniVal = Ran.Value
End Sub
Private Sub MySheet_Calculate()

If MyRange.Value <> MyIniVal Then
    Debug.Print MyRange.Address & " was changed from " & MyIniVal & " to " & MyRange.Value
    StartClass
End If

End Sub

Инициализируйте класс в модуле normall.

Dim MyClass As Class1

Sub StartClass()
Set MyClass = Nothing
Set MyClass = New Class1
MyClass.Initialize_MySheet ActiveSheet, Range("A2")
End Sub

Answer 4

Вот мой код:

Я знаю, это выглядит ужасно, но это работает! Конечно, есть решения, которые намного лучше.

Описание кода:

Когда рабочая книга открывается, значения ячеек от B15 до N15 сохраняются в переменной PrevValb до PrevValn. Если происходит событие Worksheet_Calculate(), предыдущие значения сравниваются с фактическими значениями ячеек. При изменении значения ячейка помечается красным цветом. Этот код может быть написан с функциями, так что он намного короче и легче для чтения. Есть кнопка сброса цвета (Seenchanges), которая сбрасывает цвет к предыдущему цвету.

Рабочая тетрадь:

Private Sub Workbook_Open()
PrevValb = Tabelle1.Range("B15").Value
PrevValc = Tabelle1.Range("C15").Value
PrevVald = Tabelle1.Range("D15").Value
PrevVale = Tabelle1.Range("E15").Value
PrevValf = Tabelle1.Range("F15").Value
PrevValg = Tabelle1.Range("G15").Value
PrevValh = Tabelle1.Range("H15").Value
PrevVali = Tabelle1.Range("I15").Value
PrevValj = Tabelle1.Range("J15").Value
PrevValk = Tabelle1.Range("K15").Value
PrevVall = Tabelle1.Range("L15").Value
PrevValm = Tabelle1.Range("M15").Value
PrevValn = Tabelle1.Range("N15").Value
End Sub

Modul:

Sub Seenchanges_Klicken()
Range("B15:N15").Interior.Color = RGB(252, 213, 180)
End Sub

Лист1:

Private Sub Worksheet_Calculate()
If Range("B15").Value <> PrevValb Then
    Range("B15").Interior.Color = RGB(255, 0, 0)
    PrevValb = Range("B15").Value
End If
If Range("C15").Value <> PrevValc Then
    Range("C15").Interior.Color = RGB(255, 0, 0)
    PrevValc = Range("C15").Value
End If
If Range("D15").Value <> PrevVald Then
    Range("D15").Interior.Color = RGB(255, 0, 0)
    PrevVald = Range("D15").Value
End If
If Range("E15").Value <> PrevVale Then
    Range("E15").Interior.Color = RGB(255, 0, 0)
    PrevVale = Range("E15").Value
End If
If Range("F15").Value <> PrevValf Then
    Range("F15").Interior.Color = RGB(255, 0, 0)
    PrevValf = Range("F15").Value
End If
If Range("G15").Value <> PrevValg Then
    Range("G15").Interior.Color = RGB(255, 0, 0)
    PrevValg = Range("G15").Value
End If
If Range("H15").Value <> PrevValh Then
    Range("H15").Interior.Color = RGB(255, 0, 0)
    PrevValh = Range("H15").Value
End If
If Range("I15").Value <> PrevVali Then
    Range("I15").Interior.Color = RGB(255, 0, 0)
    PrevVali = Range("I15").Value
End If
If Range("J15").Value <> PrevValj Then
    Range("J15").Interior.Color = RGB(255, 0, 0)
    PrevValj = Range("J15").Value
End If
If Range("K15").Value <> PrevValk Then
    Range("K15").Interior.Color = RGB(255, 0, 0)
    PrevValk = Range("K15").Value
End If
If Range("L15").Value <> PrevVall Then
    Range("L15").Interior.Color = RGB(255, 0, 0)
    PrevVall = Range("L15").Value
End If
If Range("M15").Value <> PrevValm Then
    Range("M15").Interior.Color = RGB(255, 0, 0)
    PrevValm = Range("M15").Value
End If
If Range("N15").Value <> PrevValn Then
    Range("N15").Interior.Color = RGB(255, 0, 0)
    PrevValn = Range("N15").Value
End If
End Sub