0

I have a worksheet (sheet1) which contains a cell A1 with formula ='sheet2'!D10. I would like to run a macro each time cell A1 in sheet1 changes (as a result of a change in D10 in sheet2). sheet2 is streaming financial data.

Because it is a change in value, Worksheet_Change does not trigger an event. I also can't seem to find a solution with Worksheet_Calculate.

In my research, the closest solution I could find was offered here, but I have not been able to successfully implement it.

Improve this question
4
  • a) 'Because it is a change in value, ...' I think you mean 'Because the change in the cell's value comes from a formula calculation ...' b) streaming can mean a lot of things. Does Sheet2's Worksheet_Change trigger when a change in cell values comes from streaming? Commented Oct 28, 2018 at 0:49
  • Thanks for replying, @Jeeped. a) Yes, you are correct. b) No, Sheet2's Worksheet_Change does not trigger when a change in cell values comes from streaming. Commented Oct 28, 2018 at 3:03
  • In the meantime, I have made progress with the solution offered by VoG in the link referenced in my question. Being Saturday night, there is no streaming data to fully check it out though. Commented Oct 28, 2018 at 3:28
  • 2
    Welcome to StackOverflow. Please read the help section on how to ask and then edit your question, as this will help the community better understand your specific issue and provide you with a good answer: stackoverflow.com/help/how-to-ask Commented Oct 28, 2018 at 16:04

2 Answers 2

Reset to default
1

You are going to have to use Worksheet_Calculate. It's unclear on whether the 'streaming' will trigger a Worksheet_Calculate in Sheet2 but the linked cell in Sheet1 will definitely trigger a Worksheet_Calculate in that worksheet's private code sheet providing you have calculation set to automatic.

You need a variable that will hold previous values of Sheet1!A1 that can be compared to the current value of Sheet1!A1. Some prefer to use a public var declared in a public module's declaration area; I prefer to use a static var within Sheet1's Worksheet_Calculate itself.

From Microsoft Docs,

Normally, a local variable in a procedure ceases to exist as soon as the procedure stops. A static variable continues to exist and retains its most recent value. The next time your code calls the procedure, the variable is not reinitialized, and it still holds the latest value that you assigned to it. A static variable continues to exist for the lifetime of the class or module that it is defined in.

The first issue is seeding the static variable for its first use. A variant-type variable that has never been given a value report True when tested with IsEmpty so when the workbook is first opened, the first calculation cycle will simply record the value of Sheet1!A1 into the static var. Any future calculation cycle will compare the value in Sheet1!A1 to the value held in the static var and if they are different, the external sub procedure ('... run a macro ...' according to your question's narrative) will be run and the new value of Sheet1!A1 will be stored in the static var. In this way, any change in the value returned by the formula in Sheet1!A1 will force a calculation cycle, hence the worksheet's Worksheet_Calculate event sub procedure which will in turn run your external sub procedure.

In Sheet1's private code sheet

Option Explicit

Private Sub Worksheet_Calculate()

    Static s2d10 As Variant

    If IsEmpty(s2d10) Then
        'load static var with expected value
        s2d10 = Cells(1, "A").Value2
    ElseIf s2d10 <> Cells(1, "A").Value2 Then
        'run sub procedure here
        '... run a macro ...'

        'load A1's current value into the static var
        s2d10 = Cells(1, "A").Value2
    End If

End Sub
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Thanks very much for the detailed and comprehensive reply. Will have to wait till Monday for streaming data to test it, but looks like it will do the job. Much appreciated.
0

Selection_Change & Change

I went into a different direction and got lost. I think there might be some useful stuff in here, so here's the code anyway. It could be working in most conditions, just lose the 'str1' lines.

The 'str1' lines are for debugging purposes and show the behavior of the cells at different conditions.

Not sure if the sub ChangeD10 is emulating your conditions.

Throwing in the towel, but would appreciate any pinpointing of errors in the code.

Option Explicit

Private TargetValue As Variant
Private TargetAddress As String

Private Sub Worksheet_Change(ByVal Target As Range)

  'The Playground
  Const cStrWs1 As String = "Sheet1"
  Const cStrWs2 As String = "Sheet2"
  Const cStrCell1 As String = "A1"
  Const cStrCell2 As String = "D10"
  'Other Variables
  Dim oWs1 As Worksheet
  Dim oWs2 As Worksheet
  Dim oRng As Range
  Dim varA1_Before As Variant
  Dim varA1_Now As Variant
  'Debug
  Const r1 As String = vbCr
  Dim str1 As String
  'Initialize
  Set oWs1 = ThisWorkbook.Worksheets(cStrWs1)
  Set oWs2 = ThisWorkbook.Worksheets(cStrWs2)
  Set oRng = oWs2.Range(cStrCell2)
  varA1_Before = oWs1.Range(cStrCell1).Value

            str1 = "Worksheet_Change"

  'Play
  If Target.Address = oRng.Address Then
    If Target.Value <> TargetValue Then
      varA1_Now = oWs2.Range(cStrCell2).Value
      oWs1.Range(cStrCell1).Value = varA1_Now

            str1 = str1 & r1 & Space(1) & "Cell '" & cStrCell2 & "' changed " _
                & "(Target.Value <> TargetValue)" & r1 & Space(2) _
                & "Before: TargetValue (" & TargetAddress & ") = '" _
                & TargetValue & "'," & r1 _
                & "         varA1_Before (" & Range(cStrCell1).Address _
                & ") = " & varA1_Before & "'," & r1 & Space(2) _
                & "Now:   Target.Value (" & Target.Address & ") = '" _
                & Target.Value & "'," & r1 _
                & "            varA1_Now (" & Range(cStrCell1).Address _
                & ") = " & varA1_Now & "'."

     Else

            str1 = str1 & r1 & Space(1) & "Cell '" & cStrCell2 _
                & "' didn't change. TargetValue = '" & TargetValue _
                & "' and Target.Value = '" & Target.Value & "'."

    End If
   Else

            str1 = str1 & r1 & Space(1) & "Cell '" & cStrCell2 _
                & "' not changed. The Target.Address is '" _
                & Target.Address & "', TargetValue is '" & TargetValue _
                & "' and Target.Value is '" & Target.Value & "'."

  End If

            Debug.Print str1

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

  Const r1 As String = vbCr
  Dim str1 As String

            str1 = "Worksheet_SelectionChange"

  If Target.Cells.Count = 1 Then

            str1 = str1 & r1 & Space(1) & "Cell '" & Target.Address _
                & "' selected " & r1 & Space(2) _
                & "Before: TargetValue (" & TargetAddress & ") = '" _
                & TargetValue & "'," & r1 & Space(2) _
                & "Now:    Target.Value (" & Target.Address & ") = '" _
                & Target.Value & "'."

    TargetValue = Target.Value
    TargetAddress = Target.Address

   Else
            str1 = str1 & r1 & Space(1) & "Multiple cells in range '" _
                & Target.Address & "'."
  End If

  Debug.Print str1

End Sub

Sub ChangeD10()
  ThisWorkbook.Worksheets("Sheet2").Cells(10, 4) = 22
End Sub
Improve this answer

1 Comment

Thanks for your reply. I am going with @Jeeped 's method for the moment, but will try finding errors in your code later when I have the time.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.