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...

Sunday, 5 April 2015

Excel VBA - industrialisation...Part Twelve "Enumerating XL4 Macro Sheets"

We are now reaching the end of the "Enumerating.." part of this series.  I am aware that these could have all been merged into one longer post but I think it's good to show the sheer variety of methods for code to interact with the Excel application.

One of the more interesting legacy methods on interacting with Excel is through the macro language that was supplied in older versions - Excel 4 XLM.  The challenge of using XLM is simply that it has been deprecated for so long it's pretty hard to actually find documentation or find anyone who remembers how to use it.  However, I have found XLM lurking around in dark corners of firms where "someone wrote this spreadsheet way back and we don't know how it works but it's mission critical"...
So to enumerate the XL4 Macro sheets in a workbook follow these steps:
  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. Execute the "EntryPointListXL4MacroSheets" method and look in the debug window
  7. Since you don't have any XL4Macro sheets in the workbook, add some using "Sheets.Add type:=excel.XlSheetType.xlExcel4MacroSheet"
  8. Now execute the "EntryPointListXL4MacroSheets" method and look in the debug window



No comments:

Post a Comment