Search code examples
vb.netexcelvbacom

VB.NET/COM Server code way slower than Excel VBA code

Background

I have a client who needs Excel VBA code that produces formula values moved to VB.NET. He is in the business of providing financial analytics, in this case delivered as an Excel add-in. I have translated the VBA into VB.NET code that runs in a separate DLL. The DLL is compiled as a COM Server because, well, Excel-callable .NET UDFs have to be. So far, so good: Excel cells have "=foo(Range1, Range2, ...)", the VB.NET Com Server's UDF is called, and the cell obtains a value that matches the VBA code's value.

The problem

The VB.NET code is way slower. I can stretch a range of VBA-based formulas and get instantaneous calculation. I can stretch a comparable range of VB.NET-based formulas and the calculation takes 5-10 seconds. It is visibly slower and unacceptable to the client.

There are a few possibilities that occur to me:

  1. native compilation of VBA is faster because of the absence of a switch
  2. the DLL may be loaded and unloaded for each UDF call
  3. the DLL calls Excel WorksheetFunction methods and requires an Application object, and creating the Application object is expensive
  4. calling an Excel WorksheetFunction method from the DLL is expensive

I don't think that (2) is true because I put calls to append to a file in the Shared New, the Public New, and Finalize functions, and all I get are:

Shared Sub New
Public Sub New
Finalize

when I open the spreadsheet, repeatedly stretch a formula range, and close the spreadsheet.

I don't think (3) is true because the file writing shows that the Application object is created only once.

The question

How do I figure out what is taking the time? How to profile in this environment? Are there obvious enhancements?

In the last category, I have tried to reduce the number of creations of an Application object (used for WorkSheetFunction calls) by making it Shared:

<Guid("1ECB17BB-444F-4a26-BC3B-B1D6F07D670E")> _
<ClassInterface(ClassInterfaceType.AutoDual)> _
<ComVisible(True)> _
<ProgId("Library.Class")> _
Public Class MyClass
    Private Shared Appp As Application ' Very annoying

Approaches taken

I've tried to reduce the dependence on Excel mathematical functions by rewriting my own. I've replaced Min, Max, Average, Stdev, Small, Percentile, Skew, Kurtosis, and a few more. My UDF code calls out to Excel much less. The unavoidable call seems to be taking a Range as an argument and converting that to a .NET Array for internal use.

Solution

  • The DLL is compiled as a COM Server because, well, Excel-callable .NET UDFs have to be

    A bit of a show-stopper if true, I agree. But of course, it isn't true at all, why else would I have started that way...

    You can write your UDFs in C++ against the Excel SDK and deliver them as an XLL, for one thing. It's a common practice among quantitative analysts in banks; in fact they seem to enjoy it, which says a lot about them as a group.

    Another, less painful option, that I've only recently come across, is ExcelDNA, which, AFAICT, provides the nasty SDK/XLL bit with a way to hook up your .NET DLLs. It's sufficiently cool that it even lets you load source code, rather than building a separate DLL, which is great for prototyping (it makes use of the fact that the CLR actually contains the compiler). I don't know about performance: I haven't attempted to benchmark it, but it does seem to get around the COM Interop issue, which is well-known to be ghastly.

    Beyond that, I can only endorse other recommendations: reference your workbook, its content and the Excel application as little as possible. Every call costs.