Contagem de arquivos

Sub AleVBA_1002()
    'Seu diretorio
    'Eu estou usando Linux
    [A1].value = FileCountA("/home/oracle/Downloads/")
    'Para Windows tente
    '[A1].value = FileCountA("C:\temp\")
End Sub

Function FileCountA(Path As String) As Long
    Dim strTemp As String
    Dim lngCount As Long
   
    strTemp = Dir(Path & "*.*")
    Do While strTemp <> ""
        lngCount = lngCount + 1
        strTemp = Dir
    Loop
   
    FileCountA = lngCount
   
End Function