[Solved] VBA: keeping a DDE history - Warning: tricky one

    You gotta be good to solve this one.


    I have an excel spreadsheet that uses DDE. In a two cells, it grabs two current numbers from a datataker (real-time peice of monitoring hardware). This updates everytime new data is available.


    Thing is, I want to keep the old data in two columns. All of this will get displayed in realtime on charts. I believe I need some VBA scripting to achieve this. It probably has to poll intermittently (in the background) to see if the values have changed, then append them to the columns.


    Any VBA guru's out there like to waste some time helping me out with this one?

    I'm sure there would be loads of help offers.


    However.... posting this in the Hey that's cool section of the board won't help your cause much...


    Also, why not register and use the board properly.... you will then be able to post in all the forums.


    I have moved the post to Excel forum just in case you want to take this further. You will have to register in order to reply.


    BTW: registering costs nothing but 2mins of your time. :)

    It doesn't have to poll intermittently. Unless you want it to. I've had decent success with the following.


    I put the DDE ref on a blank sheet. I attach code to the recalc event of the sheet to post the data to an archive. It's a fairly simple macro to post data to the next empty row of a column (the simple end-xlup technique).


    For one application I had to track historical currency data that was being fed from a Bloomberg DDE function. In this application, most of the currencies were expressed in US dollars. This meant that I had to track the value of the currency if a price change occured for either the US or the foreign currency.


    In this case, I put the foreign currency DDE link and the US currency DDE link on the same sheet. The macro tied to the recalc event of the sheet would track the history of the foreign currency in US dollars and since both DDE links were on the same sheet, it would trigger a recalc event and post an update every time the US or foreign currency price changed.


    -AB

    [SIZE="1"]Sub All_Macros(Optional control As Variant)[/SIZE]


    I didn't think you guys were allowing anonymous postings anymore?


    I recall this being a problem for me in the past because I would forget to login and reply anon accidentally.

    [SIZE="1"]Sub All_Macros(Optional control As Variant)[/SIZE]

    Hmm the recalc event of the sheet is something I hadn't considered. It sounds like a pretty damn sexy option too .:tumble:. Just have to work out how to do that exactly. Obviously you have to tie your macro to the worksheet event right? Where do I find that?


    What is the simple end-xlup technique. To append it to the column doesn't sound that difficult, I was just going to maintain a variable as to what row I was up to and simply poke a value into the next available cell, then update the row counter. If there is a better way please let me know.

    Here's a bit of code that illustrates the xldown issue Aaron was talking about


    Code
    Sub Copytolastcell()
'eg for Ozgrid, By WillR
Dim mystr As String
mystr = InputBox("Enter Value")
Range("A1").End(xlDown).Offset(1, 0).Value = mystr
End Sub


    In the VBE, double click on any worksheet, & you should be able to select Worksheet in the Left Hand drop down box in the right hand code pane... in the right hand drop down box there will be a series of events - one of them is Calculate....


    this should appear when selected

    Code
    Private Sub Worksheet_Calculate()


End Sub


    Place the code here that you want to fire on the calculate event.


    Hope this helps


    Will

    :biggrin: This all sounds pretty cool. Thankyou WillR and Aaron for your help, I should be able to work it with the information you have given me.


    Incidentally FTR it looks like the Worksheet_Change may work slightly better than the Worksheet_Calculate. Reason being that the data taker sheet then doesn't need to actually calculate anything for the event to trigger, it just comes in via dde. I could be wrong here, I have yet to test it.


    Anyway, thankyou both for your help, I now have a solution to pursue. :spin:

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login