Cat On A Spreadsheet

Cat On A Spreadsheet

Writing Cleaner, Modular VBA Code with Classes and OOP Concepts

Most VBA developers rely heavily on procedural programming: Subs, Functions, and Modules. But VBA also supports a simplified form of Object-Oriented Programming (OOP) through Class Modules.

 

Using Classes makes your code easier to maintain, more reusable and extendable as well as cleaner, by encapsulating logic and properties.

 

What is a Class Module in VBA?

 

A Class Module defines a blueprint for an object, including its:

  • Properties: variables attached to the object

  • Methods: procedures the object can perform

 

Creating Your First Class

 

Let’s build a simple Customer class to understand how classes work.

 

1. Insert a Class Module

 

  • In the VBA editor, go to Insert > Class Module

  • Rename it to Customer in the Properties window (F4)

 

2. Define Properties

 

Inside your Customer class:

Private pName As String
Private pEmail As String

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

Public Property Let Name(ByVal value As String)
     pName = value
End Property

Public Property Get Email() As String
     Email = pEmail
End Property

Public Property Let Email(ByVal value As String)
     pEmail = value
End Property

 

Explanation:

 

  • pName and pEmail are private variables

  • Property Get returns their value

  • Property Let sets their value

 

3. Add a Method

 

Let’s add a method to greet the customer:

Public Sub Greet()
     MsgBox "Hello, " & pName & " (" & pEmail & ")"
End Sub

 

Using the Class in a Module

 

In a regular Module:

Sub TestCustomer()
Dim cust As Customer

Set cust = New Customer
cust.Name = "John Doe"
cust.Email = "john.doe@example.com"
cust.Greet

Set cust = Nothing
End Sub

 

This creates a Customer object, sets its properties, and calls its Greet method.

 

Why Use Classes?

 

  • Encapsulation: Group related data and logic

  • Reusability: Create multiple instances easily

  • Readability: Cleaner code structure for complex projects

 

 Advanced Uses

 

  • Collections of Objects: Manage lists of customers, products, or tasks

  • Inheritance Workarounds: VBA lacks true inheritance but you can simulate behaviour with interfaces and polymorphism concepts

 

 Common Pitfalls

 

❌Forgetting to use Set when creating object instances

❌ Not cleaning up with Set object = Nothing to release memory

❌ Overcomplicating projects with classes when simple modules suffice

 

 Best Practice Tips

 

✔️ Use classes for entities with clear data structures and methods

✔️ Name class modules clearly (Customer, Invoice, Task)

✔️ Combine with collections to build scalable applications (e.g. Collection of Task objects in a task manager)

 

 Final Thoughts

 

Classes and OOP concepts unlock a new level of structure and professionalism in your VBA projects. They’re especially powerful for:

  • Building small internal apps

  • Managing reusable business objects

  • Future-proofing your code for expansion

With ThisWorkbook.Sheets(1)
     .Range("A1").Value = "Timestamp"
     .Range("B1").Value = "Log Level"
     .Range("C1").Value = "Message"
End With
End Sub

 

You can call this sub before running ParseLogFile.

 

⚠️ Bonus: Handling Irregular Lines

 

Some logs may have missing or malformed entries. Use error handling:

On Error Resume Next
' ... parsing logic ...
If Err.Number <> 0 Then
     Message = "PARSE ERROR: " & LineText
     Err.Clear
End If
On Error GoTo 0

 

🧠 Why Use VBA for This?

 

While tools like Python are more common for text processing, VBA offers:

  • Native integration with Excel

  • Immediate visualization and manipulation of parsed data

  • A great option for non-developers working in Excel-driven environments

 

🔚 Final Thoughts

 

VBA is more than just forms and macros—it’s a lightweight data processing engine built right into Excel. With a bit of string manipulation and file handling, you can turn chaotic log files into structured, actionable data in minutes.

 

Want to go further? Try:

  • Parsing multi-line log entries

  • Highlighting rows with ERROR or WARN

  • Exporting results as a CSV or Excel table

21 July 2025

Full Service Consulting

Reporting

Automation

Cat On A Spreadsheet

Cat On A Spreadsheet