Class Module in VBA – Create Array Class
Class module in VBA can be used to create our own objects, We can write the properties and methods in class module and later in VBA program we can create objects of this class.
After the object is initialized, we can use all the properties and method written in that class. For example, Range is a class and when we create Range object, we can use all the proeprties and method to manipulate range. Likewise, we can create the class for Array. So, Lets do it.
First of all, we need to insert a class module from “Insert” menu. Once it is inserted, we can name the class (in this example it is ArrayClass)
So Basically, What i want to do is to create some methods and properties for Array, because in VBA, there are nothing much you can do to manipulate an array without writing custom codes repetedaly (or you will have to write separate functions and will call them if/wherever needed). In this class, I will show you how you can write properties and method which can do the tricks for you.
Reason for creating class is to reduce the code redundancy, Once class is created, we can create many objects from that class and they will all behave in same manner, they all will have samae properties and methods, you will not need to write custom code for each of your array.
Once you have inserted and renamed the class, You need to create private variable, these are private so that only the object’s properties or method can only access them. Declare these two variables in your class
Private member of ArrayClass
Private varArr As Variant
Private strName As String
After that, lets write some properties to assign value to these private variables. We can write two type of properties in class.
Read and Write Property
Public Property Let Data(varArray As Variant)
varArr = varArray
Public Property Get Data()
Data = varArr
Public Property Let Name(sName As String)
strName = sName
Public Property Get Name() As String
Name = strName
Read only Property
Public Property Get RowsCount() As Long
If IsArray(varArr) Then
RowsCount = UBound(varArr)
RowsCount = -1
Public Property Get ColumnsCount() As Long
If IsArray(varArr) Then
ColumnsCount = UBound(varArr, 2)
ColumnsCount = -1
Same like properties, we can write some methods in the class module, see an example of method below
Methods in Class Module
Dim strMessage As String
strMessage = “Name = ” & Name & vbCrLf & “Rows =” & RowsCount & vbCrLf & “Columns =” & ColumnsCount
MsgBox strMessage, vbInformation, “Array Information”
Ok, So class is now create and now you know how to write properties and methods, Now, lets see how you can use this class in VBA. Copy the code below and paste it in the standard module.
Dim objArray As ArrayClass
Dim objArra2 As ArrayClass
Set objArray = New ArrayClass
Set objArra2 = New ArrayClass
‘–Using first instance of the class
objArray.Name = “MyDataTable”
objArray.Data = Range(“A1”).CurrentRegion
‘–Using Second instance of the class
objArray.Name = “MyOutputTable”
objArray.Data = Range(“E1”).CurrentRegion
You can run the code step by step to see how it is initiallizing object and how it is calling properties and method, Also notice that you dont need to write separate code to get the ArrayInfo, you just assigned the info in standard program and it is showing info by collecting from class module.
Shoot your questions in comments box. i willl be happy to reply. and ofcourse create some custom properties and method to manipulate arrays and share in comments.
You can download the file having this code from the link below.ClassArray.xlsm (83 downloads)