VBA Automation — Let AI Write the Macros You Can't
Chapter 15: VBA Basics — Let Excel Do the Repetitive Work
VBA (Visual Basic for Applications) is the built-in programming language in Excel that lets it automatically execute a series of operations. This chapter's core thesis: you don't need to learn to code — just learn to collaborate with AI to write VBA code. Eight complete, runnable VBA examples, each with an AI prompt, ready to use immediately.
What Is VBA and Why Learn It
Think about tasks you repeat weekly: merging department Excel files into one master sheet, the same copy-paste-format steps every time. VBA turns those repetitive sequences into code that runs in seconds with a single click.
When VBA Is the Right Tool
- Batch operations: apply the same formatting to 100 worksheets; delete a column from every sheet in a workbook
- Automated reports: summarize, calculate, and format data into a standardized daily/weekly report on schedule
- Complex one-click tasks: split one table into separate files by column value; generate individual payslips from a single payroll sheet
- Office integration: send Outlook emails from Excel, create Word documents, populate PowerPoint slides
No Programming Background Needed
Your workflow: describe what you want → AI writes the code → paste into VBA editor → run → if errors, paste the error message back to AI for a fix.
Recording Macros: Zero-Code VBA
Recording a macro translates your Excel actions into VBA code automatically. Steps: Developer tab → Record Macro → perform your actions → Stop Recording. Press Alt+F11 to view the generated code in the VBA editor.
Case 1: One-Click Report Formatting
Sub FormatReport() Dim ws As Worksheet Dim lastRow As Long, lastCol As Long, i As Long
Set ws = ActiveSheet
lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
lastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
With ws.Rows(1)
.Font.Bold = True
.Font.Color = RGB(255, 255, 255)
.Interior.Color = RGB(31, 78, 121)
.RowHeight = 30
End With
For i = 2 To lastRow
If i Mod 2 = 0 Then
ws.Rows(i).Interior.Color = RGB(255, 255, 255)
Else
ws.Rows(i).Interior.Color = RGB(217, 230, 245)
End If
Next i
ws.Columns(1).Resize(1, lastCol).AutoFit
With ws.Range(ws.Cells(1, 1), ws.Cells(lastRow, lastCol)).Borders
.LineStyle = xlContinuous
.Weight = xlThin
End With
MsgBox "Formatting complete! " & lastRow & " rows processed.", vbInformation
End Sub
Case 2: Create a Date-Named Sheet
Sub CreateDatedSheet() Dim sheetName As String sheetName = Format(Now(), "YYYY-MM-DD") Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets If ws.Name = sheetName Then MsgBox "Sheet '" & sheetName & "' already exists!", vbExclamation Exit Sub End If Next ws Dim newWs As Worksheet Set newWs = ThisWorkbook.Worksheets.Add(After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)) newWs.Name = sheetName MsgBox "Created sheet: " & sheetName, vbInformation End Sub
VBA Basics: Reading AI-Generated Code
' Variables Dim lastRow As Long ' integer Dim sheetName As String ' text Dim ws As Worksheet ' object — use Set to assign Set ws = ActiveSheet
' Conditions If ws.Cells(i, 3).Value > 100000 Then ws.Cells(i, 3).Interior.Color = RGB(198, 239, 206) ElseIf ws.Cells(i, 3).Value
Debugging: Press F8 in the VBA editor to step through code line by line. Use Ctrl+G to open the Immediate Window and print variable values with Debug.Print myVar.
AI Writes VBA: 8 Complete Cases
Case 1: Merge All Sheets into One Master Sheet
Sub MergeAllSheets() Dim ws As Worksheet, masterWs As Worksheet Dim lastRow As Long, masterLastRow As Long Dim headerCopied As Boolean
Application.DisplayAlerts = False
On Error Resume Next
ThisWorkbook.Worksheets("Master").Delete
On Error GoTo 0
Application.DisplayAlerts = True
Set masterWs = ThisWorkbook.Worksheets.Add(After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count))
masterWs.Name = "Master"
headerCopied = False
masterLastRow = 1
For Each ws In ThisWorkbook.Worksheets
If ws.Name <> "Master" Then
lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
If lastRow >= 1 Then
If Not headerCopied Then
ws.Rows(1).Copy masterWs.Rows(1)
masterLastRow = 2
headerCopied = True
End If
If lastRow > 1 Then
ws.Rows("2:" & lastRow).Copy masterWs.Rows(masterLastRow)
masterLastRow = masterLastRow + lastRow - 1
End If
End If
End If
Next ws
masterWs.Columns.AutoFit
MsgBox "Done! Merged " & (masterLastRow - 2) & " rows into Master sheet.", vbInformation
End Sub
Case 2: Split Sheet by Column Value (by Department)
Sub SplitByDepartment() Dim ws As Worksheet, newWs As Worksheet Dim lastRow As Long, i As Long, destRow As Long Dim deptName As String Dim deptDict As Object
Set ws = ActiveSheet
lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
Set deptDict = CreateObject("Scripting.Dictionary")
Application.ScreenUpdating = False
For i = 2 To lastRow
deptName = ws.Cells(i, 2).Value
If deptName <> "" And Not deptDict.Exists(deptName) Then
deptDict.Add deptName, True
Application.DisplayAlerts = False
On Error Resume Next
ThisWorkbook.Worksheets(deptName).Delete
On Error GoTo 0
Application.DisplayAlerts = True
Set newWs = ThisWorkbook.Worksheets.Add(After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count))
newWs.Name = deptName
ws.Rows(1).Copy newWs.Rows(1)
End If
Next i
For i = 2 To lastRow
deptName = ws.Cells(i, 2).Value
If deptName <> "" Then
Set newWs = ThisWorkbook.Worksheets(deptName)
destRow = newWs.Cells(newWs.Rows.Count, 1).End(xlUp).Row + 1
ws.Rows(i).Copy newWs.Rows(destRow)
End If
Next i
Application.ScreenUpdating = True
MsgBox "Split complete! Created " & deptDict.Count & " department sheets.", vbInformation
End Sub
Case 3: Format All Sheets at Once
Sub FormatAllSheets() Dim ws As Worksheet Dim lastRow As Long, lastCol As Long Application.ScreenUpdating = False For Each ws In ThisWorkbook.Worksheets lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row lastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column If lastRow >= 1 And lastCol >= 1 Then With ws.Rows(1) .Font.Bold = True .Interior.Color = RGB(31, 78, 121) .Font.Color = RGB(255, 255, 255) .RowHeight = 24 End With ws.Columns(1).Resize(1, lastCol).AutoFit End If Next ws Application.ScreenUpdating = True MsgBox "Formatted " & ThisWorkbook.Worksheets.Count & " sheets.", vbInformation End Sub
Case 4: Auto-Send Outlook Emails with Attachments
⚠️ Warning
Prerequisite: Outlook must be installed and configured. Enable "Microsoft Outlook xx.x Object Library" in VBA Editor → Tools → References.
Sub SendEmailWithAttachment() ' Columns: A=Name, B=Email, C=Attachment path (optional) Dim outlookApp As Object, mailItem As Object Dim ws As Worksheet, lastRow As Long, i As Long, sentCount As Long
Set ws = ActiveSheet
lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
Set outlookApp = CreateObject("Outlook.Application")
sentCount = 0
For i = 2 To lastRow
Dim recipientName As String, recipientEmail As String, attachPath As String
recipientName = ws.Cells(i, 1).Value
recipientEmail = ws.Cells(i, 2).Value
attachPath = ws.Cells(i, 3).Value
If recipientEmail <> "" Then
Set mailItem = outlookApp.CreateItem(0)
With mailItem
.To = recipientEmail
.Subject = "Monthly Report — March 2024"
.Body = "Dear " & recipientName & "," & vbCrLf & vbCrLf & _
"Please find the monthly report attached." & vbCrLf & vbCrLf & _
"Best regards," & vbCrLf & "Data Analytics Team"
If attachPath <> "" And Dir(attachPath) <> "" Then
.Attachments.Add attachPath
End If
.Send
End With
sentCount = sentCount + 1
Set mailItem = Nothing
End If
Next i
Set outlookApp = Nothing
MsgBox "Done! Sent " & sentCount & " emails.", vbInformation
End Sub
Case 5: Generate Individual Payslips
Sub GeneratePaystubs() Dim srcWs As Worksheet, destWs As Worksheet Dim lastRow As Long, i As Long, destRow As Long
Set srcWs = ActiveSheet
lastRow = srcWs.Cells(srcWs.Rows.Count, 1).End(xlUp).Row
Application.DisplayAlerts = False
On Error Resume Next
ThisWorkbook.Worksheets("Payslips").Delete
On Error GoTo 0
Application.DisplayAlerts = True
Set destWs = ThisWorkbook.Worksheets.Add(After:=srcWs)
destWs.Name = "Payslips"
destRow = 1
Application.ScreenUpdating = False
For i = 2 To lastRow
srcWs.Rows(1).Copy destWs.Rows(destRow) ' header
destRow = destRow + 1
srcWs.Rows(i).Copy destWs.Rows(destRow) ' data
destRow = destRow + 2 ' blank row separator
Next i
destWs.Columns.AutoFit
Application.ScreenUpdating = True
MsgBox "Generated " & (lastRow - 1) & " payslips in the 'Payslips' sheet.", vbInformation
End Sub
Case 6: Batch Replace Content in Multiple Workbooks
Sub BatchReplaceInFolder() Dim folderPath As String, fileName As String Dim wb As Workbook, ws As Worksheet Dim processedCount As Long
folderPath = "C:\Reports\" ' Change to your folder path
Dim findText As String: findText = "2023"
Dim replaceText As String: replaceText = "2024"
processedCount = 0
Application.ScreenUpdating = False
Application.DisplayAlerts = False
fileName = Dir(folderPath & "*.xlsx")
Do While fileName <> ""
Set wb = Workbooks.Open(folderPath & fileName)
For Each ws In wb.Worksheets
ws.Cells.Replace What:=findText, Replacement:=replaceText, LookAt:=xlPart, MatchCase:=False
Next ws
wb.Save
wb.Close
processedCount = processedCount + 1
fileName = Dir()
Loop
Application.ScreenUpdating = True
Application.DisplayAlerts = True
MsgBox "Done! Processed " & processedCount & " files.", vbInformation
End Sub
Case 7: Auto-Backup with Timestamp
Sub AutoBackup() Dim backupFolder As String, backupPath As String, timestamp As String
backupFolder = ThisWorkbook.Path & "\Backup\"
timestamp = Format(Now(), "YYYY-MM-DD_HH-MM-SS")
If Dir(backupFolder, vbDirectory) = "" Then MkDir backupFolder
Dim baseName As String
baseName = Left(ThisWorkbook.Name, InStrRev(ThisWorkbook.Name, ".") - 1)
backupPath = backupFolder & baseName & "_" & timestamp & ".xlsx"
ThisWorkbook.Save
ThisWorkbook.SaveCopyAs backupPath
MsgBox "Backup saved:" & vbCrLf & backupPath, vbInformation
End Sub
Case 8: Data Validation + Error Log
Sub ValidateAndLog() Dim ws As Worksheet, logWs As Worksheet Dim lastRow As Long, i As Long, errorCount As Long, logRow As Long
Set ws = ActiveSheet
lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
On Error Resume Next
Set logWs = ThisWorkbook.Worksheets("ValidationLog")
On Error GoTo 0
If logWs Is Nothing Then
Set logWs = ThisWorkbook.Worksheets.Add(After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count))
logWs.Name = "ValidationLog"
logWs.Range("A1:E1").Value = Array("Timestamp", "Row", "Name", "Field", "Error")
End If
logRow = logWs.Cells(logWs.Rows.Count, 1).End(xlUp).Row + 1
errorCount = 0
For i = 2 To lastRow
Dim errMsg As String: errMsg = ""
If ws.Cells(i, 1).Value = "" Then errMsg = "Name required"
If Not IsNumeric(ws.Cells(i, 3).Value) Or ws.Cells(i, 3).Value "" Then errMsg = errMsg & "; "
errMsg = errMsg & "Salary must be a positive number"
End If
If errMsg <> "" Then
ws.Cells(i, 1).Interior.Color = RGB(255, 199, 206)
logWs.Cells(logRow, 1).Value = Now()
logWs.Cells(logRow, 2).Value = i
logWs.Cells(logRow, 3).Value = ws.Cells(i, 1).Value
logWs.Cells(logRow, 5).Value = errMsg
logRow = logRow + 1
errorCount = errorCount + 1
End If
Next i
logWs.Columns.AutoFit
If errorCount = 0 Then
MsgBox "All " & (lastRow - 1) & " rows passed validation.", vbInformation
Else
MsgBox errorCount & " rows have issues. See 'ValidationLog' sheet.", vbExclamation
End If
End Sub
Debugging and Common Errors
Error 1004: Application-defined or object-defined error
Typical cause: referencing a worksheet that doesn't exist, or operating on a protected range.
AI Fix Prompt: My VBA code throws "Run-time error 1004" on this line: [paste the line]. The code is supposed to [describe purpose]. Please explain the cause and provide a fix.
Error 91: Object variable or With block variable not set
Typical cause: object variable not assigned with Set, or a Find operation returned Nothing before it was used.
AI Fix Prompt: VBA throws "Run-time error 91: Object variable or With block variable not set". Full code: [paste]. Goal is [describe]. Please find and fix the issue.
Error 9: Subscript out of range
Typical cause: accessing a worksheet by name/index that doesn't exist, or an array index exceeds bounds.
AI Fix Prompt: VBA throws "Subscript out of range" on: [paste line]. My workbook has these sheets: [list names]. Please explain and fix.
Security Notes
❌ Caution
Never run macros from unknown sources. VBA can execute arbitrary file operations, network requests, and system commands. Ransomware commonly spreads through Excel macros. Only enable macros in files from trusted senders.
- Set macro security to "Disable all macros with notification" — File → Options → Trust Center → Macro Settings
- Save macro-enabled files as .xlsm — the .xlsx format strips all macros
- Always back up files before running macros that delete or overwrite data
VBA Code Snippet Reference
| Task | Code |
|---|---|
| Last row with data | ws.Cells(ws.Rows.Count,1).End(xlUp).Row |
| Last column with data | ws.Cells(1,ws.Columns.Count).End(xlToLeft).Column |
| Loop all sheets | For Each ws In Worksheets ... Next ws |
| Add new sheet at end | Worksheets.Add(After:=Worksheets(Worksheets.Count)) |
| Delete sheet (no prompt) | Application.DisplayAlerts=False / ws.Delete |
| Paste values only | dest.PasteSpecial xlPasteValues |
| Open file dialog | Application.GetOpenFilename("Excel,*.xlsx") |
| Disable screen flicker | Application.ScreenUpdating = False |
| User input box | val = InputBox("Enter department:") |
| Write to cell | ws.Cells(row, col).Value = "data" |
Previous ← Chapter 14: Dynamic Array Functions Next Chapter 16: Python + Excel Automation →