Team member questioning moving from VBA to C#

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.

Answer 1

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.

Answer 2

I would support going to C#. Others have covered your points very well, so I won't rehash what they've said. There are definitely a lot of good reasons to stick with VBA.

However, one of my employers did an impeccable job at creating meaningful documentation. So much so that design decisions were actually written down with justification - if only all software projects had that! My point? One of the design decisions was "We're choosing to write this program in Java, because Mr. So-and-so want's to put x years of Java experience on his resume". If that's a strong reason for you to move to C#, then it can't be ignored as a triviality. It just can't be one of the reasons you use to justify it to the IT team, because they don't really care what you want on your resume, they care about the working product.

There's also the perception of VBA to think about. I know it's not true, but I know quite a few people who see 'VBA' on a resume and think "What, this guy was stuck doing intern work? Why doesn't he have experience with a real language?" They see 'VBA' and think 'excel macro developer'. Like copying/pasting one cell to another, doing simple calculations, etc. Usually the kind of people who can appreciate real development in VBA are those who have done it, and that seems like an increasingly small pool, at least in my experience. You might have a better sense of the perception of VBA in your area, but I've seen a lot of unjustified negativity towards it.

The other thing I want to zero in on: MainMa mentioned the similarities between VBA and C#. This is absolutely true, and JMK's answer offers a couple of technologies to read up on. Try copying/pasting your VBA code to a C# project and see how easy the syntax errors look to correct.

You said you had 4,000 lines of code, which is a good chunk of code and probably encompasses a lot of functionality...but it's only 4,000 lines of code. Try the copy and paste thing. I really wouldn't be surprised if you could clean up those syntax errors and have a working project. Without knowing the details of your code or the amount of free time available to you, I would think you could knock this out in a weekend.

It might not follow C# best practices, it may be inefficient, but you would end up with a functionally equivalent project in C#. You could also be relatively certain that your new C# code is no more defect-prone than your old VBA code.

Converting your VBA code to C# on your own time would do a couple of things for you:

• As you research syntax errors you'll become more familiar with C# practices - a sort of primer for actually working in C#
• It'll shine a light on any obvious issues with loading the plug-in into excel(since excel is presumably still a requirement). Perhaps there's an issue you haven't considered yet that may be a roadblock.
• It'll show a proof-of-concept to your customer(the IT team). If they can see you have a working C# plug-in with current functionality, then you'll be reducing their level of perceived risk with C#.

And going back to IT's three points:

• 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.

As mentioned, your C# code will cover the same functionality and be just as stable as your VBA. Strictly speaking, there is a chance you introduce bugs/defects or inefficiencies, but that seems unlikely. We're not talking about a port from iOS/Objective C to Android/Java, we're talking about a port between two languages that share a lot of similarities in syntax and can utilize the exact same technology - excel workbooks.

• We would have to throw away what we [I] had done in the VBA solution and recreate it from scratch in C#.

With this, you're not throwing away any effort or code.

• 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].

You'll only have 1 solution, all in C#.

All in all, your customer sees a lot of risk. If you can take the steps to mitigate that risk, they might embrace the change to C#.

Answer 3

I hate to say it, but your arguments just don't hold water.

Unit testing.

This has been a solved problem for a very long time. There are many Unit Testing frameworks out there. Pick one. You should have been doing this already. I recommend ours, because it integrates into the IDE and provides other great features.

Source control

This one is a bit tougher, there are few ready built solutions, but it's really just a matter of getting your code into and out of a repository. Here's a low brow way to do it, but there are other better options as well.

Code documentation

Also a solved problem. MZ-Toolshas an XML documentation feature that works very similarly to .Net's XML comment docs.

Better coding conventions

Just like Visual Studio has the ReSharper plugin, both MZ-Tools and Rubberduck have such features.

Better IDE

Again, there are plug-ins available for the VBA IDE.

More modularity through assemblies - can promote re-use in future tools.

Also a solved problem. No, you can't create assemblies, but you can reference other VBA Projects.

Managed deployment - can control who this tool is used by.

A bit sticker, you'll need to write code to control who is able to access the program and who can't, but you (again) probably already should have written security code.

As for deployment, it is also a solved problem. Yes, it requires code, but there are examples out there that you could use/modify.

---

On top of all of this, is the fact that you would be starting from scratch. I too will recommend Joel Spolsky's article.

