is there a way to respond to changes in conditional formatting in excel?

Question

I have an excel sheet that has dde links to real time market data. I use a timer to watch the dde prices every second. Then submit orders when certain conditions are met. I tried an infinite loop with DoEvent in the middle which works for 5 seconds then freezes the workbook.

Is there a way to respond to changes in dde updates? Change event doesn't detect them. It just detects when user makes manual change.

I was told that if I have conditional formatting there's a way to pick up that event. So I can create a cell formula to turn true when my condition is met and then conditional format that cell to some formatting when it's true and then pick up the format change event. Is that possible? If so how. Any suggestions would be appreciated.

To Clarify: I want to pick up an event IN VBA that would submit an order to trade a stock. The way i'm doing this right now is with a timer which loops over all the rows looking for true cell in the trigger column. Once found it shuts off the flag for that row (sets the true condition to false) and submits the order.

The problem is that one second is an eternity for fast moving stocks. So I need an event to be thrown in VBA when a cell in trigger column turns true so I can respond immediately and not wait for the second interval of the timer class.

As far as I know, you can't call on the timer with a value of less than a second. If I could use milliseconds my problem would be solved. I'd just loop over the list every 10 milliseconds.

As far as I know I can't create another thread in VBA. If I could I would make an infinite loop and put it to sleep after every iteration for 10 milliseconds or so.

As far as I know, I can't pull dde directly into VBA or even .net for that matter since MSDN says it's no longer supported.

I hope this clarifies. All suggestion are appreciated.

Answer 1

If you create a dummy function which has your DDE output cells as parameters then you should be able to respond to the Worksheet_Calculate event?

I'm guessing that might work, but I've no experience with DDE: the DDE update may even trigger the Calculate event directly.

Answer 2

Why don't you just recreate whatever conditional formatting logic you have in that cell into a seperate cell?

For example your conditional formatting logic might highlight a cell when it is above a certain number - you could just put that logic in another cell- eg. =if(A1>100,TRUE,FALSE)

So I guess my question is - Why pick up the format change event when you can just pick up the event itself?

Edit for your clarification:

If you want to run a macro continuously in VBA you don't need a timer - you can just do a continuous loop like this:

Sub macro1()

    Dim i As Double

    With Sheet1

        Do

            '.Cells(5, 4).Value = i

            i = i + 1

            .Cells(1, 1).Value = i

            ' you are going to want to comment this out if you want to don't need to do other things
            DoEvents

            If Sheets("Sheet1").Range("A2").Value = True Then
                ' put your code here.
            End If
        Loop

    End With
End Sub

So I must still be having trouble understanding your situation.

Answer 3

If you are asking if you can use conditional formatting to trigger an event, yes that is possible. I am not familiar with the DDE model myself, however, and mixing conditional formatting to trigger an event from a data condition does seem like an extra step, as Stepan1010 notes.

You may want to consider the discussion in Mr Excel in this link, as the issue centers around changing a cell value based changes made to cells from a DDE connection: http://www.mrexcel.com/forum/showthread.php?176508-Comments-VBA-amp-Min-Max

You may also consider using a DoEvent with a loop set for a time period based on how long you will actually implement the macro, if that applies to your application. This SO article is focused on a status bar, but I think the same logic applies in terms of an event execution, say based on a conditional within a loop: Force a screen update in Excel VBA

Hopefully this is helpful to you =)

~JOL