CODEWITHSUNDEEP
vbaexcelexcel-2010
user2385809
user2385809

Reputation: 1005

Have a Function Return an Array

I 'd like to create a function that does some checks and returns an array to my calling sub. this array cannot really be created in the sub and then passed to the function for manipulation. The sub just calls the function which must create an array and return it to the sub. Is this possible?

Here is my function. the first line is obviously incorrect but this is the idea of what i want to do.

Function rMMopened(1 to 1000)() As Long

Dim r As Long, i As Long
Dim ttd As Worksheet
Dim lrttd As Long

Set ttd = Sheets("Tasks_to_do")

lrttd = ttd.Cells(Rows.count, 1).End(xlUp).Row
ReDim rMMopened(1 To lrttd)

i = 1    
For r = 2 To lrttd    
    If ttd.Cells(r, 10) = "Phase 2" Then        
        If InStr("MASTER", ttd.Cells(r, 4)) Then            
            rMMopened(i) = r
            i = i = 1                
        End If        
    End If        
Next

ReDim Preserve rMMopened(1 To (i - 1))    
End Function

Thanks

Upvotes: 0

Views: 68

Answers (1)

Chris Rolliston
Chris Rolliston

Reputation: 4808

How about this:

Function rMMopened() As Long()      ' *** changed

Dim Values() As Long                ' *** added
Dim r As Long, i As Long
Dim ttd As Worksheet
Dim lrttd As Long

Set ttd = Sheets("Tasks_to_do")

lrttd = ttd.Cells(Rows.Count, 1).End(xlUp).Row
ReDim Values(1 To lrttd)            ' *** changed

i = 1

For r = 2 To lrttd

    If ttd.Cells(r, 10) = "Phase 2" Then

        If InStr("MASTER", ttd.Cells(r, 4)) Then

            Values(i) = r           ' *** changed
            i = i = 1

        End If

    End If

Next

ReDim Preserve Values(1 To (i - 1)) ' *** changed
rMMopened = Values                  ' *** added

End Function

Upvotes: 2

Related Questions