你的数组从未被初始化，所以它不能接收数据。然后你需要在插入每个新元素之前对它进行Redim。最后，你需要将数组转置为“列”格式，否则它将是“行”格式，你的INDEX函数将无法工作。

Function GetAllFileNames(ByVal FolderPath As String) As Variant
    Dim Result As Variant
    Dim FileName As Variant
    Dim i As Integer
    
    FileName = Dir(FolderPath)
    
    ReDim Result(1 To 1)  'Initialize array
    
    i = 1
    Do While FileName <> ""
        Result(i) = FileName
        i = i + 1
        FileName = Dir()
        If FileName <> "" Then ReDim Preserve Result(1 To UBound(Result) + 1)
    Loop
    
    GetAllFileNames = Application.Transpose(Result)  'Transpose array

End Function

也可以使用FileSystemObject

Function GetAllFileNames(FolderPath As String) As Variant
    Dim FSO As Object 'Or you can use New FileSystemObject   'Reference: Microsoft Scripting Runtime
    Dim myFolder As Object 'Folder
    Dim mySubFolder As Object 'Folder
    Dim MyFile As Object 'File
    Dim i As Long
    Dim Result As Variant

    Set FSO = CreateObject("Scripting.FileSystemObject")
    Set myFolder = FSO.GetFolder(FolderPath)
    
    ReDim Result(1 To myFolder.Files.Count)
    i = 1
    For Each MyFile In myFolder.Files
        Result(i) = MyFile.Name
        i = i + 1
    Next MyFile

    GetAllFileNames = Application.Transpose(Result)
End Function