Beginner's Guide to Excel VBA Automation

1. Enable Developer Tab & Open VBA Editor

Step 1: Go to File → Options → Customize Ribbon → Check "Developer"
Step 2: Click Developer → Visual Basic (or press Alt + F11)

2. Record Your First Macro (Easiest Way)

Go to Developer → Record Macro → Perform actions (e.g. format a report) → Stop Recording
Excel automatically writes the VBA code for you!

3. Simple Example: Auto-Format Monthly Report

Sub FormatMonthlyReport()
    Range("A1").Value = "Monthly Sales Report - " & Format(Date, "MMMM YYYY")
    Range("A1").Font.Size = 16
    Range("A1").Font.Bold = True
    Range("A:J").EntireColumn.AutoFit
    Range("A2:J100").Borders.LineStyle = xlContinuous
    MsgBox "Report formatted successfully!", vbInformation
End Sub

Run this once — your report is perfectly formatted in 2 seconds!

4. Auto-Fill Invoice Numbers (Using Loops)

Sub AutoNumberInvoices()
    Dim i As Integer
    For i = 2 To 51
        Cells(i, 1).Value = "INV-" & Format(Date, "YYYYMM") & "-" & Format(i - 1, "000")
    Next i
    MsgBox "50 invoices numbered automatically!"
End Sub

Generates: INV-202511-001, INV-202511-002, etc.

5. Highlight High-Value Sales (If Statement)

Sub HighlightBigSales()
    Dim cell As Range
    For Each cell In Range("D2:D1000")
        If cell.Value > 50000 Then
            cell.Interior.Color = RGB(144, 238, 144)  ' Light green
            cell.Font.Bold = True
        End If
    Next cell
End Sub

Automatically highlights all sales above KSh 50,000

6. One-Click PDF Export (Save Hours!)

Sub ExportToPDF()
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
        Filename:="C:\Reports\Sales_Report_" & Format(Date, "YYYY-MM-DD") & ".pdf", _
        Quality:=xlQualityStandard, IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, OpenAfterPublish:=True
    MsgBox "PDF exported successfully!"
End Sub

No more Print → Save as PDF → Rename manually!

7. Run Macro with Keyboard Shortcut

Right-click macro → Options → Assign Ctrl + Shift + F
Now press Ctrl + Shift + F → Report formatted instantly!

Real Kenyan Business Example

A Mombasa retailer used to spend 3 hours every Friday formatting and emailing reports.
After one 30-minute VBA macro → Now takes 8 seconds with one click.