CODEWITHSUNDEEP
vbaexcelvsto
user6140865
user6140865

Reputation:

VSTO Add-in vs. VBA Performance

I'm curious of the performance comparisons of the two, if you were to write functionally identical programs. I'm working on a project where I'm beginning to think that an addin may be more appropriate given the volume of compare functions I'm needing to do in VBA.

Upvotes: 3

Views: 2198

Answers (1)

Mathieu Guindon
Mathieu Guindon

Reputation: 71167

Depends what these programs do and how they're doing it.

VSTO /.net is faster than VBA and lets you write code that runs on multiple threads. But Excel is COM and ultimately everything needs to get into a STA (Single-Threaded Apartment) pipeline, and managed (.net) code talks to COM through COM Interop, i.e. it talks to Excel via Primary Interop Assemblies, which are slower than "native" VBA.

In other words:

  • Your program does a lot of processing and very few spreadsheet reads/writes, go VSTO.
  • Your program does a lot of spreadsheet interactions, go VBA. Spreadsheet interactions are pretty much the slowest thing VBA can do, but doing them with VSTO will be even more painful because everything needs to be marshaled from managed code to COM.

Best could be a hybrid solution: expose user-defined functions in VBA, and make the VBA code call into a referenced COM-visible .net DLL that does the actual computing without even working with Excel interop assemblies.

Well-written VBA code can outperform equivalent VSTO code, too.

I'm beginning to think that an addin may be more appropriate given the volume of compare functions I'm needing to do in VBA.

You can write an Excel add-in in VBA as well. The fewer technologies involved, the less they need to talk to each other; at the end of the day it's a balance between how much importance you give to a number of things, that may apply differently depending on your requirements, environment and experience:

  • Performance - which solution performs best?
  • Maintainability - which solution is easiest to maintain?
  • Deployment - which solution is easiest to update and deploy?
  • Source Control - Visual Studio hooks to Team Frustration Foundation Server and Git; the VBE can do Git with Rubberduck (a little pet project of mine), but the latest version is still beta and a bit unstable (it's open-source though, so if you know C# you can contribute and help stabilize it).
  • Unit Testing - Visual Studio makes it easy to write and run unit tests; the VBE can do the same with Rubberduck, but without a VBE add-in you'd have to resort to ad-hoc tests, or enable programmatic access to the VBIDE API and use a VBA-based unit testing framework.

Upvotes: 14

Related Questions