Tom Ogilvy, your code about locating a specified shape in VBA

[lvcha.gouqizi]

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

 

[Chip Pearson]

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]

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]

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

 

[John Coleman]

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'.

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]

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).

 

[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).

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

 

[John Coleman]

John Coleman wrote:
(snip)

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.

Not that the later is hard. Here is some code for a flexible stack
class, in a class module named Stack:

Option Explicit
'
'
'This class is built upon Class StackItem
'
'StackItem's complete definition is:
'
'Public Value As Variant
'Public NextItem As StackItem
'
'(There are no methods or properties)
'
'Stack's definition:

Private StackTop As StackItem

Public Property Get IsEmpty() As Boolean
If StackTop Is Nothing Then IsEmpty = True
End Property

Public Function Pop() As Variant
If Not Me.IsEmpty Then
If IsObject(StackTop.Value) Then
Set Pop = StackTop.Value
Else
Pop = StackTop.Value
End If
Set StackTop = StackTop.NextItem
End If
End Function

Public Function Peek() As Variant
If Not Me.IsEmpty Then
If IsObject(StackTop.Value) Then
Set Peek = StackTop.Value
Else
Peek = StackTop.Value
End If
End If
End Function

Public Sub Push(NewItem As Variant)
Dim NewTop As New StackItem
If IsObject(NewItem) Then
Set NewTop.Value = NewItem
Else
NewTop.Value = NewItem
End If
Set NewTop.NextItem = StackTop
Set StackTop = NewTop
End Sub

******************************************************

Then for example, if the following is entered into a general code
module:

Sub test()
Dim myStack As New Stack
Dim R As Range

myStack.Push Range("A1")
myStack.Push "Hello World"
MsgBox myStack.Pop
Set R = myStack.Pop
MsgBox R.Address
MsgBox myStack.IsEmpty

End Sub

Then when test() is run the three msgboxes are "Hello World", "$A$1"
and "True" in order. Notice that pushing Range("A1") pushes the range
object and not the default value. You could of course push
Range("A1").Value. The second msgbox could have just been Msgbox
myStack.Pop.Address (assuming you hadn't already popped it into R). How
this stack works is (roughly) how I sometimes wish VBA handled objects.

-John Coleman