VBA versus .NET

I was recently messaged by someone on LinkedIn, and since my response seemed full enough, I thought I'd share.

Question

I see that you also program in VBA but you have made the jump to .NET. Unfortunately, I have found C#/Excel coding to be quite slow and just wanted to hear about your experiences.

Responses

Slow? It depends on what you mean. Honest, I have had to make the pitch when building apps that it should be in .NET rather than VBA for speed. One particular app had a form that needed to fill about 20 dropdowns on load, so using async operations were essential. That same app, while executing one SQL statement in the foreground, also executed 2 background statements that filled panels. It wouldn't have performed well if done in VBA.

If you mean that it takes longer, then yes, but that is a necessity for good code anyway. If you only need a local operation, non-threaded, that doesn't need to be used across the enterprise, VBA can make sense, but with .NET comes numerous benefits that save time and energy. As an example, in a C# solution, the design will likely be model-view-presenter (MVP), that will have a class project, a unit test project, and an Excel UI view project. This can take longer, but there is the benefit of separation of concerns (SoC), and with SoC you can add unit testing. The .NET solution will also be publishable as a desktop application that does not require admin rights, with automatic updates across the enterprise and integrated with source control, in my case TFS/VSTS (now Azure DevOps). Also, if you build libraries for reuse, coding C# become quicker as you can reuse components, but with the benefit of not copy-pasting code around. .NET benefits can be huge if you use them.

BTW, I can still use VBA for prototyping, and since I can simulate threading in VBA, and often work in a class-based model, my designs usually easily transfer to .NET. And yes, sometimes VBA is just the better choice. In other cases, VBA is the choice for rapid development. One solution, while the front-end was VBA and class-based, it extracted data to XML and then used SSIS to process and load files in SQL Server, that then used SP's to turn the code into a cube. There was always the possibility that that VBA solution would be converted to C#, provided the work needed to be better productionized.