Issues passing a collection of ranges byRef

[Fool]

I think my problem is best explained without code, but I will provide
code if anyone thinks it might help.

My Goal: To search for several different strings in a document, and
then store all matches to each string in a collection of ranges.

My Solution: I have a Main sub that:

* Defines a search string and a collection
* Passes the search string and collection (both by reference)
to a Search sub

The Search sub then:

* Finds all ranges matching that string
* Adds the matched ranges to the collection.
* Exits when no more matches are found.

Control passes back to the Main sub, which changes the search string
and calls Search again. Finally, when all search strings are done, all
ranges in the collection are printed to a text file.

OK, here's the problem.

The Search function prints every "hit" to the debug window just before
it adds that hit to the collection - so I can see that it's grabbing
the appropriate ranges. And it does. They're all different and they
all look right.

However, when the program finally prints the collection's range text
to a text file, ranges are duplicated. For example, if I searched for
C*T in one search, and then searched for H*T on the next search, I'll
see things like this in the debug window:

CAT
COT
COAT

HAT
HOT
HOST

However, when the range text from the collection is printed out at the
end, I see this:

COAT
COAT
COAT
HOST
HOST
HOST

I have used collections and ranges before with no troubles, but I have
never passed them back and forth by reference. I wonder if this may be
the problem.

If a likely problem jumps out at you, please post. Otherwise I'll post
code and see if that helps.

Thank you kindly, in advance!

Fool

 

[Peter Hewett]

Hi Fool

Don't forget object variables are just pointers, so when you're adding a
range objects to the collection they are all pointing at the same object!
When you add an object to a collection it's actually adding the objects
address! In this case you're adding the address of the same object! This is
what I mean:

Dim rngOne as Word.Range
Dim rngTwo as Word.Range

Set rngOne = ActiveDocument.Content
Set rngTwo = RngOne
rngOne.Collapse wdCollapseStart

In the above example both rngOne AND rngTwo have been collapsed to the
start of the document! Even though we only changed rngOne, why?, because
both object variables point at the same object! To only change rngOne you
need to do this:

Dim rngOne as Word.Range
Dim rngTwo as Word.Range

Set rngOne = ActiveDocument.Content
Set rngTwo = RngOne.Duplicate
rngOne.Collapse wdCollapseStart

So in your code try something like this:

Dim colRanges As Collection
Dim rngCopy As Word.Range
Dim rngYourRange As Word.Range

' Set your range (rngYourRange) to something (the search result?)

' Create a new range object and add range safely to the collection
Set rngCopy = rngYourRange.Duplicate
colRanges.Add rngCopy

Hope you can make sense of the above.

HTH + Cheers - Peter

 

[Fool]

Thanks very much, this did in fact help! I'm still a little foggy on
why, though.

In the past I've done similar operations - namely, I set up a loop and
within it use selection.find to find every instance of a string in a
document. I then add each instance to a collection. The code looks
something like this:
_____________________

while n < 1000

With Selection.Find
.ClearFormatting
.Text = "blahblahblah"
.Execute

If .Found = False Then
Debug.Print "Done finding stuff."
Exit Sub
End If

colRanges.Add Selection.Range

End With

wend

_____________________

And that works just fine! I keep adding selection.range to the
collection, and each time a unique range is added. I end up with a
collection full of distinct ranges.

Is the problem that I was using Selection.Find in the past, and now
I'm using a Range.Find? And if so, isn't the Selection object a
pointer, just like a Range object?

Thanks!

Fool

 

[Peter Hewett]

Hi Fool

I guess you answered you own question, since the Selection object works -
but the range object didn't!!! The way the Find method works is way weird!

A lot of SandR code loops on the .Execute method. Within this loop you use
the same range object that you used with the Find method, but it keeps
returning different values for that range object WITHOUT screwing up the
outer Find method. It works, but don't ask me why!

Glad to have been of help even if it confused you!

Cheers - Peter

 

[Fool]

OK, thanks again Peter! I'm glad to know that I'm not the only one
perplexed by the inner workings of Search and Replace. I always
thought it would be more logical to make you declare two ranges right
from the start - a range to search in and a range to store results. It
might be less "elegant" to use two ranges, but I think it's much
easier to wrap your mind around.

 

