PDA

View Full Version : For each vs For I = ...



MWE
11-21-2005, 06:48 PM
Assume a collection of, say, OLEObjects in a given sheet. I have noticed that

Sub ObjStuff1(xlSheetName, ...)
Dim objOLE As OLEObject
Dim xlSheet As Worksheet

If xlSheetName = "" Then xlSheetName = ActiveSheet.Name
Set xlSheet = ActiveWorkbook.Worksheets(xlSheetName)

For each objOLE in xlSheet.OLEObjects

Next objOLE
does not always work, e.g., the loop is not always executed when the number of items in OLEObjects is > 0; but

Sub ObjStuff1(xlSheetName, ...)
Dim I As Long
Dim objOLE As OLEObject
Dim xlSheet As Worksheet

If xlSheetName = "" Then xlSheetName = ActiveSheet.Name
Set xlSheet = ActiveWorkbook.Worksheets(xlSheetName)

For I = 1 To xlSheet.OLEObjects.Count
set objOLE = xlSheet.OLEObjects(I)

Next I
always works

Thoughts?

Bob Phillips
11-22-2005, 02:32 AM
Assume a collection of, say, OLEObjects in a given sheet. I have noticed that

Sub ObjStuff1(xlSheetName, ...)
Dim objOLE As OLEObject
Dim xlSheet As Worksheet

If xlSheetName = "" Then xlSheetName = ActiveSheet.Name
Set xlSheet = ActiveWorkbook.Worksheets(xlSheetName)

For each objOLE in xlSheet.OLEObjects

Next objOLE
does not always work, e.g., the loop is not always executed when the number of items in OLEObjects is > 0; but

Thoughts?

I would be interested in seeing an example where that doesn't work.

Killian
11-22-2005, 03:21 AM
I would be interested in seeing an example where that doesn't work.
Indeed...
On the subject of "For each vs For I = ...", iterating though a collection is quicker that using an index

MWE
11-22-2005, 10:13 AM
I would be interested in seeing an example where that doesn't work.
OK. The code below was placed in the sheet code module for a sheet with one OLE object. The MsgBox indicating "which proc" executes fine and correctly indicates the # of OLE objects. The MsgBox inside the For each loop in ObjStuff1 never executes. The MsgBox inside the For I loop in ObjStuff2 executes correctly.

Sub ObjStuff()
Call ObjStuff1
Call ObjStuff2
End Sub

Sub ObjStuff1(Optional xlSheetName As String)

Dim objOLE As OLEObject
Dim xlSheet As Worksheet

If xlSheetName = "" Then xlSheetName = ActiveSheet.Name
Set xlSheet = ActiveWorkbook.Worksheets(xlSheetName)
MsgBox "ObjStuff1 For each ..." & vbCrLf & _
"sheet = " & xlSheet.Name & vbCrLf & _
"# objs = " & xlSheet.OLEObjects.Count

For Each objOLE In xlSheet.OLEObjects
MsgBox objOLE.Name
Next objOLE

End Sub

Sub ObjStuff2(Optional xlSheetName As String)

Dim I As Long
Dim objOLE As OLEObject
Dim xlSheet As Worksheet

If xlSheetName = "" Then xlSheetName = ActiveSheet.Name
Set xlSheet = ActiveWorkbook.Worksheets(xlSheetName)
MsgBox "ObjStuff2 For I = ..." & vbCrLf & _
"sheet = " & xlSheet.Name & vbCrLf & _
"# objs = " & xlSheet.OLEObjects.Count

For I = 1 To xlSheet.OLEObjects.Count
Set objOLE = xlSheet.OLEObjects(I)
MsgBox objOLE.Name
Next I

End Sub

Ken Puls
11-22-2005, 10:22 AM
I just dropped a spin button on my sheet and it returns me the same info from both procedures. :)

MWE
11-22-2005, 10:38 AM
I just dropped a spin button on my sheet and it returns me the same info from both procedures. :)
I tried those routines on several different sheets with varying # of OLE objects and get consistent results, i.e., the For each never works and the For I = works every time. But read further ...

Since we are discussing funny results relative to OLE objects ...

I tried the same routines on another sheet with several OLE objects, some that had not been renamed and some that had. The For each loop "recognized" the OLE objects with original names (like "Object 9"), but ignored objects that had been renamed. The For I = loop recognized both cases. :dunno

If you do a simple copy and paste of most objects (charts, boxes, etc), the new instance has a new name (would be hard for Excel to keep track of things if the name of the new object were the same as the original). But funny things happen if an OLE object has been renamed prior to the copy/paste. The old and new names are the same. :dunno

