Legacy Code - best practices to keep it alive? RRS feed

  • Question

  • Hello,

    I am not a VBA/Office developer, but my development team recently inherited a large number of critical functions - baked into spreadsheets. In general these are VBA macros embedded in spreadsheets directly. There are some add-ins as well. The question I have has to do with their dependencies.

    For instance, we have an add-in with a dependency on an old control - mscomct2.ocx. This thing is old, I mean really old. I am seeing stuff like this all over these sheets. My questions are:

    1. What are the strategies to keep using excel, but "modernize" the dependencies? Is there a control that I can swap in there?

    2. Is there a different strategy altogether - like should I be writing stuff in C# and somehow hooking it into Excel?

    3. What are other people, in this situation (I know there have to be alot of them) doing?



    Friday, April 7, 2017 2:03 PM

All replies

  • The main problem with activeX controls like this is they will not work with Office64. However even though they work in Win64 with Office32 there can be problems updating the reference each time when exchanging files between users with Win32 and Win64.

    The most common use for MSComCtl.ocx  (Microsoft Windows Common Controls) is its DateTime picker. 

    The DT picker is a relatively simple to replicate with pure VBA and there are various examples out there, though I do not have any links to hand. Other controls can be much more complicated though there are also VBA alternatives for some of them too. Free and non free depending on complexity and overall functionality.

    Friday, April 7, 2017 4:39 PM
  • Thanks Peter. That is our use case, though there are others that are similar...

    That seems to suggest a strategy of "move to pure VBA where possible". What would you do to manage it where they were more complex? What does the "state of the art" environment look like, were I to build these from scratch (in a maintainable way) today?

    Friday, April 7, 2017 5:24 PM
  • A fairly standard 'state of the art' design might split the implementation like this:

    * Non-Excel related libraries that implement the core calculations, any back-end, database or web service data access, and shared utility function like date or string processing.

    * An Excel add-ins that provides user-defined worksheet functions, macros and maybe ribbon UI extensions for access to the core libraries. This provides the glue between Excel as your 'front-end' and the core libraries, doing data type conversions and a providing a UI. The add-in might also provide a COM library to give scripting-style access to the core libraries through an object-oriented COM object model.

    * You could then provide Excel workbooks, worksheet templates etc. that make it easier to use the add-in functions and other features. This might include some VBA scripts talking the the COM object model, if you have VBA power-users who are not themselves working on the add-in or core libraries.

    Both the core libraries and Excel add-in could be implemented in whatever contemporary development environment your team is most comfortable with:

    * The .NET languages - C#, VB.NET or F#. For the Excel add-in you'd then use the Excel-DNA library (which I develop) as the 'glue'.

    * Python - you'd use something like PyXLL or XlWings for the Excel add-in glue in this case.

    * C or C++ - there are quite a few options for the Excel part, e.g. the commercial XLL+ library, or a free one like XLW.

    Of course the Excel-independent core libraries would use any further dependencies, math libraries etc. that you want.

    The main advantage of the 'core libraries with Excel add-in' approach is that you can implement a unified 'truth' for the core business - e.g. a single way of retrieving bond data from a back-end database or web services, as well as standardised calculations like bootstrapping an interest rate curve from the bond data. So all the calculation and data processing code that often bifurcates with VBA sheets get put into a single canonical implementation.

    However, you keep the end-user access in Excel and make it as flexible as possible. So you don't implement a rigid user interface with fixed forms and reports but rather provide worksheet-function based access to all your functionality, which end users can slice and dice and put together any way they want. This gives a nice separation of responsibilities between the end-users (who make and manage the workbooks) and the back end team (manage the more stable shared implementation).


    Excel-DNA - Free and easy .NET for Excel

    Friday, April 7, 2017 9:22 PM
  • I can't add to the excellent answer form Govert about your general question. However for updating or replacing your legacy controls it depends what they are and how you want to deploy them. For example as separate xlam type files or self contained within the project that uses them. Assuming  you want to keep 100% VBA.

    This is a Date Picker as an xlam

    This is a treeview as code you can include in your own workbooks

    On the same page there's a link to a non-free but much more powerful treeview and listview alternative as a separate addin type files (don't need to be 'installed' as an addins). Disclaimer - I'm involved with these 'controls'

    Saturday, April 8, 2017 10:28 AM