Re:Will VBA Die? (2019)
by non-entity on Friday February 28, 2020 @07:32AM
(#22442303)
I was working on a small program that originally used the C# interop and found the latency pretty bad as well. For a small document, it was taking like 4 or 5 seconds to do what we wanted (dont remember exactly what that was atm). Eventually, I used some OpenXML library Microsft offered that was basically LINQ to XML with some types / extensions and found something interesting:
On smaller documents, the OpenXML manipulation blew the COM interop out of the water in terms of speed, but with larger documents (hundreds of thousands of rows), I think the COM interop was either faster or at least just as fast.
The key to using C# for manipulating Excel for me was ExcelDNA. The development workflow and binary packing was pretty good, I thought.
Before ending up on ExcelDNA, I had started with a VSTO template from Visual Studio, and the development workflow was hell because of how buggy the interactions with Excel were. And I thought the ClickOnce deployment was annoying, if you can instead merely copy an excel file as well as its add-in file(s).
You don't really need to sign your add-in, though I guess that's not good.
The latency might be because the C# is running out-of-process and has to serialize all the data and make calls by (internally) sending window messages. If you can create the COM component in-process (in the Excel process) it should be a lot faster.
Edit: If this is internal, you can also set up one click deployment to some folder on the network that will auto update the add on for end users.