excel - 当我尝试将一个对象添加到集合中时，所有对象值都更改为当前对象，怎么办？

Question

我有一个 Excel 工作簿，我想从“input1”表中读取列值，并根据列值从“input”表中复制行，并将其存储在类对象中。对“input1”工作表中的所有列条目执行此操作。

Private Sub CommandButton1_Click()
    Call PrepareOutput
End Sub
Public Sub PrepareOutput()
   Dim i, indexValue,inputIndexRow As Integer
   Dim bills As New Collection
   inputIndexRow = 2
   indexValue= Worksheets("Input1").Cells(inputIndexRow , 1).Value

   While (Not IsEmpty(indexValue))
      i = indexValue+ 1
      Dim bill As New bill
      bill.quantity = Worksheets("Input1").Cells(inputIndexRow , 2).Value
      bill.cost = Worksheets("Input").Cells(i, 3).Value
      bills.Add bill
      inputIndexRow = inputIndexRow + 1
      indexValue= Worksheets("Input1").Cells(inputIndexRow , 1).Value
   Wend
End Sub

'class Bill has these public variables
Public service As String
Public serialNumber As Byte
Public cost As Double
Public quantity As Byte

Answer 1

您必须在循环中创建新的 bill 实例。您的定义 Dim bill As New bill 声明了变量 bill 并创建了一个实例，但尽管这是在您的循环中，但它并不是为每次迭代都创建一个新实例。

所以把你的代码改成

While (Not IsEmpty(indexValue))
    Dim bill As bill
    set bill = new bill
    bill.quantity = Worksheets("Input1").Cells(inputIndexRow , 2).Value
    ...
Wend