Migrating to VSTO / Excel Interop Options

Question

Hi

We currently have an Excel spreadsheet containing a large amount of VBA code. The VBA code is responsible for:

• Manipulating, transforming and formatting sheet data.
• Making calls to C# and C++ COM exposed functionality. In doing so marshalling the sheet data into the C#/C++ methods, getting the results and updating the sheet with the results.

As well as other utility code as required.

We now want to add a new sheet to our Excel workbook and we don't want any VBA. We want to use C#. As I see it I'm limited to the following options:

• Port the whole project to VSTO manually recoding the VBA as C# in the VSTO project.
• Write the new sheet's functionality in C# and expose it via COM. The C# code would make use of the Excel PIAs in order to update the sheet. The sheet would need a very thin layer of VBA to marshall data to the C# methods.

I don't think ManagedXLL will help me in this as my code requirements are as much about processing sheet data as they are about updating the sheet with results.

Please comment/ask for any more details.

Thanks.

Answer 1

You forgot:

• Port the whole project to VSTO, porting existing code to VB.Net (some of it will just copy/paste) and writing new code in C#.
• Put the new sheet in a separate workbook, and call into the old workbook as a datasource.

Answer 2

look if you want my advice... in terms of development it might take a bit longer but... whether you use VB.Net or C# take this approach..

1. do a like for like conversion. for non-trivial amounts of VBA w/ minimal to no documentation this will help you to understand the sometimes convuluted approaches to logic and data parsing.

2. change all instances of cell by cell iteration and looping for read-sheet-once write-sheet-once type approach. this WILL involve re-writing sections of the code. read all the props and values at the beggining of a method, and write all the ones you want to back at the end.

3. Ensure all data and logic processing is done IN .net with .net data types, do not use the range object or worksheet objects properties directly inside a loop, its just way to expensive.

4. finally ensure that if your using multiple threads you do all the "Excel stuff" on the main thread.

5. Read SO for lots of tips on correct disposal of COM objects. beware of false disposal patterns.