Hacker News Reader

> I would imagine most people that only know VBA would have no interest in using C# to write office interop code, and I don't blame them.

I'd be happy to use C# but it's so god damn hard to learn those libraries.

The biggest disadvantage in using VBA is speed – not to mention the idiosyncrasies of the language itself, which can make complex code rather painful.

Writing a .xlam addin is easy, except there's no tooling for creating "builds".

On a separate but related note, since writing XML for the ribbon by hand is so annoying (and you also have to write callback wrappers for each Sub you want to expose) I have actually written a python tool that parses annotations from comments in .bas files in a given folder and spits out a .xlam file. Haven't gotten around to publishing it but if folks are interested I can give it a little oomph and finish in the next few weeks, so lmk

There is so much of this I can relate to, I have worked with legacy systems too and have been fortunate in a sense to have to drill into someone else's VBA and thankfully there were people there to run me through those strangeness of it.

While I don't think it needs to continue, I believe it's been replaced in all aspects to date.. I do think that there is a requirement for a knowledgeable person to at the very least lead the re-write.

> As for javascript, I doubt anyone that knows VBA well would have a hard time learning javascript, and if the codebehind for excel was slowly migrated to that, most devs would welcome the change.

One of the main points of contact for my VBA adventure picked up vanilla javascript super fast, and within a few weeks he was already explaining how it could be made semi strongly typed, he introduced us then to a newly formed TypeScript subset and we never looked back.

It's not a language issue, its an understanding issue, give a good engineer/developer/hacker any language and they will figure it out and know how to make it work.

That said, if I ever see VB again, I will still shed some salty tears.

One thing to consider, which I've found powerful, is a hybrid approach where you use vba for what it's best at -- interacting with the Excel object model -- and have the vba load and call a C# xll that leverages the .Net libraries and does all the work that would be tedious to do in vba. For your users, they would simply open a "host" .xlam file, which is the only one they would be aware of, and this file would load additional .xlam or xll files and typically also check for updates to these files. No installer necessary.

For example, your vba code would pick up a range of index (or ETF) tickers from a sheet, use Application.Run("lib.constituents") to call a C# function that pulls the stock tickers and weights that represent each indices' constituents, your vba code would receive back an array of arrays and use Excel's row grouping feature to group the members & weights under the parent index and perhaps add some formatting and\or formulas.

The advantage of this division of labor is that you get faster dev & debug feedback loops within your Excel-centric code, while minimizing the amount of time you spend working in a dev environment that has stood still for 20+ years as others have raced far past.

I’m the other way. Started a large project in VBA once, jumped over to C# interop and never looked back. I found the libraries to map fairly well to the same named functions in VBA when it came to worksheet manipulation. My biggest issue was that the interop has quite a bit of latency. You really want to accomplish as much in one call as you can (i.e. read whole ranges into an array instead of looping through cell by cell).

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.

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.

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.

I'm curious, what's the upside of not using VBA? Aside from being cool, obviously :)

VBA is very annoying, and I wouldn't love it if I hadn't been forced to spend so much time in it creating custom functions before attempting larger scale applications.

Once you get used to classes/interfaces, however, the feeling is comparable to realizing the guitar you learned how to play on was terrible and had absurdly high action, and now that you're holding a mediocre to moderately good guitar, you can play a lot better than you thought.

I also think you have to accumulate a personal library of utility functions to make it tolerable (or sometimes even fun.) I've never noticed a real community with standards and norms around VBA, so you're on your own a lot.