none
Strategy for Converting Excel 2003 Macros to Excel 2007 RRS feed

  • Question

  • Hi Techies,
     I am recently working on a proposal to develop a migration startegy for Excel 2003 Macro basedapplication for a client.
    The client currently has a Excel 2003 Macro based application which is targeted to Finance domain so has lots of calculations, formulas & charts. This application currently performs very slow in Vista/Excel 2007 environment, secondly there are also some data integrity issues in Visa/Office 2007 environment. Because of these issues client has decided to work on the Migration strategy.
    After lot of research I have found that the only options currently available are:
    1. Converting the Excel 2003 files to Excel 2007 and let the application run  the same VBA code.
    2. Convert the entire application to VSTO 2005

    Well the client wants issues like Performance etc to be fixed in the migrated version, could anybody suggest me the best of the above possible options  for migration, if we look for performance gain in the migrated version of the application ?
    • Moved by Cindy Meister MVP Tuesday, October 14, 2008 9:22 AM Is not interested in VSTO. (Moved from Visual Studio Tools for Office to Off-Topic Posts (Do Not Post Here))
    Sunday, October 12, 2008 4:14 PM

All replies

  • It's very difficult to give an answer to this without knowing what kinds of manipulations the VBA code is making. In a general way:

    1. When manipulating the Excel object model your code will be slower if it's outside the application (not VBA). This is because it needs to work through additional "interfaces". In the case of VSTO it needs to go through the .NET/COM interface, as well as the interface into the Excel application.

    2. If your code is doing "outside work", such as querying information from a database or building XML files, then it will probably run faster in VSTO.

    3. If your code is basically creating entire Excel workbooks, or creating/changing large portions of workbooks, then it would make sense to look at doing this by working with the OpenXML file format. This means the files would be manipulated without opening them in Excel. After all the work is done, they can be opened and presented to the user. Of course, you could still provide task-specific tools for the user that are, perhaps, less performance-critical.

    Note: you would need VSTO 2008 to create document customizations for Excel 2007.
    Cindy Meister, VSTO/Word MVP
    Monday, October 13, 2008 8:54 AM
  • Shaielshpalav,

    I've done alot of this type of work, and as Cindy points says it's on a case by case basis.

    As usual time/cost constraints will be your biggest factor.

    Converting to Excel 2007 will be a fairly painless. There are some changes to graphing.

    To deliver in VSTO you are going to have to do incremental releases moving your functions from VBA to c#/VB.net. You will have to consider regression testing strategies. You will have to add time for bugs you put in the code by mistake. etc etc.

    VSTO is not a cheap option, but if you can start it, there are lots and lots of benefits.

    Something to sell to the client for VSTO effort would be
    creation of
    • .Net datalayers,
    • .Net finance library
    • .Net security (some people don't find this a benefit)

    which will result in being able to integrate with other apps.

    Monday, October 13, 2008 10:58 AM
  • thoughtthis might be handy...


    Monday, October 13, 2008 2:40 PM
  • Hi,
    Thanks for your answers and responses.
    The benefits that VSTO provide are not inline with my client reuiqrements they are more developer friendly rather than being business freindly. My client is looking for key issues likes Performance to be sorted out afetr the migartion and it is a known fact that VBA Macros run at a very slow pace in Office 2007 environment.

    Although if I suggest the client for the Ist Approach i.e (Converting the Excel 2003 files to Excel 2007 and let the application run  the same VBA code). We would take care of other issues like data integrity, charting by recerating the same. But still the performance issue remains unanswered.

    Is there anything that I can do w.r.t this issue if I suggest the 1st approach to the cleint ? Moreover the performance issue also remains unanswered if i suggest the 2nd approach beacuse of Interoperability wrapper in Dotnet.

    Kindly suggest me the best option in this case.
    Tuesday, October 14, 2008 9:16 AM
  • shaielshpalav said:

    The benefits that VSTO provide are not inline with my client reuiqrements they are more developer friendly rather than being business freindly. My client is looking for key issues likes Performance to be sorted out afetr the migartion and it is a known fact that VBA Macros run at a very slow pace in Office 2007 environment.

    Although if I suggest the client for the Ist Approach i.e (Converting the Excel 2003 files to Excel 2007 and let the application run  the same VBA code). We would take care of other issues like data integrity, charting by recerating the same. But still the performance issue remains unanswered.

    Is there anything that I can do w.r.t this issue if I suggest the 1st approach to the cleint ? Moreover the performance issue also remains unanswered if i suggest the 2nd approach beacuse of Interoperability wrapper in Dotnet.

    If VSTO is not an option for you, then you will not find an answer in this, the VSTO forum.

    I suggest you try the Innovate on Office forum. Unfortunately, I can't move the discussion there as it's not in the new forum interface. So I am moving this message to the off-topic forum.


    Cindy Meister, VSTO/Word MVP
    Tuesday, October 14, 2008 9:21 AM