Web Service 1-2-3

Have you ever done a software inventory in your organization?  You will be surprised to find out how many calculation engines or financial models that your organization has.  And if you do a sample audit, you will realize that a lot of business logic are very similar within these engines / models.  It would be great if we can have a central calculation engine that is shared by all our systems / model.  We will have consistent results, reduced risks and effectively increase our model efficiency.

So what are the problems? One of the most common challenges is that our systems, i.e. pricing, valuation, asset liability management are in an heterogeneous environment; i.e. they can be in mainframe, UNIX, Windows, web or even tablet.  They just don’t talk to each others!  Fortunately, a lot of IT leaders recognized the problem and developed an interoperable machine to machine interaction protocol that allows systems to interact in a standard prescribed by W3C (World Wide Web Consortium).  This standard, Web Services was originally proposed by Microsoft in 1999 and quickly gained supports from IBM and other major software vendors, e.g. Oracle, HP and Sun.  During the last twelve years, web service has evolved significantly and become the backbone of service-oriented architecture.  Can we use Web Service to develop a central calculation library that can be used in Excel, desktop, server, web and even smartphone / tablet?  Let me demonstrate how easy Web Service can be implemented, as simple as 1-2-3.

  1. Install Visual Studio 2010
  2. Download and install WCF REST Service Template 40 (http://visualstudiogallery.msdn.microsoft.com/fbc7e5c1-a0d2-41bd-9d7b-e54c845394cd)
  3. Create a new WCF REST Service project in Visual Studio 2010

In the New Project Dialogue, we select Online Templates, then select WCF.


You will see the WCF REST Service Template 40 that we just installed; select it and give a name to the project, then click OK button.  WCF REST Service Template 40 generated all necessary program files to run Web Service for us.  If you build the project and run it, you will get the following web page.


Service1 is the name of Web Service.  Let’s create an Add function for Service1.

Add the following lines to Services1,cs:

[WebGet(UriTemplate = "Add?a={a}&b={b}")]
public Int32 Add(Int32 a, Int32 b)
    return a + b;

After we rebuild / rerun the project, then we can enter http://localhost:40501/Service1/Add?a=1&b=2.  A web page will be displayed with the following content:

<int xmlns="http://schemas.microsoft.com/2003/10/Serialization/">3</int>

This example is very simple but you can see it is also very simple to get Web Service up and running.  If you want to find out more about Web Service, please stay tuned.  I would show you how to call more sophisticated calculation within Web Service and call Web Service from other applications, e.g. Excel, desktop application and even smartphone.


All testing results correct!

and we are ready to deploy the application.  I hate to play devil’s advocate but system development is more than just getting the correct results.  In order to have a manageable, robust, and scalable system that have maximum life expectancy; there are many important things that we have to take care before we can deploy the system.  The first thing I want to visit is actually testing. 

“Did I just tell you that all testing results were correct?”

Software Testing

Software testing is a huge subject by itself and I won’t be able to cover everything but there are a few common issues that I would like to share:

  • Testing plan / scripts should be properly documented – you may not believe it but most people could not be able to reproduce the test that they did.
  • Every code / condition / exception must be covered – I hardly found testing that would cover more than 60% of the codes.
  • Non functional testing such as scalability, performance, and security should be carefully planned – the system may work well for single tester and a few hundred test cases.  Would it still work in production environment that have million of records and hundred concurrent users?
  • User Compatibility – you may be testing your application on Internet Explorer 9 / Office 2010 when your users still use Internet Explorer 6 / Office 2003.  Good luck when you deploy the application to your users’ computer.
  • Tools – Do you have any tool to automate testing, measure code coverage and defects tracking systems?  One reason that system is not thoroughly tested, is because we don’t have enough resources.  Appropriate testing tools would automate testing, compare results and report any defect; we can do more testing with lesser resources.

I only cover the bare minimum of testing and I haven’t covered any details about unit testing, integration testing, system testing, regression testing and many more.  Wikipedia has a good article about software testing.  You may want to go over it and see if you have done all necessary testing yet.

The rule of thumb is to find and fix problems as soon as possible!

Code Refactor

All testing results are correct, are we ready to deploy the application? Not quite yet if you want to deploy an application that is easy to extend and maintain!  Let’s review the code and see if we find some of the common code smells:

  • Copy and paste – I recently fixed a very small bug, but I have to go through nearly 100 different places.  The same piece of code exists in over 20 different place and I also found similar code in another 50+ different places.
  • Jumbo functions – have you ever read functions that have over 1,000 lines?  It tries to do too much.  By the time you go through half of it, you already forgot what it is supposed to do.  You are lost in the functions. Simple and small function is much easier to fix and extend.
  • God object is similar to Jumbo function and it violates virtually every object-oriented principle that promotes faster development.
  • Global Variables – they are difficult to trace and understand because they can be read / modified in any part of the application.  Sometime, you may not even know that you are using one.
  • Morse Code / mystery variables – A1, B1, C1, Xn, Yn, Zn and to make it worse, there is no comment.  It can take you hours to understand a simple if statement.
  • You can visit Martin Fowler’s website to get a more comprehensive list of refactorings.

The bottom-line is that code refactor would allow the application to produce the same results but run faster, easier to fix bugs and extend.


It may sound obvious that any sensitive / critical data must be secured; however, a lot of business applications’ security is nothing more than illusion.  The application may require user login but the data is not secured or its security would require minimum effort to bypass.

Google “Excel Password” and you would not believe how many Excel password removal utilities are available;  most of them are even free.  How about Microsoft Access?  Why don’t you Google again?  Does your application store data on Excel?Apart from data; there may be situations that you also want to secure the code, your intellectual property.  If you develop a financial model that can price products 10 times faster and 10 times accurate; I strongly recommend that you should not deploy your model on Excel / VBA.

Let’s deploy it now?

I am going to cover software deployment in my next blog but I would like share some of my own experience in the last couple years.

  1. It was supposed to be a short project.  According to the project manager, everything was ready; I just had to migrate the application to production environment.  My first problem was that I could not even compile the programs successfully.
  2. Again, another simple deployment job.  The challenge was the job took over 10 hours when the users only had 1 hour window.
  3. We ran a stress test on a server that had only 2 cores and 4 GB RAM; it could support more than 200 concurrent users within 2 seconds.  We ordered 3 servers, each has 8 cores and 32 GB RAM; the web server crashed after 20 concurrent users.
  4. We tested the application on development server and integration server; our users tested it on users acceptance test server.  Everything was fine.  When the application finally deployed to production server, nothing worked.
  5. Our power users developed a sophisticated financial model that was going to distributed to their business partners.  It is ready to deploy; they just want to secure the model, so that their business partners would not be able to trace the logic and data.  It was developed on Excel.

Deployment is very important and no matter how well the application is developed and tested.  A poor deployment can still produce terrible users experience.

Are you developing a system? or just programming?

It is easy to do programming today; there are so many powerful development tools that are developer-friendly, e.g. Excel / Access VBA, Visual Studio, MATLAB, R…etc.  A lot of business users who are analytical, logical smart, choose to develop their own business application.  These systems can be agile, cost effective and deliver what they want!

However, many business users have handed over these hidden business applications back to IT department in a few years.  These lovely applications have grown into monsters that they can’t control; it takes too long to process, whenever they want to add a small enhancement, something unexpected fail or certain disasters happen, e.g. the business user who created the application left the company.

What are their problems?  Why did these business applications have such short life expectancy?  How can we manage our applications?


Can we afford to have a supercomputer?

Tianhe-1A is currently the fastest supercomputer in the world with 2.5 PFLOPS computation power. It is nearly 50% faster than the former number one system – the Cray  XT5 “Jaguar”.  Jaguar’s computation power comes from its 224,256 AMD Operon cores but Tianhe-1A’s architecture is fundamentally different.  Tianhe-1A is equipped with 14,336 Intel CPU and 7,168 NVidia Graphics processing units (GPU), so most of its computation power comes from video card (GPU) rather than CPU.

What can GPU do in reality?  Especially in finance industry?   In 2009, Bloomberg realized the power of GPU.  So instead of installing 1,000 new servers, Bloomberg is using 48 server / GPU pairs to price its asset-backed security.  According to their CTO, Shawn Edwards, “Overall, we’ve achieved an 800% performance increase, what used to take sixteen hours we’re computing in two hours.”  Bloomberg is not alone; BNP also uses GPU to price their derivatives.  Other software vendors such as MATLAB supports GPU in its Parallel Computing Toolbox.  General-Purpose computation on Graphics Processing Units (GPGPU) has been gaining huge momentum in financial industry.

What is GPU anyway?

If you have a computer, then you have a GPU.  GPU is the processor on your video card; it is used to produce 2D graphic, 3D scenes, capture TV signal, decode / encode high definition video… etc.  Like CPU, it is also programmable; game developers have been using Open GL and DirectX to create video games which involves a lot of computations.

What are the difference between CPU and GPU?

Currently, i7-990X is the fastest Intel CPU and it has a clock speed of 3.46 GHz.  3.46 GHz?  It is not very impressive when we looked back to CPU history; Intel Pentium 4 had already reached the clock speed of 3.4 GHz back in 2004.  The CPU clock speed didn’t increase a lot for the last 7 years; it has reached its limit.  Clock speed used to be the major performance factor for CPU.  Instead of increasing clock speed, CPU manufacturers, AMD and Intel have increased the number of cores in each CPU, e.g. 2-core, 3-core, 4-core.  There are 6 cores in i7-990X. 

NVIDIA did a comparison between CPU and GPU and we can see there is a quiet revolution.  GPU’s computation power had grown exponentially where CPU remained quite linear.


Well, let’s look at today’s GPU; AMD Radeon HD 6970 has 1536 stream processors; its computer power is 2.7 TFLOP and 683 GFLOP for single precision and double precision respectively.

How about price?  Intel i7-990X is $999 and AMD 6970 is only $359.

CPU has its advantages.  A desktop PC can have up to 24 GB memory for as little as $400 and server can install up to 144 GB memory.  GPU on the other side is quite expensive to increase its memory, e.g. NVIDIA GPU, C2070 has 6 GB memory but it costs $4,000.  So if our application requires a lot of database activities and little calculation, then CPU is still the best.

CPU is still much faster in term of single thread processing; AMD 6970 has only up to 880 MHz Engine clock where we can find CPU with clock speed over 3 GHz.  If our application involves heavily sequential processing and cannot break down for parallel computing environment  (multi cores), then CPU would perform much quicker.

How to program GPU?

GPU used to required special programming languages (e.g. OpenGL) to develop GPU applications.  In 2006, NVIDIA released CUDA, a SDK that allows developers to use C, a general programming language to code in GPU.  It is a major milestone towards General-Purpose computation on graphics processing units (GPGPU).  CUDA has been evolved to support other general programming languages such as C++, FORTRAN, Java, Python and Microsoft .Net Framework.

NVIDIA also introduced Parallel Nsight, a FREE GPU development environment that is tightly integrated with Microsoft Visual Studio, the world’s most popular development environment.

The bottom-line is if we know Visual Studio, C / C++ and have vector programming experience, then congratulation!  We can start GPGPU development now.  Here is a link to some CUDA samples.

If we like to develop GPU applications that can run on heterogeneous systems rather than just NVIDIA video cards, then we can consider either OpenCL or DirectCompute.

What is the future of GPU?

Before we look into the future, let’s look at today!  GPU is getting very popular in scientific research, financial modeling and other general purpose computation intensive areas.  For example,  Folding@Home is a distributed computer project that study the relationship between protein folding and diseases such as cancer, Mad Cow (BSE), Alzheimer’s…etc.  It has a computation power of 9.4 PFLOPS and 92% (8.7 PFLOPS) is generated from GPU.  We can find more successful stories from NVIDIA.

Not every application can benefit from GPU but GPGPU is going to offload a lot of computation intensive code from CPU in near future.  Is our application ready?


GPGPU has offered us an opportunity that is cost effective and scalable.  Let’s review Bloomberg’s bond pricing system again; how much would it cost to build 1,000 servers, plus future maintenance cost including space rental and electricity?  Now they have only 48 servers!  It is also much easier to scale it up!

No matter if our systems are going to run on CPU or a hybrid environment (both CPU / GPU), it is going to be multi core.  Intel is working on an experimental 48-core processor that can theoretical expand to 1,000 core.  So we must re-think, re-design and re-develop our algorithm, code and system to support multi core.  If we want to migrate out software to GPU, we must also consider the constraint of GPU; i.e. it does not work well with memory hunger systems.

How much video cards do we need to build a supercomputer?  I would say if we can build a PC farm that accommodates 20 video cards, then we have a supercomputer in our basement.  We can install 3 video cards on a motherboard, e.g. ASUS Rampage II Extreme; so we need about 7 PCs. 

Can we afford to have a supercomputer?

Multi core is our future!

Andrew Chan is the owner and founder of ALG Inc.

We help you to make better and faster decisions!

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.


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.

Andrew Chan is the owner and founder of ALG Inc.

We help you to make better and faster decisions!

VBA vs. VS – Part 5: First Excel Project

So much about background and theory, let’s start our first Excel project using Visual Studio 2010.

After we started Visual Studio 2010, we can build our first Excel project:

  • Select File, then New and choose Project… image
  • New Project dialogue is displayed, select Excel 2010 Workbook and enter First Excel Project into Name and then click OK button.


  • We choose to Create a new document, click OK button


  • Visual Studio creates the First Excel Project project, and adds the following files to the project.
  • First Excel Project .xlsx – Represents the Excel workbook in the project. Contains all the worksheets and charts.
  • Sheet1.vb file
  • Sheet2.vb file
  • Sheet3.vb file
  • ThisWorkbook .vb file – Contains the design surface and the code for workbook-level customizations.


  • In Solution Explorer, Right Click on Sheet1.vb and select View Code.



  • Replace the Sheet1_Startup event handler with the following code. When Sheet1 is opened, this code adds Now to first 100,000 cells in Col A.

Private Sub Sheet1_Startup() Handles Me.Startup
    Cells(1, 1) = Now

    For i = 2 To 100000
        Cells(i, 1) = Now
End Sub

  • When the code is ready, press F5 or click the green arrow button to build and run your project.


  • Excel opened First Excel Project Workbook and it would take about 10 seconds to process and added Now to 100,000 cells.


Instead of cut and paste my code into the program, if you actually type my code then you would see VS editor is much smarter.  When I tried to type Cells in the editor, it would actually show me the variable name.

And when I finished the For statement, VS editor would automatically add the Next statement.


It is a very simple program, so I decided to repeat the simple code in an ordinary Excel 2010 VBA environment.


Run the program on VBA.


I am actually surprised to see that the same logic run faster in VS environment; I would expect the other way.  So I have to do more study to find out the reasons behind.  This is a very simple demo that you may never find in practical business environment.  So your real experience would definitely be not the same.  However, this demo should give you some idea how Excel (and other Office product) can be automated in VS as well.

There are 3 types of project templates for Excel, Add-in, Template and Workbook.  We were using Excel 2010 Workbook for this demo which is a document-level customizations.  This means the code is associated with a single workbook; we have to open this particular workbook to run the workbook.   If you want the code to be avialable for all Excel workbooks, then we should choose the Add-in template.  Add-ins is associated with  Microsoft Excel, regardless of which documents are open.


I am going to write 1 more article to wrap up my stories, I would discuss the situations that I would recommend my clients to migrate their application from VBA to VS.  VBA is a great development tool and I still use it to check results from the financial models  that I developed using C++.  However, I hope you can now understand that VBA has limits and there are tools on the market that can do a better development job under certain circumstances.  For example, do you want to integrate your line of business (LOB) systems like SAP or PeopleSoft with Microsoft Office suite?  It would be much easier to do it with VS than VBA, you can find some information from Microsoft MSDN: Microsoft Office and LOB integration.

Andrew Chan is the owner and founder of ALG Inc.

We help you to make better and faster decisions!

VBA vs. VS – Part 4: .Net Framework

.Net Framework may be new to a lot of VB6 / VBA developers; however, the idea is not new.  When we used VBA / VB6 to create a simple graphic user interface (GUI), e.g. MsgBox or use Data Access Objects (DAO) to retrieve data from Microsoft Access, we are actually calling a runtime class library that contains millions line of code.  We don’t have to worry about all the Windows / database API and we don’t need to manage the messages flying around domain specific objects.  The VB6 runtime and DAO would take care everything for us and we only need to focus on business logic.  This is the beauty of VB6 / VBA.  Let Microsoft takes care about the technical complexity ! 

This is the same concept behind .Net Framework; the Common Language Runtime (CLR), the runtime environment for .Net Framework is more powerful than VB6 runtime and the Framework Class Library provides a larger range of features.

If you read my blog VBA vs. VS – Part 1: Performance Review, you can see how easy and powerful .Net Framework can be.  Instead of using a regular For statement, I used Parallel.For; Parallel is a new feature of .Net Framework which would make the best use of our multi core CPU.  The performance was improved by 2.5 times.  We would not be able to maximize our hardware investment if we are using VB6 / VBA.

.Net Framework

The .Net Framework consists of 2 main components, Common Language Runtime (CLR) and Framework Class Libraries (FCL).

    Common Language Runtime

    CLR is an abstraction layer over the operating system that all .Net programs are running on.  It takes care all technical details for developers; so we don’t need to worry about:

  • CPU management
  • Memory management
  • Thread management
  • Objects management
  • Exception handling
  • Garbage collection
  • Security

We can use VB.Net, C#, C++ and other .Net compliance languages to develop applications.  CLR makes it easy to design components and applications whose objects interact across different programming languages. Objects written in .Net languages can communicate with each other, and their behaviors can be tightly integrated.

Framework Class Libraries

FCL is a a collection of thousands of reusable classes that encapsulate a large number of common system functionality.  To name a few, it provides simple file I/O, GUI, and database services or we can use something much more powerful and sophisticated such as parallel programming, distributed computing, workflow and cryptographic services. 

As a business developer, even if I have the technical knowledge, it may take me months to build a financial application that can fully utilize my multi cores CPU.  How much effort do we have to spend if we want to migrate the same application to a server farm with hundred or even thousand CPU?


.Net Framework has many advantages over VB6 / VBA.

Multi-Language Development Platform

Instead of just using VB, CLR allows us to use the language we prefer.  We can use:

  • Static Language – Visual Basic.Net, C# and C++ .
  • Dynamic Language – Managed JScript, IronRuby, and IronPython.

User Experiences

Developers can use Windows Presentation Foundation (WPF) or Silverlight to develop richer GUI.

Most Windows developers are not graphic designer and graphic designers do not always know how to program.  WPF provides a common file format (XAML) which allows designers to work alongside developers in a workflow that promotes creativity while maintaining full fidelity.  WPF is a powerful framework that we can integrates GUI, documents, 2D and 3D graphics, hardware accelerated effects, and media content. 

Silverlight provides a cross-browser, cross-platform, and cross-device plug-in for advertising and rich interactive applications

Web Applications

Everyone is using the web now!  How can we run our applications on web?

ASP.NET enables developers to create anything from small, personal Web sites through to large, enterprise-class dynamic Web applications. Together with Asynchronous JavaScript and XML (AJAX ), developers can quickly create efficient, interactive, and highly personalized web sites

Distributed Systems

Do our systems need to communicate with our business partners or services providers?  Do we want to build a server farm to run our financial model?

Windows Communication Foundation (WCF) provides a model that developers can rapidly build service-oriented applications that communicate across the web and the enterprise.

Business Processes

We work as a team.  How can we work more effectively together?

Windows Workflow Foundation (WWF) delivers a  model that developers can use to build a business process which enable closer collaboration among business users.

Data Access

We already have DAO to access data from relational database but we now have data from XML and other applications.

ADO.NET provides a rich set of components for creating distributed, data-sharing applications,providing access to relational, XML, and application data.


If you visit Microsoft .NET Framework Class Library, you would see how feature rich it is.  I have used .Net Framework for over 5 years but I seriously doubt if I ever use more than 10% of the functionality.  I can  now develop web sites, distributed systems, and cloud applications.  Accessing data from RDB, XML and other applications is much easier.  I can  do thing that was impossible with VB6 / VBA or at least 10 times harder!

Andrew Chan is the owner and founder of ALG Inc.

We help you to make better and faster decisions!

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

I mentioned in my last blog, VBA vs. VS – Part 2: Programming Paradigm Review that VB.Net is now an object oriented (OO) programming language.  So what OO features have been introduced to VB.Net programming language?


VB.Net supports inheritance; it allows us to derive classes from an existing base class by extending the properties and methods of the base class. They can also override inherited methods with new implementations.

Class Base
    Sub Method1()
        MsgBox("This is a method in the base class.")
    End Sub
    Overridable Sub Method2()
        MsgBox("This is an override method in the base class.")
    End Sub
End Class

Class Override
    Inherits Base
    Public Field2 As Integer
    Overrides Sub Method2()
        MsgBox("This method is overrided in a derived class.")
    End Sub
End Class

Protected Sub Test()
    Dim O1 As New Base
    Dim O2 As New Override
End Sub


Overloading is the ability to create several methods with the same name which differ from each other in terms of the type of the input and the type of the output of the function.

Overloads Sub Print(ByVal theChar As Char)
    ‘ Add code that displays Char data.
End Sub
Overloads Sub Print(ByVal theInteger As Integer)
    ‘ Add code that displays Integer data.
End Sub
Overloads Sub Print(ByVal theDouble As Double)
    ‘ Add code that displays Double data.
End Sub


Overrides allows derived classes to override member variable or functions that are inherited from parent class. 

Const BonusRate As Decimal = 1.45D
Const CommissionRate As Decimal = 14.75D

Class Commission
    Overridable Function Payment( _
        ByVal FaceAmount As Decimal, _
        ByVal CommissionRate As Decimal) _
        As Decimal

        Payment = FaceAmount * CommissionRate
    End Function
End Class

Class BonusCommission
    Inherits Commission
    Overrides Function Payment( _
        ByVal FaceAmount As Decimal, _
        ByVal CommissionRate As Decimal) _
        As Decimal

        ‘ The following code calls the original method in the base
        ‘ class, and then modifies the returned value.
        Payment = MyBase.Payment(FaceAmount, CommissionRate) * BonusRate
    End Function
End Class

Sub RunCommission()
    Dim CommissionItem As Commission = New Commission
    Dim BonusCommissionItem As New BonusCommission
    Dim FaceAmount As Decimal = 40000D

    MsgBox("Normal pay is: " & _
        CommissionItem.Payment(FaceAmount, CommissionRate))
    MsgBox("Pay with bonus is: " & _
        BonusCommissionItem.Payment(FaceAmount, CommissionRate))
End Sub

Constructors and Destructors

Constructors are functions that control initialization of new instances of a class. Conversely, destructors are methods that free system resources when a class leaves scope or is set to Nothing.  VB.Net supports constructors and destructors using the Sub New and Sub Finalize procedures.


Interfaces determine what properties and methods of the classes can be consumed within the system.  It is a logical view of the classes, which provide no implementations.

Interface IAsset
    Event ComittedChange(ByVal Success As Boolean)
    Property AccountBalance() As String
    Function GetBalance() As Double
End Interface

Class InvestmentAccount
    Implements IAsset

    Public Event ComittedChange(ByVal Success As Boolean) _
       Implements IAsset.ComittedChange

    Private AccountBalanceValue As String

    Public Property AccountBalance() As String _
        Implements IAsset.AccountBalance

            Return AccountBalanceValue
        End Get
        Set(ByVal value As String)
            AccountBalanceValue = value
            RaiseEvent ComittedChange(True)
        End Set
    End Property

    Private BalanceValue As Double

    Public Function GetBalance() As Double _
        Implements IAsset.GetBalance

        Return BalanceValue
    End Function

    Public Sub New(ByVal AccountBalance As String, ByVal Balance As Integer)
        Me.AccountBalanceValue = AccountBalance
        Me.BalanceValue = Balance
    End Sub
End Class


Delegate is a reference to function and it is particular useful if we want to raise events that can call different event handlers under different circumstances.  We can dynamically associate event handlers with events by creating a delegate when we use the AddHandler statement. At run time, the delegate forwards calls to the appropriate event handler.  Delegate is not limited for event handlers and we can use it to call different versions of functions at run time.

Delegate Sub MyFirstDelegate(ByVal x As Integer)

Sub TestSub(ByVal x As Integer)
MsgBox("The value of x is: " & CStr(x))
End Sub
End Class

Protected Sub DelegateTest()
Dim T1 As New TestClass
Dim msd As MyFirstDelegate= AddressOf T1.TestSub
End Sub

Shared members

Shared members are properties, procedures, and fields that are shared by all instances of a class or structure.

Public Class SharedClass
    Public Shared Count As Integer = 1
    Public Shared Sub ShareMethod()
        MsgBox("Current value of Count: " & Count)
    End Sub

    Public Sub New(ByVal Name As String)
        Me.SerialNumber = Count
        Me.Name = Name
        Count += 1
    End Sub
    Public SerialNumber As Integer
    Public Name As String
    Public Sub InstanceMethod()
        MsgBox("Information in the first object: " & _
            Me.SerialNumber & vbTab & Me.Name)
    End Sub
End Class

Sub TestShared()
    Dim Shared1 As New SharedClass("keyboard")
    Dim Shared2 As New SharedClass("monitor")

End Sub


There are many non OO related enhancements that were added to VB.Net, e.g. structured error handling, multithreading.  However, I am not going to cover them in this article.  So far, I covered most of the OO features in VB.Net and I the examples are not so difficult to follow.  But in case if you have any question, please feel free to ask.

I often tell people that the OO programming is easy to pick up; the challenge is OO design.  How can we design a class library that is simple to maintain and easy to reuse?  It would take us years before we can master the OO design.  But at the end, it is worth the efforts!

Andrew Chan is the owner and founder of ALG Inc.

We help you to make better and faster decisions!

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!

VBA vs. VS – Part 1: Performance Review

I had used VB6 / VBA for many years and I like them a lot!  Like most Windows programmers, I developed my first Windows application using Visual Basic ; I didn’t have to learn or understand Windows API which drastically reduced the learning curve.  However, technology has changed a lot in the last 10 years.  Microsoft stopped support VB6 in March 2008 and as of July 1, 2007, Microsoft no longer offered VBA distribution licenses to new customers. 

Yes, we can still find VBA in Office 2010 but what improvement has been introduced in the latest VBA?  We can now migrate Excel 4 macros to VBA – this is the only enhancement that was mentioned in Microsoft Technet , Changes in Excel 2010.  Does anyone still have any Excel 4 worksheet?

I don’t think Microsoft would get rid of VBA in near future but at the same time, is Microsoft going to invest any significant effort to improve VBA?  I seriously doubt it.  So do you want to use a development tools that didn’t have any major upgrade for the last 10 years?  Or do you prefer to use the latest development tool that allow you to simplify coding, debugging and deployment?  A tool that you can target multiple platforms, e.g. Windows, web, cloud and of course Office environment.

Let’s look at the difference between Visual Studio and VBA by first reviewing their performance.  I developed a very simple financial calculator using Excel VBA.  I ran it.

Option Explicit

Public Sub Main()

Dim startTime As Date

Dim endTime As Date

Dim run As Long
Dim runs As Long

Dim Age As Integer
Dim AgeStart As Integer
Dim AgeLimit As Integer

Dim InterestRate As Double
Dim MeanInterestRate As Double
Dim Variance As Double
Dim Scenarios As Integer
Dim Scenario As Integer

Dim i As Integer
Dim PV As Double

runs = 10000

AgeStart = 20
AgeLimit = 100

Scenarios = 500
MeanInterestRate = 0.05
Variance = 0.05

startTime = Now

For run = 1 To runs

    For Age = AgeStart To AgeLimit
        For Scenario = 1 To Scenarios
            InterestRate = MeanInterestRate + (0.5 – Rnd()) * Variance
            PV = run * 1000 * (1 – (1 + InterestRate) ^ (Age – 100)) / InterestRate




endTime = Now

MsgBox (startTime & endTime)

End Sub


It took 114 seconds to complete.

I copy and paste the whole program to Visual Studio 2010.  I compiled it and ran it again.


It took 58 seconds so it is nearly 2 times faster.

I modified couple lines in Visual Studio and ran it again.


Now it only took 23 seconds; nearly 5 times faster than VBA.  What did I change in the program?  Let’s look at it.

Imports System.Threading.Tasks

Module Module1

    Sub Main()
        Dim startTime As Date

        Dim endTime As Date

        Dim runs As Long

        Dim Age As Integer
        Dim AgeStart As Integer
        Dim AgeLimit As Integer

        Dim InterestRate As Double
        Dim MeanInterestRate As Double
        Dim Variance As Double
        Dim Scenarios As Integer
        Dim Scenario As Integer

        Dim i As Integer
        Dim PV As Double

        runs = 10000

        AgeStart = 20
        AgeLimit = 100

        Scenarios = 500
        MeanInterestRate = 0.05
        Variance = 0.05

        startTime = Now

        Parallel.For(0, runs, Sub(run)

                                  For Age = AgeStart To AgeLimit

                                      For Scenario = 1 To Scenarios

                                          InterestRate = MeanInterestRate + (0.5 – Rnd()) * Variance
                                          PV = run * 1000 * (1 – (1 + InterestRate) ^ (Age – 100)) / InterestRate



      End Sub)

        endTime = Now

        MsgBox(startTime & endTime)

    End Sub

End Module

Do you know what the changes are?

The CPU inside my computer is an Intel Q9400 which is a quad core processor.  However, the first 2 runs (VBA and VS 2010) only utilized 1 core.  Visual Studio 2010 supports parallel programming which would automatically make use of all cores on my system.  The code to utilize parallel programming is quite simple;  I just replaced the For statement with Parallel.For, then Visual Studio would execute the program on all cores rather than single core.

Without spending any effort on optimization, the performance gain to migrate from VBA to VS 2010 is already enormous, i.e. nearly 500%.

  Execution Time (Seconds)
Excel VBA 114
Visual Studio 2010 (w/o Parallel Programming) 58
Visual Studio 2010 (with Parallel Programming) 23

Someone may argue my program does not reflect a real financial application because it does not include any database input / output (I/O).  They are right!  My program does not have any I/O and I/O may not have such significant performance gain.  However, today’s financial model may involve hundred to thousand scenarios and each scenario can contain a few million calculations, therefore, the effort to I/O a small piece of data would be very minimal.

The purpose of this demonstration is to illustrate a performance gain can be easily achieved by migrating to Visual Studio 2010.  My next blog would be addressing the different programming methodology between VBA and VS, i.e. Object Based and Object Oriented.

Andrew Chan is the owner and founder of ALG Inc.

We help you to make better and faster decisions!