Master Advanced Excel & VBA

Learn Excel formulas, data analysis, dashboards, VBA programming, and business automation through structured practical lessons.

COURSE SYLLABUS
1. Excel Basics
Introduction to Advanced Excel Workbook, Worksheets, Rows & Columns Data Types & Cell Formatting Cell References & Keyboard Shortcuts
2. Advanced Excel Formulas
Advanced Formulas Introduction IF, AND, OR & IFERROR Excel Tables XLOOKUP INDEX & MATCH SUMIFS & COUNTIFS
3. Pivot Analytics
Pivot Analytics Introduction Pivot Tables Calculated Fields & Items Timelines & Slicers Text Functions & Data Cleaning
4. Excel Dashboards
Interactive Dashboards Introduction Data Validation Conditional Formatting Combo Charts Sparklines
5. Excel Data Tools
Data Integration Introduction Importing Text Files Power Query Worksheet Protection Formula Auditing
6. VBA Fundamentals
VBA Fundamentals Introduction Macro Recorder VBE Workspace VBA Objects & Properties Variables & Data Types MsgBox & InputBox If...Then...Else Select Case Loops & Dynamic Ranges
7. VBA Automation
VBA Automation Introduction Subroutines & Arguments User-Defined Functions Workbook Events Worksheet Events Form Controls & Buttons
8. VBA UserForms
UserForms Introduction UserForm Design Toolbox Controls Input Validation CRUD Operations with UserForms
9. VBA Optimization & Security
Optimization Introduction Error Handling Debugging Tools Performance Optimization VBA Project Security
10. Corporate Capstone Project
Capstone Project Introduction Data Consolidation Data Cleaning & Analysis Automated PDF Reports Dashboard Automation
11. Excel Practice & Assignments
Practice Introduction Excel Formula Exercises Pivot Table Exercises Dashboard Exercises VBA Programming Exercises Final Assignments

CRUD Operations Using VBA UserForms

Learn how to Create, Read, Update, and Delete Excel records using VBA UserForms with practical examples.


1. Introduction to CRUD Operations

CRUD represents four basic operations used to manage data: Create, Read, Update, and Delete. In Excel VBA, these operations can be performed through a UserForm that interacts with worksheet records.

Operation Description Example
Create Add a new record. Add a new employee.
Read Find and display a record. Search by Employee ID.
Update Modify an existing record. Change an employee's salary.
Delete Remove a record. Delete an employee entry.
Learning goal: Build an Employee Management UserForm that performs all four operations on an Excel worksheet.

2. Prepare the Excel Worksheet

Create a worksheet named Employees. Enter the following column headings in row 1.

Column A Column B Column C Column D
EmployeeID EmployeeName Department Salary
101 Amit Sharma IT 35000
102 Neha Patil HR 30000
Note: This tutorial assumes row 1 contains headings, employee records start from row 2, and EmployeeID is unique. Test deletion on sample data first.

3. Design the UserForm

Open the Visual Basic Editor using Alt + F11. Insert a UserForm and set its (Name) property to frmEmployee.

Add the following controls using the Toolbox.

Control Name Property Purpose
TextBox txtID Employee ID
TextBox txtName Employee name
TextBox txtDepartment Department
TextBox txtSalary Salary
CommandButton cmdCreate Create a record
CommandButton cmdRead Read a record
CommandButton cmdUpdate Update a record
CommandButton cmdDelete Delete a record
CommandButton cmdClear Clear the form

4. Create Helper Procedures

Open the code window of frmEmployee. Add the following code. These helper procedures find an employee by ID and clear the input fields.

Option Explicit

Private Function GetEmployeeSheet() As Worksheet
    On Error Resume Next
    Set GetEmployeeSheet = ThisWorkbook.Worksheets("Employees")
    On Error GoTo 0

    If GetEmployeeSheet Is Nothing Then
        MsgBox "Worksheet 'Employees' was not found.", vbExclamation
    End If
End Function

Private Function FindEmployeeRow(ByVal employeeID As String) As Long
    Dim ws As Worksheet
    Dim foundCell As Range

    Set ws = GetEmployeeSheet()
    If ws Is Nothing Then Exit Function

    Set foundCell = ws.Columns("A").Find( _
        What:=employeeID, _
        After:=ws.Cells(1, "A"), _
        LookIn:=xlValues, _
        LookAt:=xlWhole, _
        SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, _
        MatchCase:=False)

    If Not foundCell Is Nothing Then
        If foundCell.Row >= 2 Then
            FindEmployeeRow = foundCell.Row
        End If
    End If
