Sometimes can't assign to array and sometimes can

Question

I have the next code:

Function findRanges(keyword) As Variant()

Dim foundRanges(), rngSearch As Range
Dim i, foundCount As Integer

i = 0
foundCount = 0
ReDim foundRanges(0)

Set rngSearch = ActiveDocument.Range
    Do While rngSearch.Find.Execute(FindText:=keyword, MatchWholeWord:=True, Forward:=True) = True
        Set foundRanges(i) = rngSearch.Duplicate
        i = i + 1
        ReDim Preserve foundRanges(UBound(foundRanges) + 1)
        rngSearch.Collapse Direction:=wdCollapseEnd

    Loop

ReDim Preserve foundRanges(UBound(foundRanges) - 1)

findRanges = foundRanges

End Function

And:

Sub test()
Dim rngIAM_Code() As Range
...
Dim rngIAM_Title() As Range

rngIAM_Code = findRanges("IAM_Code")
...
rngIAM_Title = findRanges("IAM_Title")


End Sub

What is very confuding is that sometimes the compiler says "Can't assign to array" and sometimes it works fine. For example, when I only try to search one value and populate one array, the code works. When I try to populate both array, there is an error "Can't assign to an array". I can then switch lines of code like this:

rngIAM_Title = findRanges("IAM_Title")
...
rngIAM_Code = findRanges("IAM_Code")

And then the error happens with another array. The error can happen anywhere: on the first line, in the middle, or in the end, but it is consistent as long as I don't move lines. And again, if I leave only one-two lines of code with arrays in sub "test"everything works fine.

Answer 1

Here is an alternative based on a Collection instead of an Array:
I used also included Cindys Input regarding passing the document and adding wrap.
I don't exactly know what the you use the return value for, but in general a collection is a bit more flexible than an Array.
I also removed the underscores since they indicate a function of an implemented Interface and may cause problems later down the line. are used when implementing an Interface (improves readability).
As explained here you can use wrap or collapse to prevent a continuous Loop.

Option Explicit

Sub test()
    Dim rngIAMCode As Collection
    Dim rngIAMTitle As Collection

    Set rngIAMCode = findRanges("IAM_Code", ActiveDocument)
    Set rngIAMTitle = findRanges("IAM_Title", ActiveDocument)

    Debug.Print "Code found : " & rngIAMCode.Count & " Times."
    Debug.Print "Title found : " & rngIAMTitle.Count & " Times."

End Sub



Function findRanges(ByVal keyword As String, doc As Document) As Collection

    Set findRanges = New Collection
    Dim rngSearch As Range

    Set rngSearch = doc.Content
    With rngSearch.Find
        .Text = keyword
        .MatchWholeWord = True
        .Forward = True
        .Wrap = wdFindStop

        While .Execute
            findRanges.Add rngSearch.Duplicate
            rngSearch.Collapse Direction:=wdCollapseEnd
        Wend

    End With
End Function

Answer 2

The following works for me.

In this code, every object variable is explicitly assigned a type. In VBA, every variable must be typed, else it's assigned the type Variant by default. In the following declaration line, for example, foundRanges() is of type Variant because it's not followed by As with a data type. The same with i in the next line of code in the question.

Dim foundRanges(), rngSearch As Range

And since the arrays in the calling procedure are of type Range the function should return the same type.

I also took the liberty of passing the Document object to the function as, conceivably, some day the document in question might not be ActiveDocument but a Document object assigned using Documents.Open or Documents.Add. If this is not desired it can be changed back, but not relying on ActiveDocument is more reliable...

Additionally, I added the Wrap parameter to Find.Execute - it's always a good idea to specify that when executing Find in a loop to prevent the search from starting again at the beginning of the document (wdFindContinue).

Sub testRangesInArrays()
    Dim rngIAM_Code() As Range
    Dim rngIAM_Title() As Range

    rngIAM_Code = findRanges("You", ActiveDocument)
    rngIAM_Title = findRanges("change", ActiveDocument)
End Sub

Function findRanges(keyword As String, doc As Word.Document) As Range()
    Dim foundRanges() As Range, rngSearch As Range
    Dim i As Integer, foundCount As Integer

    i = 0
    foundCount = 0
    ReDim foundRanges(0)

    Set rngSearch = doc.content
    Do While rngSearch.Find.Execute(findText:=keyword, MatchWholeWord:=True, _
                       Forward:=True, wrap:=wdFindStop) = True
        Set foundRanges(i) = rngSearch.Duplicate
        ReDim Preserve foundRanges(UBound(foundRanges) + 1)
        i = i + 1
        rngSearch.Collapse Direction:=wdCollapseEnd
    Loop

    findRanges = foundRanges

End Function