Automatically execute an excel macro on a cell changed by a real-time add-in

Question

I have an Excel add-in (Bloomberg) which streams real-time prices into a spreadsheet. I need to add a column which contains the last time a price was updated. I've tried to do this with the worksheet change event, but this doesn't seem to get fired by the changes (I assume something to do with it being a real-time update).

The requirement is the same as the question below, but the solutions do not apply since the events do not fire in this case.

http://stackoverflow.com/questions/409434/automatically-execute-an-excel-macro-on-a-cell-change

Answer 1

With help from another forum, I've found an easy way to do this.

First define a function as below in a VBA module accessible to the sheet:

Public Function GetTime(target As Double) As Double
    GetTime = Now()
End Function

Then in the 'last updated' column, add a call to this function with the formula pointing to the cell you wish to monitor. Whenever the target cell is updated, even from a real-time update, the function will fire and cause the time to be updated.