对一个进程使用For循环，并在for循环Excel VBA中对相同进程调用该循环

Question

我使用COM-接口从第3部分应用程序中获取参数名称和值。

我正在使用这个VBA脚本来获取Model的独立参数文件名，以及这个组中的值、参数组名称和参数组信息以及参数组信息，在这个组中，在其结束的任何地方进行相同的处理。

还有附加在主模型上的子模型，我也在为它们做同样的过程。

我对循环的每个级别都使用不同的变量，但是每个级别的过程都是相同的，所以我想学习一下，难道没有简单的方法可以得到它吗？不使用变量的第二级和第三级参数组和子模型。

我函数的拓扑结构

​

如果你看一下结构的拓扑结构，你就能理解我的意思。对于相同的过程，我使用相同的函数，但对于子级参数组，我有困难，我为它们使用了一个额外的变量，例如；

    Dim j1 As Integer               ' Parameter Group of Parameter Group Item / Count
    Dim j2 As Integer               ' Parameter Group of Parameter Group of Parameter Group Item / Count

   Dim ParameterGroupsX1 As Object      '2nd Level Parameter Groups Objects
    Dim ParameterGroupX1 As Object       '2nd Level Parameter Group
    Dim nParameterGroupX1 As Integer     'Number of 2nd Level Parameter Group Objects

    Dim ParameterGroupsX2 As Object      '3rd Level Parameter Groups Objects
    Dim ParameterGroupX2 As Object       '3rd Level Parameter Group
    Dim nParameterGroupX2 As Integer     'Number of 3rd Level Parameter Group Objects

同时，我对子模型也使用了相同的方法

Dim s1 As Integer               ' Sub Model 1st Level
Dim s2 As Integer               ' Sub Model 2nd Level

Dim SubstrsX1 As Object           ' 2nd Level Substructures Object
Dim SubstrX1 As Object            ' 2nd Level Substructure
Dim nSubstrX1 As Integer          ' Number of 2nd Level Substructure Objects

Dim SubstrsX2 As Object         ' 3rd Level Substructures Object
Dim SubstrX2 As Object          ' 3rd Level Substructure
Dim nSubstrX2 As Integer        ' Number of 3rd Level Substructure Objects

我认为我可以调用函数或For循环，而无需使用上述变量来定义子级别的参数组和参数。

你能告诉我如何对我的拓扑做同样的处理吗？

守则列示如下；

Dim Target_Sheet As Worksheet
Dim Main_Sheet As Worksheet

Dim ParameterGroups As Object        ' 1st Level Parameter Groups Object
Dim ParameterGroup As Object         ' Single Parameter Group
Dim nParameterGroup As Integer       ' Number of Parameter Group Objects

Dim Parameters As Object             ' 1st Level Parameters Object
Dim Parameter As Object              ' 1st Level Parameter
Dim nParameter As Integer            ' Number of Parameters Objects

Dim ParameterGroupsX1 As Object      '2nd Level Parameter Groups Objects
Dim ParameterGroupX1 As Object       '2nd Level Parameter Group
Dim nParameterGroupX1 As Integer     'Number of 2nd Level Parameter Group Objects

Dim ParameterGroupsX2 As Object      '3rd Level Parameter Groups Objects
Dim ParameterGroupX2 As Object       '3rd Level Parameter Group
Dim nParameterGroupX2 As Integer     'Number of 3rd Level Parameter Group Objects

Dim Substrs As Object             ' 1st Level Substructures Object
Dim Substr As Object              ' 1st Level Substructure
Dim nSubstr As Integer            ' Number of 1st Level Substructure Objects

Dim SubstrsX1 As Object           ' 2nd Level Substructures Object
Dim SubstrX1 As Object            ' 2nd Level Substructure
Dim nSubstrX1 As Integer          ' Number of 2nd Level Substructure Objects

Dim SubstrsX2 As Object         ' 3rd Level Substructures Object
Dim SubstrX2 As Object          ' 3rd Level Substructure
Dim nSubstrX2 As Integer        ' Number of 3rd Level Substructure Objects

Dim i As Integer                ' Parameter Item/Count
Dim j As Integer                ' Parameter Group Item / Count
Dim j1 As Integer               ' Parameter Group of Parameter Group Item / Count
Dim j2 As Integer               ' Parameter Group of Parameter Group of Parameter Group Item / Count
Dim s As Integer                ' Sub Model Main Level
Dim s1 As Integer               ' Sub Model 1st Level
Dim s2 As Integer               ' Sub Model 2nd Level
Dim cLine As Long

Option Explicit


Sub ReadParameterSimpack()          ' Main function get parameter information
Set Target_Sheet = ThisWorkbook.Worksheets("Parameters")  ' Parameter's page which i list the names and their values
Call Setup_Module.SetupSimpack          ' This is the module where i get the model information from third part Application
cLine = 0

Set Parameters = Mdl.getParameterList(False)  ' Gettting Parameters List
Call ParameterRead(Parameters, cLine)           ' Calls the Parameter Read Function

Set ParameterGroups = Mdl.getParameterGroupList(False)  ' Getting the Parameter Group List
Call ParameterGroupRead(ParameterGroups, cLine)         ' Calls the Parameter Group Read Function

Set Substrs = Mdl.getSubstrList(False) ' Getting the Sub-Model List
Call SubstructureRead(Substrs, cLine) ' Calls the Model's submodel list

