Welcome to ESE Learning Platform

Master VBA Excel Programming and Database Conception

VBA Excel Mastery

Learn macro recording, VBA programming, and Excel automation

Database Conception

Master database design and implementation concepts

Macro Recording in Excel

What is Macro Recording?

Macro recording is Excel's built-in feature that captures your actions and converts them into VBA code automatically. It's the easiest way to start with Excel automation.

Step-by-Step Guide

1

Enable Developer Tab

Go to File → Options → Customize Ribbon → Check "Developer"

Screenshot: Enabling Developer Tab

2

Start Recording

Click Developer → Record Macro → Give it a name → OK

Macro Name: FormatCells
Shortcut: Ctrl+Shift+F
Store in: This Workbook
3

Perform Actions

Execute the actions you want to automate (formatting, data entry, etc.)

4

Stop Recording

Click Developer → Stop Recording

Example: Recording a Formatting Macro

Let's record a macro that formats text as Bold, Italic, Times New Roman, size 16, green color:

Sub FormatText()
    Selection.Font.Bold = True
    Selection.Font.Italic = True
    Selection.Font.Name = "Times New Roman"
    Selection.Font.Size = 16
    Selection.Font.Color = RGB(0, 255, 0)
End Sub

Creating Macros Manually

Manual Macro Creation

Learn to write VBA code from scratch to create powerful Excel automation.

Accessing VBA Editor

1

Open VBA Editor

Press Alt + F11 or Developer → Visual Basic

2

Insert Module

Right-click on VBAProject → Insert → Module

3

Write Your Code

Sub MyFirstMacro()
    MsgBox "Welcome to ESE Oran"
End Sub

Common Macro Patterns

1. Cell Value Assignment

Sub AssignValues()
    Range("A1").Value = 25
    Range("A2").Value = 12
    Cells(4, 1).Value = 12  ' Row 4, Column 1
End Sub

2. Basic Calculations

Sub CalculateSum()
    Dim sum As Double
    sum = Range("A1").Value + Range("A2").Value
    Range("A3").Value = sum
End Sub

Worksheet 1 - Excel Basics & Formulas

Student Grade Management System

Create and manage a student grading system with automatic calculations and formatting.

Student Data Table

Code BAC Family Name First Name Grade 1 (coef 5) Grade 2 (coef 4) Grade 3 (coef 1) Grade 4 (coef 2) Average Decision Observation
SGE 001 SC BEN AMINA Yacine 12 19 4 15 =FORMULA =FORMULA =FORMULA
SGE 002 SG KADRI JAMEL 15 15 15 14 =FORMULA =FORMULA =FORMULA

Tasks to Complete:

  1. Reproduce the table in an Excel workbook
  2. Calculate student averages using weighted coefficients
  3. Format averages to show only 2 decimal places
  4. Decision formula: Display "Adjourned" or "Received"
  5. Observation formula: Display grade categories based on average
  6. Create a chart comparing different grades
  7. Sort students by ascending order of averages
  8. Filter the list for averages ≥ 10
  9. Group the grade columns
  10. Apply conditional formatting with colors for each observation

Required Formulas:

Average Calculation (Weighted):

=(E10*5 + F10*4 + G10*1 + H10*2)/(5+4+1+2)

Decision Formula:

=IF(I10>=10,"Received","Adjourned")

Observation Formula:

=IF(I10<10,"Adjourned",IF(I10<12,"Fair",IF(I10<14,"Quite good",IF(I10<16,"Good","Very good"))))

Worksheet 2 - Macro Recording & VBA Introduction

VBA Macro Recording Exercise

Learn to record macros and understand the generated VBA code.

Step-by-Step Instructions:

  1. Open Excel workbook
  2. Add Developer tab to the ribbon
  3. View the VB editor (Alt + F11)
  4. Record a formatting macro with these specifications:
    • Bold text
    • Italic text
    • Font: Times New Roman
    • Size: 16
    • Color: Green
  5. View the generated code
  6. Run the macro
  7. Optimize the macro code
  8. Create a button named "MEF" and assign the macro
  9. Create a keyboard shortcut
  10. Add toolbar button
  11. Use RGB method for color
  12. Add success message at the end

Expected VBA Code:

Original Recorded Code:

Sub FormatText()
    Selection.Font.Bold = True
    Selection.Font.Italic = True
    Selection.Font.Name = "Times New Roman"
    Selection.Font.Size = 16
    Selection.Font.ThemeColor = xlThemeColorAccent6
End Sub

Optimized Code with RGB:

Sub FormatText_Optimized()
    With Selection.Font
        .Bold = True
        .Italic = True
        .Name = "Times New Roman"
        .Size = 16
        .Color = RGB(0, 255, 0)  ' Green color
    End With
    MsgBox "Operation completed successfully"
End Sub

Worksheet 3 - VBA Programming Fundamentals

Programming Exercises

Exercise 1: Welcome Message

Task: Write a macro that displays the message "Welcome to ESE Oran"

