1

I have written a VSTO plugin in C# which populates several worksheets.

During the population process cell values or formula are added one at a time according to user-definable criteria taken from a hidden configuration worksheet.

Typically there are 200 rows with around 50 columns, containing SUMPRODUCT formula, VLOOKUPs, etc.

The cells are also formatted and some have conditional formatting.

You're going to say "that sounds slow" - right? You are correct.

Even with the display and auto calculation switched off during population, this still takes forever on my client's standard workstation (i5 processor, 4GB ram).

For example, adding 10 rows usually takes around 15 minutes on those workstations, but on my dev PC (i7 processor, 16gb ram) I can do 200 rows in 2 minutes.

I appreciate that the speed issue is most probably related to the many RPC calls which take place while each cell is modified.

Consequently I have been searching for a way to this more efficiently and have found a way to populate an object array with values, then copy these en-mass to a worksheet.

Unfortunately there is no such option when the worksheet contains mostly formula, and it doesn't address the formatting issues.

What I'm wondering is, are there any alternatives here? Is there a way to deactivate the RPC calls while the worksheet is built? Is there an object I can inherit from that I can use in an array to build an off-line copy of the worksheet and then copy en-mass to the worksheet?

I'm pretty sure that any suggestion you can supply will add to my reservoir of ideas, so grateful to all who can chip-in with valuable info!

Improve this question
9
  • I found that updating an Excel document using OLEDB was superslow with the document open (where you could see it happening), but with the document closed it was quite quick. That may not help, but its possible you could create a temp sheet and then merge it in afterwards.
    – gbjbaanb
    Apr 21, 2015 at 9:55
  • I did not use VSTO, but Excel DNA (exceldna.codeplex.com) for creating Excel plugins in C# or VB.NET in the past. Excel DNA plugins run directly in the Excel process, and I was under the impression the VSTO architecture works similar, with no interprocess communication involved. To my experience, this can give you an up to 15x speed improvement compared with Office.Automation. So are you 100% sure the performance problem arises from "RPC calls"? For example, did you try to recreate your "inner loop tasks" in VBA and made a speed comparison?
    – Doc Brown
    Apr 21, 2015 at 10:48
  • ... additionally: try to deactivate/omit the conditional formatting and then make a speed comparison.
    – Doc Brown
    Apr 21, 2015 at 10:52
  • Tbh I'm not 100% sure it is due to RPC (hence the "most probably" bit). However, if not RPC, there is probably other stuff going on which isn't immediately obvious apart from the performance hit.
    – CompanyDroneFromSector7G
    Apr 21, 2015 at 10:54
  • I think it would probably be helpful to post a description of how the process works. Will do so shortly...
    – CompanyDroneFromSector7G
    Apr 21, 2015 at 10:55

1 Answer 1

Reset to default
1

After several changes performance is perfectly acceptable now.

There are a few things to always bear-in-mind for best performance when making a large number of changes to a worksheet:

  1. set calculations to manual (reset after)

  2. turn off screen updates (and back on after)

  3. if deleting data from a large number of rows, delete entire rows if you can - much faster than deleting a few columns and shifting those below up

  4. if inserting a large number of rows, consider whether you can just update the rows below rather than insert new ones. If preserving formats is an issue, copy existing formats to the new rows with FillDown.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.