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

       
        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.

• 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!