Dalla versione 2007 di Excel è stata eliminata la propietà FileSearch dell'oggetto Application che permetteva, tra l'altro, di avere il numero di file presenti in una cartella. L'alternativa, adesso, è usare l'oggetto Scripting.FileSystemObject e le sue relative proprietà. Di seguito la funzione che conta i file presenti nella cartella corrente.

Function ContaFile() As Integer
    Dim objFSO As Object
    Dim objFolder As Object
    Dim objFile As Object
    
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set objFolder = objFSO.GetFolder(Application.ActiveWorkbook.Path)
    ContaFile= objFolder.Files.Count
End Function

A questo link un file con la macro che permette di selezionare una cartella ed elencare tutti i file in essa presenti ed il risultato viene inserirto in un foglio Excel