excel - 当我尝试将对象添加到集合中时，所有对象值都更改为当前对象，如何？

Question

我有一个 excel 工作簿，我想从“input1”表中读取列值，并根据列值从“输入”表中复制行，并将其存储在类对象中。对“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

标签： excelvba

Answer 1

您必须在循环中创建新的账单实例。您的定义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