Should i develop this application in C# or VBA?
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:
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.
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.
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.
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.
Answer
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.