Ken Puls
11-22-2005, 10:44 AM
Okay...

Here's what I just did:
-added the following to my worksheet from the Control Toolbox: Spinbutton, Combobox, Commandbutton.
-renamed the combobox and commandbutton
-ran the macro and both came up with the exact same results. Showed me all names, in both procs, no issue

-copied the commandbutton and pasted it
-ran the proc, again showed the same in all areas

I get the same results from the proc in all cases so far... I don't understand why you're getting different. :dunno

Killian
11-22-2005, 10:49 AM
I tried a few worksheet controls and both work... maybe this is related to the specific type of object you're testing ???

MWE
11-22-2005, 10:53 AM
Okay...

Here's what I just did:
-added the following to my worksheet from the Control Toolbox: Spinbutton, Combobox, Commandbutton.
-renamed the combobox and commandbutton
-ran the macro and both came up with the exact same results. Showed me all names, in both procs, no issue

-copied the commandbutton and pasted it
-ran the proc, again showed the same in all areas

I get the same results from the proc in all cases so far... I don't understand why you're getting different. :dunno

Well, you can not say that I have uninteresting problems :devil:

I wonder if this is a problem with the version of Excel/VBA I am running (Excel2K under WinXP).

Zack Barresse
11-22-2005, 11:00 AM
MWE, can you post a sample sheet with the objects which give you skewed results, so others can test?

mvidas
11-22-2005, 12:45 PM
Unrelated to the issue of 'for each' not always working (sounds like some sort of system issue, unless you can show us an example), but related to "For each vs For I = ..."

ObjStuff1 (For Each): 5.76603125000111 seconds
ObjStuff1 (For Each): 5.71878124999785 seconds
ObjStuff1 (For Each): 5.73415625000052 seconds
ObjStuff1 (For Each): 5.71890624999651 seconds
ObjStuff1 (For Each): 5.74990624999919 seconds
ObjStuff1 (For Each): 5.74990624999919 seconds
ObjStuff1 (For Each): 5.82803124999919 seconds
ObjStuff1 (For Each): 5.89065624999785 seconds
ObjStuff1 (For Each): 5.78190624999843 seconds
ObjStuff1 (For Each): 5.79778125000303 seconds
ObjStuff2 (For i): 6.76640624999709 seconds
ObjStuff2 (For i): 6.70315624999785 seconds
ObjStuff2 (For i): 6.71890624999651 seconds
ObjStuff2 (For i): 6.70403125000303 seconds
ObjStuff2 (For i): 6.70415625000169 seconds
ObjStuff2 (For i): 6.73553125000035 seconds
ObjStuff2 (For i): 6.70365624999977 seconds
ObjStuff2 (For i): 6.68878124999901 seconds
ObjStuff2 (For i): 6.71915625000111 seconds
ObjStuff2 (For i): 6.81365625000035 secondsSub ObjStuff()
Dim TheTime As Double, k As Long
With Workbooks.Add(1).ActiveSheet.OLEObjects
.Add "Forms.CheckBox.1"
.Add "Forms.TextBox.1"
.Add "Forms.CommandButton.1"
.Add "Forms.OptionButton.1"
.Add "Forms.ListBox.1"
.Add "Forms.ComboBox.1"
.Add "Forms.ToggleButton.1"
.Add "Forms.SpinButton.1"
.Add "Forms.ScrollBar.1"
.Add "Forms.Label.1"
.Add "Forms.Image.1"
End With
For k = 1 To 10
TheTime = Timer
ObjStuff1
Debug.Print "ObjStuff1 (For Each): " & Timer - TheTime & " seconds"
Next

For k = 1 To 10
TheTime = Timer
ObjStuff2
Debug.Print "ObjStuff2 (For i): " & Timer - TheTime & " seconds"
Next
End Sub
Sub ObjStuff1(Optional xlSheetName As String)
Dim tempStr As String, j As Long
Dim objOLE As OLEObject

If Len(xlSheetName) = 0 Then xlSheetName = ActiveSheet.Name
With ActiveWorkbook.Worksheets(xlSheetName)
For j = 1 To 10000
For Each objOLE In .OLEObjects
tempStr = objOLE.Name
Next
Next
End With

Set objOLE = Nothing
End Sub
Sub ObjStuff2(Optional xlSheetName As String)
Dim tempStr As String, j As Long
Dim i As Long

If Len(xlSheetName) = 0 Then xlSheetName = ActiveSheet.Name
With ActiveWorkbook.Worksheets(xlSheetName)
For j = 1 To 10000
For i = 1 To .OLEObjects.Count
tempStr = .OLEObjects(i).Name
Next
Next
End With
End SubMatt