VBA Optional Arguments - Return PDF Name When It Contains Certain Text?-CodePudding

I am currently trying to write VBA code that looks through a folder for a document and then returns the name of that document when it is found. I've scoured the internet and can't find much. I am doing this by using a code for returning existence and trying to add optional arguments to then use a function to return the file name.

The original code for the test of existence is:

Private Sub tester()
Dim FSO As Object, FolDir As Object, FileNm As Object, LastRow As Integer
Dim Flag As Boolean, WS As Worksheet, Cnt As Integer
Set FSO = CreateObject("scripting.filesystemobject")
Set FolDir = FSO.GetFolder("FILE PATH")
Set WS = Sheets("SHEET NAME")
With WS
    LastRow = .Range("A" & .Rows.Count).End(xlUp).Row
End With
For Cnt = 1 To LastRow
Flag = False
For Each FileNm In FolDir.Files
If FileNm.Name Like "*" & ".pdf" Then
If InStr(FileNm.Name, CStr(WS.Cells(Cnt, "A"))) Then
WS.Cells(Cnt, "B") = "YES"
Flag = True
Exit For
End If
End If
Next FileNm
If Not Flag Then
WS.Cells(Cnt, "B") = "NO"
End If
Next Cnt
Set FolDir = Nothing
Set FSO = Nothing
End Sub

It works perfectly well. I then tried some optional arguments but I can't figure out how to adapt this and I don't think I really understand how optional arguments work. Right now I have:

Private Function GetPdfName( _
    ByRef File As String, _
    Optional ByRef Flag As Variant) As String
    Dim FSO As Object, FolDir As Object, FileNm As Object, LastRow As Integer
    Dim Flag As Boolean, WS As Worksheet, Cnt As Integer
    Set FolDir = FSO.GetFolder("FILE PATH")
    Dim FileName As String
    Dim FSO As New FileSystemObject
    Set FSO = CreateObject("scripting.filesystemobject")
    Flag = False
    Set WS = Sheets("sheet4")
    For Each FileNm In FolDir.Files
        If FileNm.Name Like "*" & ".pdf" Then
            If InStr(FileNm.Name, CStr(WS.Cells(Cnt, "A"))) Then
            WS.Cells(Cnt, "B") = FileName
            Flag = True
            Exit For
            End If
        End If
    Next FileNm
    If Not Flag Then
        WS.Cells(Cnt, "B") = "NO"
    End If
    Next Cnt
    Set FolDir = Nothing
    Set FSO = Nothing
    
    FileName = FSO.GetFileName("FILE PATH")


End Function

Private Sub FSOGetFileName()
    GetPdfName( _
        With WS
        LastRow = .Range("A" & .Rows.Count).End(xlUp).Row
        End With
        For Cnt = 1 To LastRow)

End Sub

The Sub is returning an error on expected expression and I can't figure out how to fix this. Can someone please help me or explain optional arguments and how to best format them in code?

CodePudding user response：

Not sure what optional parameter you need. Only 2 parameters are required, the folder to search and the target range for the results.

Option Explicit

Sub tester()

    Const FILE_PATH = "c:\temp\"
    Const SHEET_NAME = "Sheet1"

    Dim FSO As Object, FolDir As Object
    Set FSO = CreateObject("Scripting.FileSystemObject")
    Set FolDir = FSO.GetFolder(FILE_PATH)
    
    Dim ws As Worksheet, r As Long, lastrow As Long
    Set ws = Sheets(SHEET_NAME)
    With ws
        lastrow = .Range("A" & .Rows.Count).End(xlUp).Row
        For r = 1 To lastrow
            Call GetPdfName(FolDir, .Cells(r, "A"))
        Next
    End With
End Sub

Function GetPdfName(oFolder As Object, rng As Range)

    Dim oFile As Object, fname As String, s As String
    For Each oFile In oFolder.Files
        fname = oFile.Name
        If fname Like "*.pdf" Then
            s = Left(fname, Len(fname) - 4) ' remove .pdf
            If InStr(1, s, CStr(rng), vbTextCompare) Then
                rng.Offset(0, 1) = oFile.Name
                rng.Offset(0, 2) = True
                Exit Function
            End If
        End If
    Next
    rng.Offset(0, 1) = ""
    rng.Offset(0, 2) = False

End Function