Compiler 'Argument not optional' in Function 2

[Darrylles]

OK, getting desperate enough to drop this one in (then continue to try to resolve it, until some wit highlights my schoolboy mistake).
I have been a member for 17 years though!

I have a function that basically returns a list of files found in any directory (folder).
It worked fine whilst using an array. I decided to switch to a collection (arrays were getting on my nerves).
The function should return a collection.
(And as I type that, I had an inkling that maybe I should PASS a collection to it, as, the collection is actually being created WITHIN the function! Hmmm).

Anyway, I've started so I'll finish. Here's the function (the compiler baulks at 'ListFilesDir = col')...

Public Function ListFilesInDir(strPath As String, _
                               Optional strFilter As String = "*") As Collection
    Dim col      As Collection
    Dim strFile  As String
 
On Error GoTo ErrHandle
 
    ' Make sure path has terminating \ ...
    If Right(strPath, 1) <> "\" Then
        strPath = strPath & "\"
    End If
    
    strFile = Dir(strPath & "*." & strFilter)                      ' Add file extension.
    Set col = New Collection
    
    ' Loop through DIR files...
    Do While strFile <> vbNullString
        If strFile <> "." And strFile <> ".." Then
            col.Add strFile                              ' Add found file to collection.
        End If
        strFile = Dir                                                 'Next file in DIR.
    Loop
    
    ListFilesInDir = col                                  ' <----------------Error here.
    col.Clear
 
ErrExit:
    On Error Resume Next
    Exit Function
 
ErrHandle:
    MsgBox "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: ListFilesInDir" & vbCrLf & _
           "Error Description: " & Err.Description
    Resume ErrExit
End Function

If you get to it before me - you get 2 stars.
After = 1.

Thanks,

Darrylle

 

[Darrylles]

OK, I changed the function to a sub, and passed a collection 'byref' (newly defined in the calling sub).

Testing that now - but it does at least compile ok.
Doesn't sound right having to do that though.

Any info. appreciated.

ATB,

Darrylle

 

[combo]

[tt]Set ListFilesInDir = col[/tt] should work.

combo

 

[Darrylles]

combo

ListFilesInDir is a function.

Can you 'Set' a function return value?


ATB,

Darrylle

 

[Darrylles]

This is not 'solved', however; I replaced the function with a procedure with a parameter of a collection passed ByVal (by default), where the collection was defined by the calling procedure (not the called function).

It seems strange that I can pass an array variable back to a calling sub - without the sub ever 'knowing' about the array created within the called function, but, it doesn't seem to work for collections.

Is this a 'quick 'n dirty' MS implementation of collections? (Collections ARE very useful, but I'd expect a 'generic' implementation of them).

combo: your idea may well 'work', but it certainly does not 'fit in' with the general / consistent framework of how to pass function results back.

ATB,

Darrylle

 

[strongm]

>Can you 'Set' a function return value?

yes

But then

col.Clear

becomes a problem.

When you Set, you simpoly create a pointer to the same object. So if you were able to clear Col, then you would also clear the collection that you are returning since col and ListFilesInDir point to the same object.

 

[combo]

There is no Clear method of collection in vba:

Collection is an object, insteat of [tt]col.Clear[/tt] you can use [tt]Set col = Nothing[/tt]. Finally the function returns collection, can be used in the way:
[tt]Set MyFilesDirCollection = ListFilesInDir("SearchedDir")[/tt]

combo

 

[strongm]

>without the sub ever 'knowing' about the array created within the called function, but, it doesn't seem to work for collections.

This is because an array is not an object - and something very different gets passed for either ByVal or ByRef

I'll simplify a little here;

If you pass ByVal, a copy is made of the array, and that copy is what gets passed to the procedure/function

ByRef passes the original array.

But, with an object, whether ByVal or ByRef, you always pass (a pointer to) the original object. This may appear to be different behaviour, but in actuality is completely consistent with how VBA actually passes variables around.