How to optimize (or avoid as far as possible) loops in Excel VBA

Question

Various problems in Excel VBA involving selections (and how to avoid them) and "heavy" loops have been discussed on this forum earlier on. I'd like to expand on that a little. Let's say I want to change a certain string to another string and do something else with numeric data. Consider the following code:

Option Explicit
Sub LoopExample()
    Dim rownumber, colnumber As Integer

    ' Step 1: start a loop from a desired position
    For rownumber = 30 To 3000
            For colnumber = 30 To 3000

                ' Step 2: check that the cell is not empty
                If IsEmpty(Cells(rownumber, colnumber).Value) = False Then

                    ' Step 3: change all "Cat" texts to "Dog"
                    If Cells(rownumber, colnumber).Value = "Cat" Then
                        Cells(rownumber, colnumber).Value = "Dog"

                    ' Step 4: if the cell value is numeric...
                    ElseIf IsNumeric(Cells(rownumber, colnumber).Value) Then

                        ' ...check another thing and execute something accordingly
                        If (Cells(rownumber, colnumber).Value) / 2 < 0.5 Then
                            Cells(rownumber, colnumber) = Round(Cells(rownumber, colnumber).Value, 4)
                        Else
                            Cells(rownumber, colnumber) = Round(Cells(rownumber, colnumber).Value, 1)
                        End If

                    End If

                End If
            Next colnumber
    Next rownumber
End Sub

Now, this code works "perfectly" but is hardly an efficient one. Should I optimize it by: 1) first finding all cells that contain data in the first place, 2) loop only over those cells again, 3) divide the data now even further to numeric and non-numeric data, and 4) execute what I want on these numeric and non-numeric ranges? Or should I possibly combine some of these steps in one loop?

You could first create a Range variable, and Set it = the total search area. That is quicker than accessing the cells each time. You could also use things like Excel Filtering to find all instances of "Cat", and change those cells to "Dog". There are a lot of ways to be efficient; this is a pretty broad question - the short answer is - access Excel as little as possible, and use built-in filtering to limit your data [including things like the Find feature]. In the end, though, sometimes you need to do a loop of all rows by all columns. — Grade 'Eh' Bacon, Feb 12 '16 at 21:47
Why wouldn't you first use `For each c in Worksheets("Sheetname").Range("Desiredrange").Cells` and then place your statements? Also, it would be more efficient to just do a find and replace all (through vba) in the range to replace Cat with Dog. This will just leave you with using a for loop to check what the content type is and then execute your remaining statement. — Dan, Feb 12 '16 at 21:49
First, you actually only have 1 loop. There are a few nested `IF` statements that can be cleaned up with some of things others have said, but most importantly, only loop on or work with the cells you need to actually work with. — Scott Holtzman, Feb 12 '16 at 21:49
If looping through individual cells is somehow unavoidable (it isn't in your sample logic) then stuff all of hte cell values into a two-dimensional variant array and loop through that. — , Feb 12 '16 at 21:57
A general comment - all your ranges (`Cells()`) aren't qualified as to the worksheet you want to access those `Cells()`. It *can* cause issues. It will also "tighten up" the code a little bit. Either use `With Sheets("MySheet")` or add `Sheets("MySheet")` before all `Cells()` uses (of course edit the name as appropriate.) — BruceWayne, Feb 12 '16 at 21:57
Wouldn't this discussion fit best over at [codereview.stackexchange.com](http://codereview.stackexchange.com)? — Porcupine911, Feb 12 '16 at 22:05
`rownumber` is declared as `variant` in your code. Use VBA Array . Don't test if is empty (`isempty("")` answers false) . Screenupdating, Enableevents , Calculation set to False is basic. — Patrick Lepelletier, Feb 13 '16 at 20:04
Thank you for all the helpful comments here. I will basically put all of these into practice: a) I will not use a loop but rather a `For each c in` [...range] type of solution, and b) I will control the settings of ScreenUpdating, EnableEvents and Calculation. I will definitely try the two-dimensional array approach at some point, too. — jaggedjava, Feb 14 '16 at 11:45

score 2 · Answer 1 · answered Feb 12 '16 at 23:47

2

It would be several orders of magnitude faster to get the complete range into a variant (result is a variant containing a 2d array), loop the variant array making the changes and then return the variant array to the worksheet.

answered Feb 12 '16 at 23:47

