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)

VBA Class Module

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
End Property

Public Property Get Data()
      Data = varArr
End Property

Public Property Let Name(sName As String)
      strName = sName
End Property

Public Property Get Name() As String
      Name = strName
End Property

Read only Property

Public Property Get RowsCount() As Long
      If IsArray(varArr) Then
            RowsCount = UBound(varArr)
            RowsCount = -1
      End If
End Property

Public Property Get ColumnsCount() As Long
      If IsArray(varArr) Then
            ColumnsCount = UBound(varArr, 2)
            ColumnsCount = -1
      End If
End Property

Same like properties, we can write some methods in the class module, see an example of method below

Methods in Class Module

Sub ArrayInfo()
      Dim strMessage As String
      strMessage = “Name = ” & Name & vbCrLf & “Rows =” & RowsCount & vbCrLf & “Columns =” & ColumnsCount 
      MsgBox strMessage, vbInformation, “Array Information”
End Sub

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.

Sub CreateArray()
      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
End Sub

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 (225 downloads)

Join my vba whatsApp Group to know more

0 replies

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply

Your email address will not be published. Required fields are marked *