Excel FoundationsModule 34: Enterprise VBA Architecture: Class Modules, Dictionaries & REST API Integration

34.1 VBA Architecture & Object-Oriented Principles: Procedural vs Class Modules

Transcend monolithic spaghetti code, understand the principles of Object-Oriented Programming (OOP) in VBA, and construct your first Class Module with private backing fields.

Free preview

You are previewing a full lesson.

This lesson is a free preview. No account is needed to read it.

34.1 VBA Architecture & Object-Oriented Principles: Procedural vs Class Modules

The video for this lesson has not been published yet. The written lesson below is the full material for this session.

Instant AutoSumAutomates SUM formula insertion over highlighted rows and columns.

⌥Alt=

Toggle FiltersTurns on header dropdown filters for data sorting and filtering.

⌃Ctrl⇧ShiftL

Convert to Excel TableFormats range as a self-expanding, structured Excel table.

⌃CtrlT

Format Cells DialogOpens complete number, border, fill, and font formatting controls.

⌃Ctrl1

Lock Reference ($)Cycles through absolute and mixed reference locks in formulas.

F4

Lesson notes & syllabus guide

34.1 VBA Architecture & Object-Oriented Principles: Procedural vs Class Modules

Enterprise software engineering me sabse bada challenge code maintainability hota hai. Jab ek hi standard module me 2,000 lines ka spaghetti code likha hota hai, to ek chhota sa bug dhoondhna nightmare ban jata hai.

Object-Oriented Programming (OOP) data aur usse related business rules ko ek clean logical blueprint—Class Module—me encapsulate karta hai.

1. Procedural vs Object-Oriented Architecture

Characteristic	Procedural Code (Standard Modules)	Object-Oriented Code (Class Modules)
Organization	Standalone Subs and Functions	Custom objects encapsulating data & methods
Data Scope	Global variables scattered across modules	Private fields protected inside classes
Maintainability	Fragile in large enterprise systems	Highly modular, testable, and reusable
Real-World Analogy	Cooking recipe with loose steps	Pre-packaged autonomous kitchen appliance

2. Creating Your First Class Module: `CEmployee`

VBE me jayein (⌥AltF11) → Insert → Class Module.
Properties Window (F4) me (Name) rename karein: CEmployee.
Class Module me private backing variables declare karein:

Option Explicit

' Private Encapsulated State (Cannot be touched directly from outside)
Private mEmployeeID As String
Private mEmployeeName As String
Private mDepartment As String
Private mSalary As Currency

3. Exposing Encapsulated State: `Property Get` & `Property Let`

Class variables ko safely expose karne ke liye property procedures use kiye jaate hain:

' === [ EmployeeID Property ] ===
Public Property Get EmployeeID() As String
    EmployeeID = mEmployeeID
End Property

Public Property Let EmployeeID(ByVal value As String)
    value = Trim$(value)
    If Len(value) = 0 Then
        Err.Raise vbObjectError + 201, "CEmployee.EmployeeID", "Employee ID cannot be empty."
    End If
    mEmployeeID = value
End Property

' === [ Salary Property with Business Validation ] ===
Public Property Get Salary() As Currency
    Salary = mSalary
End Property

Public Property Let Salary(ByVal value As Currency)
    If value < 0 Then
        Err.Raise vbObjectError + 202, "CEmployee.Salary", "Salary cannot be a negative amount."
    End If
    mSalary = value
End Property

[!NOTE] Property Get vs Property Let:

Property Get: Value ko read karne ke liye (Getter).

Property Let: Value-type variables (Strings, Numbers) ko assign karne ke liye (Setter).

Property Set: Object-type variables (Worksheets, Ranges) ko assign karne ke liye use hota hai.

Interactive Spreadsheet Practice Sandbox

Practice live Excel formulas directly in your browser. Real calculation verified by HyperFormula.

Formula practice lab

Exercise

Exercise 1: Basic SUM Addition

Calculate total revenue in cell D6 by summing all items in column D (cells D2 to D5).

Every formula you enter is evaluated by a real spreadsheet engine in your browser — results are not pre-computed.

Exercise 1: Basic SUM Addition practice sheet
#	A	B	C	D
1	Product	Quantity	Unit Price	Total
2	Laptop	5	45000	225000
3	Mouse	20	500	10000
4	Keyboard	15	1200	18000
5	Monitor	8	12500	100000
6	Grand Total

Chapter 34 Knowledge Assessment: Classes, Dictionaries & REST APIs

Pass mark 70%

Evaluate your understanding of Class Modules, Encapsulation, Scripting.Dictionary, and HTTP REST API integration.