[Tushar Mehta]

How are you adding the range to the collection object? The code below
works just fine. It adds the cell as an object with the address as a
key that should guarantee uniqueness.

Sub testIt()
Dim x As Range, y As Collection
Set y = New Collection
For Each x In Range("b1:b3")
y.Add x, x.Address
Next x
For Each x In y
Debug.Print TypeName(x)
Debug.Print x.Address & " = " & x.Value
Next x
End Sub

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Peter Hewett]

Hi Tushar

This is Word not Excel. Words Range object does not have an address
property. The other thing is, although Excels Range object supports an
Address property it actually returns a string. So your loop is just adding
a series of strings to the collection, which of course works.

What Fool wanted to do was build a collection of (Word) Range objects. When
I was talking about addresses I meant true memory addresses in the
Hex$(ObjPtr(RangeObject)) sense, not cell reference addresses.

Cheers - Peter

 

[Tushar Mehta]

Hi Peter,

Hi Tushar

Address property it actually returns a string. So your loop is just adding
a series of strings to the collection, which of course works.

No, not really. It adds the range object using the address as the
*key.* Note that the 2nd loop retrieves the *object* from the
collection and uses the .Address and .Value properties in the
Debug.Print statement.

In any case, if the OP is still following this, the following works
with Word. It works around the issue of the same object being added to
the collection through the introduction of a new, temporary, variable
x1. It also works around the issue of selecting things.

Option Explicit

Sub testIt()
Dim x As Range, y As Collection, StartPos As Long
Set y = New Collection
'ActiveDocument.Select: Selection.HomeKey wdStory
Set x = ActiveDocument.Content
With x.Find
.ClearFormatting
.Text = "[hH]ell*"
.Replacement.Text = ""
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchWholeWord = False
.MatchWildcards = True
.MatchCase = True
.MatchSoundsLike = False
.MatchAllWordForms = False
'On Error GoTo WrapUp
If Not .Execute(Forward:=True) Then Exit Sub
StartPos = x.Start
Do
x.Expand wdWord
Debug.Print "x=" & x.Text
'x.Select
addToCollection x, y
x.Move Unit:=wdWord, Count:=1
.Execute Forward:=True
Loop Until x.Start = StartPos
End With
For Each x In y
Debug.Print TypeName(x) & ", " & x.Text
Next x
End Sub
Sub addToCollection(ByVal x As Range, ByRef y As Collection)
Dim x1 As Range
Static i As Integer
i = i + 1
Set x1 = ActiveDocument.Characters(x.Start + 1)
x1.Expand wdWord
y.Add x1, "_Find" & x.Text & "_" & i
End Sub


--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Peter Hewett]

Hi Tushar

Of course your code works, the problem is you don't understand why!

Lets go back to basics:

Dim rngA As Word.Range
Dim rngB As Word.Range

Set rngA = ActiveDocument.Content
rngA.Collapse wdCollapseStart
Set rngB = rngA
rngB.Collapse wdCollapseEnd

We set the range object rngA to the contents of the MainTextStory. We then
collapse the range to the start. Next we setup another reference to the
same range object (note the use of "the same")! Then we collapse it to the
end of the document. So what does rngA map to - the start of the document?
Nope, the end of the document because both rngA and rngB point to The Same
object. So what's just happened here? We create an object and VBA allocates
memory for that object, it places a refererence to that object in the
object variable. However, this is just the address of the object not the
object itself. So when we create a second reference "rngB" all that's
happened is that VBA has allocated another reference (object pointer) to
the same object. VBA is of course doing reference counting so that it knows
when it can destroy the real object.

Ok, from the above we can extend this to include collections:

Dim rngA As Word.Range
Dim colI As New Collection

Set rngA = ActiveDocument.Content
rngA.Collapse wdCollapseStart
colI.Add rngA, "DummyKey"
rngA.Collapse wdCollapseEnd

As above, we set the range object rngA to the contents of the
MainTextStory. We then collapse the range to the start. We then add "rngA"
to the collection. Again all we've done here as create another reference
pointer to the "rngA" object. However, this time the second refererence to
it is held by the collection instead of another object variable. So after
adding "rngA" to the collection we then collapse it to the end of the
document. So where does the range object in the collection point to? The
beginning of the document? Nope, the end of the document. Because it the
same object that being manipulated through two different object variables.

