return multiple values function

Question

I need to get some information about some files so im trying to build a function that returns 3 information like this:

sub main()
    File = getFile(path) 'future code to list all file information on range

end sub


Function getFile(ByVal path as String)

Dim result() as String
Dim Arquivo as File

Set Folder = FSO.getFolder(Path)

For Each File in Folder.Files
    result(n) = File.Name
    result2(n) = File.DateLastModified
    result3(n) = File.ParentFolder
Next    

getFile = Result(), Result2(),Result3()???

End function

Is it possible to return the 3 file information in a single funcion?

so should I get n =1 for file name, n=2 file date modified, n=3 file folder, n=4 filename and so on? and only use result(n). — kdh90
– kdh90, Commented Mar 18, 2022 at 19:12
If you use Array, then yes (as it currently is implicitly). — BigBen
– BigBen, Commented Mar 18, 2022 at 19:26

VBasic2008 · Accepted Answer · 2022-03-18 20:11:33Z

2

Get File Info

Option Explicit

Sub GetFileInfoTEST()
    
    Const FolderPath As String = "C:\Test"
    Const wsName As String = "Sheet1"
    Const FirstCellAddress As String = "A2"
    
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    Dim ws As Worksheet: Set ws = wb.Worksheets(wsName)
    
    Dim FileInfo As Variant: FileInfo = GetFileInfo(FolderPath)
    Dim rCount As Long: rCount = UBound(FileInfo, 1)
    If IsEmpty(FileInfo) Then Exit Sub
    
    With ws.Range(FirstCellAddress).Resize(, UBound(FileInfo, 2))
        .Resize(rCount).Value = FileInfo
        .Resize(ws.Rows.Count - .Row - rCount + 1).Offset(rCount).ClearContents
        .EntireColumn.AutoFit
    End With
    
End Sub

Function GetFileInfo( _
    ByVal FolderPath As String) _
As Variant

    Dim fso As Object: Set fso = CreateObject("Scripting.FileSystemObject")
    If Not fso.FolderExists(FolderPath) Then Exit Function
    
    Dim fsoFolder As Object: Set fsoFolder = fso.GetFolder(FolderPath)
    Dim fCount As Long: fCount = fsoFolder.Files.Count
    If fCount = 0 Then Exit Function
    
    Dim Data As Variant: ReDim Data(1 To fCount, 1 To 3)
    
    Dim fsoFile As Object
    Dim r As Long
    
    For Each fsoFile In fsoFolder.Files
        r = r + 1
        Data(r, 1) = fsoFile.Name
        Data(r, 2) = fsoFile.DateLastModified
        Data(r, 3) = fsoFile.ParentFolder
    Next fsoFile
    
    GetFileInfo = Data

End Function

edited Mar 18, 2022 at 20:11

answered Mar 18, 2022 at 20:00

VBasic2008

56.8k5 gold badges21 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

VBasic2008 Over a year ago

I have edited the code to properly show the dates in the worksheet. Give it another try.

FaneDuru · Accepted Answer · 2022-03-18 19:48:07Z

Please, try the next adapted function:

Function getFile(ByVal path As String) As Variant
 Dim fso As Object, folder As Object, file As Object
 Dim arrName, arrDat, arrPar, n As Long

 Set fso = CreateObject("Scripting.FileSystemObject")
 Set folder = fso.GetFolder(path)
 ReDim arrName(folder.files.count - 1)
 ReDim arrDat(folder.files.count - 1)
 ReDim arrPar(folder.files.count - 1)

 For Each file In folder.files
    arrName(n) = file.name
    arrDat(n) = file.DateLastModified
    arrPar(n) = file.parentfolder
    n = n + 1
 Next
 getFile = Array(arrName, arrDat, arrPar)
End Function

It can be tested in the next way:

Sub main()
   Dim file As Variant, path As String
   
   path = "the folder path" 'please use here a real path...
   file = getFile(path) 'future code to list all file information on range
    Debug.Print file(0)(0), file(1)(0), file(2)(0)
End Sub

Collectives™ on Stack Overflow

return multiple values function

2 Answers 2

Get File Info

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Get File Info

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related