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?
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.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments