Hi Tom,

I got your help in locating shapes in VBA. I need to return the located
shape so I modified it into a function. But whenver I step into this
line "findOval = shp", it just jumps out of the function without
returning any shape. Do you have any suggestions? Thanks.

This is your original code:
Sub DD()
Dim shp As Shape
Dim s As String
Dim TargetText As String
TargetText = "dog"
For Each shp In ActiveSheet.Shapes
On Error Resume Next
s = ""
s = shp.TextFrame.Characters.Text
On Error GoTo 0
If InStr(1, s, TargetText, vbTextCompare) Then
shp.Select
Exit For
End If
Next
End Sub


And this is what I modified:
Function findOval(TargetText As String)
Dim shp As Shape
Dim s As String

For Each shp In ActiveSheet.Shapes
On Error Resume Next
s = ""
s = shp.TextFrame.Characters.Text
On Error GoTo 0
If InStr(1, s, TargetText, vbTextCompare) Then
shp.Select
findOval = shp
Exit For
End If
Next
End Function

I think you need to change

findOval = shp
' to
Set findOval = shp


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"lvcha.gouqizi" wrote in message
ups.com...
Hi Tom,

I got your help in locating shapes in VBA. I need to return the
located
shape so I modified it into a function. But whenver I step into
this
line "findOval = shp", it just jumps out of the function
without
returning any shape. Do you have any suggestions? Thanks.

This is your original code:
Sub DD()
Dim shp As Shape
Dim s As String
Dim TargetText As String
TargetText = "dog"
For Each shp In ActiveSheet.Shapes
On Error Resume Next
s = ""
s = shp.TextFrame.Characters.Text
On Error GoTo 0
If InStr(1, s, TargetText, vbTextCompare) Then
shp.Select
Exit For
End If
Next
End Sub


And this is what I modified:
Function findOval(TargetText As String)
Dim shp As Shape
Dim s As String

For Each shp In ActiveSheet.Shapes
On Error Resume Next
s = ""
s = shp.TextFrame.Characters.Text
On Error GoTo 0
If InStr(1, s, TargetText, vbTextCompare) Then
shp.Select
findOval = shp
Exit For
End If
Next
End Function

Thanks. That's the reason. But I don't understand why I need to use
Set. What's the difference of setting a variable directly and by using
"Set"?

Set creates a reference to an object. Let, on the other hand, assigns a
value. Since Let is used far more than Set, the VBA compiler allows one
to omit it, i.e., a=b is actually
Let a=b.

For more search XL VBA help for 'set statement'.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Multi-disciplinary business expertise
+ Technology skills
= Optimal solution to your business problem
Recipient Microsoft MVP award 2000-2005

In article .com,
says...
Thanks. That's the reason. But I don't understand why I need to use
Set. What's the difference of setting a variable directly and by using
"Set"?

Tushar Mehta wrote:
Set creates a reference to an object. Let, on the other hand, assigns a
value. Since Let is used far more than Set, the VBA compiler allows one
to omit it, i.e., a=b is actually
Let a=b.

For more search XL VBA help for 'set statement'.

--
Regards,

Tushar Mehta

This is tangential to the OP, but this aspect of VBA has always annoyed
me. I don't see why the compiler/interpreter can't handle this
automatically. Since something like "myShape = Shapes(1)" is *always* a
syntax error, there would be no possible ambiguity in letting the
compiler convert it implicitly into a Set. I always thought that the
point of VBA is to allow you to write straightforward code in a rich
object-universe while letting the compiler handle the background
pointers and memory management. I'm not sure why the designers of VBA
allowed pointers to peak through here. Things like shape variables that
are transparent to the user would be quite nice. What am I missing?

-John Coleman

Default values?

Eg lots of folk write code like this:

z = Activesheet.Range("A1")

Results of running this will depend on how z is declared (if indeed it is at
all). If z is a string/number etc then Excel will assign the default
property of the given range (".value") to z. If z is a range object then
the statement will fail.

You can't not specify default values *and* have the compiler decide whther
you meant Set or Let (or at least IMO that wouldn't be an improvement).

--
Tim Williams
Palo Alto, CA


"John Coleman" wrote in message
oups.com...

Tushar Mehta wrote:
Set creates a reference to an object. Let, on the other hand, assigns a
value. Since Let is used far more than Set, the VBA compiler allows one
to omit it, i.e., a=b is actually
Let a=b.

For more search XL VBA help for 'set statement'.

--
Regards,

Tushar Mehta

This is tangential to the OP, but this aspect of VBA has always annoyed
me. I don't see why the compiler/interpreter can't handle this
automatically. Since something like "myShape = Shapes(1)" is *always* a
syntax error, there would be no possible ambiguity in letting the
compiler convert it implicitly into a Set. I always thought that the
point of VBA is to allow you to write straightforward code in a rich
object-universe while letting the compiler handle the background
pointers and memory management. I'm not sure why the designers of VBA
allowed pointers to peak through here. Things like shape variables that
are transparent to the user would be quite nice. What am I missing?

-John Coleman