This may not be the best examples (because there are more efficient ways of doing this), but it should suffice.
Let's say I'm trying to find which two numbers in a range add up to a certain sum. My range is A1:J25000, which is 250,000 cells. So, I'll have two loops to do this. When I find the first combination of two cells that add up to myValue, I need to display their addresses in a message box. This is the code:
Code:
Public Sub WhichTwoAddUp()
Const myValue As Double = 1234.56
Const myRange As String = "A1:J25000"
Dim rng1 As Excel.Range
Dim rng2 As Excel.Range
Dim rngSearch As Excel.Range
Set rngSearch = ActiveWorkbook.Worksheets("Sheet1").Range(myRange)
For Each rng1 In rngSearch.Cells
For Each rng2 In rngSearch.Cells
If rng1.Value + rng2.Value = myValue Then
Call MsgBox(rng1.Address & " + " & rng2.Address & " = " & myValue)
Exit Sub
End If
Next rng2
Next rng1
End Sub
If you run this macro, it will take FOREVER to execute. Moreover, if you run it from VBE, you won't be able to switch over to Excel. If you have Task Manager running, you'll see that it's using up all of your CPU (or 1/2 of the CPU if you have dual-core, etc).
Here's another version of this code, with addition of DoEvents:
Code:
Public Sub WhichTwoAddUp()
Const myValue As Double = 1234.56
Const myRange As String = "A1:J25000"
Dim rng1 As Excel.Range
Dim rng2 As Excel.Range
Dim rngSearch As Excel.Range
Set rngSearch = ActiveWorkbook.Worksheets("Sheet1").Range(myRange)
For Each rng1 In rngSearch.Cells
For Each rng2 In rngSearch.Cells
If rng1.Value + rng2.Value = myValue Then
Call MsgBox(rng1.Address & " + " & rng2.Address & " = " & myValue)
Exit Sub
End If
Next rng2
DoEvents
Next rng1
End Sub
Now, after each inner loop, VBA will process system events - such as switching over to your Excel window, etc. It will also process anything that's stacked up waiting for free CPU time.
Does that help?