我用一个类创建了一个接口。
我有一个子获取多个工作表，相关的命名范围和行金额，我用这个循环通过数据，我需要分组在一起。
一旦数据被逐行读取，它就示例化对象（类通过从模块接收范围的公共init方法将数据从单元格中取出），我将它们添加到模块中的一个没有键的简单集合中。
在某些情况下，我需要将对象示例彼此关联起来，因为我需要在稍后阶段以稍微不同的方式处理它们（后面的输出项中会有更多列）。
我在数据中添加了一个唯一标识符，该标识符仅由相关示例共享，但我不知道如何从此处开始创建这样的关联。
这是读：

'Create solution is placed between Class creation and sub to define the target sheets & ranges
Option Explicit
Sub ReadData(Solutions As Collection)

Set Solutions = New Collection

Dim Solution As Variant
Dim ws As Worksheet
Dim rng As Range
Dim rowamount As Long

'define length of range
rowamount = Worksheets("source").Range("Named_ranges").Rows.Count

Dim myrow As Integer
Dim suspectWorksheet As String
Dim TargetWorksheet As Worksheet
Dim TargetWorkRange As String
Dim TargetRangeCount As Integer
Dim x As Integer

For myrow = 1 To rowamount

    'Identify the visible sheets from the source matrix & init worksheet
    suspectWorksheet = Worksheets("source").Range("Named_ranges").Cells(myrow, 1)
    Set TargetWorksheet = Worksheets(suspectWorksheet)
    If TargetWorksheet.Visible = True Then
    
        ' Init the range variable and get the max amount of lines to scan
        TargetWorkRange = Worksheets("source").Range("Named_ranges").Cells(myrow, 2)
        TargetRangeCount = Worksheets("source").Range("Named_ranges").Cells(myrow, 3)

        ' Start the lineloop to inject the data into the class
        For x = 1 To TargetRangeCount
            Debug.Print "Loop " & x
            'Is there an active line in the target range?
            If Worksheets(suspectWorksheet).Range(TargetWorkRange).Cells(x + 1, 1) > 0 Then
                Set rng = Worksheets(suspectWorksheet).Range(TargetWorkRange).Resize(1, 60).Offset(x, 0)
                Set Solution = solutionClassFactory(rng)
                Solutions.Add Solution
                 
                'Solution.PrintOut
            End If
        Next x
    End If
Next myrow

Set TargetWorksheet = Nothing
End Sub

' Checks the type of solution and returns into a class
Function solutionClassFactory(rng As Range) As Variant

Dim solutionType As String

solutionType = rng.Cells(1, 51)

Dim Solution As Variant
Select Case solutionType
    Case "something":
        Set Solution = New something
End Select

Solution.Init rng

' solution is returned to be added to the main collection
Set solutionClassFactory = Solution

End Function

这是写作部分：

Sub Create()
Dim Solution As Variant
Dim Solutions As Collection
Dim TargetWorksheet As String
Dim i As Integer
'Define to which sheet it needs to be written
TargetWorksheet = "sheet"

ReadData Solutions
i = 5

For Each Solution In Solutions

    Worksheets(TargetWorksheet).Cells(i, 1) = Solution.amount
    'more

    i = i +1

Next Solution

End Sub

出于性能原因，我不想恢复到循环中的循环。
分类代码

' class derived from Solution interface
Option Explicit

 ' Implements Solution interfacs
Implements Solution

Private amount_ As Integer
Private amountRef_ As String

Private Sub Class_Initialize()

End Sub

Public Sub Init(rng As Range)
    amount_ = rng.Cells(1, 1)
    amountRef_ = "'" & rng.Parent.Name & "'!" & rng.Columns.Item(1).address
End Sub

Public Sub PrintOut()
Debug.Print amount_, TypeName(Me), linekey_ & vbNewLine;
Debug.Print amountRef_, TypeName(Me), linekeyRef_ & vbNewLine;
End Sub

Private Sub Class_Terminate()
    ' Debug.Print "WAN class instance deleted"
End Sub

Public Property Get amount() As Integer
    amount = amount_
End Property

Public Property Let amount(ByVal Value As Integer)
    amount = amount_
End Property

Public Property Get linekeyRef() As String
    linekeyRef = linekeyRef_
End Property

Public Property Let linekeyRef(ByVal Value As String)
    linekeyRef = linekeyRef_
End Property

' Implement required interface properties
Private Property Get Solution_address() As String
    Solution_address = address
End Property