This quiz has 5 questions. Sign in to answer them — results are saved to your account.

Practical Project 34: Class-Based REST API Ingestion Engine with Dictionary Caching

Community discussion

Lesson Q&A & Peer Discussions

Stuck on a formula or edge case? Ask questions, upvote solutions, and earn community XP.

No questions yet on this lesson

Be the first to ask a doubt or start a discussion with peers and mentors!

34.1 VBA Architecture & Object-Oriented Principles: Procedural vs Class Modules

Object-Oriented Programming (OOP) data aur usse related business rules ko ek clean logical blueprint—Class Module—me encapsulate karta hai.

1. Procedural vs Object-Oriented Architecture

Characteristic

Procedural Code (Standard Modules)

Object-Oriented Code (Class Modules)

Organization

Standalone Subs and Functions

Custom objects encapsulating data & methods

Data Scope

Global variables scattered across modules

Private fields protected inside classes

Maintainability

Fragile in large enterprise systems

Highly modular, testable, and reusable

Real-World Analogy

Cooking recipe with loose steps

Pre-packaged autonomous kitchen appliance

2. Creating Your First Class Module: CEmployee

VBE me jayein (⌥AltF11) → Insert → Class Module.

Properties Window (F4) me (Name) rename karein: CEmployee.

Class Module me private backing variables declare karein:

Option Explicit ' Private Encapsulated State (Cannot be touched directly from outside) Private mEmployeeID As String Private mEmployeeName As String Private mDepartment As String Private mSalary As Currency

3. Exposing Encapsulated State: Property Get & Property Let

Class variables ko safely expose karne ke liye property procedures use kiye jaate hain:

' === [ EmployeeID Property ] === Public Property Get EmployeeID() As String EmployeeID = mEmployeeID End Property Public Property Let EmployeeID(ByVal value As String) value = Trim$(value) If Len(value) = 0 Then Err.Raise vbObjectError + 201, "CEmployee.EmployeeID", "Employee ID cannot be empty." End If mEmployeeID = value End Property ' === [ Salary Property with Business Validation ] === Public Property Get Salary() As Currency Salary = mSalary End Property Public Property Let Salary(ByVal value As Currency) If value < 0 Then Err.Raise vbObjectError + 202, "CEmployee.Salary", "Salary cannot be a negative amount." End If mSalary = value End Property

[!NOTE] Property Get vs Property Let:

Property Get: Value ko read karne ke liye (Getter).
Property Let: Value-type variables (Strings, Numbers) ko assign karne ke liye (Setter).
Property Set: Object-type variables (Worksheets, Ranges) ko assign karne ke liye use hota hai.

Product

Quantity

Unit Price

Total

Laptop

45000

225000

Mouse

500

10000

Keyboard

1200

18000

Monitor

12500

100000

Grand Total

34.1 VBA Architecture & Object-Oriented Principles: Procedural vs Class Modules

You are previewing a full lesson.

34.1 VBA Architecture & Object-Oriented Principles: Procedural vs Class Modules

Lesson notes & syllabus guide

34.1 VBA Architecture & Object-Oriented Principles: Procedural vs Class Modules

1. Procedural vs Object-Oriented Architecture

2. Creating Your First Class Module: CEmployee

3. Exposing Encapsulated State: Property Get & Property Let

Interactive Spreadsheet Practice Sandbox

Exercise 1: Basic SUM Addition

Chapter 34 Knowledge Assessment: Classes, Dictionaries & REST APIs

Practical Project 34: Class-Based REST API Ingestion Engine with Dictionary Caching

Lesson Q&A & Peer Discussions

34.1 VBA Architecture & Object-Oriented Principles: Procedural vs Class Modules

You are previewing a full lesson.

34.1 VBA Architecture & Object-Oriented Principles: Procedural vs Class Modules

Lesson notes & syllabus guide

34.1 VBA Architecture & Object-Oriented Principles: Procedural vs Class Modules

1. Procedural vs Object-Oriented Architecture

2. Creating Your First Class Module: CEmployee

3. Exposing Encapsulated State: Property Get & Property Let

Interactive Spreadsheet Practice Sandbox

Exercise 1: Basic SUM Addition

Chapter 34 Knowledge Assessment: Classes, Dictionaries & REST APIs

Practical Project 34: Class-Based REST API Ingestion Engine with Dictionary Caching

Lesson Q&A & Peer Discussions

2. Creating Your First Class Module: `CEmployee`

3. Exposing Encapsulated State: `Property Get` & `Property Let`

2. Creating Your First Class Module: `CEmployee`

3. Exposing Encapsulated State: `Property Get` & `Property Let`