Background Last year, I was asked to create a tool to be used for business plann
ID: 645953 • Letter: B
Question
Background
Last year, I was asked to create a tool to be used for business planning for around 10 users. This was done on behalf of another IT team who "sub-contracted" the work to me, and due to the project deadlines being a little unplanned on their side, I had to implement it in a bit of a rush.
At the time, we decided that the quickest way would be to create an Excel workbook with VBA and then have the users download this VBA-enhanced workbook from an Intranet to use on their PCs. Excel was a constraint in this case because the planning system (i.e. database) we use can only interact via an Excel add-in which must be loaded at the same time the planning workbook is open. However, VBA was not a constraint at that time.
The workbook I created around 4,000 lines of VBA code and whilst I tried to separate data and presentation layers, I couldn't in all cases due to the project deadlines. To be honest, whilst I am proud of creating this workbook, I am at the same time a little disappointed in that it could've been done better, in both terms of coding and also deployment to the users.
Today
Back to today and the IT team has again come to me to request a similar workbook (so I could reuse parts of the other workbook above), but this time it is a lot more complicated and will be used by a greater number of users (around 200).
However, this time, it is a little better planned and I can see that we have a bit more time to plan things. Based on this, I thought about the solution and infrastructure as programming for 100 users has more of an impact than for 10 users. Therefore, I suggested to the team that perhaps we should consider migrating the existing code to a C# solution so that we could manage the code in a more refined way. I'm still considering it as an add-in written using VSTO / Excel-DNA which can then be deployed to the users.
I discussed this with the IT team two weeks ago and everything seemed to be fine, until yesterday I received a mail from one of the team (who does not know VBA or C#) questioning why we should start this new project in C# versus using the same approach as before. Some of their concerns were:
It is a fairly important project so it has to work - a C# solution would not be as stable or work as well as the existing a VBA-based solution.
We would have to throw away what we [I] had done in the VBA solution and recreate it from scratch in C#.
Someone will have to support two separate solutions, one in VBA and one in C#. [actually, they currently do not have anyone for support, I usually step in].
Now, I can understand some of their concerns to some degree, but I need to come to a decision on next steps and what to go back to them with. Personally, I would like to implement in C# because I feel it would lend itself better to building an "Enterprise" solution like this. Furthermore, I would like to take this opportunity brush up on my C# skills as I am currently not as competent in C# as I am VBA and I'd like a project like this to take me to the "next level".
I prepared a list of points that I could use to try and convince them that a C# solution would be better for this project, this is what I have so far:
Unit testing.
Source control.
Code documentation - for knowledge transfer to other support persons.
Better coding conventions - can use things like ReSharper to enforce better naming and structure.
Better IDE - fewer mistakes due to error highlighting.
More modularity through assemblies - can promote re-use in future tools.
Managed deployment - can control who this tool is used by.
Question: What other points could I add to convince them? Or am I trying to bite off more than I can chew with this project? Should I just keep quiet and do it in VBA anyway?
I am aware that just moving to a new language because its "newer" or seen to be "cooler" should not be a basis for a decision and as such I have resisted to include it as a decision point - this is about facts.
Also, I am not asking for a literal comparison between C# and VBA as languages, as there are plenty of comparisons on SO.
Explanation / Answer
The three points you listed seem fair:
It is a fairly important project so it has to work - a C# solution would not be as stable or work as well as the existing a VBA-based solution.
Indeed, later, you tell: "I would like to take this opportunity brush up on my C# skills as I am currently not as competent in C# as I am VBA" (emphasis mine).
In other words, you have a solution which works and went through intensive user testing. You want to throw all this and rewrite everything in a language you don't know well. See the problem?
We would have to throw away what we [I] had done in the VBA solution and recreate it from scratch in C#.
Things You Should Never Do comes to mind. You are throwing the code, as well as the user testing. Not a good thing.
Someone will have to support two separate solutions, one in VBA and one in C#. [actually, they currently do not have anyone for support, I usually step in].
If the VBA version would still be used, the rewrite is indeed even more problematic. Why would you have two disparate systems which require your attention, when you may have only one which already works and which you can refactor and add features to?
Some of your points, on the other hand, can be criticized:
Unit testing.
You can unit test your current project as well. If there is no convenient framework for that, create one.
Source control.
Source control deals with text. Your current code is text, therefore you can use source control for it.
The language, the operating system, the framework or the ecosystem are completely irrelevant. You can (and should) use source control for any code you write: code in Visual Studio, or a piece of code you draft in a few minutes in LINQPad, or PowerShell scripts which automate a task, or database schema, or Excel macros.
Code documentation - for knowledge transfer to other support persons.
Agreed.
Better coding conventions - can use things like ReSharper to enforce better naming and structure.
Define "better". Are there coding conventions for Excel's macros? If yes, use them: they are not better or worse than any other. If not, create ones and publish them so that other people can use them too. The answers to a question posted in 2010 seem rather disappointing, but there may be new tools available since then.
Note that the important part of coding conventions is that they should be enforced on commit.
Better IDE - fewer mistakes due to error highlighting.
Agreed. The fact that we can't write macros in Visual Studio is very unfortunate.
More modularity through assemblies - can promote re-use in future tools.
I'm pretty sure your current product can use some degree of modularity as well.
Managed deployment - can control who this tool is used by.
Agreed.
Instead of a complete rewrite, you might search for a way to progressively move code from the macro to an ordinary assembly written in VB.NET. Why in VB.NET? Three reasons:
There is less difference between VBA and VB.NET as there is between VBA and C#.
You know VBA better, and this alone is a good reason to use VB.NET instead of C#. If you want to "brush up on your C# skills", do it with your personal projects, not business critical stuff.
Any rewrite from a language to another leads to potential bugs. You don't need that for this project.
Moving to a .NET assembly can give you the convenient environment of Visual Studio, with the convenient unit testing, TFS and error highlighting you currently use in other projects.
At the same time, if you move your code step by step, you don't take the risk of a complete rewrite (i.e. spending four months creating something nobody wants to use because the high number of new bugs). For instance, you need to work on a specific feature? Think how you can move this particular feature to .NET first.
This is quite similar to refactoring. Instead of rewriting the whole thing because you learnt some new design patterns and language features, you simply do small changes on the code on which you work right now.
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.