Compile error "wrong number of arguments or invalid property assignment" passing folder object to recursive sub

Question

This fills in a worksheet based on recursively looking at files in sub folders. Encountering this error when Recur sub is called, searched similar threads with no luck, trying to figure out what I'm missing here. As far as I can tell, the calls to Recur match its parameter. Am I missing something obvious? Thanks.

Public Wb As Workbook
Public Ws As Worksheet
Public CLP As String
''''''''''''''''''''''''''''''''''''''''''''''''''
Function GFold(Ttl, Dflt As String) As String
''''''''''''''''''''''''''''''''''''''''''''''''''
Dim FDlog As FileDialog
Dim FStr As String

Set FDlog = Application.FileDialog(msoFileDialogFolderPicker)
With FDlog
    .Title = Ttl
    .AllowMultiSelect = False
    .InitialFileName = Dflt
    If .Show <> -1 Then GoTo NextCode
        FStr = .SelectedItems(1)
    End With
NextCode:
    GFold = FStr
    Set FDlog = Nothing
End Function
''''''''''''''''''''''''''''''
Sub CheckOffDocs()
''''''''''''''''''''''''''''''
Dim MyFSO, MyFld As Object
Dim Wb As Workbook
Dim Path, CLPath As String

' • Get folder path
Path = GFold("Select Parent Folder", Application.DefaultFilePath)

' • Check folder path
If Len(Path) = 0 Then
    Exit Sub
End If

' • Create file sys obj
Set MyFSO = CreateObject("Scripting.FileSystemObject")
' • Get ahold of folder at path address
Set MyFld = MyFSO.getfolder(Path)

Set Wb = ActiveWorkbook
Set Ws = Wb.Sheets(1)

'• prompt clear checks input
If MsgBox("Clear existing inventory?", vbQuestion + vbYesNo) = vbYes Then
    For c = 1 To 2
        For r = 14 To 113
            If Len(Ws.Cells(r, (c * 3) - 2).Value) > 1 Then
                Ws.Cells(r, c * 3).ClearContents
            End If
        Next r
    Next c
End If

'• pass folder to recur
Call Recur(MyFld)

'• clean up
Set MyFld = Nothing
Set MyFSO = Nothing

' • Set default location and prompt for save as info
SvName = CLPath & "L01 Contract File Checklist.xlsm"
SvName = Application.GetSaveAsFilename(InitialFileName:=SvName, fileFilter:="Excel files (*.xlsm), *.xlsm")
' • Check valid info
If Len(SvName) > 0 And InStr(SvName, "FALSE") = 0 And SvName <> False Then
    If Left(UCase(Ws.Cells(1, 14).Value), 3) = "L01" Then Ws.Cells(1, 14).Value = "X"
    Wb.SaveAs Filename:=SvName, FileFormat:=xlOpenXMLWorkbookMacroEnabled
Else
    MsgBox "File not saved."
End If

End Sub
''''''''''''''''''''''''''''''''''''''''''''''''''
Sub Recur(ByVal Fld As Object)
''''''''''''''''''''''''''''''''''''''''''''''''''
Dim r, c As Integer
Dim MySub, MyFIle As Object

'• Recursively loop all folders
For Each MySub In Fld.subfolders
    Call Recur(MySub)
Next MySub

If InStr(UCase(Fld.Name), "CHECKLIST") > 0 Then
    CLP = Fld.Path & "\"
End If

For Each MyFIle In Fld.Files
    For c = 1 To 2
        For r = 14 To 113
            '• Check for valid code row and match to filename
            If Len(Ws.Cells(r, (c * 3) - 2).Value) > 1 And UCase(Left(MyFIle.Name, 3)) = UCase(Ws.Cells(r, (c * 3) - 2).Value, 3) Then
                '• Mark "x" column
                Ws.Cells(r, (c * 3)).Value = "X"
                '• Bail out of loops after match
                GoTo bail
            End If
        Next r
    Next c
bail:
Next MyFIle

End Sub

Note that in VBA, you need to specify the data type for each variable in declarations: `Dim MySub, MyFIle As Object` should be `Dim mySub as Object, MyFIle as Object`, and similarly elsewhere. — BigBen, Mar 19 '20 at 20:31
Check out this post https://stackoverflow.com/questions/22645347/loop-through-all-subfolders-using-vba — ACCtionMan, Mar 19 '20 at 20:56
There is a Left missing here - UCase(ws.Cells(r, (c * 3) - 2).Value, 3), It should be UCase(Left(ws.Cells(r, (c * 3) - 2).Value, 3)) — CDP1802, Mar 19 '20 at 21:07
@BigBen sorry, I miss read your comment (I blame reading on the phone!) I _thought_ you wrote that `MySub` would be an `Object` when Dim'd like the OP did. When in fact you wrote change it to that. — chris neilsen, Mar 20 '20 at 01:48

BigBen · Accepted Answer · 2020-03-20T18:08:23.717

0

Note that in VBA, you need to specify the data type for each variable in declarations, otherwise they are Variants.

Change

Dim MySub, MyFIle As Object

to

Dim MySub As Object, MyFIle As Object

and similarly elsewhere.

edited Mar 20 '20 at 18:08

answered Mar 20 '20 at 18:02

BigBen

46,229
7
24
40

Compile error "wrong number of arguments or invalid property assignment" passing folder object to recursive sub

1 Answers1