0

I am working on a trading algorithm in Excel and VBA. A cell in the Excel document gets updated from a trader client with the time remaining. I want to call a method every time that cell changes its value. How is this done?

Anders
  • 9,988
  • 7
  • 30
  • 36
  • 3
    you can take a look at Siddharth Rout's solution at http://stackoverflow.com/questions/10441286/need-a-macro-to-detect-if-cell-value-changes-from-current-value and use the `Worksheet_Change` part. – Larry Jan 31 '13 at 10:44
  • There has to be a simpler way. Are there nothing I can write in the excel document to call a method on cell change state? – Anders Jan 31 '13 at 10:52
  • 1
    @user17170: simpler ? I can't see what can be simpler than Larry's suggestion. – iDevlop Jan 31 '13 at 10:58
  • You have to write VBA to achieve the even triggering that you require. Cells can only call a VBA `Function` and Excel is designed to not allow a `Function` to edit the state of a workbook – JustinJDavies Jan 31 '13 at 11:01
  • 1
    Calling `functions` and `cell_change events` please take care of your sheet from going into infinite loops.. `Application.EnableEvents` can ensure the macro/function triggered by cell change does NOT cause the change event to run again and get itself in a loop... – bonCodigo Jan 31 '13 at 13:04
  • agree with @bonCodigo. You might also want to see http://stackoverflow.com/questions/13860894/ms-excel-crashes-when-vba-code-runs/13861640#13861640 – Siddharth Rout Jan 31 '13 at 13:49
  • 1
    @SiddharthRout I remember clearly when you pointed that out the last time :) thanks mate. – bonCodigo Jan 31 '13 at 13:59
  • couldn't search this post, wanted to :( – Larry Jan 31 '13 at 14:45

1 Answers1

1

See the solution on this page:

automatically execute an Excel macro on a cell change

To trigger an event when cell A1 changes, 

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Target.Worksheet.Range("A1")) Is Nothing Then Macro
End Sub
Community
  • 1
  • 1
JustinJDavies
  • 2,663
  • 4
  • 30
  • 52
  • 3
    + 1 for `Worksheet_Change` However may I take this opportunity to draw your attention towards this thread? http://stackoverflow.com/questions/13860894/ms-excel-crashes-when-vba-code-runs/13861640#13861640 – Siddharth Rout Jan 31 '13 at 13:46