Charles Williams

23,121
5
38
38

Yes, this approach has been discussed [earlier on this forum](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros), and I'm keen to try it out. I assume this method works faster than an ordinary loop, even if I controlled the settings of ScreenUpdating, EnableEvents and Calculation. – jaggedjava Feb 14 '16 at 11:58

score 2 · Answer 2 · answered Feb 13 '16 at 04:04

I have made something that seems with this.

1°: Long usage is better that integer to highly counters.

2°: Use Range Property to check over.

3°: Use Application property to excel make easily the calculations.

You don't need to use "For...0 to 3000" to do this. you can try this one:

Sub LoopExample()

Application.ScreenUpdating = False

Application.Calculation = xlCalculationManual

Application.EnableEvents = False

Dim i As Range

For Each i In Range("A:Z")

If IsEmpty(i.Value) = False Then

If i.Value = "Cat" Then

i.Value = "Dog"

ElseIf IsNumeric(i.Value) Then

If (i.Value) / 2 < 0.5 Then

i.Value = 0.5

Else

End If

End If

End If

Next


Application.EnableEvents = True

Application.Calculation = xlCalculationAutomatic

Application.ScreenUpdating = True

End Sub

But, if insist to use counters, try something like this:

Sub numberlist()

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False

Dim a As Long
Dim b As Long

Dim i As Range

Do While Not Cells(3000, 3000).Value > 0

b = b + 1

Cells(1, b).Value = 1 + b * 100 - 100

Cells(2, b).Value = 2 + b * 100 - 100

Set i = Range(Cells(1, b), Cells(3000, b))

Set SourceRange = Range(Cells(1, b), Cells(2, b))

Set fillRange = i

SourceRange.AutoFill Destination:=fillRange

Loop


Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

Make a test drive. its just going to spent a lot of time to calculate :v

Thank you, your upper example code is very helpful. Indeed, many of the solutions that you presented are more or less what the other forum users, too, suggested me (in the beginning of this page). — jaggedjava, Feb 14 '16 at 12:07

score 1 · Accepted Answer · answered Feb 13 '16 at 04:09

1

I think you could optimize this a lot by having Excel only look at cells with values. To do this, you can use a combination of the SpecialCells for both constants and formulas -- I think the combination of the two would capture all "populated" or non-blank cells:

Sub LoopExample()

  Dim ws As Worksheet
  Set ws = ActiveSheet
  Dim r, s As Range

  Set r = Application.Union(ws.UsedRange.SpecialCells(xlCellTypeConstants), _
      ws.UsedRange.SpecialCells(xlCellTypeFormulas))

  For Each s In r

    If s.Row >= 30 And s.Row <= 3000 And s.Column >= 30 And s.Column <= 3000 Then
      If s.Value2 = "Cat" Then
        s.Value2 = "Dog"
      End If
    End If
  Next s

End Sub

I didn't list all of your logic for evaluating the cells, but you get the idea.

answered Feb 13 '16 at 04:09

Hambone

15,600
8
46
69

Many thanks for your suggestion that is concise and elegant. I tried this approach with a dataset consisting of about 6 x 11000 cells. (To make the code run, I changed the code so that I used `SpecialCells` for constants only, as I didn't have any formulas in my dataset.) It took 1-2 seconds to execute. The usual modifications of ScreenUpdating, EnableEvents and Calculation were not necessary in this case, but might come handy when dealing with very large data. – jaggedjava Feb 14 '16 at 14:47
To satisfy my own morbid curiosity, would you mind telling me how long the original code ran? – Hambone Feb 15 '16 at 03:49
By all means, that's the least I can do. So, for this roughly 6 x 11000 cell data, I ran my original code with `For rownumber = 30 To 12000` and `For colnumber = 10 To 3000` type of lines. As for your solution, I only limited from which cell to start the process, so I ran it with: `If s.Row >= 30 And s.Column >= 10 Then`. The result: 66 seconds vs. 2 seconds, respectively. So, there are - at least - two major advantages in your solution: 1) the speed, 2) you don't have to know how wide/long the data is, as `UsedRange.SpecialCells` covers this. – jaggedjava Feb 15 '16 at 21:37
Fantastic... you made my day. Thanks for the follow-up. – Hambone Feb 15 '16 at 22:13

How to optimize (or avoid as far as possible) loops in Excel VBA

3 Answers3