Running VBA function on LTP change

[JerryBlue]

Hello,

This is not exactly a API thing. But its about Excel VBA. But the reason , I am asking here is because someone might have already done or might have knowledge.

This is a simple scenario, I want to run a VBA script when ever LPT changes. Lets sat I want to show a MsgBox .

How do I do It ?

Edit : Also please suggest me a good discussion forum to join for VBA.

Thanks

[devasheesh]

You can use worksheet change event. This is a link which can help you.

https://www.excel-easy.com/vba/events.html

[JerryBlue]

I started with worksheet change. But it needs to press enter for the event to trigger. The event does not trigger when update cell is updated through the script .

Thanks

[Umesh_300151]

Use Worksheet_Calculate event. Please tell if it works

[JerryBlue]

Thanks, it works

[HowUTrade]

@jerryblue

Worksheet_Calculate and Worksheet_Change events will kill your excel if not used properly and both events should be used only when no other options are available.

If a refrenced value is changed, then excel automatiocally calculates the respective formula which may contain one or more functions (Built-in Functions or UDF)

Example UDF:

Function Test(ByVal Ltp As Double) As String
Test = Format(Now(), "hh:mm:ss")
End Function

=Test(A1)
Assuming A1 contains LTP

[JerryBlue]

@howutrade

As you said , although Worksheet_calculate does the job but executes whenever any calculation in worksheet occurs. The above example should keep the CPU lean , I guess.

Let me try your suggestion and get back .Thanks.

[JerryBlue]

This Worked perfectly well. This hardly clogs cpu. Very smooth.
I tested this during market hours. I am planning to modify my sheet based on your suggestion so making it super efficient.

I had small problem I got some object error which I currently commented.
Sheet10.Range("I8").Interior.ColorIndex = 4
The above had no problem when i had used workbook_calculate.

Thanks.

[JerryBlue]

@howutrade said in Running VBA function on LTP change:

Function Test(ByVal Ltp As Double) As String
Test = Format(Now(), "hhss")
End Function

Function Testa(ByVal Ltp As Double) As String
Sheet1.Range("A1").Interior.ColorIndex = 37
Testa = Format(Now(), "hhss")
End Function

the above example wont work, it does not allow me to write to cell A1

[HowUTrade]

@jerryblue

Excel will not allow you to write a cell value from functions.
This is a safety measure in MS Excel.