MS Excel + DDE + VBA

[letdown427]

Market Eye linked to an Excel spreadsheet by DDE.

One thing that has escaped me, is how to get a function written in VB in Excel to run each time a DDE cell is updated?

Basically, the spreadsheet receives stock price information, and I've writen(/am writing) some things to work with that data, the question is, can I get it to run each time a cell is updated, or is it the ungainly approach of running it every second or some nonsense?

Any help/pointers would be greatly appreciated.

EDIT:

Hmm, there appears to be a WorkSheet_Change subroutine. I presume that a DDE update will fire that function? It's a bit hard (i.e impossible) to really test it at the moment, without making my own DDE'er, as there's isn't much stock action at the moment 😕 😉

 

[xtknight]

Here is some VB code to get you started.

Not sure what you mean by DDE but I don't think it's required.

If you want to do it within Excel/VBA I also included sample code for that.

P.S. Screenshots of it from VB:

http://xtknight.atothosting.com/images/xlevent1.png
http://xtknight.atothosting.com/images/xlevent2.png

 

[letdown427]

Originally posted by: xtknight
...

Wow, thanks.

DDE is 'Dynamic Data Exchange'. It's a slightly older version of something OLE can do. Basically the Market Eye program passes info to Excel which it uses to update the prices and things in a spreadsheet.

If I don't manage to get the Worksheet_Change sub working, I can definately use your code to figure out what (if anything) I can respond to.

Thanks very much! 🙂

 

[xtknight]

You'll want to see how the program works. If it's a macro that directly changes a cell (i.e. Range("D14").Text = "..."), the previous text can't be collected properly because SelectionChange is never fired on that cell. It might even improperly report the previous cell text if oldText was set by another cell's SelectionChange event. But, Change should still be fired so you can deal with the new text at least. Maybe there's a way to get the previous text right within the Change event but I haven't researched that. Mine uses Worksheet_Change too. 😉

I had to use a class module, because for some reason VBA doesn't like WithEvents in a regular module. SelectionChange is called whenever the user or a macro clicks on a cell. From the GUI, the only way of changing a cell is to click on it first (that I know of), so it all works out. But a macro doesn't have to click on it since it can just directly address it. So the previous cell's text is uncollectable or may contain another cell's text. If the macro does call Select on the cell first then it can be collected however.