Slow Speed When Working with Excel Objects

Add-in Express™ Support Service
That's what is more important than anything else

Slow Speed When Working with Excel Objects
 
mwynn


Guest


I am experiencing very slow interaction when working with Range and Worksheet objects. As an example, I have tested the loop below, and it takes about 0.1 seconds for each iteration. When I try a similar loop in Excel VBA, the elapsed time is almost imperceptible (0.0001 or something less). Am I doing something wrong in my code or application that is causing the interaction with Excel objects to be so slow? Any help would be greatly appreciated.


Private Sub RangeTesting()

For i As Integer = 1 To 10
Try
Dim rngtest As Excel.Range = ExcelApp.Range("A1:D95").SpecialCells(Excel.XlCellType.xlCellTypeConstants, 7)
Catch ex As Exception

End Try
Next i

End Sub
Posted 23 Oct, 2016 11:09:20 Top
Andrei Smolin


Add-in Express team


Posts: 18829
 Joined: 2006-05-11
Hello Matt,

This may occur because .NET needs to recompile the code when you run it for the first time. If you run this method for the second time, it will execute significantly faster. Also, the code will run even faster if you move the ExcelApp.Range("A1:D95") part out of the loop.


Andrei Smolin
Add-in Express Team Leader
Posted 24 Oct, 2016 08:25:13 Top
mwynn


Guest


Hi Andrei,

Thank you for the quick response. I have tried executing the procedure multiple times, and unfortunately the speed does not change between the first run and later runs. The procedure above was just a test procedure to help explain why my application was working so slowly. I boiled it down to the statement above that references "SpecialCells". In my actual application, I have to loop through several hundred range areas, and the "SpecialCells" method is causing the procedure to take far too long.

I'm puzzled why this procedure works infinitely faster in the Excel VBA environment. Any other suggestions? I need to find a solution to this speed issue, as it's instrumental to my Office add-in application. Would be willing to engage your services team to help me with this, if possible.

Thanks again,
Matt
Posted 24 Oct, 2016 08:38:48 Top
Andrei Smolin


Add-in Express team


Posts: 18829
 Joined: 2006-05-11
Hello Matt,

Please send me a project and workbook for testing. You can find the support email address in {Add-in Express installation folder}\readme.txt. Please make sure your email contains a link to this topic.


Andrei Smolin
Add-in Express Team Leader
Posted 25 Oct, 2016 07:09:44 Top
Andrei Smolin


Add-in Express team


Posts: 18829
 Joined: 2006-05-11
Hello Matt,

Thank you for sending me the project. First off, your time measurement in VBA isn't correct; Now() doesn't produce fractions of a second. Also, I've increased the loop count to 500. 

Private Declare Function GetTickCount Lib "kernel32" () As Long

Sub TestSpeed()
    Dim TimeTicks(0 To 1) As Long
    Dim startRng As Excel.Range
    Set startRng = Range("A1:D95")
     
    Dim i As Integer
    On Error Resume Next
    TimeTicks(0) = GetTickCount()
    For i = 1 To 500
        TestRng = startRng.SpecialCells(xlCellTypeConstants, 7)
    Next i
    TimeTicks(1) = GetTickCount()
    Debug.Print TimeTicks(1) - TimeTicks(0)
End Sub


On my machine the code above produces values in the 125-175 range, while the result of the add-in code (implementing the same approach) fluctuates between 435 and 500. I get the same numbers if I test this code in VSTO. I assume the managed code works slower because of the need to marshal the COM objects the code creates from unmanaged memory to managed one.


Andrei Smolin
Add-in Express Team Leader
Posted 26 Oct, 2016 10:22:23 Top