DDE, Worksheet_Calculate, run once or wait 1 minute...help needed

[stonekitty]

Using Excel 2013:

I need some assistance or pointers in the right direction.

I am presently using a DDE feed to obtain data.
I use the worksheet to calculate the DDE data by assigning a calculation to a cell that reports a true (1) or false (0) condition.
When the assigned cell reports true, it's supposed to launch a macro from the module.

I am using Worksheet_Calculate.

What happened was that when the worksheet (my assigned cell) became true, my macro ran, and ran again, again,
Until the assigned cell reported false.This was very taxing on my system since the macro in the module runs a Windows executable file.

My expectation was for the the macro to run once and wait for the assigned cell to return to false or true again, at the top of the minute.

I only need to evaluate the data once every minute.

I had done this successfully using the worksheet.change event, using an XML data feed and a timer. I would request (ActiveWorkbook.RefreshAll) the data once per minute. The problem with the XML feed was that I would have to synchronize the data refresh at the top of the minute and use a timer. This seemed crude but worked with some inefficiencies. My macros launched once per minute, if the condition was true.

I changed to a DDE feed...

I had hoped that with a RTD / DDE feed, I would not need a timer or refresh, since the true or false condition would occur at the top of the minute. However DDE doesn't seem to work with worksheet.change event.

So I applied the same method, using worksheet.calculate event. but it seems to run my module macros every time the RTD / DDE feed updates.

I tried using timers and loops but nothing seems to work. When the assigned cell became true, the macro ran and ran again, and wouldn't stop until the cell returned false.

My DDE data source feed does report time, and was considering using a SetLinkOnData to monitor a change in time. If that change occurred, then it would evaluate my assigned cell, and run the module macro once.

Anyway, I seem to have hit a wall.

This is what I need my macro to do...Evaluate data once every minute when the new minute starts, and run a macro if my assigned cell is true, and wait for the next minute.

Here is my sample code:

Private Sub Worksheet_Calculate()

' DDE Macro Launcher
' The idea was to launch a macro, after a dde update was calculated to be true or false
' Then it would launch a macro once

Dim ubb1 As Integer
Dim ubb2 As Integer

ubb1 = Range("F47").Value
ubb2 = Range("F47").Value


' ----------------------------- Condition 1 --------------------------------------------

If ubb1 = 1 Then
Application.Run ("macro1")
End If


' ----------------------------- Condition 2 --------------------------------------------

If ubb2 = 1 Then
Application.Run ("macro2")
End If

End Sub


However, after writing this, I don't think my code will work unless I do something awful to it.

So I am hoping someone can give me a few pointers

Thanks

 

[pjmorris]

Morning Stonekitty,

In truth I don't completely follow your entire discussion about XML Data Feeds and DDE etc, but if I've understood your problem the key issue is that once your

...worksheet (my assigned cell) became true, my macro ran, and ran again, again...

. My suggestion therefore is to capture the change in your assigned cell rather than merely respond to its state ie you only let the macro call your windows executable when the previous state of the assigned cell was 'FALSE' and it is now 'TRUE' all other variations simply exit the macro.

To achieve this you need to declare a variable at the module level (ie not within a macro): Public bLastState As Boolean
Within your macro you need a construct similar to this:

        If bLastState = False And Target.Value Then
            bLastState = Target.Value
            MsgBox "Execute Windows Program"
        Else
            bLastState = Target.Value
            Exit Sub
        End If

where the 'Target.value' refers to your assigned cell.

Hope this helps.

Regards

 

[stonekitty]

Thank you Peter,

This helps a lot...