Training videos for learning VBA - CLASSES AND STRUCTURES

We have 6 VBA - classes and structures videos listed below. You might also like to consider booking a place on one of our online or classroom VBA courses, where you will meet the owls behind the videos!

Excel VBA Part 34 - Type Declarations

Posted by Andrew Gould on 13 October 2014

The Type statement in VBA allows you to define your own static data structures which you can then use in your variable declarations. This video explains how to declare a type, how to employ the type in variable declarations, how to read to and write from the variable, as well as a couple of fun features such as using enumerations within a type declaration and nesting types.

Excel VBA Part 35 - Class Modules

Posted by Andrew Gould on 13 October 2014

Class Modules in VBA allow you to define your own classes of objects. This video explains why that's a useful and shows you how to go about creating a class. You'll learn about the two class events: initialise and terminate, you'll see how to define properties, including how to create read-only properties and set default values. You'll also find out how easy it is to create methods for your class and how to use your class in your regular code.

Excel VBA Part 36 - Class Modules in Other Projects

Posted by Andrew Gould on 27 October 2014

One small limitation of VBA Class Modules is that, by default, you can only use them in the VBA Project in which they were created. This video explains how you can make your class modules available to other VBA projects. You'll learn about the Instancing property of a class, how to set references to VBA projects, how to write a function to return a new instance of a class and how to create an Excel Add-In as a convenient way to distribute your class modules.

Excel VBA Part 37 - Custom Collections

Posted by Andrew Gould on 27 October 2014

Excel VBA is full of collections and you can even create your own custom collections using the Collection object. This video teaches you how to create a new custom collection, how to add items to it, how to reference those items individually and how to loop over all of the items in the collection. You'll also see how you can add items based on a custom class module to a collection.

Excel VBA Part 38 - Typed Collections

Posted by Andrew Gould on 27 October 2014

One of the issues with the Collection class in VBA is that it's untyped. This means you can add any kind of object to the collection which can lead to issues later on. This video explains how to use class modules to create strongly typed collections. You'll learn how to create a private collection variable in a class module, how to create a custom Add method to restrict the type of item that can be added to the collection and how to use a custom collection class in your other procedures.

Excel VBA Part 39 - Dictionaries

Posted by Andrew Gould on 27 October 2014

The Dictionary class is similar to the Collection class in that it allows you to store a custom collection of objects. This video teaches you how to use dictionaries, including how to reference the Scripting Runtime Object Library, how to create a new Dictionary and add, remove, count and reference the items in it. You'll also learn about the Items and Keys arrays contained within a Dictionary and how to loop over these arrays in order to process all of the items that the Dictionary contains.

This page has 1 thread Add post
captain   Reply to captainReply
29 Jan 22 at 14:06

Hi,

I recently came across the code below.

If you have the time, could you please make a video or blog explaining what is going on here?

I suspect it's an advanced topic, probably class factories or something similar.

Thanks

 

''''''''''''''''''''''Standard module:

Option Explicit

Private Enum TransportationMode
    ByBus = 1
    ByTrain
    ByPlane
End Enum

Public Sub Main()
    
    Dim myPlanner As TravelPlanner
    Set myPlanner = New TravelPlanner
    
    myPlanner.SetTravelStrategy Travelby(ByBus)
    myPlanner.Drive 1000
    
    myPlanner.SetTravelStrategy Travelby(ByTrain)
    myPlanner.Drive 1000
    
    myPlanner.SetTravelStrategy Travelby(ByPlane)
    myPlanner.Drive 900
    
    Set myPlanner = Nothing
    
End Sub

Private Function Travelby(ByVal Mode As TransportationMode) As IStrategy
    Set Travelby = GetListOfObjects.Item(VBA.CStr(Mode))
End Function

Private Function GetListOfObjects() As Collection
    Static Coll As Collection
    If Coll Is Nothing Then
        Set Coll = New Collection
        Coll.Add New Bus, VBA.CStr(TransportationMode.ByBus)
        Coll.Add New Train, VBA.CStr(TransportationMode.ByTrain)
        Coll.Add New Plane, VBA.CStr(TransportationMode.ByPlane)
    End If
    Set GetListOfObjects = Coll
End Function

 

''''''''''''''''''''''Class Module Bus:

Option Explicit

Implements IStrategy

Private Const MYMODE As String = "Bus"

Private Property Get IStrategy_costPerKilometer() As Single
    IStrategy_costPerKilometer = 20
End Property

Private Property Get GetGeneralMethods() As Misc
    Set GetGeneralMethods = New Misc
End Property

Private Sub IStrategy_drive(ByVal kilometers As Long)
    Dim totalCost As Double
    totalCost = GetGeneralMethods.CalculateCostOfTravel(kilometers, IStrategy_costPerKilometer)
    GetGeneralMethods.ShowResultOnImmediateWindow MYMODE, kilometers, totalCost
End Sub

 

''''''''''''''''''''''Class Module IStrategy:

Option Explicit

Public Property Get costPerKilometer() As Single
End Property

Public Sub Drive(ByVal kilometers As Long)
End Sub

''''''''''''''''''''''Class Module Misc:

Option Explicit

Public Sub ShowResultOnImmediateWindow(ByVal TravelWith As String, ByVal kilometers As Long, ByVal totalCost As Double)
    Debug.Print "Total cost of travel with " & TravelWith & " for " & kilometers & " KM is : " & VBA.Format(totalCost, "Standard")
End Sub

Public Function CalculateCostOfTravel(ByVal kilometers As Single, ByVal costOfPerKilometers As Long) As Double
    CalculateCostOfTravel = kilometers * costOfPerKilometers
End Function

 

''''''''''''''''''''''Class Module Plane:

Option Explicit

Implements IStrategy

Private Const MYMODE As String = "Air Plane"

Private Property Get IStrategy_costPerKilometer() As Single
    IStrategy_costPerKilometer = 50
End Property

Private Property Get GetGeneralMethods() As Misc
    Set GetGeneralMethods = New Misc
End Property

Private Sub IStrategy_drive(ByVal kilometers As Long)
    Dim totalCost As Double
    totalCost = GetGeneralMethods.CalculateCostOfTravel(kilometers, IIf(kilometers > 1000, 25, IStrategy_costPerKilometer))
    GetGeneralMethods.ShowResultOnImmediateWindow MYMODE, kilometers, totalCost
End Sub

 

''''''''''''''''''''''Class Module Train:

Option Explicit

Implements IStrategy

Private Const MYMODE As String = "Train"

Private Property Get IStrategy_costPerKilometer() As Single
    IStrategy_costPerKilometer = 10
End Property

Private Property Get GetGeneralMethods() As Misc
    Set GetGeneralMethods = New Misc
End Property

Private Sub IStrategy_drive(ByVal kilometers As Long)
    Dim totalCost As Double
    totalCost = GetGeneralMethods.CalculateCostOfTravel(kilometers, IStrategy_costPerKilometer)
    GetGeneralMethods.ShowResultOnImmediateWindow MYMODE, kilometers, totalCost
End Sub

 

''''''''''''''''''''''Class Module TravelPlanner:

 

Option Explicit

Public TravelStrategy As IStrategy

Public Sub SetTravelStrategy(ByVal myStrategy As IStrategy)
    Set TravelStrategy = myStrategy
End Sub

Public Sub Drive(ByVal TravelDistance)
    TravelStrategy.Drive TravelDistance
End Sub

 

Andrew G   Reply to Andrew GReply
31 Jan 22 at 15:24

Interfaces in VBA has been on my list of videos to make for ages! At some point I'll get around to it but it's unlikely to happen in the very near future!

Add a new post