Home>

Sorry for being a duplicate question with My Wisdom Bag (I'm looking for a quick answer)

I'm rewriting the skeleton source to ask questions to improve the existing scheduler

The development language is EXCEL vba

The item class is defined and a function to list items from the worksheet is
Trying to define

(Cls item)
Public id as int
Public todo as string
Sub Main
Dim Items as collection
Set items = new cls items
Dim tbl as object
Set Tbl = sheet1.usedrange
Items.WsRead tbl
Omitted
Question

I want to read the whole sheet and generate an items collection

Currently, functions outside the class are defined, and the loop is turned item by item.

I want to put it in a class method, but the output is not a single object,
I'd like to include multiple lists, but I'm not sure about this.

Public function WsRead (ByVal tbl as object) as collection
Omitted
vba
  • Answer # 1

    The question code example is simple, but you want to use this as a template to implement more complex processing using classes?

    The presentedCls itemis 1 data, so it is not possible to list it.
    If you want to implement a method that loads a list into a class,
    You need a separate class that listsCls items(such as collection or array).

    I have an image of the code.

    'Class module Clss item
    Public id as int
    Public todo as string
    'Class module Clls item list
    Option Explicit
    Dim Items As Collection
    Private Sub Class_Initialize ()
        Set Items = New Collection
    End Sub
    Private Sub Class_Terminate ()
        'Drop the Clss items in the Collection in a loop
    End Sub
    Public Sub WsRead (ByVal tbl As Object)
        Dim item As Clss item
        Dim i As Long
        For i = 1 To tbl.rows.count
            Set item = New Clss item
            Item.id = *****
            Item.todo = *****
            Items.Add item
        Next
    End Sub

    That means you need an item class and an item list class.
    Speaking of Excel objects,
    You can imagine the relationship between the Worksheet object and the WorkSheets object.

  • Answer # 2

    Perhaps you have an image of an array?
    If the class properties are only id and todo, there is almost no merit in defining the class, and it is better to define it in an array.
    There are many merits to define in a class, but the biggest merit is that VBA is effective when items to be handled are complicated, such as data and functions can be grouped together, property can be set, value can be set to other properties, etc. I think.

    However, variables declared in a class can also be handled like arrays.

    Dim a (100) As Class_A
        For i = 0 To 100
            Set a (i) = New Class_A
        Next i

    VBA classes are not all-round, but the way to deal with them is often in power.
    However, forcibly created files will only be difficult to maintain later.
    How about using worksheets well and using each function well?

  • Answer # 3

    Dictionary item defined in class module
    Isn't it possible to store it?

    Set items = New Cls
     items.id = 1
     items.todo = "Tomorrow"
     Dim myDic As Object
     Set myDic = CreateObject ("Scripting.Dictionary")
     myDic.Add "Project1", items