参数不是可选的，参数数量错误或属性赋值无效错误

Question

我希望每次工作簿打开时都运行以下代码。 我收到了一些错误，您可以在我的评论中看到。 我很确定这是我错过的一件非常简单的事情，我一直无法弄清楚。 谢谢你的时间！

Public FSO As Scripting.FileSystemObject
Public SourceFolder As Scripting.Folder, SubFolder As Scripting.Folder
Public FileItem As Scripting.File
Public FolderName As Object
Private Sub Workbook_Open()
'I would like to run this every time the workbook opens. This is where I am getting my Argument Not Optional Error.
Call ListFilesInFolder(SourceFolder)
End Sub
Sub ListFilesInFolder(SourceFolder As Scripting.Folder, Subfolders As Boolean) 'Do I need everything in the parentheses?
Application.ScreenUpdating = False
MsgBox "SOP's Database will update automatically"
Dim r, LstR As Long
Dim SOPRng As Range
Set SOPRng = ActiveSheet.ListObjects("SOPDatabase").Range
ActiveSheet.ListObjects("SOPDatabase").Unlist
SOPRng.ClearFormats
UserName = Environ("UserName")
fPath = "Target File Path"
Set FSO = New Scripting.FileSystemObject
Set SourceFolder = FSO.GetFolder(fPath)
r = 2
On Error Resume Next
For Each FileItem In SourceFolder.Files
Cells(r, 1) = r - 1
Cells(r, 2) = FileItem.Name
Cells(r, 3) = FileItem.Type
Cells(r, 4) = FileItem.DateLastModified
Cells(r, 6).Formula = "=HYPERLINK(""" & FileItem.Path & """,""" & "Click Here to Open" & """)"
r = r + 1
Next FileItem
I can get up to here to work just fine.  Going into the subfolders below is not working.  I am getting  error.
If Subfolders Then
For Each SubFolder In SourceFolder.Subfolders
ListFilesInFolder SubFolder, True '"wrong number of arguments or invalid property assignment" and "ListFilesInFolder" is highlighted
Next SubFolder
End If
'The rest of the macro is working just fine.
Set FileItem = Nothing
Set SourceFolder = Nothing
Set FSO = Nothing
LstR = ActiveSheet.Columns("$B").End(xlDown).Row
Set SOPRng = ActiveSheet.Range("$A$1:$G$" & LstR)
Set SOPDatabase = ActiveSheet.ListObjects.Add(xlSrcRange, SOPRng, , xlYes, , "TableStyleMedium2")
SOPDatabase.Name = "SOPDatabase"
With SOPDatabase.Range
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
End With
With SOPDatabase.HeaderRowRange
.Font.Bold = True
.Font.Size = 14
.Font.Color = vbBlack
.WrapText = True
End With

Application.ScreenUpdating = True
End Sub

可能是我对 VBA 以及如何定义变量缺乏了解。 我仍在努力理解这一部分。

Answer 1

更改

Call ListFilesInFolder(SourceFolder)

自

Call ListFilesInFolder(SourceFolder,True)

这至少应该产生与您当前不同的错误

Answer 2

我认为您发布的代码不会编译。您调用Call ListFilesInFolder(SourceFolder)，但 sub 需要两个参数：源文件夹和子文件夹。您是否在ListFilesInFolder SubFolder, True中收到"参数数错误"错误后将Subfolders参数添加到ListFilesInFolder(SourceFolder As Scripting.Folder, Subfolders As Boolean)？

换句话说，这段代码当前是否编译并运行到ListFilesInFolder SubFolder, True？