Can a user-defined function return an array?

[Mota]

Hi;
I want a function returns all files in a specific folder.Using
Dir(MyPath,VBNormal) and then Dir in a loop i've got all that file names,but
dont know how to store them,instead of Debug.Print,so calling the Function
GetAllFiles,give me all that files.
Can anyone please help me?Thank you in advance.

 

[Douglas J. Steele]

Here's one way:

Function ReturnArray() As Variant
Dim strArray(5) As String

strArray(0) = "This"
strArray(1) = "is"
strArray(2) = "the"
strArray(3) = "array"
strArray(4) = "of"
strArray(5) = "values"

ReturnArray = strArray

End Function

Sub CallReturnArray()
Dim intLoop As Integer
Dim varArray As Variant

varArray = ReturnArray
For intLoop = LBound(varArray) To UBound(varArray)
Debug.Print varArray(intLoop)
Next intLoop

End Sub

Here's the results of running CallReturnArray in the Immediate window:

CallReturnArray
This
is
the
array
of
values

 

[Mota]

Thank you;
just hae a small problem in calling method.Does varArray=ReturnArray
expression take all the subscripts of the ReturnArray in one step,without
using a loop ?
Thanx.

 

[Douglas J. Steele]

Since varArray contains all the array values after that statement, obviously
no loop is required.

Did you try running the code?

 

[Mota]

Yes,
But i faced to another problem.How to check if there is no file in the
requested directory.In fact,what happens to a variant if strArray() contains
nothing?(i use Redim Preserve to fill out strArray).I tried to give an empty
string to the function,if an error ocures.and when calling function,first
checked if its not empty,null or zero.But a Type Mismatch error ocures.So
when there is no file of type i checked for,what wd be the value of the
function that must return a variant in any case?Can u please help me?
i appreciate ur help and thank you again.

 

[MikeB]

Your Function ReturnArray can return an array of string without the variant...

Private Sub GetArray()
Dim s() As String
s = ReturnArray()
MsgBox s(0)

End Sub
Function ReturnArray() As String()
Dim strArray(5) As String

strArray(0) = "This"
strArray(1) = "is"
strArray(2) = "the"
strArray(3) = "array"
strArray(4) = "of"
strArray(5) = "values"

ReturnArray = strArray

End Function

 

[Douglas J. Steele]

Depends on what version of Access you're using. I still work mostly with
Access 97, and that's not possible in Access 97. You're correct, however,
that it's possible in newer versions of Access.

 

[Douglas J. Steele]

With my approach, you can check whether what's returned is null.

Sub CallReturnArray()
Dim intLoop As Integer
Dim varArray As Variant

varArray = ReturnArray
If IsNull(varArray) Then
Debug.Print "Array is empty"
Else
For intLoop = LBound(varArray) To UBound(varArray)
Debug.Print varArray(intLoop)
Next intLoop
End If

End Sub

It can be done using Mike's approach as well, but I don't remember how off
the top of my head.

 

[Douglas J. Steele]

Should probably mention that ReDim is a relative "expensive" operation in
terms of resource requirements.

Rather than doing a ReDim each time you encounter a new element, you should
allocate a number of elements at a time, and do a final ReDim at the end
once you know how many elements are actually in the array.

 

[Mota]

Thank you for all your guides.

Depends on what version of Access you're using. I still work mostly with
Access 97, and that's not possible in Access 97. You're correct, however,
that it's possible in newer versions of Access.

 

[Mota]

Thank you;
but my problem about null values remains still vague.How to check if array
function returns no file?

 

[Mota]

Ok,Thank you.will try it and ask the group,if another error raised.