Should i develop this application in C# or VBA?

Question

First off, this question is not meant to be a debate of the overall merits of C# vs VBA. I have a project that has some parts better suited to C# and other that seem more appropriate for VBA, and I'm trying to figure out how best to structure my development, as I don't have time to build it several different ways and see which one is faster. I'm posting the question here because there might be something about one language that I haven't considered that clearly makes it the more attractive choice for this project.

The project:

1. get a data set from a Sharepoint list, either with a web services query or by refreshing the contents of an Excel sheet that's linked to this list.

2. join the Sharepoint data set to an Excel data set of workbook filenames using inequality relationships between the sets' fields. this will be a many-to-many.

3. for each workbook in each set of matches, export some of its data to a new workbook in a different folder, so that each match set is in its own folder.

4. zip all the folders in one archive.

Step 1 seems like either .NET or VBA would be satisfactory. Step 2 seems better suited to C# because it involves a join query that would be easier to accomplish with LINQ rather than with a bunch of VBA loops. Step 3 involves Excel data transfer, which makes VBA a better candidate, as I've read a lot of posts agreeing that VBA is faster than .NET Interop for Office operations. Step 4 seems like it would be better in C# because I can use DotNetZip rather than having to rely on shell commands.

But a mixture of C# and VBA would be much less convenient to maintain than a single-language solution. So I'm not sure what approach I should take.

Solution 1

The only point in favor of VBA you mention is supposed performance gain when interacting with Excel.

The overhead of interacting with Excel from C# compared to VBA will be negligible compared to the cost of the other tasks : data retrieval, calculations...

If not it means you are doing things badly : e.g. filling values or applying formatting of cells one by one.

As a general rule you should only use VBA if the mainteners of the application have not enough development skills to use C# AND do not want to learn a new language.

This is typically the case when the maintener is the final client who knows only how to craft basic macros, and would be completly powerless if your C# addin had any issue.

Solution 2

Looking at your requirements...

Part 1 - Excel has no real problem getting xml data or doing web service queries. I was doing that sort of thing back in Excel 2002.

part 2 - Probably you're right that the data integration would be syntactically nicer and probably better optimised in C# - linq is very handy. But it depends on what that integration is.

part 3 is definitely vba territory, all the updates etc.

part 4 doing that in c# might look prettier UI wise by not opening cmd windows - but how often is the code really going to run vs how easy they are to hide.

Other considerations...

Unless you do late binding, your C# app will be version-tied to Excel (as given away by the inclusion of excel version when you add the reference). If you had a new version of Excel deployed then you'd have to update your C# app where vba would most likely 'just work'.

There shouldn't be an issue with resourcing either - you said you have C# devs and anyone who has the skills to call themselves a developer should be able to adapt pretty quickly to the major procedural languages and libraries they might reference for .net development could be in either C# or VB.Net equally - it's not like you're writing it in F#.

There's a lot to be said for keeping all the dependencies together as well since you have an excel dataset to merge in.

The biggest downside with vba is that not being compiled if you give it to someone else they might be tempted to tinker. :)

All things considered I'd suggest a VBA solution as it would be the most appropriate tool for this job. There aren't any 'showstopping issues' which would dramatically swing it either way though.