在 VBA 中的自调用函数中返回数组 [英] Return Array in Self-Calling Function in VBA

问题描述

我想弄清楚如何在调用自身的函数中返回一个字符串数组.该功能可查看特定路径中的所有文件夹和子文件夹，以生成文本文件及其所在位置的列表.现在，子程序是All_Folder.我以前曾尝试使 All_Folder 成为一个函数，并尝试从该函数返回值，但这无济于事.这是我现在的代码:

I am trying to figure out how to return a string array in a function that calls itself. It is a function that looks through all folders and subfolders within a specific path to generate a list of text files and where they are located. Right now, the subroutine is All_Folder. I have previously tried making All_Folder a function, and trying to return the value from the function, but that has gotten me nowhere. This is my code now:

Public file_locations() As String
Sub Some_Subroutine()
    Dim pathway As String: pathway = "C:\Users"
    Dim FileSystem As Object
    Set FileSystem = CreateObject("Scripting.FileSystemObject")
    All_Folder FileSystem.GetFolder(pathway)

    ...

End Sub
Public Sub All_Folder(Folder)
    Dim filetype as string: filetype = "*.txt"
    Dim Counter As Integer: Counter = 0
    Dim SubFolder: Dim File
    For Each SubFolder in Folder.SubFolders
        All_Folder SubFolder
    Next
    For Each File in Folder.Files
        If Dir(Folder & "\" & filetype) <> "" Then
            ReDim Preserve file_locations(Counter)
            file_locations(Counter) = CallByName(File, Path, VbGet)
            Counter = Counter + 1
        End If
    Next
End Sub

我的问题是每次运行这个函数时，file_locations 中的输出总是被下一个调用目录中的内容覆盖.

My problem is that every time this function is ran, the output in file_locations is always overwritten by what is in the next called directory.

我在子例程 All_Folder 结束时得到了列表，我只需要能够将它存储/附加到当前的 file_locations 列表中.

I have the list by the end of the subroutine All_Folder, I just need to be able to store/append it to the current file_locations list.

推荐答案

As Tim Williams 和 PeH 建议，我没有将我的 Counter 设置为全局多变的.这是更新后的代码:

As Tim Williams and PeH suggested, I had not set my Counter as a global variable. Here would be the updated code:

Public file_locations() As String
Public Counter As Integer
Sub Some_Subroutine()
    Dim pathway As String: pathway = "C:\Users"
    Counter = 0
    Dim FileSystem As Object
    Set FileSystem = CreateObject("Scripting.FileSystemObject")
    All_Folder FileSystem.GetFolder(pathway)

    ...

End Sub
Public Sub All_Folder(Folder)
    Dim filetype as string: filetype = "*.txt"
    Dim SubFolder: Dim File
    For Each SubFolder in Folder.SubFolders
        All_Folder SubFolder
    Next
    For Each File in Folder.Files
        If Dir(Folder & "\" & filetype) <> "" Then
            ReDim Preserve file_locations(Counter)
            file_locations(Counter) = CallByName(File, Path, VbGet)
            Counter = Counter + 1
        End If
    Next
End Sub

感谢你们两个帮助我.

这篇关于在 VBA 中的自调用函数中返回数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！