VBA code

@Martin: This is not convenience..

Each call to Worksheets("PartsData") needs to be resolved. Thus you have three calls in the first case, but only one in the last..

Option Explicit

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

Public Sub Test1()

  Dim cel As Excel.Range

  For Each cel In Worksheets("PartsData").Range(Worksheets("PartsData").Cells(1, 1), Worksheets("PartsData").Cells(10, 10))
  Next cel

End Sub

Public Sub Test2()

  Dim cel As Excel.Range
  Dim ws As Excel.Worksheet

  Set ws = Worksheets("PartsData")
  For Each cel In ws.Range(ws.Cells(1, 1), ws.Cells(10, 10))
  Next cel
  
  Set ws = Nothing

End Sub

Public Sub TestComplex()

  Dim Ticks As Long
  Dim Count As Long
  
  Ticks = GetTickCount
  For Count = 0 To 16384
    Test1
  Next Count
  Debug.Print "Test1:" & (GetTickCount - Ticks) / 1000

  Ticks = GetTickCount
  For Count = 0 To 16384
    Test2
  Next Count
  Debug.Print "Test2:" & (GetTickCount - Ticks) / 1000

End Sub

Public Sub TestSimple()

  Dim Ticks As Long
  Dim Count As Long
  
  Dim cel As Excel.Range
  Dim ws As Excel.Worksheet
  Set ws = Worksheets("PartsData")
  
  Ticks = GetTickCount
  For Count = 0 To 16384
    For Each cel In Worksheets("PartsData").Range(Worksheets("PartsData").Cells(1, 1), Worksheets("PartsData").Cells(10, 10))
    Next cel
  Next Count
  Debug.Print "Test1:" & (GetTickCount - Ticks) / 1000

  Ticks = GetTickCount
  For Count = 0 To 16384
    For Each cel In ws.Range(ws.Cells(1, 1), ws.Cells(10, 10))
    Next cel
  Next Count
  Debug.Print "Test2:" & (GetTickCount - Ticks) / 1000

  Set ws = Nothing
  
End Sub

Select allOpen in new window

Yes, that's true.

I'm happy to split the points so I'm not objecting, but I don't think that ste5an should have gotten the most point just because he pointed out a mis-statement in my post.

No comment has been added to this question in more than 14 days, so it is now classified as abandoned.

If you feel this question should be closed differently, post an objection and a moderator will read all objections and then close it as they feel fit. If no one objects, this question will be closed automatically the way described above