Return Array from Function
 

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

Return Array from Function
 

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,
--
Gary Brown

If this post was helpful, please click the ''Yes'' button next to ''Was this
Post Helpfull to you?''.


" wrote:

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

Return Array from Function
 

Gary,

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

Thanks again,

John