End Function

Private Sub ClearFields()
    Me.txtID.Value = vbNullString
    Me.txtName.Value = vbNullString
    Me.txtDepartment.Value = vbNullString
    Me.txtSalary.Value = vbNullString
End Sub

Explanation: FindEmployeeRow returns the worksheet row number when an employee exists. If the ID is not found, it returns zero.

5. CREATE Operation

The Create operation inserts a new employee record into the next available row. It also validates required fields and prevents duplicate employee IDs.

Private Sub cmdCreate_Click()
    Dim ws As Worksheet
    Dim nextRow As Long
    Dim employeeID As String
    Dim employeeName As String
    Dim department As String
    Dim salaryValue As Double

    employeeID = Trim$(CStr(Me.txtID.Value))
    employeeName = Trim$(CStr(Me.txtName.Value))
    department = Trim$(CStr(Me.txtDepartment.Value))

    If employeeID = "" Or employeeName = "" Or department = "" _
        Or Trim$(CStr(Me.txtSalary.Value)) = "" Then

        MsgBox "Please fill in all fields.", vbExclamation
        Exit Sub
    End If

    If Not IsNumeric(Me.txtSalary.Value) Then
        MsgBox "Salary must be numeric.", vbExclamation
        Exit Sub
    End If

    salaryValue = CDbl(Me.txtSalary.Value)

    If salaryValue < 0 Then
        MsgBox "Salary cannot be negative.", vbExclamation
        Exit Sub
    End If

    If FindEmployeeRow(employeeID) > 0 Then
        MsgBox "Employee ID already exists.", vbExclamation
        Exit Sub
    End If

    Set ws = GetEmployeeSheet()
    If ws Is Nothing Then Exit Sub

    nextRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row + 1
    If nextRow < 2 Then nextRow = 2

    ws.Cells(nextRow, "A").NumberFormat = "@"
    ws.Cells(nextRow, "A").Value = employeeID
    ws.Cells(nextRow, "B").Value = employeeName
    ws.Cells(nextRow, "C").Value = department
    ws.Cells(nextRow, "D").Value = salaryValue

    MsgBox "Employee created successfully.", vbInformation
    ClearFields
End Sub
How it works
  1. Read the values from the TextBox controls.
  2. Check for empty fields.
  3. Validate salary and reject negative values.
  4. Check whether the Employee ID already exists.
  5. Insert the record into the next available row.

6. READ Operation

The Read operation searches for an employee using Employee ID and displays the matching details in the UserForm.

Private Sub cmdRead_Click()
    Dim ws As Worksheet
    Dim employeeID As String
    Dim recordRow As Long

    employeeID = Trim$(CStr(Me.txtID.Value))

    If employeeID = "" Then
        MsgBox "Enter an Employee ID to search.", vbExclamation
        Exit Sub
    End If

    recordRow = FindEmployeeRow(employeeID)

    If recordRow = 0 Then
        MsgBox "Employee record not found.", vbInformation
        Exit Sub
    End If

    Set ws = GetEmployeeSheet()
    If ws Is Nothing Then Exit Sub

    Me.txtID.Value = CStr(ws.Cells(recordRow, "A").Value)
    Me.txtName.Value = CStr(ws.Cells(recordRow, "B").Value)
    Me.txtDepartment.Value = CStr(ws.Cells(recordRow, "C").Value)
    Me.txtSalary.Value = CStr(ws.Cells(recordRow, "D").Value)

    MsgBox "Employee record loaded.", vbInformation
End Sub
Example: Enter Employee ID 101 and click Read. The form displays Amit Sharma's employee details.

7. UPDATE Operation

Update modifies an existing employee record. First, use Read to load the record. Edit its details and click Update to save the changes.

