Function in VBA

Posted on 2005-04-10
Medium Priority
Last Modified: 2008-03-17
I use a function TDCLOP(...) in VBA todetermine a cell value.  This function itself takes as input a value returned by  Bloomberg provided Excel Function that get the price of a security in realtime.   TDCLOP(...) return a buy or a sell signal.  I want to take some action ONLY when the signal chanes lets say form a BUY to a SELL value.

Please advice on how I can do that.  Since this function gets evaluated all the time on relatime price update  I do not want to take an action if the BUY is retained.

To further clarify the action I want ot take is:

print the time at which the signal changed from buy to sell or blank.

Question by:khyberman

Expert Comment

ID: 13749747
I don't really understand what you said, but if I get the point,
you can try WorkSheet.Change Event to trigger your function, this event is used to notify cell value changed .
about print the time , "MsgBox Now()" ? I don't know how you want to print, but if you don't know how to get the time, try Now()

Author Comment

ID: 13751027
WorkSheet.Change  is triggered only if the cell changes and sinc ehtis cell is an output to a formula it doesn't seem to change.

Please advice.

Expert Comment

ID: 13751250
1)declare a public variable e.g
Public FirstVal As Long

2) On Worksheet activate event assign the FirstVal with the Value in the Cell i.e. Buy/Sell
FirstVal = Sheet1.Range("A1:A1").Value

3) Now with each Worksheet_SelectionChange call Check the Previous & new values.

4) if the value is right for processing do it or else just ignore.
5) Further you can print the time by just inserting the code when you check the change.

Hope this will be helpful for you.
LVL 81

Accepted Solution

byundt earned 2000 total points
ID: 13799565
Hi khyberman,
The Worksheet_Calculate sub will run when a cell changes value due to a formula change. You could store the status in a Static variable, and call your macro when the status changes from BUY to SELL. To illustrate, here is some code that watches Sheet1!C1:C10, and calls a sub SellMacro whenever the status changes to SELL.

Note: you may need to make your worksheet volatile by including at least one cell formula using a volatile function like: NOW, TODAY, INDIRECT, OFFSET, RAND, INFO or CELL. It the code doesn't run, put a formula like the following in any cell:

The other reason the code may not run is if events have been disabled. To re-enable events, run the RestoreEvents macro.

'This code goes in a regular module sheet. The Public declaration must go before any subs or functions
Public Status(1 To 10) As String
Public rgStatus As Range

Sub SellMacro()
MsgBox "Time to sell"
End Sub

Sub RestoreEvents()
End Sub

'This code goes in the ThisWorkbook code pane
Sub Workbook_Open()
Dim cel As Range
Dim i As Long
Set rgStatus = Worksheets("Sheet1").Range("C1:C10")
For Each cel In rgStatus
    i = i + 1
    Status(i) = cel
End Sub

'This code goes in the code pane for the worksheet being watched
Private Sub Worksheet_Calculate()
Dim cel As Range
Dim i As Long
Static Status(1 To 10) As String
For Each cel In rgStatus
    i = i + 1
    Select Case Status(i)
    Case ""
    Case Is <> cel
        If cel = "SELL" Then SellMacro
    End Select
    Status(i) = cel
End Sub


Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you’re thinking to yourself “That description sounds a lot like two people doing the work that one could accomplish,” you’re not alone.
If you are a mobile app developer and especially develop hybrid mobile apps then these 4 mistakes you must avoid for hybrid app development to be the more genuine app developer.
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …
Screencast - Getting to Know the Pipeline

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question