Featured post

Fixed Income Trading: New venues

A simple question came up recently in a conversation – how many new Fixed Income trading venues are there?  I could not think of anywhere th...

Friday, 3 April 2015

Excel VBA - industrialisation...Part Nine "Enumerating DDE Servers"

A DDE Server is an old technology that is still used by a number of vendors to push real time data into Excel.  It has mainly been replaced by RTD but is still in use by many firms for internal applications and for some legacy applications.  In some quite particular use cases DDE outperforms RTD and so it will remain in use for some time yet.

The requirement for Excel Industrialisation is to provide an operating paradigm such that a portfolio of Excel spreadsheets can be supported and managed using code rather than people.  Over the course of this series we'll build up to show a complete proof-of-concept, although production ready code will not be shipped.
This post covers the case of enumerating all of the DDE servers that are included with an Excel Workbook.
  1. Download the code from github
  2. Create a new Excel workbook with one worksheet.
  3. Save the new workbook as an xlsm format workbook
  4. Enter the VBA editor
  5. Import the module you just downloaded from github
  6. Add a reference to Windows Script Host Object Model within the new workbook
  7. Create a number of DDE formula links within the worksheet in the workbook you just created. If you don't have an DDE server available, download the C++ code to create one from github
  8. Execute the "GetDDEServersUsedByActiveWorkbook" method and look in the debug window
  9. Execute the "GetWorkbookLinksByActiveWorkbook" method and look in the debug window 
Note that the C++ on github is extracted from the Microsoft website and the original code can be found here: http://support.microsoft.com/en-us/kb/238133/en-us

We now have shown in code a series of methods in "proof-of-concept" standard code that with Excel Industrialisation we can modify VBA, list all references, list DDE Servers used, list RTD Servers used (and trace using the registry to find the dll or exe server responsible), integrate with git, export VBA to files, interact via MSMQ with a central error management service and catch errors at the level of line-of-code granularity.
As with all of the code published in this series, this is not production standard, it's proof-of-concept to illustrate what can be done and what has been done with other firms.  So there is no error handling and other features that are needed to make the user experience robust and a real value-add are missing.
See also
Excel industrialisation...Part One "Code to write code"    
Excel industrialisation...Part Two "Export VBA code to file system"
Excel industrialisation...Part Three "What is Excel Industrialisation?"
Excel industrialisation...Part Four "Enumerate Addin references"
Excel industrialisation...Part Five "Integration with Git"
Excel industrialisation...Part Six "Error Handling"
Excel industrialisation...Part Seven "Central Error Reporting"
Excel industrialisation...Part Eight "Enumerating RTD Servers"
Excel industrialisation...Part Nine "Enumerating DDE Servers"  
Excel industrialisation...Part Ten "Enumerating COMAddins"
 

No comments:

Post a Comment