Excel VBA集合保持重复最后一个对象 [英] Excel VBA Collection Keeps Repeating Last object

问题描述

我有一个奇怪的问题，试图填充我的对象集合。当我为 PromptsRange.Rows 的每个循环做一切都是完美的。您可以通过 NewPrompt 区域，并观察每一行通过，并最终添加到 PromptsCollection 中。我遇到的问题就是这一点。如果您尝试为 PromptsCollection 上的每个循环执行一次，则每个对象（36）完全相同，是查找表中的最后一个值。我陷入困境也许其中一位学者可以帮助我。

I am having a strange issue trying to populate my object collection. When I am doing the for each loop for PromptsRange.Rows everything is perfect. You can step through the NewPrompt area and watch each row pass through and ultimately get added to PromptsCollection at the end. The problem I have is after this point. If you try to do a for each loop on PromptsCollection, each object(36) is the exact same and is the last values from the lookuptable. Im stumped. Perhaps one of the scholars can help me out.

这是我的lookuptable

Here's my lookuptable

以下代码位于userform模块内。
选项显式

The following code is inside a userform module. Option Explicit

Private pPromptsCollection As New Collection
Private pProductPromptMapping As New clsOrderPromptRow
Private pOrderPrompts As New clsOrderPromptRow
Private pTarget As Range
Private pSKU As String

Public Property Get PromptsCollection() As Collection
    Set PromptsCollection = pPromptsCollection
End Property

Public Property Let PromptsCollection(Value As Collection)
    Set pPromptsCollection = Value
End Property

Private Sub SetPromptControls()
Dim PromptsRange As Range
Dim PromptRow As Range

Set PromptsRange = Range("LookUpTablePrompts")

For Each PromptRow In PromptsRange.Rows
    Dim NewPrompt As New clsPrompt
    NewPrompt.Name = PromptRow.Cells(1, 1)
    NewPrompt.ControlType = PromptRow.Cells(1, 2)
    NewPrompt.ComboboxValues = PromptRow.Cells(1, 3)
    NewPrompt.HelpText = PromptRow.Cells(1, 4)
    NewPrompt.TabIndex = PromptRow.Cells(1, 5)
    NewPrompt.ColumnIndex = PromptRow.Cells(1, 6)
    NewPrompt.TableIndex = PromptRow.Cells(1, 7)
    NewPrompt.ControlName = PromptRow.Cells(1, 8)

    PromptsCollection.Add NewPrompt, CStr(NewPrompt.Name)
Next
PromptsCollection.Count
End Sub

所以现在这里是我得到的问题。这将导致36个对象都相同。

So now here is where i get the problem. This will result with 36 objects all the same.

Dim Prompt As New clsPrompt

For Each Prompt In PromptsCollection
    MsgBox (Prompt.Name)

Next

我甚至把它推到了观察窗口，以验证所有的对象是否相同。它总是与表的最后一行底部抽屉正面高度。

i even pushed it to the watch window to verify all the objects were in face identical. It always results with the last row of the table Bottom Drawer Front Height.

希望这是很清楚的。感谢提前。

Hope this is clear enough. Thanks in advance.

推荐答案

当使用 As New 时，VBA将创建第一次使用对象的新实例。您应该避免使用 As New 并使用此模式：

When using As New , VBA will create a new instance of an object the first time it is used. You should avoid using As New and use this pattern instead:

Dim NewPrompt As clsPrompt

For Each PromptRow In PromptsRange.Rows
    Set NewPrompt = New clsPrompt

这篇关于Excel VBA集合保持重复最后一个对象的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！