Function in VBA

Posted on 2005-04-10
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
    LVL 6

    Expert Comment

    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

    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.
    LVL 1

    Expert Comment

    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 80

    Accepted Solution

    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

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    Go is an acronym of golang, is a programming language developed Google in 2007. Go is a new language that is mostly in the C family, with significant input from Pascal/Modula/Oberon family. Hence Go arisen as low-level language with fast compilation…
    This is an explanation of a simple data model to help parse a JSON feed
    Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …
    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 …

    734 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

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now