Private Sub cmdUpdate_Click()
    Dim ws As Worksheet
    Dim employeeID As String
    Dim employeeName As String
    Dim department As String
    Dim salaryValue As Double
    Dim recordRow As Long

    employeeID = Trim$(CStr(Me.txtID.Value))
    employeeName = Trim$(CStr(Me.txtName.Value))
    department = Trim$(CStr(Me.txtDepartment.Value))

    If employeeID = "" Then
        MsgBox "Enter an Employee ID.", vbExclamation
        Exit Sub
    End If

    recordRow = FindEmployeeRow(employeeID)

    If recordRow = 0 Then
        MsgBox "Employee record not found. Read the record first.", vbExclamation
        Exit Sub
    End If

    If employeeName = "" Or department = "" _
        Or Trim$(CStr(Me.txtSalary.Value)) = "" Then

        MsgBox "Please fill in all fields.", vbExclamation
        Exit Sub
    End If

    If Not IsNumeric(Me.txtSalary.Value) Then
        MsgBox "Salary must be numeric.", vbExclamation
        Exit Sub
    End If

    salaryValue = CDbl(Me.txtSalary.Value)

    If salaryValue < 0 Then
        MsgBox "Salary cannot be negative.", vbExclamation
        Exit Sub
    End If

    If MsgBox("Update this employee record?", _
        vbQuestion + vbYesNo, "Confirm Update") <> vbYes Then
        Exit Sub
    End If

    Set ws = GetEmployeeSheet()
    If ws Is Nothing Then Exit Sub

    ws.Cells(recordRow, "B").Value = employeeName
    ws.Cells(recordRow, "C").Value = department
    ws.Cells(recordRow, "D").Value = salaryValue

    MsgBox "Employee record updated successfully.", vbInformation
End Sub
Important: The Employee ID is used to locate the row and is not changed by this procedure.

8. DELETE Operation

Delete removes the worksheet row matching the entered Employee ID. The user must confirm the operation before the row is removed.

Private Sub cmdDelete_Click()
    Dim ws As Worksheet
    Dim employeeID As String
    Dim recordRow As Long

    employeeID = Trim$(CStr(Me.txtID.Value))

    If employeeID = "" Then
        MsgBox "Enter an Employee ID to delete.", vbExclamation
        Exit Sub
    End If

    recordRow = FindEmployeeRow(employeeID)

    If recordRow = 0 Then
        MsgBox "Employee record not found.", vbInformation
        Exit Sub
    End If

    If MsgBox("Permanently delete Employee ID " & employeeID & "?", _
        vbQuestion + vbYesNo + vbDefaultButton2, _
        "Confirm Delete") <> vbYes Then
        Exit Sub
    End If

    Set ws = GetEmployeeSheet()
    If ws Is Nothing Then Exit Sub

    ws.Rows(recordRow).Delete

    MsgBox "Employee record deleted successfully.", vbInformation
    ClearFields
End Sub
Warning: Deleting a row removes its data from the worksheet. Test this operation on sample records before using it with important workbook data.

9. Clear the UserForm

The Clear button removes the values from the form fields without changing any worksheet records.

Private Sub cmdClear_Click()
    ClearFields
End Sub

10. Run the UserForm

Insert a standard VBA Module and add the following procedure to open the form.

Public Sub OpenEmployeeForm()
    frmEmployee.Show
End Sub
Execution Steps
  1. Save the workbook as an Excel Macro-Enabled Workbook (.xlsm).
  2. Confirm the worksheet is named Employees.
  3. Confirm the UserForm is named frmEmployee.
  4. Confirm all control names match the code.
  5. Run OpenEmployeeForm from the VBA editor.
  6. Test Create, Read, Update, and Delete using sample records.

11. Practice Exercises

Exercise 1: Add Email

Add an Email TextBox and save the email address in column E when creating and updating employee records.

Exercise 2: Search by Name

Create a search feature that finds an employee using their name instead of Employee ID.

Exercise 3: Display All Records

Add a ListBox to display all employee records from the worksheet.

Exercise 4: Improve Validation

Add additional validation for Employee ID format, department values, and salary limits.

Important Points to Remember

  • CRUD stands for Create, Read, Update, and Delete.
  • Use a unique Employee ID to identify each record.
  • Validate user input before saving data.
  • Confirm destructive operations before deleting records.
  • Worksheet and control names must match the VBA code.
  • Keep a backup before testing delete functionality.

Summary

VBA UserForms provide a simple interface for managing worksheet data. Create inserts records, Read retrieves records, Update modifies existing records, and Delete removes records after confirmation.

Combining CRUD operations with validation and unique IDs helps you build a basic Employee Management System in Excel.