VBA vs. VS – Part 2: Programming Paradigm Review

Part of this article is also being published in the July issue of Comp Act

When Microsoft upgraded VB6 to VB.Net in 2002, they decided to make VB.Net an object oriented programming language.  It was a hard choice because it created a huge gap for VB6  developers.  Most VB developers were not familiar with object oriented, even though some of them had worked extensively with objects; e.g. Microsoft Excel Objects.  VB6 / VBA only allowed developers to create their own business class.  Why was it so hard for some VB6 / VBA developers to migrate to VB.Net?  And why did Microsoft take such bold move to migrate VB to OO programming language?

VBA is an object based programming language.  They allow us to create classes and instantiate objects.  However, unlike object oriented programming language, they don’t have inheritance and hence without polymorphism.  So what are inheritance and polymorphism?  And why are they so important?  Let’s review some of the basic object oriented concepts first.

Object Oriented Concepts

There are a few object oriented concepts:

  • Abstraction
  • Encapsulation
  • Inheritance
  • Polymorphism


Abstraction is "the mechanism and practice of abstraction reduce and factor out details so that one can focus on a few concepts at a time."  Since abstraction extracts key characteristics of an object and hides other immaterial complexity, our readers should easily understand and visualize what we want to discuss.

"I just bought a new Samsung 46-inch 1080p 120Hz LCD TV," my friend told once told me. Size, number of lines, refresh rate and type are the key characteristics of an HDTV. He can go on to tell me about its physical dimension, weight, power consumption, etc., but most people don’t care, and would prefer being told "I just bought a new TV," abstracting the technical specifications to hide unnecessary details.


Encapsulation is "the process of compartmentalizing the elements of an abstraction that constitute its structure and behavior; encapsulation serves to separate the contractual interface of an abstraction and its implementation."

Encapsulation allows programmers to use any method without understanding the details of implementation. This can drastically reduce the learning curve or maintenance effort of an financial system.

For example, how many developers understand the implementation of ADO.Net? With ADO.Net, most of us can learn in a couple of hours how to write a simple function to retrieve data.

If our financial system consists of 1,000+ classes, do we want all our developers to understand every single class and every method within? It would take forever to train a new developer. Encapsulation will shorten the learning curve of developers and will provide better system manageability and stability.


Inheritance is "a way to form new classes (instances of which are called objects) using classes that have already been defined. Inheritance is employed to help reuse existing code with little or no modification."

When we derive a new annuity calculator from its base class, we only have to implement the new features. It can save us enormous development, checking and testing time.  We can add new data type and member functions or even override existing functions.

Inheritance also increases overall system stability and reduces quality assurance effort. It allows developers to reuse code, enhance and modify existing class.


"Polymorphism in the context of object-oriented programming, is the ability of one type, A, to appear as and be used like another type, B."   Inheritance is required in order to achieve polymorphism.

Polymorphism can greatly simplify coding and allow extensibility for future enhancements. With polymorphism, we can call 10 different annuity calculators from a single line of code. When we want to add five more new annuity calculators, we don’t need to modify the calling function.

Benefits of Object Oriented

Since OO promotes code reuse which would result in better reliability, robustness, extensibility and maintainability.  No wonder more and more computer systems have been developed using OO languages.  According to a study by TIOBE, OO programming languages are the most popular for more than 4 years; 55.4% code is based on OO languages.

However, migrating to OO programming language would not automatically grant us all these benefits!  Let’s say we had an old financial system that we developed using VB 6 and it had 20 program files. We decided to migrate to the latest Visual Basic.Net and create 20 classes to store each program files respectively. Moreover, since we couldn’t think of a good name for each class, we simply named them fin01, fin02 … fin20, the same name as each of the program files. We then copied the content of each program file into the corresponding class. By doing this, we created a system that uses OO programming language. But what benefits does our system have from this migration? Not a lot unfortunately!

In order to realize the true benefits of an OO programming language, we must understand the SOLID OO principles first.

SOLID Object Oriented Principles

  • Single Responsibility Principle
  • Open Closed Principle
  • Liskov Substitution Principle
  • Interface Segregation Principle
  • Dependency Inversion Principle

Single Responsibility Principle (SRP)

"There should never be more than one reason for a class to change."–Robert Martin, SRP paper linked from The Principles of OOD.

Simple is beautiful. Each class should have only one responsibility and focus to do one single thing.

Our annuity calculator classes are already very sophisticated. If we also implement policy projection, decrement calculation and cashflow projection within it, then it would be huge and all our developers may always work on this big class together.

SRP would promote the reuse of code, clarity and readability. Our system would also be easier to test, enhance and maintained. Developers would also find less contention for source code files.

Open Closed Principle (OCP)

"Software entities (classes, modules, functions, etc.) should be open for extension, but closed for modification."–Robert Martin paraphrasing Bertrand Meyer, OCP paper linked from The Principles of OOD.

