Excel FoundationsModule 33: Advanced VBA: Search, Update, Delete (CRUD), Database Connectivity & File I/O

33.1 Enterprise CRUD Architecture: Create & Exact/Partial Read Operations

Understand the CRUD paradigm (Create, Read, Update, Delete), build modular procedures, execute exact ID lookups with Find, and implement partial name searches.

Free preview

You are previewing a full lesson.

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

33.1 Enterprise CRUD Architecture: Create & Exact/Partial Read Operations

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

33.1 Enterprise CRUD Architecture: Create & Exact/Partial Read Operations

CRUD (Create, Read, Update, Delete) software engineering ka sabse fundamental data lifecycle paradigm hai. Real-world business applications (Inventory registers, CRM databases, Student portals) me data sirf add nahi hota; usse search, edit aur safely remove bhi karna hota hai.

1. The 4 Operations of CRUD

Operation	SQL Keyword	Excel Action	Business Example
C — Create	`INSERT`	Append new row to table	Naya customer ya employee add karna
R — Read	`SELECT`	Search & display record details	Employee ID se details retrieve karna
U — Update	`UPDATE`	Modify specific columns in-place	Salary increment ya Department change karna
D — Delete	`⌫Bksp`	Remove row or mark inactive	Resigned employee record remove karna

2. Architectural Separation of Responsibilities

Enterprise VBA projects me UserForm code ko business logic se alag rakha jata hai:

frmEmployeeManager: Pure user interface (Buttons, TextBoxes, Labels).
modEmployeeCRUD: Database business operations (AddEmployee, FindEmployeeRow, UpdateEmployee, DeleteEmployee).
modValidation: Input validation logic (IsNumeric, IsValidEmail).

3. Read Operation 1: Exact Primary Key Search (`FindEmployeeRow`)

Public Function FindEmployeeRow(ByVal employeeID As String) As Long
    Dim ws As Worksheet
    Dim foundCell As Range
    
    Set ws = ThisWorkbook.Worksheets("Employees")
    
    Set foundCell = ws.Columns("A").Find( _
        What:=Trim$(employeeID), _
        LookIn:=xlValues, _
        LookAt:=xlWhole, _
        MatchCase:=False)
        
    If foundCell Is Nothing Then
        FindEmployeeRow = 0 ' Record Not Found
    ElseIf foundCell.Row = 1 Then
        FindEmployeeRow = 0 ' Header Row Ignored
    Else
        FindEmployeeRow = foundCell.Row
    End If
End Function

LookAt:=xlWhole: Strict whole-cell match karta hai taaki EMP01 search karne par EMP010 match na ho!

4. Read Operation 2: Partial Text Search with `InStr`

Jab user ko exact ID na pata ho aur name ka hissa search karna ho:

Public Sub SearchByName()
    Dim ws As Worksheet
    Dim searchName As String, results As String
    Dim lastRow As Long, i As Long
    
    Set ws = ThisWorkbook.Worksheets("Employees")
    searchName = Trim$(InputBox("Enter partial employee name to search:"))
    If searchName = "" Then Exit Sub
    
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    
    For i = 2 To lastRow
        If InStr(1, CStr(ws.Cells(i, "B").Value), searchName, vbTextCompare) > 0 Then
            results = results & ws.Cells(i, "A").Value & " - " & ws.Cells(i, "B").Value & " (" & ws.Cells(i, "C").Value & ")" & vbCrLf
        End If
    Next i
    
    If results = "" Then
        MsgBox "No matching employees found.", vbInformation
    Else
        MsgBox "Matching Records Found:" & vbCrLf & vbCrLf & results, vbInformation, "Search Results"
    End If
End Sub

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 33 Knowledge Assessment: CRUD, Database & File Systems

Pass mark 70%

Evaluate your understanding of CRUD architecture, in-place updates, parameterized queries, and audit logging.

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

Practical Project 33: Production-Ready Employee CRUD Application with Audit Logging

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!

33.1 Enterprise CRUD Architecture: Create & Exact/Partial Read Operations

1. The 4 Operations of CRUD

Operation

SQL Keyword

Excel Action

Business Example

C — Create

INSERT

Append new row to table

Naya customer ya employee add karna

R — Read

SELECT

Search & display record details

Employee ID se details retrieve karna

U — Update

UPDATE

Modify specific columns in-place

Salary increment ya Department change karna

D — Delete

