none
Excel Range.Formula is taking time in c# RRS feed

  • Question


  • I am accessing a particular range formula but it is taking more time then that of VBA.

    My code in C#

              
     Excel.Range rng = Application.get_Range("D23:AB10023");
                System.Diagnostics.Stopwatch stwatch = new System.Diagnostics.Stopwatch();
                stwatch.Start();
                System.Diagnostics.Debug.Print("Before getting formula---->" + stwatch.Elapsed.ToString());
                object[,] array = rng.Formula;
                System.Diagnostics.Debug.Print("after getting formula----->" + stwatch.Elapsed.ToString());



    and the same code in VBA
     
     Dim dataarray As Variant
       Debug.Print Strings.Format(Now, "dd-MMM-yyyy HH:nn:ss") & "." & Strings.Right(Strings.Format(Timer, "#0.00"), 2)
       dataarray = Sheet1.Range("$D$23:$AB$10023").Formula
       Debug.Print Strings.Format(Now, "dd-MMM-yyyy HH:nn:ss") & "." & Strings.Right(Strings.Format(Timer, "#0.00"), 2)
    


    Time in VBA is

    05-Oct-2012   20:51:26.34
    05-Oct-2012   20:51:26.52

    Time in C#

    Before getting formula---->00:00:00.0002964

    after getting formula----->00:00:8.4754285

    In C# it is taking around 8 second and in VBA it is taking less than a minute.

    i thing it is may because of VSTO

    can u please let me know what is the problem.
    Friday, October 5, 2012 3:42 PM

All replies


  • I am accessing a particular range formula but it is taking more time then that of VBA.

    My code in C#

              
     Excel.Range rng = Application.get_Range("D23:AB10023");
                System.Diagnostics.Stopwatch stwatch = new System.Diagnostics.Stopwatch();
                stwatch.Start();
                System.Diagnostics.Debug.Print("Before getting formula---->" + stwatch.Elapsed.ToString());
                object[,] array = rng.Formula;
                System.Diagnostics.Debug.Print("after getting formula----->" + stwatch.Elapsed.ToString());



    and the same code in VBA
     
     Dim dataarray As Variant
       Debug.Print Strings.Format(Now, "dd-MMM-yyyy HH:nn:ss") & "." & Strings.Right(Strings.Format(Timer, "#0.00"), 2)
       dataarray = Sheet1.Range("$D$23:$AB$10023").Formula
       Debug.Print Strings.Format(Now, "dd-MMM-yyyy HH:nn:ss") & "." & Strings.Right(Strings.Format(Timer, "#0.00"), 2)


    Time in VBA is

    05-Oct-2012   20:51:26.34
    05-Oct-2012   20:51:26.52

    Time in C#

    Before getting formula---->00:00:00.0002964

    after getting formula----->00:00:8.4754285

    In C# it is taking around 8 second and in VBA it is taking less than a second.


    can u please let me know what is the problem.

    Monday, October 8, 2012 3:54 AM
  • Hi Monil Gangar,

    What do you mean by

    can u please let me know what is the problem.

    ?

    If you mean the time difference then I'll suggest you post it on a forum which is related to Operating System or .netFramework or something underlying. Get the Range.Formula can be achieved by using C++ or Python other languages, and I believe all these solutions have different time cost.

    In your C# project you're using stop watch to record the time while in your VBA you're using the NOW() formula, these 2 things are just logically the same but not totally equivalent. They may have different time consuming.

    HTH

    Regards,
    Fermin

    Tuesday, October 9, 2012 5:37 AM
  • Hiii Fermin,

    I have one Excel Application in c#.

    when i am trying to access the range's formula through c# it is taking around 8 seconds on my machine.

    for my reference i have put stopwatch to know that actually how much time it is taking.

    actual code in c# is

    Excel.Range rng = Application.get_Range("D23:AB10023");
    object[,] array = rng.Formula;

    but the same range's formula through VBA is taking less than a second.

    and same in VBA also NOW() call is for my reference only.

    actual code in VBA is

    Dim dataarray As Variant
    dataarray
    = Sheet1.Range("$D$23:$AB$10023").Formula

    for smaller range both VBA and C# time is almost same.

    but for larger range like in my case it is taking that much of time.

    i am using .Net 4.0 and Excel 2007 and visual studio 2010.

    Regards,

    Monil Gangar.


    Tuesday, October 9, 2012 8:01 AM
  • Hi Monil,

    Thank you for your last reply, and sorry to reply to you late.

    Still I think there's no need to compare the differnece in time consuming, since the we cannot see how get_Range() or Range() is defined.

    I feel that VBA is a script language and all script language have the advantage of high efficiency. .Net code will be compiled into  Common Intermediate Language before you run it.

    HTH

    Fermin

    Friday, October 19, 2012 6:04 PM