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

Tim Williams wrote:
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


Good point Tim, but not quite decisive. "Set" could be retained for the
rare occasions that the compiler can't infer it. Alternatively, objects
which have a default property could have say a "self" property to be
used when you want to assign the object itself.

I suspect that a better reason to retain "Set" is that if x,y are
ordinary variables then x=y is assignment by copying but for object
variables set x = y is assignment by sharing (so changing the object
that y points to changes the object that x points to (since they are
the same object), so code that mentions y could in some sense effect
the meaning of x.) In this sense an explicit "Set" functions as
something like an obligatory comment that the following assignment
*means* something different. I don't doubt that the language *could* be
altered in such a way that 90% of the occurences of "set" could be
eliminated, but the cost might be code which is harder to debug.

The main example where this issue bothered me recently was that I had
written a Stack class which implemented a stack of variants. It worked
fine, until I imported it into a project for which I wanted to have a
stack of shapes. I had to rewrite the class almost completely to create
a class of "ShapeStacks", though in retrospective I should have bitten
the bullet and tried to rewrite the original class so that Push and Pop
would first check if they were being asked to push or pop an object and
then act accordingly.

Thanks for your input.

-John Coleman