Here's the simple proof, just create a Word document and make sure it
contains a couple of sentences:

Public Sub RangeTest1()
Dim rngI As Word.Range
Dim colRs As New Collection

Set rngI = ActiveDocument.Content
rngI.Collapse wdCollapseStart

' Add first word
rngI.Expand wdWord
Debug.Print rngI.Text
colRs.Add rngI, "A"

' Add second word
rngI.Move wdWord
rngI.Expand wdWord
Debug.Print rngI.Text
colRs.Add rngI, "B"

' Add third word
rngI.MoveStart wdWord
rngI.Expand wdWord
colRs.Add rngI, "C"
Debug.Print rngI.Text

Debug.Print "<-----Collection debug----->"
Debug.Print Hex$(ObjPtr(colRs("A"))), colRs("A").Text
Debug.Print Hex$(ObjPtr(colRs("B"))), colRs("B").Text
Debug.Print Hex$(ObjPtr(colRs("C"))), colRs("C").Text
End Sub


The 3 collection references all return a reference to the SAME object!!!
In this case it's the third object!!!

Now in the following example, the code works correctly:

Public Sub RangeTest2()
Dim rngA As Word.Range
Dim rngB As Word.Range
Dim colRs As New Collection

Set rngA = ActiveDocument.Content
rngA.Collapse wdCollapseStart

' Add first word
rngA.Expand wdWord
Set rngB = rngA.Duplicate
colRs.Add rngB, "A"
Debug.Print rngB.Text

' Add second word
rngA.Move wdWord
rngA.Expand wdWord
Set rngB = rngA.Duplicate
colRs.Add rngB, "B"
Debug.Print rngB.Text

' Add third word
rngA.MoveStart wdWord
rngA.Expand wdWord
Set rngB = rngA.Duplicate
colRs.Add rngB, "C"
Debug.Print rngB.Text

Debug.Print "<-----Collection debug----->"
Debug.Print Hex$(ObjPtr(colRs("A"))), colRs("A").Text
Debug.Print Hex$(ObjPtr(colRs("B"))), colRs("B").Text
Debug.Print Hex$(ObjPtr(colRs("C"))), colRs("C").Text
End Sub

The reason it works correctly is that a new range object is being created
and then added to the collection. This in not just a new object variable
reference to "RngA" but a unique new object is being created each time!

Now the reason your code works is that it's obviously creating a new range
object! This is happening in your "AddToCollection" procedure in the
following line:

Set x1 = ActiveDocument.Characters(x.Start + 1)

Keep your conceptual proofs simple so that other things do not mislead you.

HTH + Cheers - Peter

Hi Peter,

Hi Tushar

Address property it actually returns a string. So your loop is just
adding a series of strings to the collection, which of course works.

No, not really. It adds the range object using the address as the
*key.* Note that the 2nd loop retrieves the *object* from the
collection and uses the .Address and .Value properties in the
Debug.Print statement.

In any case, if the OP is still following this, the following works
with Word. It works around the issue of the same object being added to
the collection through the introduction of a new, temporary, variable
x1. It also works around the issue of selecting things.

Option Explicit

Sub testIt()
Dim x As Range, y As Collection, StartPos As Long
Set y = New Collection
'ActiveDocument.Select: Selection.HomeKey wdStory
Set x = ActiveDocument.Content
With x.Find
.ClearFormatting
.Text = "[hH]ell*"
.Replacement.Text = ""
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchWholeWord = False
.MatchWildcards = True
.MatchCase = True
.MatchSoundsLike = False
.MatchAllWordForms = False
'On Error GoTo WrapUp
If Not .Execute(Forward:=True) Then Exit Sub
StartPos = x.Start
Do
x.Expand wdWord
Debug.Print "x=" & x.Text
'x.Select
addToCollection x, y
x.Move Unit:=wdWord, Count:=1
.Execute Forward:=True
Loop Until x.Start = StartPos
End With
For Each x In y
Debug.Print TypeName(x) & ", " & x.Text
Next x
End Sub
Sub addToCollection(ByVal x As Range, ByRef y As Collection)
Dim x1 As Range
Static i As Integer
i = i + 1
Set x1 = ActiveDocument.Characters(x.Start + 1)
x1.Expand wdWord
y.Add x1, "_Find" & x.Text & "_" & i
End Sub

 

