結論

Dir関数を使用してタイトルにある関数を作成できます。

Dir関数

Dir関数は、「引数に指定したファイルパスに一致したファイル」のうちの1ファイルのファイル名を取得する関数です。
また、引数を指定しない場合、「最後に指定したファイルパスに一致したファイル」のうち、まだ取り出していない1ファイルのファイル名を取得します。
引数の有無にかかわらず、該当するものがない場合は空文字が返ってきます。
この関数を利用してタイトルにある関数を作成します。

前提

このVBA記述されたエクセルファイルと同階層に「test」というディレクトリがあるとします。
また、そのディレクトリのファイル構成は以下です。

エクセル1.xlsx
エクセル2.xlsx
エクセル3.xlsx
エクセル4.xlsx
エクセル5.xlsx
テキスト1.txt
テキスト2.txt
テキスト3.txt
テキスト4.txt
テキスト5.txt

サンプルコード

Sub 検証()
    Dim DirectoryName As String: DirectoryName = "test"
    Dim FileNames() As String
    'ThisWorkbook.Pathは、開いているエクセルファイルのファイルパス
    FileNames = GetFileNamesInDirectory(ThisWorkbook.Path & "\" & DirectoryName)

    '空配列チェック
    If (Not FileNames) = -1 Then
        Exit Sub
    End If
    For i = LBound(FileNames) To UBound(FileNames)
        Debug.Print (FileNames(i))
    Next
End Sub

Private Function GetFileNamesInDirectory(ByRef DirectoryPath As String) As String()
    Dim tmp() As String
    Dim i As Long: i = 1

    '指定したファイルパスに一致したもののうち、1ファイルのファイル名を取り出す
    Filename = Dir(DirectoryPath & "\")
    While Filename <> ""
        ReDim Preserve tmp(1 To i)
        tmp(i) = Filename
        i = i + 1
        '最後に指定したファイルパスに一致したもののうち、まだ取り出していない1ファイルのファイル名を取り出す
        Filename = Dir
    Wend

    GetFileNamesInDirectory = tmp
End Function

結果

エクセル1.xlsx
エクセル2.xlsx
エクセル3.xlsx
エクセル4.xlsx
エクセル5.xlsx
テキスト1.txt
テキスト2.txt
テキスト3.txt
テキスト4.txt
テキスト5.txt

無事、ディレクトリ内のファイルを取得できました。

補足

上記の関数では指定パスのディレクトリに含まれているファイルだけが取得できます。「指定パスのディレクトリに含まれているディレクトリ」は取得できません。
Dir関数で指定するパスはディレクトリでなく、ファイルパスも指定できます。そしてワイルドカードも使用できます。これを応用すると、エクセルファイルに絞り込んで配列に格納することもできます。

Sub 検証2()
    Dim DirectoryName As String: DirectoryName = "test"
    Dim FileNames() As String
    'ThisWorkbook.Pathは、開いているエクセルファイルのファイルパス
    FileNames = GetExcelFileNamesInDirectory(ThisWorkbook.Path & "\" & DirectoryName)

    '空配列チェック
    If (Not FileNames) = -1 Then
        Exit Sub
    End If
    For i = LBound(FileNames) To UBound(FileNames)
        Debug.Print (FileNames(i))
    Next
End Sub

Private Function GetExcelFileNamesInDirectory(ByRef DirectoryPath As String) As String()
    Dim tmp() As String
    Dim i As Long: i = 1

    '指定パスのファイルを検索して、1ファイルのファイル名を取り出す
    Filename = Dir(DirectoryPath & "\" & "*.xls*")
    While Filename <> ""
        ReDim Preserve tmp(1 To i)
        tmp(i) = Filename
        i = i + 1
        '最後にパスを指定したフォルダの中から、まだ取り出していない1ファイルのファイル名を取り出す
        Filename = Dir
    Wend

    GetExcelFileNamesInDirectory = tmp
End Function

エクセル1.xlsx
エクセル2.xlsx
エクセル3.xlsx
エクセル4.xlsx
エクセル5.xlsx

以上です。

結論

Dir関数

前提

サンプルコード

結果

補足

参考