none
Will a VBA worksheet change event be triggered by a DDE external link? RRS feed

  • Question

  • I have an Excel 2003 workbook that accepts real time stock trading data during the 9:30 - 4:00 trading hours. The data feeds from a remote DDE server into an 8-column, multi-row array. As the data in one field of the array changes I want to detect the change and trigger a VBA routine to take specific action. I have tried VBA Worksheet_Calculate(). I can't relate all of the problems but suffice to say I've given up on it.

    I am now looking at Worksheet_Change(ByVal Target as Excel.Range). I have read that this event routine is triggered in one of three ways:  a) any cell in the worksheet is changed by the user, which I take to mean typed in;  b) a cell(s) is changed by a VBA procedure;  c) one or more cells are changed by an external link.  I've satisfied myself that, off line, a) and b) work in my application in test mode and achieve the desired end results.  My question is:  will an online change caused by an update of data from the remote server also trigger the change event?  It's clearly a DDE server, so I assume it's a DDE link (although I don't know exactly what that entails), and I have seen oblique references to DDE links in conjunction with the worksheet calculate event.

    Would appreciate any advice or words of wisdom you can offer.

    I run WinXP Pro SP3 with all updates on a home-built system with 3GHz Intel Core Duo, 4 GB RAM, and more disk space than I will ever use
    Friday, October 16, 2009 2:27 AM

Answers

  • Educated guess- It should work.
    • Marked as answer by Tim Li Friday, October 23, 2009 8:26 AM
    Tuesday, October 20, 2009 3:13 PM
  • Hello,

    Worksheet_Change will be trigged, I've just done a test for this.

    Thanks


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    • Marked as answer by Tim Li Friday, October 23, 2009 8:26 AM
    Friday, October 23, 2009 8:26 AM

All replies

  • Educated guess- It should work.
    • Marked as answer by Tim Li Friday, October 23, 2009 8:26 AM
    Tuesday, October 20, 2009 3:13 PM
  • Hello,

    Worksheet_Change will be trigged, I've just done a test for this.

    Thanks


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    • Marked as answer by Tim Li Friday, October 23, 2009 8:26 AM
    Friday, October 23, 2009 8:26 AM
  • NO...

    dde will not cause an worksheet_change event to be triggered!

    1. Setup a worksheet and add the worksheet_change handler in the vba code module and pop-up a MsgBox when an event is triggered;
    2. Add a dde reference into one of the cells in the worksheet (Make sure your dde server is working and security options are enabled for this content)

    Results: Message Box will NOT pop-up for dde; Now, manually change a cell  and the Message Box WILL pop-up...

    Workaround:
    Add a reference to the dde cell and use that reference to trigger your event.

    Example
    Put your dde in cell A1 and then add a reference in cell B1 to refer to cell A1 (that is, cell B1 has the formula "=A1") Now when B1 changes to the the dde in cell A1 changing, the event will be triggered!








    Wednesday, October 28, 2009 2:52 PM
  • NO...

    dde will not cause an worksheet_change event to be triggered!

    1. Setup a worksheet and add the worksheet_change handler in the vba code module and pop-up a MsgBox when an event is triggered;
    2. Add a dde reference into one of the cells in the worksheet (Make sure your dde server is working and security options are enabled for this content)

    Results: Message Box will NOT pop-up for dde; Now, manually change a cell  and the Message Box WILL pop-up...

    Workaround:
    Add a reference to the dde cell and use that reference to trigger your event.

    Example
    Put your dde in cell A1 and then add a reference in cell B1 to refer to cell A1 (that is, cell B1 has the formula "=A1") Now when B1 changes to the the dde in cell A1 changing, the event will be triggered!









    No unfortuantely, this workaround doesn't work on my machine, using Excel 2007, xP sp3.
    Monday, March 8, 2010 2:12 PM
  • NO NO

    I have just tested this, putting references in cells C3:C5, and unfortunelly nothing happened.

    I can´t find what i did wrong. I would be grateful for any help to fix the problem.

    You can see the examples immediately below.

      

      |by DDE   |by DDE   |by Ref   |

      |    A        |    B      |    C      |

    1 | Product | Value   |Ref to B |
    2 +---------+---------+---------+
    3 | Prod_1  |      1,12|     1,12|
    4 | Prod_1  |    19,33|    19,33|
    5 | Prod_1  |     -2.01|    -2.01|

     


    -----------------------------------------------------------> This Fail.
    Private Sub Worksheet_Calculate()
    Application.EnableEvents = False

        MsgBox "Changed!"

    Application.EnableEvents = True
    End Sub

    -----------------------------------------------------------> This Fail.
    Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False

        If not Intersect(Target, Range("B3:B5")) Is Nothing Then
           MsgBox "Changed!"
        End If
       
    Application.EnableEvents = True
    End Sub


    -----------------------------------------------------------> This Fail.
    Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False

        If not Intersect(Target, Range("C3:C5")) Is Nothing Then
           MsgBox "Changed!"
        End If
       
    Application.EnableEvents = True
    End Sub

    Wednesday, August 31, 2011 1:04 PM