CODEWITHSUNDEEP
arraysvbafunction
Michael Smith
Michael Smith

Reputation: 72

VBA Passing an array to a function and back

I've started to review cpearson's essay on passing and returning arrays, and I'm getting a bit lost in the details. The below doesn't work, and is confirmed by the essay. It seems like I might be able to correct the problem by looping through the arrays to set each element equal to the element in the other array. How do I do this with a function?

Sub test()

    Dim Arr() as Variant
    Dim CurrDoc as Word.Document

    ReDim Arr(0 to 39, 0 to 1)

    Arr() = FillArr(CurrDoc, Arr())

End Sub

Function FillArr(CurrentDocument As Word.Document, CurrentArray() As Variant) As Variant

    j = 1

    For Each chk In CurrentDocument.ContentControls

        If chk.Type = 8 Then

            CurrentArray(j, 1) = chk.Title
            CurrentArray(j, 2) = chk.Checked

            j = j + 1

        End If

    Next chk

FillArr() = CurrentArray()

End Function

Upvotes: 0

Views: 3905

Answers (2)

Warcupine
Warcupine

Reputation: 4640

I don't feel like making a word doc to match yours exactly but you can pass arrays through functions. I'm going to populate mine with random characters.

Sub test()
    Dim arr() As Variant
    ReDim arr(1 To 40, 1 To 2)

    arr() = fillarr(arr())

    Dim i As Long

    For i = 1 To 40
        Debug.Print i, arr(i, 1)
        Debug.Print i & "* 5", arr(i, 2)
    Next i
End Sub

Function fillarr(currentarr() As Variant) As Variant
    Dim j As Long

    For j = 1 To 40
        currentarr(j, 1) = Chr(j + 64)
        currentarr(j, 2) = Chr(j * 5)
    Next j

    fillarr = currentarr
End Function

There's actually an implicit by ref because in VBA arrays are always passed by reference so you don't actually need a function to do this, though you probably should as it is clearer, but you can see this here:

Sub test()
    Dim arr() As Variant
    ReDim arr(1 To 40, 1 To 2)

    fillarr arr() 'no value set

    Dim i As Long

    For i = 1 To 40
        Debug.Print i, arr(i, 1)
        Debug.Print i & "* 5", arr(i, 2)
    Next i
End Sub

Sub fillarr(currentarr() As Variant)
    Dim j As Long

    For j = 1 To 40
        currentarr(j, 1) = Chr(j + 64)
        currentarr(j, 2) = Chr(j * 5)
    Next j

    'Note the lack of return
End Sub

Upvotes: 1

Tomalak
Tomalak

Reputation: 338138

Personally I would approach this differently.

The usefulness of a function that returns titles and "checked" state of all "ceckbox" type content controls is lower than that of a function that just returns all content controls of a certain type. Here is why:

The code that calls the function already knows that it wants to use Title and Checked, so nothing is lost when it gets an array of ContentControl instances instead of a multi-dimensional array of strings and booleans. But when the calling code wants to manipulate the checkboxes, the "strings and booleans" approach falls short.

Under this assumption we can shed one dimension of the array, and get a function that can be re-used in different scenarios.

Also we don't need any array passing. Just build the array and return it:

Function FindControls(Doc As Word.Document, ControlType As WdContentControlType) As Variant
    Dim cct As Variant, i As Integer

    ' figure out how many matching controls there are and allocate an array
    For Each cct In Doc.ContentControls
        If cct.Type = ControlType Then i = i + 1
    Next cct
    ReDim cctArray(i - 1)

    ' save references to matching controls in array
    i = 0
    For Each cct In Doc.ContentControls
        If cct.Type = ControlType Then
            Set cctArray(i) = cct
            i = i + 1
        End If
    Next cct

    FindControls = cctArray
End Function

Now we can use it quite naturally in the calling code:

Dim c As Variant

For Each c In FindControls(ActiveDocument, wdContentControlCheckbox)
    Debug.Print c.Title, c.Checked
Next

Upvotes: 3

Related Questions