Sub Exercise1()
    MsgBox "Welcome to ESE Oran"
End Sub

Exercise 2: Active Cell Information

Task: Display the value, row index, column index, and cell reference of ActiveCell

Sub Exercise2()
    With ActiveCell
        MsgBox "Value: " & .Value & vbCrLf & _
               "Row: " & .Row & vbCrLf & _
               "Column: " & .Column & vbCrLf & _
               "Address: " & .Address
    End With
End Sub

Exercise 3: Sheet Operations

Tasks:

  • Activate Sheet1
  • Assign value 12 to cell D4
  • Assign value 25 to cells A1 and A2
  • Add A1 + A2 and put result in A3
  • Change border style and fill color for range A1:A4
Sub Exercise3()
    ' Activate Sheet1
    Sheets("Sheet1").Activate
    
    ' Assign values
    Cells(4, 4).Value = 12        ' D4
    Range("A1").Value = 25
    Range("A2").Value = 25
    
    ' Calculate sum
    Range("A3").Value = Range("A1").Value + Range("A2").Value
    
    ' Format range A1:A4
    With Range("A1:A4")
        .Borders.LineStyle = xlContinuous
        .Interior.Color = RGB(255, 255, 0)  ' Yellow fill
    End With
End Sub

Worksheet 4 - Advanced VBA: Invoice Generator

Invoice Template Generator

Create a sophisticated invoice generator with automatic calculations and formatting.

Invoice Template Structure:

INFO FUTURE
Invoice No_______
DATEPayment methodAdvance paymentBalance due
N°ItemQuantityUnit PriceAmount
1
2
Subtotal Excluding Tax
Discount
VAT 21%
Total Including Tax

Macro Requirements:

1. Create Macro "create":

  • Automatically create invoice template
  • Use AutoFill for item numbers
  • Rename sheet to "Invoice"
  • Calculate amounts using FormulaA1 and FormulaR1C1
  • Calculate 10% discount if total > 20,000
  • Apply currency format
Sub Create()
    ' Create invoice template
    Sheets.Add.Name = "Invoice"
    
    ' Headers
    Range("A1:F1").Merge
    Range("A1").Value = "INFO FUTURE"
    Range("A1").Font.Size = 16
    Range("A1").Font.Bold = True
    Range("A1").HorizontalAlignment = xlCenter
    
    ' Invoice details
    Range("A3").Value = "Invoice No"
    Range("A4").Value = "DATE"
    Range("B4").Value = "Payment method"
    
    ' Item headers
    Range("A6").Value = "N°"
    Range("B6").Value = "Item"
    Range("C6").Value = "Quantity"
    Range("D6").Value = "Unit Price"
    Range("E6").Value = "Amount"
    
    ' AutoFill item numbers 1-8
    Range("A7").Value = 1
    Range("A7:A14").DataSeries Rowcol:=xlColumns, Type:=xlLinear, Step:=1
    
    ' Totals section
    Range("A16").Value = "Subtotal Excluding Tax"
    Range("A17").Value = "Discount"
    Range("A18").Value = "VAT 21%"
    Range("A19").Value = "Total Including Tax"
    
    ' Formulas using FormulaA1
    Range("E16").Formula = "=SUM(E7:E14)"
    Range("E17").Formula = "=IF(E16>20000,E16*0.1,0)"
    Range("E18").Formula = "=(E16-E17)*0.21"
    Range("E19").Formula = "=E16-E17+E18"
    
    ' Currency formatting
    Range("D7:E19").NumberFormat = "€#,##0.00"
    
    ' Borders and formatting
    Range("A1:E19").Borders.LineStyle = xlContinuous
End Sub

2. New Invoice Macro:

Sub NewInvoice()
    Dim sheetName As String
    sheetName = InputBox("Enter new sheet name:", "New Invoice")
    
    If sheetName <> "" Then
        Sheets("Invoice").Copy After:=Sheets(Sheets.Count)
        ActiveSheet.Name = sheetName
        ' Clear data but keep template
        Range("E7:E14").ClearContents
    End If
End Sub

Additional Tasks:

  1. Create buttons in Quick Access Toolbar
  2. Assign macros to buttons
  3. Test with sample invoice data
  4. Verify all calculations work correctly

Database Conception

Entity-Relationship Modeling

Learn to design database schemas using ER diagrams

Normalization

Understand normal forms and database optimization

SQL Basics

Master SQL queries, joins, and data manipulation

Worksheet 1 - Complete Solution

Step 1: Table Creation

Create the student table with all required columns and sample data.

Step 2: Average Formula

Formula for cell I10:
=(E10*5+F10*4+G10*1+H10*2)/(5+4+1+2)

Step 3: Decision Formula

Formula for cell J10:
=IF(I10>=10,"Received","Adjourned")

Step 4: Observation Formula

Formula for cell K10:
=IF(I10<10,"Adjourned",IF(I10<12,"Fair",IF(I10<14,"Quite good",IF(I10<16,"Good","Very good"))))