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 reduce 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
Next
Next
Next
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
Next
Next
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.
Better and Faster Decisions 