2 Replies - 5645 Views - Last Post: 24 May 2012 - 11:17 AM Rate Topic: -----

#1 nighttrain   User is offline

  • D.I.C Regular

Reputation: 8
  • View blog
  • Posts: 485
  • Joined: 22-September 10

Slow looping throught the cells of Excel

Posted 24 May 2012 - 06:35 AM

Hello guys,

Need really your help, i am converting some excel macro to vb.net, and it's almost done, but when i am looping throught +- 3000 rows and checking with 2 sheets it takes about 3 hours !

Here's the loop i am using and it is doing this time, mayby some of you could help and rebuilt it or something which could help me?:

 Dim Site1 As String
            Dim Site2 As String
            Dim Group1 As String
            Dim Group2 As String
            Dim Count As String
            Dim z As Integer = 5
            Dim x As Integer = 2


                Site1 = .Cells(x, 1).Value
                Group1 = .Cells(x, 2).Value
                Count = .Cells(x, 3).Value

                If .Cells(x, 3).Value <> Nothing Then

                    Dim y As Integer = 2
                        y = y + 1
                        Group2 = .Cells(y, 1).Value
                        Site2 = .Cells(y, 2).Value
                    Loop Until (Site2 = Site1 And Group2 = Group1) Or .Cells(y, 2).Value = Nothing

                    .Cells(y, 1).Value = Group1
                    .Cells(y, 2).Value = Site1
                    .Cells(y, z).Value = .Cells(y, z).Value + Count

                End If


                x = x + 1
            Loop Until .Cells(x, 1).Value = Nothing

Is This A Good Question/Topic? 0
  • +

Replies To: Slow looping throught the cells of Excel

#2 tlhIn`toq   User is offline

  • Xamarin Cert. Dev.
  • member icon

Reputation: 6532
  • View blog
  • Posts: 14,447
  • Joined: 02-June 10

Re: Slow looping throught the cells of Excel

Posted 24 May 2012 - 07:11 AM

I don't do excel programs... Just saying that right off.

But it looks like every iteration of the this loop is going to cause the spreadsheet to activate the "all" page and then then "warehouse" page. Two page activations times 3000 cells: That's a lot of GUI repainting. The GUI is amongst the slowest performing thing you can work with in any application.

If you can work the cells on those pages without actually activating those pages you might see a big improvement in speed. Sadly I'm not familiar enough with coding the Office namespace to tell you how. But I know the formulae used in Excel can do it: You can reference a cell on another sheet. So I would expect you can do it programmatically as well.
Was This Post Helpful? 0
  • +
  • -

#3 BobRodes   User is offline

  • Lovable Curmudgeon
  • member icon

Reputation: 600
  • View blog
  • Posts: 3,071
  • Joined: 19-May 09

Re: Slow looping throught the cells of Excel

Posted 24 May 2012 - 11:17 AM

This is not a problem with .Net per se; we had the same issues in VB6. The macro runs in the same process that the Excel application does, but when you automate Excel with code (.Net code or COM code), the Excel and .Net aspects of your code run in two separate processes. Each time you reference a cell, you have to package a request in the .Net process and marshal it to the Excel process. You then have to do the same with the response. The marshaling is expensive; if you want to dig into why, start here, paying particular attention to "using chatty interfaces that require extensive round trips" and "design chunky interfaces to avoid round trips."

So, to optimize your code, you need to minimize the calls between your code and the Excel sheet. Two possible improvements come to mind: consider calling your macro directly from your code and letting Excel VBA do all the work, and copy your entire cell range to an array and do your looping work on the array. Avoid using Automation to loop through cell references directly.

See here for information on how to copy cell ranges to and from arrays.

You can go into your sheets and name the ranges that you use if you want more readability.

This post has been edited by BobRodes: 24 May 2012 - 11:34 AM

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1