Return Array from Function

[jgreigdavis]

I have the following function that works fine, unless I add arguements.
When I add the arguments I get a Run-Time error '13': Type mismatch.
Any help would be greatly appreciated.

Option Base 1
Function FileSearch(Directory As String, Criteria As String)

Dim aFiles()


With Application.FileSearch

.NewSearch
.LookIn = Directory 'ActiveWorkbook.Worksheets("Control").Cells(3,
2) 'Directory
.SearchSubFolders = True
.Filename = Criteria '
ActiveWorkbook.Worksheets("Control").Cells(4, 2) 'Criteria
.Execute

ReDim aFiles(.FoundFiles.Count)

For i = LBound(aFiles) To UBound(aFiles)
aFiles(i) = .FoundFiles(i)
Debug.Print i, aFiles(i)
Next i

End With

'FileSearch = Application.WorksheetFunction.Transpose(aFiles)
FileSearch = aFiles
End Function

 

[Gary L Brown]

You aren't returning the function to an array...

Sub TestMe()
Dim ary()
ary() = FileSearch("D:\Temp\", "*.*")
End Sub

works because you are returning your array to an array.

Suggestion, try to stay out of the habit of using 'reserve' words like
'FileSearch' as the name of your functions and procedures.

HTH,

 

[jgreigdavis]

Gary,

Thank you worked like a charm. I will be sure not to use reserved
words in the future.

Thanks again,

John