Excel 是最強大的報表引擎之一。VBA 控制 Excel 產生專業報表,核心在於:**理解物件模型 (Application → Workbook → Worksheet → Range)**、**避開錄製巨集的冗長代碼**、**採用陣列批次寫入**、**善用屬性簡化格式化**。本文從表頭合併、欄寬自適應、文字換行、批次寫入、迴圈產生多表、邊框繪製六大實戰場景,示範「錄製巨集 → 精簡重構」的完整過程。
' 錄製巨集產生的冗長代碼 (略)
' 精簡版:
ws.Range("A2:A5").MergeCells = True
' 或指定區塊
ws.Range(ws.Cells(2, 1), ws.Cells(5, 1)).MergeCells = True
**關鍵屬性**:`MergeCells`、`HorizontalAlignment = xlCenter`、`VerticalAlignment = xlCenter`、`WrapText`、`Orientation`、`ShrinkToFit`。
' 固定欄寬 (第 14 欄 = 12 字元寬度)
ws.Columns(14).ColumnWidth = 12
' 範圍自動調整 (A:I 欄)
ws.Columns("A:I").AutoFit
' 禁止自動調整、鎖定欄寬
ws.Columns("A:I").ColumnWidth = 15 ' 先 AutoFit 再鎖定最佳寬度
' 第 3 列文字換行
ws.Rows(3).WrapText = True
' 垂直靠上 (預設置中,換行後靠上較美觀)
ws.Rows(3).VerticalAlignment = xlTop
' 同步調整列高 (AutoFit 會自動處理,但可手動微調)
ws.Rows(3).AutoFit
' 第 2 列底色藍色 (ColorIndex 5 = 藍色)
ws.Rows(2).Interior.ColorIndex = 5
ws.Rows(2).Font.Color = vbWhite ' 白字
' 第 1 列標題紅字 (ColorIndex 3 = 紅)
ws.Rows(1).Font.ColorIndex = 3
ws.Rows(1).Font.Bold = True
**現代寫法**:使用 `ThemeColor` / `RGB` 取代 `ColorIndex` (56 色限制)。
ws.Rows(2).Interior.Color = RGB(0, 51, 102) ' 深藍
ws.Rows(2).Font.Color = RGB(255, 255, 255)
Dim dataArr(1 To 1000, 1 To 20) As Variant
Dim r As Long, c As Long
' 1. 記憶體中組裝資料 (模擬)
For r = 1 To 1000
For c = 1 To 20
dataArr(r, c) = "R" & r & "C" & c
Next c
Next r
' 2. 一次寫入 (Range 大小必須與陣列維度完全一致)
ws.Range("A2").Resize(1000, 20).Value = dataArr
' 或指定左上右下
' ws.Range(ws.Cells(2,1), ws.Cells(1001, 20)).Value = dataArr
**關鍵點**:
Dim ws As Worksheet
Dim rStart As Long, cStart As Long, rEnd As Long, cEnd As Long
Dim dataArr(1 To 10, 1 To 5) As Variant
Dim i As Long
Set ws = ThisWorkbook.Worksheets("Report")
For i = 1 To 40
' 1. 產生/取得本期資料 → 填入 dataArr (略)
' 2. 計算目標區塊 (每張表佔 5 列,間隔 1 列)
rStart = (i - 1) * 6 + 2
rEnd = rStart + 9 ' 10 列資料
cStart = 1
cEnd = 5
' 3. 批次寫入
ws.Range(ws.Cells(rStart, cStart), ws.Cells(rEnd, cEnd)).Value = dataArr
' 4. 畫邊框 (一次套用整個區塊)
With ws.Range(ws.Cells(rStart, cStart), ws.Cells(rEnd, cEnd)).Borders
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Next i
With rng.Borders(xlEdgeBottom)
.LineStyle = xlDouble
.Weight = xlThick
.Color = RGB(0, 51, 102)
End With
With rng.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlHairline
End With
With rng.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlHairline
End With
Sub ReportGenerator()
On Error GoTo Cleanup
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
' ... 核心報表產生邏輯 ...
Cleanup:
Application.ScreenUpdating = True
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
If Err.Number <> 0 Then MsgBox Err.Description, vbCritical
End Sub
| 坑點 | 現象 | 解法 |
|---|---|---|
| 合併儲存格後寫入陣列 | 資料錯位、只寫入左上儲存格 | 先寫入資料、再合併;或避開合併區寫入 |
| 隱藏工作表操作 | `ws.Range` 失效 | 先 `ws.Visible = xlSheetVisible`、操作完再隱藏 |
| 日期/數值格式被自動轉換 | 長數字變科學記號、日期格式錯誤 | 預先設定 `NumberFormat = "@"` (文字) 或 `yyyy/mm/dd` |
| 大量資料寫入卡頓 | UI 凍結、記憶體飆升 | 分批寫入 (每 5000 列)、關閉三大件、考慮 ADO 直接寫入 |
| 儲存為 CSV 編碼問題 | 中文亂碼 | 用 ADO `SaveAs` 指定 `65001` (UTF-8) 或 `950` (Big5) |
Excel VBA 報表自動化的核心心法:**「記憶體組裝陣列 → 一次性貼上 Range → 批次套用格式/邊框 → 關閉三大件」**。摒棄錄製巨集的 `Select`/`Activate`、逐儲存格寫入,即可從「能跑」進化為「秒出千行專業報表」。進階可引入 **Excel-DNA (C# UDF)**、**Python xlwings**、**Office Scripts (TypeScript)** 將邏輯移出 VBA,擁抱現代開發生態。