End Sub


Sub ParameterRead(Parameters, cLine)  ' This is the function of reading Parameters

nParameter = Parameters.Count         ' Number of Parameters

For i = 0 To nParameter - 1
Set Parameter = Parameters.Item(i)    ' Getting parameter from the List of Parameter
Target_Sheet.Cells(cLine + 1, 1).Value = Parameter.FullName  ' Writes parameter name to Excel Sheet
cLine = cLine + 1
Next i

End Sub


Sub ParameterGroupRead(ParameterGroups, cLine) ' This is the function of reading Parameter Groups
nParameterGroup = ParameterGroups.Count     ' This is the number of Parameter Groups
For j = 0 To nParameterGroup - 1
Set ParameterGroup = ParameterGroups.Item(j)  'Getting Parameter Group from Parameter Groups List
Target_Sheet.Cells(cLine + 1, 1).Value = ParameterGroup.FullName  ' Writes parameter group name to Excel Sheet
cLine = cLine + 1
Set Parameters = ParameterGroup.getParameterList(False)  'This is getting Parameters listed below the Parameter Group
Call ParameterRead(Parameters, cLine)           ' Calls the parameter read function to get parameters which listed under Parameter groups
cLine = cLine + 1
Set ParameterGroupsX1 = ParameterGroup.getParameterGroupList(False) ' Sub-Level Parameter Group
nParameterGroupX1 = ParameterGroupsX1.Count     ' Number of Sub-Level Parameter Group

For j1 = 0 To nParameterGroupX1 - 1
Set ParameterGroupX1 = ParameterGroupsX1.Item(j1)
Target_Sheet.Cells(cLine + 1, 1).Value = ParameterGroupX1.FullName

Set Parameters = ParameterGroupX1.getParameterList(False)   ' Getting the Parameters of Parameter Groups
Call ParameterRead(Parameters, cLine)                       ' Call Parameter Read Function
cLine = cLine + 1

Set ParameterGroupsX2 = ParameterGroupX1.getParameterGroupList(False) 'Sub SubLevel of Parameter Group
nParameterGroupX2 = ParameterGroupsX2.Count
For j2 = 0 To nParameterGroupX2 - 1
Set ParameterGroupX2 = ParameterGroupsX2.Item(j2)
Target_Sheet.Cells(cLine + 1, 1).Value = ParameterGroupX2.FullName
cLine = cLine + 1
Set Parameters = ParameterGroupX2.getParameterList(False)
Call ParameterRead(Parameters, cLine)
cLine = cLine + 1
Next j2
Next j1
Next j

End Sub

Sub SubstructureRead(Substrs, cLine)
nSubstr = Substrs.Count
For s = 0 To nSubstr - 1
Set Substr = Substrs.Item(s)
Target_Sheet.Cells(cLine + 1, 1).Value = Substr.FullName
cLine = cLine + 1
Set Parameters = Substr.getParameterList(False)
Call ParameterRead(Parameters, cLine)
Set ParameterGroups = Substr.getParameterGroupList(False)
Call ParameterGroupRead(ParameterGroups, cLine)

Set SubstrsX1 = Substr.getSubstrList(False)
nSubstrX1 = SubstrsX1.Count

For s1 = 0 To nSubstrX1 - 1
Set SubstrX1 = SubstrsX1.Item(s1)
Target_Sheet.Cells(cLine + 1, 1).Value = SubstrX1.FullName
cLine = cLine + 1
Set Parameters = SubstrX1.getParameterList(False)
Call ParameterRead(Parameters, cLine)
Set ParameterGroups = SubstrX1.getParameterGroupList(False)
Call ParameterGroupRead(ParameterGroups, cLine)

Set SubstrsX2 = Substr.getSubstrList(False)
nSubstrX2 = SubstrsX2.Count

For s2 = 0 To nSubstrX2 - 1
Set SubstrX2 = SubstrsX2.Item(s2)
Target_Sheet.Cells(cLine + 1, 1).Value = SubstrX2.FullName
cLine = cLine + 1
Set Parameters = SubstrX2.getParameterList(False)
Call ParameterRead(Parameters, cLine)
Set ParameterGroups = SubstrX2.getParameterGroupList(False)
Call ParameterGroupRead(ParameterGroups, cLine)

Next s2
Next s1
Next s

结束子对象

Answer 1

如果不了解您所拥有的类/对象，则只能给出示例。我认为在您的情况下，您需要2个递归Subs。模型的前1位，然后恢复ParameterGroup。

Option Explicit

Private oFSO As Object

Sub ListFilesFromRootFolder()
    ' FileSystemObject: https://msdn.microsoft.com/en-us/library/6tkce7xa(v=vs.84).aspx
    Set oFSO = CreateObject("Scripting.FileSystemObject")
    ' e.g. List all files and subfolders from User's Temp folder
    ListFilesAndFolders oFSO.GetFolder(Environ("TMP"))
    Set oFSO = Nothing
End Sub

Private Sub ListFilesAndFolders(oFDR As Object)
    Dim oFile As Object, oSubFDR As Object
    ' Prints current Folder path
    Debug.Print oFDR.Path
    ' List the files in this folder
    For Each oFile In oFDR.Files
        Debug.Print oFile.Name
    Next
    ' Recurse SubFolders
    For Each oSubFDR In oFDR.SubFolders
        ListFilesAndFolders oSubFDR
    Next
End Sub