VIDEOS BY CATEGORY▼
- VIDEOS HOME PAGE
- .NET (14)
- Business Intelligence (40)
- Integration Services (19)
- Macros and Programming (82)
- Microsoft Excel (69)
- Microsoft Office (91)
- Miscellaneous (1)
- Power BI (35)
- Python (31)
- Report Builder (107)
- Reporting Services (113)
- SQL (42)
- Visual Basic for Applications (215)
- Visual C# (14)
VBA CATEGORIES▼
- Excel VBA - Basics (24)
- VBA User Forms (22)
- Excel VBA - pivot tables (9)
- Excel VBA - charts (6)
- VBA - advanced (14)
- VBA - working with files (12)
- VBA - linking applications (12)
- VBA - working with Outlook (14)
- Built-in VBA functions (9)
- VBA - working with data (57)
- VBA - scraping websites (25)
- VBA - working with shapes (5)
- VBA - classes and structures (6)
VBA - CLASSES AND STRUCTURES VIDEOS▼
VIDEOS BY AUTHOR▼
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.
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
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!