[Tushar Mehta]

Hi Tushar

Of course your code works, the problem is you don't understand why!

That's a pretty strong -- and, as it turns out, unjustified --
assumption to make.

Why do you think I created a new object from scratch rather than just
setting it equal to the argument passed to the procedure?

[Of course, I should have used the Duplicate method, but, hey, I had to
know about it. ;-)]

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

Hi Tushar

Of course your code works, the problem is you don't understand why!

Lets go back to basics:

Dim rngA As Word.Range
Dim rngB As Word.Range

Set rngA = ActiveDocument.Content
rngA.Collapse wdCollapseStart
Set rngB = rngA
rngB.Collapse wdCollapseEnd

We set the range object rngA to the contents of the MainTextStory. We then
collapse the range to the start. Next we setup another reference to the
same range object (note the use of "the same")! Then we collapse it to the
end of the document. So what does rngA map to - the start of the document?
Nope, the end of the document because both rngA and rngB point to The Same
object. So what's just happened here? We create an object and VBA allocates
memory for that object, it places a refererence to that object in the
object variable. However, this is just the address of the object not the
object itself. So when we create a second reference "rngB" all that's
happened is that VBA has allocated another reference (object pointer) to
the same object. VBA is of course doing reference counting so that it knows
when it can destroy the real object.

Ok, from the above we can extend this to include collections:

Dim rngA As Word.Range
Dim colI As New Collection

Set rngA = ActiveDocument.Content
rngA.Collapse wdCollapseStart
colI.Add rngA, "DummyKey"
rngA.Collapse wdCollapseEnd

As above, we set the range object rngA to the contents of the
MainTextStory. We then collapse the range to the start. We then add "rngA"
to the collection. Again all we've done here as create another reference
pointer to the "rngA" object. However, this time the second refererence to
it is held by the collection instead of another object variable. So after
adding "rngA" to the collection we then collapse it to the end of the
document. So where does the range object in the collection point to? The
beginning of the document? Nope, the end of the document. Because it the
same object that being manipulated through two different object variables.

Here's the simple proof, just create a Word document and make sure it
contains a couple of sentences:

Public Sub RangeTest1()
Dim rngI As Word.Range
Dim colRs As New Collection

Set rngI = ActiveDocument.Content
rngI.Collapse wdCollapseStart

' Add first word
rngI.Expand wdWord
Debug.Print rngI.Text
colRs.Add rngI, "A"

' Add second word
rngI.Move wdWord
rngI.Expand wdWord
Debug.Print rngI.Text
colRs.Add rngI, "B"

' Add third word
rngI.MoveStart wdWord
rngI.Expand wdWord
colRs.Add rngI, "C"
Debug.Print rngI.Text

Debug.Print "<-----Collection debug----->"
Debug.Print Hex$(ObjPtr(colRs("A"))), colRs("A").Text
Debug.Print Hex$(ObjPtr(colRs("B"))), colRs("B").Text
Debug.Print Hex$(ObjPtr(colRs("C"))), colRs("C").Text
End Sub


The 3 collection references all return a reference to the SAME object!!!
In this case it's the third object!!!

Now in the following example, the code works correctly:

Public Sub RangeTest2()
Dim rngA As Word.Range
Dim rngB As Word.Range
Dim colRs As New Collection

Set rngA = ActiveDocument.Content
rngA.Collapse wdCollapseStart

' Add first word
rngA.Expand wdWord
Set rngB = rngA.Duplicate
colRs.Add rngB, "A"
Debug.Print rngB.Text

' Add second word
rngA.Move wdWord
rngA.Expand wdWord
Set rngB = rngA.Duplicate
colRs.Add rngB, "B"
Debug.Print rngB.Text

' Add third word
rngA.MoveStart wdWord
rngA.Expand wdWord
Set rngB = rngA.Duplicate
colRs.Add rngB, "C"
Debug.Print rngB.Text

