VBA | How to create a class module which builds ob

问题:

I am somewhat new to coding and may not use the correct terminology, hopefully, what I say makes sense.

I have created a class module to build objects. My class module currently has some variables (I intend to add some methods as I build upon my code).

'CLASS MODULE NAMED clsNodes

Public i As Single
Public j As Single
Public coll As Collection

My module will build objects based on cell value input by the user

Option Explicit

Dim i As Single, j As Single
Dim ni_nodes As Single, nj_anchors As Single

ni_nodes = range("A1")
nj_nodes = range("A2")

For i = 1 to ni_nodes
    For j = 1 to nj_nodes

        Set node = New clsNodes
        node.i = i
        node.j = j

    Next j
Next i

The current problem with my code is that when it steps through my for loop the object node is overwritten every step it runs through the j for loop.

What I want to do is create a new object or add the object to a collection so I can easily reference the object and its variables. For example in my module I would like to call up my nodes similar to...

1stnode_i_value = node(1).i
3rdnode_j_value = node(3).j

Hopefully, this makes sense... I may not be using the best approach so please enlightened me.

回答1:

You can store your objects in a Collection:

Sub Test()

    Dim Node As clsNodes, i As Long, j As Long
    Dim coll As New Collection, n

    For i = 1 To 5
        For j = 1 To 5

            Set Node = New clsNodes
            Node.i = i
            Node.j = j
            coll.Add Node

        Next j
    Next i

    'list out stored objects
    For Each n In coll
        Debug.Print n.i, n.j
    Next n

End Sub

回答2:

A collection can only be keyed with a String value. You can store an object instance as an item in a collection, but collections come with many shortcomings so go for better options, like an array or a dictionary.

Since we want to keep this simple, I would go with an array. Your instance of the class is overwritten because you are not saving it. To save all of your class instances you can do this:

Option Explicit

Dim i As Single, j As Single
Dim ni_nodes As Single, nj_anchors As Single
dim arr() as variant
dim cnt as integer


ni_nodes = range("A1")
nj_nodes = range("A2")

redim arr(1 to ni_nodes*nj_nodes)
cnt=0
For i = 1 to ni_nodes
    For j = 1 to nj_nodes

        Set node = New clsNodes
        node.i = i
        node.j = j
        cnt=cnt+1
        set arr(cnt)=node
    Next j
Next i

to retrieve your objects from the array you should do this:

dim obj as object
set obj=arr(i) 'i is the index you want

or:

dim obj as clsNode
set obj=new clsNode
set obj=arr(i)