Running VBA function on LTP change
-
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
-
You can use worksheet change event. This is a link which can help you.
-
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
-
Use Worksheet_Calculate event. Please tell if it works
-
Thanks, it works
-
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
-
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.
-
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.
-
@howutrade said in Running VBA function on LTP change:
Function Test(ByVal Ltp As Double) As String
Test = Format(Now(), "hhss")
End FunctionFunction Testa(ByVal Ltp As Double) As String
Sheet1.Range("A1").Interior.ColorIndex = 37
Testa = Format(Now(), "hhss")
End Functionthe above example wont work, it does not allow me to write to cell A1
-
Excel will not allow you to write a cell value from functions.
This is a safety measure in MS Excel.