VBA vs. VS – Final: To Migrate or NOT To Migrate

I hope I didn’t get all of you bored to death with all the technical stuffs in the last 2 weeks.  Don’t worry; this article would be the last one of the series.  I started this series because I know a lot of part time Excel VBA developers; their full time job may be accountants, financial analyst, traders….  They have been using VBA for a long time; and I admit some of them are even better than me.  However, they don’t seem to understand that VBA has its pros and cons and there are other development products which can do better job than VBA.  They just don’t have the time to find out about it.  So I started writing about VBA and its potential replacement, i.e. Visual Studio (VS).

I want to wrap up this series of blog by summarizing if you should use VBA or VS.

To Migrate or NOT To Migrate

Stay with VBA if:

• You want to use a lot of Excel functions and Add-In.
• You have deployment constraint within your organization.
• Your code is tightly integrated with Excel.
• Your code is simple or for one time use.
• Your code does not involve a lot of business logic.
• You code has to run on older version of Excel (2003 and older).

Migrate to Visual Studio if:

• Performance is an issue.  VS compiler allows better optimization and .Net Framework provides mechanisms to handle parallel and distributed processing
• Your code involves a lot of business logic.  VB.Net is an object oriented programming language which provides a more effective way to model business logic and inheritance supports robust code reuse.
• Your business processes requires frequent changes and deployment.  VS has bundled with Team Foundation Server (TFS).  TFS is a comprehensive application lifecycle management (ALM) suite which covers requirement management, change management, project management, configuration management, build management, testing, release management. deployment and issue management.
• You need easier code maintenance.  VS stores data, code and assembly in separated locations; so it facilities easier code maintenance
• You want to run your applications on web, cloud or even smart phone.  .Net Framework provides the foundation of multi platform development.
• You need to integrate with line of business (LOB) systems such as SAP and PeopleSoft.  .Net Framework has built in Web Services support that we can easily integrate your LOB system with Microsoft Office.
• You need a wide variety of user interfaces.  VS offers Windows Form, Windows Presentation Foundation (WPF) and Silverlight which would cover the needs of all your user experiences.
• You need to deploy the application to a wide spectrum of users (both onsite and offsite).  Microsoft System Center Configuration Manager can automatically deploy your .Net applications, setup the right permission and even apply future patches.
• You need to fine tune the application security for different users.  .Net Frameworks allows you to implement a more flexibility security model.

Conclusion

To migrate or not to migrate, it is always a tough decision. The examples I showed you, seemed very simple but in reality the migration is always a huge undertaking in term of both resources and risk.  You should consider their difference with regard to code maintenance, deployment, programming paradigm, performance, and security.  Will your application benefit from Microsoft .NET Framework?  Do your developers know how to do object oriented design (OOD) and understand the difference between VBA and VB.Net programming syntax?  WPF supports more GUI features than VBA UserForms; will your users have better experience with WPF?  It may sounds a stupid question but you may want to confirm with your IT department that you can install .Net Framework to all the computers.

However, the benefits are also significant!  Your application can run 10, 100 or even 200 times faster.  It can be deployed to cloud so you don’t need to buy new hardware or software plus you can add more horse power anytime you want.  You can find out what / who / when / why / how about a particular change.  Object Oriented (OO) is a proven methodology that can reduce development efforts; this is why all the popular programming languages like Java, C#, C++ and Objective C are OO.  There are more benefits about migrating to VS but I have to stop here.

I hope my blogs can help you to make an educated decision on the choice of development tools.  And if you have any further question or comment, please don’t hesitate to contact me at chan_a@algconsultings.com.

• Happy 17 Birthday Visual Basic for Applications!

• VBA vs. VS – Part 1: Performance Review

• VBA vs. VS – Part 2: Programming Paradigm Review

• VBA vs. VS – Part 3: What is new in VB.Net?

• VBA vs. VS – Part 4: .Net Framework

• VBA vs. VS – Part 5: First Excel Project
• VBA vs. VS – Final: To Migrate or NOT To Migrate

Andrew Chan is the owner and founder of ALG Inc.

We help you to make better and faster decisions!