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.

Further i think the error you're getting is because you are assigning a variant array to a array. If you change `Dim rngIAM_Code() as range` to `Dim rngIAM_Code as variant` i think you should be good. Be aware that this is a variant array. — Mr ML, Dec 19 '18 at 09:25
`foundRanges()` and `i` are initialized as variants. See: https://stackoverflow.com/a/28238732/To properly declare them use `Dim i as Integer, dim foundCount As Integer`, or use `Dim i as Long, dim foundCount As Long` directly as VBA will store Integer to Long anyway (internally) https://stackoverflow.com/a/26409520/ — L8n, Dec 19 '18 at 09:31
On another note, maybe use a `Collection` to store your ranges, this avoids the re-dimming and you can get the count by using `aCollection.Count`. You can then also just run `for each aSomething in aCollection` to loop through every stored object. — L8n, Dec 19 '18 at 09:36

score 2 · Accepted Answer · answered Dec 19 '18 at 10:18

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

L8n · Answer 2 · 2018-12-19T13:33:56.890

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

edited Dec 19 '18 at 13:33

answered Dec 19 '18 at 11:01

L8n

728
1
5
15

"I also removed the underscores since they indicate a function of an implemented Interface and may cause problems later down the line." not really, not in VBA. In VBA that would only be a person convention - the programming language doesn't care. – Cindy Meister Dec 19 '18 at 11:58
Thanks! I merged your approaches and it works just fine – Сергей Dec 19 '18 at 12:39
@Сергей If you found more than one "Answer" useful you should be able to upvote (click the up arrow) any contribution(s) you did not mark as "The Answer" (with the checkmark). That's the more reliable way to signal to others that information was useful - comments tend to get deleted... You should be able to upvote any contribution on the site (questions and answers) that you consider helpful. Once you've gotten more reputation points (125) you can also downvote those contributions you consider destructive. – Cindy Meister Dec 19 '18 at 12:45
@CindyMeister true, the problem only comes up if you use an underscore in the name of a function inside an Interface. But it (IMO) improves the readability of the code. – L8n Dec 19 '18 at 13:28

Sometimes can't assign to array and sometimes can

2 Answers2