Performance -- vs VBA, half the speed of VBA RRS feed

  • General discussion

  • Hello All,

    I could not find any useful info on peformance, so I did the following simple test.   It tests the COM calls, calling from VBA or VSTO into Excel, which has always been the pain point.  Properly JIT compiled VSTO code should be an order of magnitude faster than VBA, but that is very rarely an issue unless you are doing heavy maths.  Optimizing VBA is all about minimizing the COM calls. 

    (My code below would obviously run much faster if I got all the values at once.   But that is not the point.  (And I do not know how to do it in VSTO.))

    VSTO in Visual Studio: 0.40 seconds
    VSTO stand alone:      0.29
    VBA:                            0.15

    So VBA will live on.

    Below are the code fragments.  Any feedback or others experience much appreciated.

                var excel = Globals.ThisAddIn.Application;
                var sh = (Excel.Worksheet)excel.ActiveSheet;     
                var start = DateTime.Now;
                double sum=0;
                for (var ct = 0; ct < 10; ct++)
                    var rng = sh.get_Range("A1", "J20");
                    foreach (var cl in rng)
                        var cell = (Excel.Range)cl;
                        var val = cell.Value;
                        sum += (double)val;
                MessageBox.Show("End " + sum + " time " + (DateTime.Now - start)); // 0.40 seconds.  0.29 stand alone.



    Sub itterate()
      Dim sum#, rng As Range, cell As Range, ct&, tim
      tim = Timer
      For ct = 1 To 10
        Set rng = ActiveSheet.Range("A1", "J20")
        For Each cell In rng
          sum = sum + cell
        Next cell
      Next ct
      MsgBox "Value " & sum & " tim " & (Timer - tim) ' 0.15
    End Sub

    Tuesday, October 4, 2011 2:50 AM

All replies

  • Hi Anthony

    One major factor in the scenario is that code in VSTO also has to work through the .NET/COM barrier (via the PIAs). That will always tend to make .NET code slower than VBA code doing the same automation/interop task.

    Tendentially, .NET code will only be faster for tasks carried out entirely within the .NET Framework.

    You migiht also want to test any difference between using var (which forces C# to work out the datatype) and explicitly assigning the datatype.

    Excel.application excel =...;

    Also interesting could be timing the difference of PInvoke (GetType().InvokeMember()) vs. the other two.

    Cindy Meister, VSTO/Word MVP
    Tuesday, October 4, 2011 6:18 AM
  • Hello Cindy,

    Thanks for that.  I suspected as much, but was interested to have a rough idea how much that extra overhead would be.  5%?  500%?.  Now we have a rough idea -- 100%.

    I do not understand your var comment.  Var in c# is about type inference done at compile time.  It is quite different from the var in JavaScript etc.  I would be very surprised if that affected performance.

    I do not undrstand your PInvoke question.  But if it was possible to call .Net as an ordinary C dll from VBA that could be fast.  And dangerous.



    Tuesday, October 4, 2011 8:20 AM
  • Hi Anthony

    <<I do not undrstand your PInvoke question.  But if it was possible to call .Net as an ordinary C dll from VBA that could be fast.  And dangerous.>>

    The other way around, actually. "Late binding" to Office (which actually functions over IDisp, under the covers) can under some circumstances be faster than "early binding" when you're working over "boudaries" (.NET/COM, for instance). I've never done any exhaustive testing, though.

    Cindy Meister, VSTO/Word MVP
    Tuesday, October 4, 2011 1:01 PM