Blog.Rees.Biz

Currently I am working on upgrading an existing complicated spreadsheet which makes extensive use of VBA.  It is used for formulae prototyping and then the code is translated (manually) into C#.  This is far from ideal not to mention slow so I am converting it to use VSTO.

Personally, I am not a big fan of VSTO it feels like a hack to allow .Net developers to extend Office applications (usually Excel).  But under the hood it is basically wrapping the code into a COM wrapper to allow it to work.  Can you believe that after ten years of .Net, there is still no native support for .Net inside Office applications. Ok, thats my rant for the day.

The process is pretty straight forward, using the templates provided in Vs2008, however there are a number of "gotchas" I thought I would record for when I come back to this project some months later and wonder why.

1. TURN OFF RESHARPER!!
   Currently I am using Resharper for C# v4.5, and although this is one of my favourite tools of all time, it doesn't play nice with Excel and VSTO. Turn it off using Tools-Addin Manager and untick the box in Vs2008.  With it on it will consistently crash (hang more precisely) Vs and Excel about every 2-4 minutes.
   
   
   
   
2. When writing data to an Excel sheet, do not loop through many cells writing data this will be very slow.  Obviously there is a lot of overhead writing data to a cell.  Instead write it to a jagged array and write the array in one chunk to a range. Using a named range will probably be tidiest.
   
   
             

               var lTable = new object[calc.Term + 1, 10];

               foreach (var lLine in calc.CashflowSolution) {

                   // Get Data - details not important

                   int lPeriodNumber = ++lLineNumber;

                   DateTime lPeriodDate = GetDate();

                   var lPrincipal = GetPrincipal();

                   var lPayment = GetPayment();

                   var lClosingBalance = GetClosingBalance();

                   var lInterest = GetInterest();

                   var lAdminFee = GetAdminFee();

                   var lTotalInstalment = lPeriodNumber == 0 ? 0 : GetInstalment();

   
   

                   lTable[lRow, PeriodColumn] = lPeriodNumber;

                   lTable[lRow, DateColumn] = lPeriodDate;

                   lTable[lRow, PrincipalColumn] = lPrincipal;

                   lTable[lRow, InterestColumn] = lInterest;

                   lTable[lRow, PaymentColumn] = lPayment;

                   lTable[lRow, ClosingBalanceColumn] = lClosingBalance;

                   lTable[lRow, AdminFeeColumn] = lAdminFee;

                   lTable[lRow, TotalInstalmentColumn] = lTotalInstalment;

                   lRow++;

               }

   
   

               Range lRange = this.get_Range(string.Format(CultureInfo.InvariantCulture, "B40:I{0}", lTable.GetUpperBound(0) + 40), Missing.Value);
3. When using an object array to "paste" data in one big hit into a sheet, be sure the clear the area first, otherwise the second invocation could produce a smaller array than the first leaving some bad data!
   
4. There are at least two commonly used overloads for getting a Range.
   
   this.InterestAccrualTable.ClearContents(); // A named ranged defined in the sheet as B2:C10000
   int lNumberOfDays = 1082;
   var lTable = new object[lNumberOfDays + 1, 2];
   // Overload 1
   Range lRange = this.get_Range("B2", "C" + lNumberOfDays + 2);
   // Overload 2Range lRange = this.get_Range(string.Format(CultureInfo.InvariantCulture, "B2:C{0}", lTable.GetUpperBound(0) + 2), Missing.Value);
   
   Overload 1
   The first overload seem to produce "#N/A"'s underneath the range specified in the method.
   
   
   
   Overload 2
   Works much better:
   
   
   
   
5. I'm currently having deployment problems as well. This is the error message I'm getting after installing on a target machine (with Office 2007 SP2).
   
   
   "The common language runtime could not be loaded by ..."
   
   After dealing with various issues (ensuring .Net runtime, Office 2007 Sp2 etc), I am starting to see VSTO is shakey at best to deploy and use.  I read about a known issue with Office apps not being able to reliably select the appropriate .Net runtime.  The recommendation in a forum was to create an Excel.Exe.Config file...
   
   <?xml version="1.0"?> 
<configuration> 
    <startup>
        <supportedRuntime version="v2.0.50727"/>
    </startup>
</configuration>
   This, however does not work in my case.
   
   I found another article recommending not using a Setup Project (which is what I was doing here) but rather use the Publish feature by right clicking the VSTO Project. This creates a folder containing various files, most importantly a Setup.Exe.
   
   This fixes the issue of being able to install on a target machine, however the install process is not customisable! So you cannot add a shortcut etc.