Pass a function value as string to a procedure

[brlissab]

Hello everyone.

Trying to pass a string valut from a function to a routine but i don't understand why not my value is not accepted by the procedure

Public Function UsrInput() As String
UserInput = InputBox("Path:")
End Function

Sub TestListFilesInFolder()
    Workbooks.Add ' create a new workbook for the file list
    ' add headers
    With Range("A1")
        .Formula = "Folder contents:"
        .Font.Bold = True
        .Font.Size = 12
    End With
    Range("A3").Formula = "File Name:"
    Range("B3").Formula = "File Size:"
    Range("C3").Formula = "File Type:"
    Range("D3").Formula = "Date Created:"
    Range("F3").Formula = "Date Last Modified:"
    Range("A3:H3").Font.Bold = True
    ListFilesInFolder UsrInput()
    'ListFilesInFolder "G:\Doc"
End Sub

Sub ListFilesInFolder(SourceFolderName As String)
Dim FSO As Scripting.FileSystemObject
Dim SourceFolder As Scripting.Folder
Dim FileItem As Scripting.File
Dim r As Long
    Set FSO = New Scripting.FileSystemObject
    If FSO.FolderExists(SourceFolderName) Then
    Set SourceFolder = FSO.GetFolder(SourceFolderName)
    
    r = Range("A65536").End(xlUp).Row + 1
    For Each FileItem In SourceFolder.Files
        ' display file properties
        Cells(r, 1).Formula = FileItem.Name
        Cells(r, 2).Formula = FileItem.Size
        Cells(r, 3).Formula = FileItem.Type
        Cells(r, 4).Formula = FileItem.DateCreated
        Cells(r, 6).Formula = FileItem.DateLastModified
        r = r + 1 ' next row number
    Next FileItem
    
    Columns("A:H").AutoFit
    End If
    Set FileItem = Nothing
    Set SourceFolder = Nothing
    Set FSO = Nothing
    ActiveWorkbook.Saved = True
End Sub

Remark that the line of code 'ListFilesInFolder "G:\Doc" work perfectly but why not my value from the function?
Thanks for ure advises

 

[PHV]

Replace this:
UserInput = InputBox("Path:")
with this:
UsrInput = InputBox("Path:")

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[mintjulep]

Because your function returns nul.

For the reason highlighted by PHV.

 

[brlissab]

Ok so it is the value inside the function that is used instead the function itselfs. Thanks!
If i would call again the "inputbox" function to have another folder to write on another work book how could i do that without "quiting" the macro?
Thank u for ure advises

 

[Andrzejek]

First I would use [tt]Option Explicit[/tt] at the top of your code.

"so it is the value inside the function that is used instead the function itselfs" - no, you miss-spelled your Function:
[tt]UsrInput[/tt] your function's name
[tt]UserInput[/tt] (undeclared) variable's name, that's why Option Explicit.

Have fun.

---- Andy

 

[brlissab]

Thank u for the explanations!