Debug.Print "<-----Collection debug----->"
Debug.Print Hex$(ObjPtr(colRs("A"))), colRs("A").Text
Debug.Print Hex$(ObjPtr(colRs("B"))), colRs("B").Text
Debug.Print Hex$(ObjPtr(colRs("C"))), colRs("C").Text
End Sub

The reason it works correctly is that a new range object is being created
and then added to the collection. This in not just a new object variable
reference to "RngA" but a unique new object is being created each time!

Now the reason your code works is that it's obviously creating a new range
object! This is happening in your "AddToCollection" procedure in the
following line:

Set x1 = ActiveDocument.Characters(x.Start + 1)

Keep your conceptual proofs simple so that other things do not mislead you.

HTH + Cheers - Peter

Hi Peter,

Hi Tushar

Address property it actually returns a string. So your loop is just
adding a series of strings to the collection, which of course works.

No, not really. It adds the range object using the address as the
*key.* Note that the 2nd loop retrieves the *object* from the
collection and uses the .Address and .Value properties in the
Debug.Print statement.

In any case, if the OP is still following this, the following works
with Word. It works around the issue of the same object being added to
the collection through the introduction of a new, temporary, variable
x1. It also works around the issue of selecting things.

Option Explicit

Sub testIt()
Dim x As Range, y As Collection, StartPos As Long
Set y = New Collection
'ActiveDocument.Select: Selection.HomeKey wdStory
Set x = ActiveDocument.Content
With x.Find
.ClearFormatting
.Text = "[hH]ell*"
.Replacement.Text = ""
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchWholeWord = False
.MatchWildcards = True
.MatchCase = True
.MatchSoundsLike = False
.MatchAllWordForms = False
'On Error GoTo WrapUp
If Not .Execute(Forward:=True) Then Exit Sub
StartPos = x.Start
Do
x.Expand wdWord
Debug.Print "x=" & x.Text
'x.Select
addToCollection x, y
x.Move Unit:=wdWord, Count:=1
.Execute Forward:=True
Loop Until x.Start = StartPos
End With
For Each x In y
Debug.Print TypeName(x) & ", " & x.Text
Next x
End Sub
Sub addToCollection(ByVal x As Range, ByRef y As Collection)
Dim x1 As Range
Static i As Integer
i = i + 1
Set x1 = ActiveDocument.Characters(x.Start + 1)
x1.Expand wdWord
y.Add x1, "_Find" & x.Text & "_" & i
End Sub

 

[Peter Hewett]

Hi Tushar

That was unjustified and uncalled for on my part - my appologies, you
caught the end of a bad day.

I'd assumed you ignored the fact that I'd used the range objects Duplicate
method in an earlier post in this thread.

This is one of a number of standardised searches I use with Word:

Sub FindWithAction()
Dim rngReplace As Word.Range
Dim rngFound As Word.Range

Set rngReplace = ActiveDocument.Content
With rngReplace.Find
.ClearFormatting
.Replacement.ClearFormatting
.Text = "“*”"
.Replacement.Text = ""
.Forward = True
.Wrap = wdFindStop
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchAllWordForms = False
.MatchSoundsLike = False
.MatchWildcards = True

' Find all occurrences in the document
Do While .Execute

' Create and use a totally independant range object
Set rngFound = rngReplace.Duplicate

' Colourise the quoted text
rngFound.MoveStart wdCharacter, 1
rngFound.MoveEnd wdCharacter, -1
rngFound.Font.Color = wdColorBrown

' Resume the search after the text that we just found
rngReplace.Collapse wdCollapseEnd
Loop
End With
End Sub

The above just changes the colour of any quoted text. But I always
Duplicate the outer search range before using it in an inner loop. It may
not *always* be needed but I never have the code failing on me either!

Cheers - Peter

Hi Tushar

Of course your code works, the problem is you don't understand why!

That's a pretty strong -- and, as it turns out, unjustified --
assumption to make.

Why do you think I created a new object from scratch rather than just
setting it equal to the argument passed to the procedure?

[Of course, I should have used the Duplicate method, but, hey, I had to
know about it. ;-)]