They did it by making the single worst strategic mistake that any software company can make:

They decided to rewrite the code from scratch.

Your IT guys are right. You shouldn't be re-writing this from scratch. You should be solving the problems with your current solution.

Now, with all of that said, I can absolutely understand why you would want to do this in either C# or VB.Net. They really are a better solution, if you're starting a new project, but from the sounds of it, it's not a new project. It's much better to improve what you have then to break it by starting over.

Answer 4

It really depends on how you intend to move to C# (ie what technology you are going to use).

If you are going to use OpenXML, then you are talking a total re-write which, if you have a solution that works, isn't recommended.

If you are talking about using Interop, you may find that the process is surprisingly smooth. I've moved a lot of code from VBA to C# (with Interop) in the past and once you are plugged into the workbook, like so:

var excelApplication = new Application();
var workbook = excelApplication.Workbooks.Open(@"C:\location.xlsx");

In a lot of cases you can copy/pasta your VBA code, prefixing function calls with workbook., replacing Dim with var etc where appropriate and adding semicolons on the end.

It's essentially the same Framework underneath (Excel), you are just changing Syntax from VBA to C#.

When you're done, be sure to save and close the application:

workbook.Save();
excelApplication.Quit();

Then release the application with the Marshal:

Marshal.ReleaseComObject(excelApplication);

I would probably write a wrapper class implementing IDisposable around the Excel Application object, then be sure to use using when this object is used.

A good way to make your case would be to spend an hour or so doing this, which in a short space of time would demonstrate how quickly you could port code across, and convince your colleagues that this is a good idea.

The code would remain largely unchanged in a way, but you have all of the benefits you mentioned of having a C# project within Visual Studio.

Answer 5

You can point out that even Microsoft states in this article:

Updating the code to improve features or to fix bugs would require that the Office artifact be re-emailed, re-structured, and re-worked by every single user and in every single file that has been using the customization.

The article is about different approaches of developing stuff based on Office, maybe you can take also some other pros and cons from it in your discussion.

Answer 6

I am in same position, of having a very large (20k+ lines of VBA code) application built into an EXCEL workbook. Upon testing with Office 2013 it simply fails to work, believed due to changes in the sequence of start-up event in the new office model, and possibly more strict enforcement of EMET. I am thus in the position of doing a crash-conversion to C# and VB.NET before the roll-out of Office 2013 this year. Other, simpler (VBA enhanced) workbooks used around the organization have not been immune, some working and others not.

The errors occur in the Workbook_Open event, where the sheets of the workbook are no longer available, and in internal EXCEL code prior to any VBA code being referenced.

Being comfortable in all of VBA, C# and VB.NET I am writing the conversion in both of the latter two. Framework code is being written in C# because the language idioms allow me to write certain functional constructs in that language 3-4 times faster than in VB.NET. The bulk of the code is in VB.NET because then my rewrite is less extensive, and there are certain idioms there that are not present in C#.

Prior to making any decision, I strongly recommend that you test the existing application with OFFICE 2013 and the fullest extent of EMET enforcement that the organization foresees rolling out over the next 2-3 years. You might, like me, discover that the existing application will simply not run in that environment without a rewrite anyways - in which case the rewrite might as well be in DOT NET and VSTO.

Addendum:

Writing a little automated extract utility, that loops through the entire contents of the workbook VBA and exports all modules, classes and forms to files, is 1-2 days work, depending on how fancy you want it to be. Likewise with the reverse to import the files from a directory to a blank workbook. With these two it is quite possible to have all your VBA code in proper source control, and to have a Build process from source code for your workbook.

OR - use a free utility such as Excel VBA Code Cleaner

Answer 7

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".

Be honest. Do you plan on sharing this information with the other people involved in making this decision? If not, I would place the entire blame on you if the project fails. Since a similar project has already been created and put out into the field, everybody has formed what they expect to happen in their own mind. They know how long it took to build the last one and will believe you can create this one in less time (which may or may not be true based on the additional requirements). Are you ready to take on this responsibility along with learning a new language?

I recommend porting the old app to C# ASAP. Maybe you can learn enough in the process before a decision is made. At least you'll achieve your goal of increasing your skills with the new language and still get the project done on time.

Then again, if you feel that strong about it and building the project is all resting on your shoulders, do it the way you want. It's always easier to get forgiveness than permission.