excel - 无法在 Excel VBA 中迭代函数返回的对象

Question

我想在 Excel VBA 中迭代对象。但它显示错误“运行时错误 438。对象不支持此属性或方法”。

Sub FunctionTest()
    
    Dim src, dist, production As Workbook
    Dim files As Object
    
    Set dist = ThisWorkbook
    
    Set files = getFiles(dist.path)
    
    For Each file In files
    
        Debug.Print file.Name
    
    Next
    
End Sub

Function getFiles(path As String) As Object
    
    Dim fsObject, folderObj, FileObj As Object
    
    Set fsObject = CreateObject("Scripting.FileSystemObject")
    Set folderObj = fsObject.GetFolder(path)
    Set filesObj = folderObj.files
    
    Set getFiles = fsObject
    
End Function

但如果它不是这样返回的对象，它会很好地工作。

Function getFiles(path As String) As Object
    
    Dim fsObject, folderObj, FileObj As Object
    
    Set fsObject = CreateObject("Scripting.FileSystemObject")
    Set folderObj = fsObject.GetFolder(path)
    Set filesObj = folderObj.files
    
    For Each file In filesObj
    
        Debug.Print file.Name
    
    Next
    
    Set getFiles = fsObject
    
End Function

标签： excelvbafunctionobject

Answer 1

您的代码返回了错误的对象。它应该是：

 Set getFiles = filesObj