PDA

View Full Version : [SOLVED] On Error GoTo ???



Mister_joe
01-31-2014, 04:32 AM
Guys,
I placed one Image control and two CommandButton controls on a worksheet. The code shown here ought to be simple enough, right?

Sub Macro1()
Dim obj As OLEObject
Dim img As Image

For Each obj In ActiveSheet.OLEObjects
On Error GoTo Label2
Set img = obj.Object
Debug.Print obj.Name
Label2:
Set img = Nothing
Next
End Sub

The code runs for the Image control and for the first CommandButton control, but fails for the second CommandButton control. Does anyone know what is going on here?
Thanks.

GTO
01-31-2014, 05:24 AM
Greetings Joe,

I'm about to hit the hay, but for any "answerer", it is a lot easier if you would attach the workbook with the code and oleobjects. That way we don't need to guess at re-creating what we imagine you are looking at. Hope that makes sense :-)

Mark

Mister_joe
01-31-2014, 05:54 AM
Here's the macro I used to place the controls on the worksheet.

Sub Macro1()
ActiveSheet.OLEObjects.Add(ClassType:="Forms.Image.1", Link:=False, DisplayAsIcon:=False, Left:=162, Top:=96, Width:=175.5, Height:=87).Select

ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1", Link:=False, DisplayAsIcon:=False, Left:=439.5, Top:=103.5, Width:=153, Height:= 37.5).Select

ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1", Link:=False, DisplayAsIcon:=False, Left:=432, Top:=165.75, Width:=159.75, Height:=33.75).Select
End Sub

anandbohra
01-31-2014, 07:22 AM
Try This


Sub Macro1()
Dim obj As OLEObject
Dim img As Image

For Each obj In ActiveSheet.OLEObjects
On Error GoTo Label2
Set img = obj.Object
Debug.Print obj.Name
Next
Exit Sub
Label2:
Set img = Nothing

End Sub

Mister_joe
01-31-2014, 07:31 AM
Thanks, but this does not allow the entire collection to be iterated. The moment an error occurs, the loop is exited and the execution ends. This is not exactly what I want. I want the entire collection to be iterated, but when an error is encountered, the looping should go to the next step.

Mister_joe
01-31-2014, 07:42 AM
I notice that when the code encounters error and halts, debugging and hovering the mouse on obj.Object shows a ToolTip that has "obj.Object = False". Obviously, False cannot be assigned to a variable of type Image. But why is the second CommandButton returning False?

Mister_joe
01-31-2014, 08:42 AM
People,
Who is really the culprit here, the "On Error GoTo Label2" or the "Set img = obj.Object" ? When the code execution encounters a error, execution ought to jump to the line labeled Label2. The "Set img = obj.Object" detects the type mismatch, but the execution does not jump to the line identified by Label2. Is it that VBA does not regard the error as error?

Bob Phillips
01-31-2014, 09:36 AM
If you get an error condition, you should clear it before carrying on


Sub Macro1()
Dim obj As OLEObject
Dim img As Image

For Each obj In ActiveSheet.OLEObjects
On Error GoTo Label2
Set img = obj.Object
Debug.Print obj.Name
Label2:
Set img = Nothing
Resume Label3
Label3:
Next
End Sub

But that is a very poor coding practice to get into in my view, might work here, but it will bite you in more complex code. Far better to handle the error at point, such as


Sub Macro1()
Dim obj As OLEObject
Dim img As Object

For Each obj In ActiveSheet.OLEObjects
Set img = obj.Object

If TypeName(obj.Object) = "Image" Then

Debug.Print obj.Name
End If
Set img = Nothing
Next
End Sub

Mister_joe
01-31-2014, 02:42 PM
Thank you so very much. Your solution is exactly what I am looking for.