VBA - Trying to understand how to call Class Modules

Question

I'm learning VBA through Google, YouTube, etc.. and I came across Class Modules.

I have a Tracker Template.
Every few days I get a report sent to me ("Ice cream FG Inv.xlsm")

While trying to understand Class Modules, I found a template that created a Class Module (within the Tracker Template) WBIceCreamFGINVxlsm creating a CodeName for all of the worksheets within the Ice Cream FG Inv.xlsm Workbook.

Example:

Public Property Get wsinventory() As Worksheet
    Set wsinventory = Workbook.Worksheets("Inventory")
End Property

In my module, I want to reference wsinventory, but not understanding exactly how to 'call' the Class Module..

Both Workbooks are Open.

I tried to start with:

Dim Data As Variant
    Data = wsinventory.Range("A1").CurrentRegion.Value   (**Variable not Defined**)

Then I tried:

Dim wsinventory As Worksheets
With wsinventory
    Dim Data As Variant
        Data = .Range("A1").CurrentRegion.Value (**Object variable or With variable not set**)
End With

Do I still need to use:

Dim DataSource As Workbook
Set DataSource = Workbooks("Ice Cream FG Inv.xlsm")
    With DataSource.Worksheets("Inventory")
    End With

If so, what would be the reasoning for using Class Modules?

I very well could be! I'm googling VBA like crazy trying to find the best / efficient way to do things.. so, when would be a time to use Class Modules. — CDay, Sep 15 '20 at 18:02
Not this occasion... I'd focus on learning the Excel object model first. — BigBen, Sep 15 '20 at 18:03
Related or duplicate: https://stackoverflow.com/a/1740020/13813219 — JAlex, Sep 17 '20 at 19:45

Warcupine · Accepted Answer · 2020-09-15T18:13:49.663

You need to create a class object before you can access the properties of that class.

Assuming you have this Class and naming it TestClass:

Private pwsinventory As Worksheet

Public Sub init()
    Set pwsinventory = Worksheets("Inventory")
End Sub

Public Property Set wsinventory(lwsinventory As Worksheet)
    Set pwsinventory = lwsinventory
End Property

Public Property Get wsinventory() As Worksheet
    Set wsinventory = pwsinventory
End Property

You can set / get the properties like so:

Sub test()
    Dim datacls As TestClass
    Dim data As Worksheet
    
    Set datacls = New TestClass
    Set datacls.wsinventory = Worksheets("inventory")
    Set data = datacls.wsinventory
    
    Debug.Print data.Name
End Sub

This, however, is kind of weird and when you have a property you don't want to set (you need to pass an argument) you should use an initiate function. Unfortunately there is no way I know of to do this without manually calling that sub after the class object is created.

Sub Test2()
    Dim datacls As TestClass
    
    Set datacls = New TestClass
    
    datacls.init
    
    Debug.Print datacls.wsinventory.Name
End Sub

The most common case I use classes for is better containers. Generally storing many of the same class type inside an array / dictionary so it is clear what I'm calling, especially if I need to modify the data in the same manner for each instance.

score 1 · Answer 2 · answered Sep 17 '20 at 20:34

I am going to give another example. Create a class definition and name it ArrayData, and define multiple initialization subroutines

ArrayData.cls

Private m_data() As Variant

Private Sub Class_Initialize()
        
End Sub

Public Sub IntializeEmpty(ByVal rows As Long, ByVal columns As Long)
    ReDim m_data(1 To count, 1 To columns)
End Sub

Public Sub InitializeFromRange(ByRef target As Range)
    If target.rows.count > 1 Or target.columns.count > 1 Then
        m_data = target.Value2
    Else
        ReDim m_data(1 To 1, 1 To 1)
        m_data(1, 1) = target.Value
    End If
End Sub

Public Sub InitializeFromArray(ByRef data() As Variant)
    m_data = data
End Sub

Public Property Get RowCount() As Long
    RowCount = UBound(m_data, 1) - LBound(m_data, 1) + 1
End Property

Public Property Get ColCount() As Long
    ColCount = UBound(m_data, 2) - LBound(m_data, 2) + 1
End Property

Public Property Get Item(ByVal row As Long, ByVal col As Long) As Variant
    Item = m_data(row, col)
End Property
Public Property Let Item(ByVal row As Long, ByVal col As Long, ByVal x As Variant)
    m_data(row, col) = x
End Property

Module

To test the code in a code module initialize the class with the New keyword and then call one of the custom initialization subroutines.

Public Sub TestArray()

    Dim arr As New ArrayData
    arr.InitializeFromRange Sheet1.Range("A2").Resize(10, 1)
    
    Dim i As Long
    For i = 1 To arr.RowCount
        Debug.Print arr.Item(i, 1)
    Next i

End Sub

PS. Also read this article on how to designate one property as the default. In the example above if Item was the default property then you could write code such as

Debug.Print arr(5,2)

instead of

Debug.Pring arr.Item(5,2)

VBA - Trying to understand how to call Class Modules

2 Answers2

ArrayData.cls

Module