Most of us work on existing systems rather than build new systems from scratch. When we add new features to a system, we often feel more comfortable adding new functions than modifying an existing codebase. Why? We worry that our modifications would accidentally add new bugs to the systems, especially fragile systems. OCP recommends extending existing codebase, not modifying it.

If we already have 10 different annuity calculator classes, adding a new one should not modify any existing code.

Once we have followed SRP to build our system, it would be easier to implement OCP. Systems following OCP are often more stable because existing code does not change, and new changes are isolated. Deployment is also faster because existing features would not be accidentally modified.

Liskov Substitution Principle (LSP)

"Functions that use pointers or references to base classes must be able to use objects of derived classes without knowing it."–Robert Martin, LSP paper linked from The Principles of OOD

This principle is just an extension of the Open Close Principle, and it means that we must make sure that new derived classes are extending the base classes without changing their behavior so that the derived classes must be completely substitutable for their base class. Otherwise the new classes can produce undesirable effects when they are used in existing program modules.

Below is a classic example of LSP:

public class Rectangle
protected int _width;
protected int _height;
public int Width
get { return _width; }
public int Height
get { return _height; }
public virtual void SetWidth(int width)
_width = width;
public virtual void SetHeight(int height)
_height = height;
} public class Square: Rectangle
public override void SetWidth(int width)
_width = width;
_height = width;
public override void SetHeight(int height)
_height = height;
_width = height;
public class RectangleTests
public void AreaOfRectangle()
Rectangle r = new Square();
// Will Fail – r is a square and sets
// width and height equal to each other.
Assert.IsEqual(r.Width * r.Height,10);

Square class is derived from Rectangle class; so C++ allows a Square object to be cast into a Rectangle object. However, Square class has its own setter functions; so r.SetWidth and r.SetHeight would set width and height equal to each other. What do we expect r.Width * r.Height to be equal to? Is r a Rectangle or Square object?

LSP would make the system easier to test and provide a more stable design.

Interface Segregation Principle (ISP)

"Clients should not be forced to depend upon interfaces that they do not use."–Robert Martin, ISP paper linked from The Principles of OOD

Again, it is another "simple is beautiful" principle. We should have multiple slim interfaces rather than a giant interface. Each interface should serve one purpose only.

If we have both policy month and calendar month projections, put them in two separate interfaces. For example, use IPMCashflowProj and ICMCashflowProj rather than just one interface named ICashflowProj.

With ISP, design would be more stable and flexible; changes are isolated and do not cascade throughout the code.

Dependency Inversion Principle (DIP)

"A. High level modules should not depend upon low level modules. Both should depend upon abstractions.

B. Abstractions should not depend upon details. Details should depend upon abstractions."–Robert Martin, DIP paper linked from The Principles of OOD.

Low-level classes implement basic and primary operations, and high-level classes often encapsulate complex logic and rely on the low-level classes. It would be natural to implement low-level classes first and then to develop the complex high-level classes. This seems logical as the high-level classes consume low-level classes. However, this is not a flexible design. What happens if we need to add or to replace a low-level class?

If our annuity classes (high level) contain cashflow classes (low level) directly, and we want to introduce a new cashflow class, we will have to change the design to make use of the new cashflow class.

In order to avoid such problems, we can introduce an abstraction layer between the high-level classes and the low-level classes. Since the high-level modules contain complex logic, they should not depend on the low-level modules. The new abstraction layer should not be created based on the low-level modules. The low-level classes are created based on the abstraction layer.

Once we implement DIP, our financial system will be significantly easier to extend, and deploying new features will take less time.


Please keep in mind that all of these principles are just guidelines that would make our system more stable, easier to maintain and enhance; but they are not ironclad rules. We must apply our own judgement and experience.

The learning curve from VBA to VB.Net is challenging; however, the benefits can also be enormous and definitely worth the efforts.

Andrew Chan is the owner and founder of ALG Inc.

We help you to make better and faster decisions!

About Andrew Chan
Andrew Chan is an Business Consultant who gives you accurate, consistent and timely information so that you can make better and faster decisions. He is an Associate of Society of Actuaries with over 20 years of IT experience. Apart from strong analytical skills and proven technical background, he was also a former system director at Manulife who had extensive project management experience. If you are looking for someone to gather, consolidate, validate, visualize and analyze data, look no further! Andrew can provide the most cost effective business analytics solution so that you can explore, optimize, predict and visualize your business. Don’t guess on any decision, no matter it is finance, operation, marketing or sales! Always ask for evidence!

6 Responses to VBA vs. VS – Part 2: Programming Paradigm Review

  1. Pingback: Visual Basic.Net, Part 3 – What is new? « Technologies and your business

  2. Pingback: Happy 17 Birthday Visual Basic for Applications! « Technologies and your business

  3. Pingback: VBA vs. VS – Part 1: Performance Review « Technologies and your business

  4. Pingback: VBA vs. VS – Part 4: .Net Framework « Technologies and your business

  5. Pingback: VBA vs. VS – Part 5: First Excel Project « Technologies and your business

  6. Pingback: VBA vs. VS – Final: To Migrate or NOT To Migrate « Technologies and your business

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: