The advantage of using a custom class versus a custom type in VBA

Custom types are a notion in VBA that is very close to custom classes, but much simpler and with far less features.

When you are ready to add some complexity and abstraction in you VBA code, you should consider leaving VBA custom types and migrating to custom classes.

How to create custom types in VBA

Click here to read the article about custom types in VBA that was created before on the same blog.

This is the piece of code to add in a module to create a custom variable type Person containing the characteristics of a person.

Public Type vPerson
     Name as string
     Height as Double
     Weight as Double
     Wealth as currency
End Type

Use a custom class to create a similar data type

You can achieve exactly the same result by creating a cPerson class (in a class module called cPerson) that would contain:

Public Name As String
Public Height As Double
Public Weight As Double
Public Wealth As Currency

Note the Public keyword before each variable name within the class: it means that these variables can be accessible from outside the class module. This is crucial for the following code to work.

This is the test code to use in a module:

Sub testvPerson()
Dim Peter As vPerson
Peter.Name = "Peter"
Peter.Height = 1.75
Peter.Wealth = 50000
Peter.Weight = 75
End Sub
 
Sub testcPerson()
Dim Peter As New cPerson
'create an object from the class with the “new” keyword
Peter.Name = "Peter"
Peter.Height = 1.75
Peter.Wealth = 50000
Peter.Weight = 75
End Sub

Create arrays of custom data types and custom classes in VBA

Although the Class approach requires that you create a class module, it is much better to use this approach from now on. One benefit of this approach is that while you can’t create collections of custom type variables, you can create collections of custom class objects! Collections can be extremely useful to create associative lists. It is possible though to create array of custom type variables as well as custom class objects!

Below we create arrays of classes and types. The approach works for both classes and types, but note that you can’t write it exactly the same, especially because of the “new” keyword which is necessary when you create an object using the custom class, and the “set” keyword when you assign a value to an object:

Sub ArrayPushTYPE()
Dim arrPersons(1 To 10) As vPerson
Dim Peter As vPerson
Peter.Name = "Peter"
Peter.Height = 1.75
Peter.Wealth = 50000
Peter.Weight = 75
Dim Uber As vPerson
Peter.Name = "Uber"
Peter.Height = 1.95
Peter.Wealth = 500000
Peter.Weight = 90
Dim Alexis As vPerson
Peter.Name = "Alexis"
Peter.Height = 1.6
Peter.Wealth = 1000
Peter.Weight = 60
'Then we put each “person” we have created in the array
arrPersons(1) = Peter
arrPersons(2) = Uber
arrPersons(3) = Alexis'We can then read the content of the arrayMsgbox arrPersons(1).Name
End Sub
 
Sub ArrayPushCLASS()
Dim arrPersons(1 To 10) As cPerson
Dim Peter As new cPerson
Peter.Name = "Peter"
Peter.Height = 1.75
Peter.Wealth = 50000
Peter.Weight = 75
Dim Uber As New cPerson
Peter.Name = "Uber"
Peter.Height = 1.95
Peter.Wealth = 500000
Peter.Weight = 90
Dim Alexis As New cPerson
Peter.Name = "Alexis"
Peter.Height = 1.6
Peter.Wealth = 1000
Peter.Weight = 60
'Note the “set” keyword
Set arrPersons(1) = Peter
Set arrPersons(2) = Uber
Set arrPersons(3) = AlexisMsgbox arrPersons(3).Height
End Sub

It is also possible to take a shortcut from the previous approach for both custom types and classes, but let’s focus on classes from now on. Since each element in the array is already typed as a cPerson object, we just need to create a new instance of the object in each element of the array:

Sub ArrayPushCLASS2()
Dim arrPersons(1 To 10) As cPerson
Set arrPersons(1) = New cPerson
        arrPersons(1).Name = "Peter"
        arrPersons(1).Height = 1.75
        arrPersons(1).Wealth = 50000
        arrPersons(1).Weight = 75
Set arrPersons(2) = New cPerson
        arrPersons(2).Name = "Uber"
        arrPersons(2).Height = 1.95
        arrPersons(2).Wealth = 500000
        arrPersons(2).Weight = 90
Set arrPersons(3) = New cPerson
        arrPersons(3).Name = "Alexis"
        arrPersons(3).Height = 1.6
        arrPersons(3).Wealth = 1000
        arrPersons(3).Weight = 60
End Sub

Create collections of custom classes in VBA

When it comes to collections (which we could consider as associative arrays), the code below can only work with classes:

Sub CollectionPushCLASS()
Dim collPersons As New Collection
Dim Peter As New cPerson
Peter.Name = "Peter"
Peter.Height = 1.75
Peter.Wealth = 50000
Peter.Weight = 75
Dim Uber As New cPerson
Peter.Name = "Uber"
Peter.Height = 1.95
Peter.Wealth = 500000
Peter.Weight = 90
Dim Alexis As New cPerson
Peter.Name = "Alexis"
Peter.Height = 1.6
Peter.Wealth = 1000
Peter.Weight = 60
 
collPersons.add Peter, "Peter"
collPersons.add Uber, "Uber"
collPersons.add Alexis, "Alexis"
'Collection with an index make it very easy to find quickly an item 'in the collection and then read the object it contains
Msgbox collPersons("Uber").Wealth
End Sub

 

Matthieu Liatard
Remember this!
Se souvenir de cela
Play!
Tester mes connaissances
Close

Bonjour :)
Avec Question-Player, apprenez de nouvelles choses et n'oubliez plus rien. Jamais. Et en plus c'est gratuit.

Pour nous aider, vous pouvez créer un compte:

Login / Créer un compte
Notez cet article :
1
2
3
4
5

Up next

Articles similaires