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

image

It took 114 seconds to complete.

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

image

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

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

image

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.

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!

15 Responses to VBA vs. VS – Part 1: Performance Review

  1. JP says:

    “Does anyone still have any Excel 4 worksheet?”

    There are still some things you can only do using Excel 4 macros (I don’t recall what they are, but I can find out if you insist). Did Microsoft update VBA to allow you to do those things, so XLM macros can finally go away? I doubt it.

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

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

  4. Pingback: VBA vs. VS – Part 2: Programming Paradigm Review « Technologies and your business

  5. Pingback: VBA vs. VS – Part 3: What is new in VB.Net? « Technologies and your business

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

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

  8. akpars says:

    Just FYI,

    Parallel class became available only with the release of .Net framework 4 and VS 2010.

    • Andrew Chan says:

      Hi akpars,

      Thanks for the reminder. I already mentioned in my blog that “Visual Studio 2010 supports parallel programming which would automatically make use of all cores on my system” but I shoudl add “.Net Framework 4” as well which is bundled with VS 2010.

  9. It seems that the code for the FOR loop in parallel was pretty easy, what you are talking about is really a dramatic shift from sequential programming to parallel programming. For the more general VBA programmers who are mostly business experts who do a bit of programming, moving to a parallel paradigm will be quite a learning curve, although I wonder if other applications could really benefit simply by changing all FOR Loops to Parallel.For.

  10. Andrew Chan says:

    Hi Rick,

    You are right, there are situations that parallel processing requires much more efforts to implement. However, there are definitely situations that simple implementation can achieve significant improvement.

    Using life insurance as an example, actuaries have to run valuation against all their policies to determine reserves, capital requirement…. Each policy may have to run against hudred of secnarios and each scenario can contain million of calculation. Their actuarial systems do not care the order of processiing as long as all are processed. So Parrall.For can definitely improve the processing time if they have a quad core or even hexa core CPU.

    We can get a hexa core CPU for less than $300 from newegg.com.

  11. Pingback: Is Excel our only option? « Technologies and your business

  12. Pingback: 2010 in review « Technologies and your business

  13. tham tu says:

    Thanks.
    But I faced with VB6 + Dual Core. It seems that VB Virtual machine is not compatibility.

    Sad,
    Tham tu thanh long

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 )

Google photo

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

Twitter picture

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

Facebook photo

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

Connecting to %s

%d bloggers like this: