Excel VBA 入入門

Excel 有 許 多 許 多 人 在 用 ,但 我 工 作 了 幾 十 年 ,遇 到 的 大 部 分 人 都 只 是 在 用 Excel 的 皮 毛 。甚 至 有 很 多 人 ,還 只 是 把 Excel 用 來 打 個 Table,又 或 者 用 來 計 算 下 簡 單 的 加 減 乘 除 數 。

當 然 Excel 裡 面 的 功 能 相 當 多 ,但 作 為 programmer,我 最 喜 歡 用 的 還 是 VBA。VBA 的 全 名 是 Visual Basic for Applications,是 專 門 在 Microsoft Office 環 境 裡 面 用 的 programming language。

使 用 VBA 可 以 非 常 容 易 地 manipulate 所 有 Microsoft Office 的 文 件 格 式 ,例 如 Word、Excel 和 PowerPoint 等 等 。也 可 以 用 VBA 實 現 Excel 原 本 沒 有 的 功 能 。

例 如 早 幾 天 有 個 舊 同 學 ,問 我 用 那 一 條 formula 可 以 自 動 把 sheet1 的 column A copy 去 sheet2。用 formula 我 也 想 不 到 什 麼 好 方 法 ,但 如 果 用 VBA 的 話 ,其 實 幾 行 程 式 碼 就 可 以 完 工 了 。

今 天 就 用 這 個 超 簡 單 的 VBA 做 為 例 子 ,給 大 家 簡 介 一 下 VBA 吧 。

曾 幾 何 時 ,VBA 是 電 腦 病 毒 的 溫 床 ,風 評 非 常 之 惡 劣 ,所 以 後 來 Microsoft 針 對 VBA 增 加 了 很 多 安 全 措 施 。

首 先 ,VBA 功 能 預 設 是 半 隱 藏 的 。如 果 想 要 在 ribbon ( 功 能 區 ) 啟 用 VBA 的 相 關 功 能 ,就 要 先 打 開 Excel 選 項 ( Options ),去 到 「自 訂 功 能 區 」( Customize Ribbon ),然 後 勾 選 「開 發 人 員 」( Developer )。

然 後 ,在 ribbon 上 就 會 多 了 一 頁 ,就 叫 做 「開 發 人 員 」。在 這 一 頁 ,會 有 很 多 VBA 相 關 的 功 能 。例 如 可 以 在 Excel 插 入 控 制 項 ,可 以 控 制 巨 集 ,可 以 打 開 Visual Basic 編 輯 器 等 等 。

第 二 ,Microsoft 已 經 disable 了 一 般 excel 檔 案 ( *.xlsx ) 裡 面 的 VBA。要 啟 動 VBA 功 能 ,就 要 把 檔 案 儲 存 成 *.xlsm 格 式 。

VBA 是 一 種 object-oriented 的 程 式 語 言 。簡 單 說 ,就 是 可 以 針 對 一 個 個 object 來 寫 程 式 。那 excel 裡 面 有 什 麼 object 呢 ?其 實 你 見 得 到 的 都 是 objects,例 如 一 個 workbook、一 個 worksheet、一 個 row、一 個 column、一 個 cell 等 等 等 等 ,通 通 都 是 objects。

每 一 種 objects 都 有 其 自 身 的 properties、methods、events 等 等 等 等 。例 如 每 一 個 cell 都 可 以 儲 存 一 個 值 ,那 就 是 Value property。

Range("A1").Value

用 以 上 的 程 式 碼 ,就 可 以 讀 取 cell A1 裡 面 的 值 。

Range("A1").Value = "Abc"

以 上 的 程 式 碼 ,就 是 把 字 串 「Abc」,寫 入 到 cell A1。當 然 ,並 不 是 每 一 個 object 都 有 value 的 ,例 如 worksheet、column 之 類 ,就 是 沒 有 value 的 。如 果 你 在 VBA 指 定 了 不 存 在 的 property,執 行 時 就 會 出 現 錯 誤 。

VBA 也 是 event-driven 的 程 式 語 言 。意 思 是 你 可 以 指 定 一 段 程 式 碼 ,只 在 特 定 event 時 才 執 行 。那 有 什 麼 event 呢 ?例 mouse click 就 是 event,打 字 時 每 一 個 keystroke 也 是 event,你 改 變 一 個 cell 的 value 時 也 是 一 個 event,你 save document 時 也 是 一 個 event。

Event 同 property 一 樣 ,也 是 不 同 object 會 有 自 己 不 同 的 events 的 。例 如 workbook,就 有 open、close、save 之 類 的 events。但 worksheet呢 ,就 會 有 以 上 那 些 events 了 。

回 到 今 天 的 題 目 ,如 何 自 動 把 sheet1 的 column A copy 去 sheet2 呢 ?一 般 來 說 ,如 果 sheet1 的 column A 的 內 容 沒 有 變 化 的 話 ,我 們 是 不 需 要 不 停 地 copy & paste 的 。只 有 在 column A 有 變 更 時 ,才 有 copy & paste 的 需 要 。

不 過 可 惜 column 是 沒 有 相 關 的 event 的 ,所 以 我 們 只 能 把 程 式 寫 在 sheet1 的 change 事 件 裡 面 。意 思 就 是 當 sheet1 有 任 何 變 更 時 ,我 們 的 程 式 碼 就 會 執 行 。

Sheet1 的 Change 事 件 的 寫 法

好 了 ,了 解 了 一 些 基 礎 知 識 ,我 們 就 可 以 正 式 開 始 寫 我 們 的 第 一 個 VBA 程 式 。先 打 開 Excel,開 一 個 空 白 的 workbook,然 後 把 它 save as 為 vba-copy-column.xlsm。

為 了 方 便 編 寫 程 式 碼 ,使 用 中 文 版 excel 的 朋 友 ,可 以 先 把 工 作 表 的 名 字 都 改 成 英 文 的 。在 我 的 例 子 ,我 會 把 工 作 表 的 名 字 改 為 「sheet1」和 「sheet2」。

然 後 按 一 下 Visual Basic 按 鈕 ,打 開 Visual Basic 編 輯 器 ( 也 可 以 用 keyboard shortcut:Alt – F11 )。

在 左 手 邊 的 「專 案 總 管 」,就 可 以 見 到 打 開 了 的 Excel workbooks,和 每 個 workbook 裡 面 的 worksheets。

因 為 我 們 想 把 程 式 寫 在 sheet1 的 change 事 件 裡 面 ,所 以 我 們 先 選 擇 sheet1。

然 後 在 sheet1 的 程 式 碼 視 窗 裡 ,選 擇 worksheet 和 change。這 樣 VB Editor 就 會 自 動 幫 我 們 插 入 了 一 個 叫 Worksheet_Change 的 Subroutine。不 過 ,我 們 不 一 定 要 使 用 這 個 自 動 插 入 的 功 能 ,其 實 我 們 自 己 手 動 輸 入 也 是 一 樣 的 。

Private Sub Worksheet_Change(ByVal Target As Range)

End Sub

所 有 在 這 個 Subroutine 裡 面 的 程 式 碼 ,即 是 由 Private Sub …. 到 End Sub 之 間 的 程 式 碼 ,都 只 會 在 sheet1 的 內 容 有 改 變 時 ,才 會 執 行 。

Copy & Paste 的 幾 種 寫 法

然 後 就 可 以 開 始 寫 copy & paste 了 。在 VBA 最 簡 單 最 快 捷 把 cell value 複 製 去 另 一 個 cell 的 寫 法 ,就 是 用 等 於 符 號 ( = )。

Range("B1").value = Range("A1").value

以 上 的 程 式 碼 的 意 思 ,就 是 把 A1 的 value,寫 入 去 B1。即 是 如 果 A1 的 值 是 123,那 程 式 碼 執 行 之 後 ,B1 的 value 亦 會 是 123。

Private Sub Worksheet_Change(ByVal Target As Range)

Sheets("sheet2").Range("B:B").Value = Sheets("sheet1").Range("A:A").Value

End Sub

按 照 我 們 的 命 題 ,就 要 寫 成 上 面 的 樣 子 。把 整 個 sheet1 的 column A 的 值 ,寫 入 去 sheet2 的 column B。

第 二 個 寫 法 ,就 是 用 copy method。

Private Sub Worksheet_Change(ByVal Target As Range)

Sheets("sheet1").Range("A:A").Copy Sheets("sheet2").Range("B1")

End Sub

這 個 寫 法 ,就 是 等 於 我 們 用 mouse 或 者 keyboard 去 做 copy & paste。首 先 有 個 source,然 後 copy 去 一 個 destination。就 如 同 keyboard mouse 一 樣 ,你 的 destination 不 一 定 要 和 source 的 大 小 相 等 ,Excel 會 自 動 幫 你 調 整 destination 的 大 小 。

當 然 你 也 可 以 把 destination 寫 成 Range(“B:B”),執 行 的 結 果 是 一 樣 的 ,但 感 覺 上 就 是 有 點 笨 。

Copy method 和 等 於 符 號 ,二 者 的 寫 法 ,source 和 destination 剛 好 是 前 後 相 反 的 。這 是 程 式 語 言 本 身 的 語 法 ,是 硬 性 規 定 要 這 樣 寫 的 。當 然 每 個 程 式 語 言 的 寫 法 也 隱 含 一 定 的 哲 理 ,不 過 我 覺 得 大 家 初 學 還 是 不 要 深 究 ,死 記 硬 背 習 慣 了 就 好 。

那 以 上 兩 個 寫 法 那 一 個 比 較 好 呢 ?簡 單 答 案 是 那 個 執 行 得 比 較 快 ,就 是 更 加 好 。在 一 般 絕 大 部 分 的 情 況 ,用 等 於 符 號 是 會 執 行 得 比 較 快 的 。但 如 果 用 在 copy & paste 一 整 個 column 這 種 特 殊 情 況 ,用 Copy method 反 而 會 比 較 快 。

所 以 在 這 個 VBA 入 門 例 子 ,我 是 會 用 Copy method 的 。

那 有 比 較 聰 明 的 寫 ,又 有 沒 有 比 較 笨 的 寫 呢 ?當 然 有 ,而 且 有 很 多 很 多 。Programming 其 實 就 跟 作 文 一 樣 ,可 以 有 無 限 咁 多 種 寫 法 。一 般 來 說 執 行 得 最 快 的 只 有 一 種 寫 法 ,而 其 他 咁 多 種 寫 法 ,都 是 比 較 笨 ( 慢 ) 的 寫 法 。

Private Sub Worksheet_Change(ByVal Target As Range)

For i = 1 to 1048576
  Sheets("sheet2").cells(i,1).Value = Sheets("sheet1").cells(i,1).Value
Next i

End Sub

上 面 就 是 一 種 特 別 笨 的 寫 法 ,我 們 用 了 一 個 Iteration ( For loop ),把 整 個 column 的 cells 都 loop 一 次 ,每 個 cell 都 做 一 次 assign value。為 什 麼 這 麼 做 就 笨 呢 ,很 簡 單 ,因 為 這 樣 做 會 超 級 慢 。

加 快 VBA 的 執 行 速 度

因 為 寫 程 式 的 目 的 不 是 去 拿 諾 貝 爾 文 學 獎 ,所 以 程 式 寫 得 再 優 美 ,也 比 不 上 執 行 速 度 飛 快 的 程 式 碼 。所 以 養 成 寫 執 行 速 度 快 的 寫 法 ,是 非 常 重 要 的 。

因 為 VBA 本 身 效 能 算 是 十 分 差 ,所 以 我 們 也 會 有 一 些 加 快 執 行 速 度 的 小 技 巧 。

Private Sub Worksheet_Change(ByVal Target As Range)

Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False

Sheets("sheet1").Range("A:A").Copy Sheets("sheet2").Range("B1")

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

End Sub

最 常 用 的 就 是 關 閉 Auto Calculation 和 Screen Updating。Auto Calculation 就 是 Excel Formula 的 Auto Calculation,Screen Updating 就 是 Excel 更 新 畫 面 的 狀 態 。常 見 的 做 法 就 是 在 程 式 開 始 執 行 之 前 ,先 disable 了 以 上 兩 者 ,然 後 在 程 式 碼 的 結 尾 處 ,再 enable 以 上 兩 者 。

對 於 絕 大 部 分 的 Excel VBA,有 沒 有 用 這 個 小 技 巧 ,程 式 的 執 行 時 間 會 差 天 共 地 。

Documetation

最 後 ,別 忘 記 作 為 一 個 優 秀 的 Programmer,寫 好 Documentation 也 是 十 分 重 要 的 !VBA 的 comment 的 語 法 就 是 用 single quotation ( ‘ )。

在 VB 編 輯 器 裡 面 ,comment 就 會 用 綠 色 來 呈 現 。所 以 凡 是 綠 色 的 東 西 ,都 是 不 會 執 行 的 。

執 行 結 果

隨 便 在 sheet1 的 column A 輸 入 一 些 東 西 。

完 全 相 同 ,一 模 一 樣 的 東 西 ,就 會 自 動 出 現 在 sheet2 的 column B。

這 個 VBA 小 程 序 ,只 要 寫 一 次 ,就 會 永 遠 在 背 景 執 行 。其 實 很 多 同 類 的 重 複 性 的 工 作 ,都 是 可 以 用 VBA 來 自 動 化 的 。而 且 ,寫 VBA,其 實 也 可 以 很 簡 單 。

檔 案 下 載 :vba-copy-column.xlsm

ctleung張先生,男性,肖龍。
職業:I.T. Consultant
簡介:不好好讀書;七尺差五寸,手長過膝,雙耳垂肩;性寬和,寡言語,喜怒不形於色。據說少時曾斬白蛇於鳳凰山下……

This entry was posted in Computer & Network and tagged , , , , , , . Bookmark the permalink.

Leave a Reply

Your email address will not be published.