Happy 17 Birthday Visual Basic for Applications!

Visual Basic for Applications (VBA) is 17 years old!  It was introduced with Excel 5.0 in 1993; it offered Excel great programmability and allowed Excel users to automate repetitive processes and added extra functionality to Excel.

VBA is easy to learn and simple to use, so a lot of power users have used VBA to develop business applications.  I don’t know exactly how many developers have used VBA but VBA is definitely one of the most popular development platforms.  Developers have used it with Excel, Outlook, Visio, Word and even some non-Microsoft products. Excel appears to be the most common one that you would find VBA running on.

Microsoft has added new features to VBA in every new version of Office.  However, the biggest change is the introduction of Visual Studio Tools for Office (VSTO). VSTO allows developers to program Microsoft Office 2003 and later versions of Office. Why has Microsoft offered 2 development platforms? Why is Microsoft so excited about VSTO? This is what I am going to discuss in this blog.

VBA is no doubt a very powerful development tools which allows developers to build business applications but it has limits and issues:

· Performance – VBA compiles code into pseudo code which would slow down the execution.

· Multi-Threaded – Modern CPU can run up to 16 threads but VBA can only support single thread application.

· Object Oriented Programming – VBA is a object based programming language and it does not offer the same level of code reusability and easy maintenance as other object oriented programming languages.

· Document Centric – Most VBA applications store the code and data in the same file. Business users have to update the data frequently so it makes change management and deployment extremely difficult.

· Windows Functionality – VBA only provides a very limited set of Windows functionality so if your programs need to access other Windows core services, then you have to program through Windows API and it would be cumbersome.

· Security – VBA can potentially open the door for malware and other security issues.

· New Technology – VBA does not support new technologies, e.g. Common Language Runtime, .NET Framework, Web Services, Parallel Programming…. etc.; these new technologies can help you to develop more manageable, robust, and scalable business applications.

· Development Environment – anyone know when was the last time VBA development environment got enhanced? Today’s development tools have richer debugging tools, customizable IDE interface, emulators, add-ins and more; so you can build your system quicker.

In simple English, VBA does not support today new technologies, both hardware and software. Although, VBA is still available in Office 2010; Microsoft no longer offers VBA distribution licenses to new customers as of 2007.  I can imagine VBA would have its 20th birthday or even 25th birthday.  However, do you want to continue to use VBA for your development?  I doubt if Microsoft is going to make any significant investment on it.

Microsoft introduced Visual Studio .Net in 2003; an effort tried to unify all Microsoft development environments and bring object oriented to its programming languages, e.g. C# and Visual Basic .Net. Microsoft also introduced .NET Framework and Common Language Runtime which would expedite and optimize the development process and provide access to system functionality.

Visual Studio Tools for Office (VSTO) is a set of project templates available in Visual Studio .Net and allows the Office applications to be written in .Net environments.   So you can now integrate the best from 2 worlds, i.e. Office applications and .NET Framework.

Microsoft just released Visual Studio 2010 couple months before Office 2010.  You should really try the development features in both products.

Which one is more suitable for you?  VBA?  VSTO?

• 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

Andrew Chan is the owner and founder of ALG Inc.

We help you to make better and faster decisions!