CODEWITHSUNDEEP
c#excelvbamath
Ben
Ben

Reputation: 3912

Excel Function called from C# running slowly

I have a method CalculateLinearRegression() that calls the LinEst function of an Excel.Application.WorksheetFunction class. LinEst is called roughly 3,500 times each time CalculateLinearRegression() is called. I inject the WorksheetFunction class into my method so I know that only one instance of the class gets created.

Each call of CalculateLinearRegression() takes around 10 minutes and I am certain it is the LinEst function that is slowing everthing down (removing the call to LinEst and hard coding the results means the method to run in a couple of seconds).

I appreciate that LinEst is doing a fair bit of computation, but I doubt it's doing so much as to cause this delay, therefore I think it is probably the call to the Excel function that is adding on the lag. Does anyone know of either a way of speeding this up, or a C# direct replacement for the LinEst function?

Upvotes: 0

Views: 614

Answers (3)

Charles Williams
Charles Williams

Reputation: 23550

Each call to the Excel object model such as WorksheetFunction has to go through Interop-Com. Since the overhead of Interop is high multiple calls are slow.

One simple solution might be to switch to the XLL interface (which is many times faster) using Excel-DNA.

Upvotes: 1

SpruceMoose
SpruceMoose

Reputation: 10320

Personally I would steer well clear of using Excel interop unless you absolutely need to (e.g. for saving a file to a binary MS excel format). Automating Excel involves some fairly horrific costs in terms of performance.

If you just need the functionality offered by LINEST consider using code from an article such as the following: http://www.codeproject.com/Articles/25335/An-Algorithm-for-Weighted-Linear-Regression Although I haven't used the code myself, the article seems to be highly regarded.

Upvotes: 1

cirrus
cirrus

Reputation: 5672

There are some alternative C# implementations of LinEst, but I can't vouch for any directly. There's a discussion here for instance: What is the C# equivalent to LINEST from Excel?

I'd like to add some support to your long running function too, but there's not quite enough information in your question to comment. Are you able to post a sample of what it looks like? Is it just the Excel call that's slowing everything down?

Upvotes: 1

Related Questions