⌫Bksp

Remove row or mark inactive

Resigned employee record remove karna

2. Architectural Separation of Responsibilities

Enterprise VBA projects me UserForm code ko business logic se alag rakha jata hai:

frmEmployeeManager: Pure user interface (Buttons, TextBoxes, Labels).

modEmployeeCRUD: Database business operations (AddEmployee, FindEmployeeRow, UpdateEmployee, DeleteEmployee).

modValidation: Input validation logic (IsNumeric, IsValidEmail).

3. Read Operation 1: Exact Primary Key Search (FindEmployeeRow)

Public Function FindEmployeeRow(ByVal employeeID As String) As Long Dim ws As Worksheet Dim foundCell As Range Set ws = ThisWorkbook.Worksheets("Employees") Set foundCell = ws.Columns("A").Find( _ What:=Trim$(employeeID), _ LookIn:=xlValues, _ LookAt:=xlWhole, _ MatchCase:=False) If foundCell Is Nothing Then FindEmployeeRow = 0 ' Record Not Found ElseIf foundCell.Row = 1 Then FindEmployeeRow = 0 ' Header Row Ignored Else FindEmployeeRow = foundCell.Row End If End Function

LookAt:=xlWhole: Strict whole-cell match karta hai taaki EMP01 search karne par EMP010 match na ho!

4. Read Operation 2: Partial Text Search with InStr

Jab user ko exact ID na pata ho aur name ka hissa search karna ho:

Public Sub SearchByName() Dim ws As Worksheet Dim searchName As String, results As String Dim lastRow As Long, i As Long Set ws = ThisWorkbook.Worksheets("Employees") searchName = Trim$(InputBox("Enter partial employee name to search:")) If searchName = "" Then Exit Sub lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row For i = 2 To lastRow If InStr(1, CStr(ws.Cells(i, "B").Value), searchName, vbTextCompare) > 0 Then results = results & ws.Cells(i, "A").Value & " - " & ws.Cells(i, "B").Value & " (" & ws.Cells(i, "C").Value & ")" & vbCrLf End If Next i If results = "" Then MsgBox "No matching employees found.", vbInformation Else MsgBox "Matching Records Found:" & vbCrLf & vbCrLf & results, vbInformation, "Search Results" End If End Sub

Product

Quantity

Unit Price

Total

Laptop

45000

225000

Mouse

500

10000

Keyboard

1200

18000

Monitor

12500

100000

Grand Total

33.1 Enterprise CRUD Architecture: Create & Exact/Partial Read Operations

You are previewing a full lesson.

33.1 Enterprise CRUD Architecture: Create & Exact/Partial Read Operations

Lesson notes & syllabus guide

33.1 Enterprise CRUD Architecture: Create & Exact/Partial Read Operations

1. The 4 Operations of CRUD

2. Architectural Separation of Responsibilities

3. Read Operation 1: Exact Primary Key Search (FindEmployeeRow)

4. Read Operation 2: Partial Text Search with InStr

Interactive Spreadsheet Practice Sandbox

Exercise 1: Basic SUM Addition

Chapter 33 Knowledge Assessment: CRUD, Database & File Systems

Practical Project 33: Production-Ready Employee CRUD Application with Audit Logging

Lesson Q&A & Peer Discussions

33.1 Enterprise CRUD Architecture: Create & Exact/Partial Read Operations

You are previewing a full lesson.

33.1 Enterprise CRUD Architecture: Create & Exact/Partial Read Operations

Lesson notes & syllabus guide

33.1 Enterprise CRUD Architecture: Create & Exact/Partial Read Operations

1. The 4 Operations of CRUD

2. Architectural Separation of Responsibilities

3. Read Operation 1: Exact Primary Key Search (FindEmployeeRow)

4. Read Operation 2: Partial Text Search with InStr

Interactive Spreadsheet Practice Sandbox

Exercise 1: Basic SUM Addition

Chapter 33 Knowledge Assessment: CRUD, Database & File Systems

Practical Project 33: Production-Ready Employee CRUD Application with Audit Logging

Lesson Q&A & Peer Discussions

3. Read Operation 1: Exact Primary Key Search (`FindEmployeeRow`)

4. Read Operation 2: Partial Text Search with `InStr`

3. Read Operation 1: Exact Primary Key Search (`FindEmployeeRow`)

4. Read Operation 2: Partial Text Search with `InStr`