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

Posted in Computer & Network | Tagged , , , , , , | Leave a comment

3D 設計:Fusion 360 vs TinkerCad

年 近 歲 晚 ,又 是 大 掃 除 的 日 子 。今 年 還 特 意 夾 好 時 間 ,在 淘 寶 買 了 5 個 不 同 大 小 的 膠 櫃 桶 回 來 執 屋 用 。因 為 一 早 預 好 時 間 ,所 以 膠 櫃 桶 在 年 廿 七 就 送 到 了 家 裡 。

膠 櫃 桶 是 組 裝 的 ,十 來 分 鐘 就 組 合 完 成 一 個 。膠 櫃 桶 本 身 很 好 ,擺 在 原 來 預 算 的 位 置 ,長 寬 高 都 是 剛 剛 好 ( 只 有 淘 寶 才 有 這 麼 多 不 同 尺 寸 的 選 擇 )。不 過 十 分 可 惜 的 是 ,5 個 膠 櫃 桶 裡 面 ,竟 然 有 1 個 少 了 1 件 零 件 。

根 據 說 明 書 ,這 個 零 件 叫 做 「後 條 」。第 一 時 間 聯 繫 了 客 服 ,客 服 也 第 一 時 間 免 費 補 發 了 零 件 。但 因 為 已 經 是 年 廿 八 ,快 遞 和 集 運 都 已 經 休 假 ,所 以 最 快 最 快 都 要 初 十 以 後 才 能 在 香 港 收 到 貨 。

這 個 時 候 ,就 是 你 跟 太 座 ( 也 可 以 是 兒 女 對 父 母 ) 炫 耀 為 什 麼 家 裡 一 定 要 買 一 台 3D 打 印 機 的 時 候 了 。

「後 條 」( 是 不 是 辣 條 的 朋 友 ? ) 是 功 能 性 很 強 的 零 件 ,既 要 連 接 左 右 側 板 ,同 時 又 要 連 接 上 下 後 板 。

馬 上 取 出 我 改 裝 過 的 電 子 卡 尺 ( 改 用 AAA 充 電 池 ,環 保 ! ),量 度 各 個 部 分 的 尺 寸 。有 齊 各 個 尺 寸 ,一 會 兒 繪 製 模 型 會 比 較 方 便 。

要 繪 畫 3D 模 型 ,最 簡 單 的 ,首 選 用 TinkerCad。TinkerCad 是 一 個 網 上 3D 設 計 軟 件 ,打 開 browser 就 用 得 ,簡 單 易 用 ,而 且 完 全 免 費 。

從 上 面 的 圖 片 ,你 可 以 大 置 了 解 TinkerCad 的 使 用 方 法 。TinkerCad 裡 面 會 有 大 量 的 預 設 立 體 圖 形 ,伙 可 以 把 不 同 圖 形 拼 拼 貼 貼 、加 加 減 減 ,最 終 組 合 成 你 想 要 的 3D 模 型 。

不 過 如 果 想 再 進 一 步 ,例 如 建 立 複 雜 一 點 的 模 型 ,又 或 者 要 時 常 修 改 模 型 ,TinkerCad 就 會 比 較 麻 煩 ,這 時 候 就 可 以 改 用 Fusion 360。Fusion 360 有 幾 層 不 同 的 收 費 ,如 果 是 非 商 業 的 個 人 使 用 Personal Use ( non comercial ),則 是 免 費 的 。

和 TinkerCad 使 用 預 設 的 3D 模 型 不 同 ,Fusion 360 是 要 先 畫 2D 草 圖 ( sketch ),然 後 再 由 2D 拉 伸 ( extrude ) 成 為 3D 模 型 。舉 個 最 簡 單 的 例 子 ,如 果 要 建 立 一 個 正 立 方 體 ,TinkerCad 你 可 以 直 接 創 建 一 個 立 方 體 ,然 後 設 定 長 寬 高 的 尺 寸 即 可 。

而 在 Fusion 360,你 就 要 先 畫 一 個 2D 的 正 方 型 Sketch,然 後 再 由 正 方 形 extrude 一 個 高 度 而 成 為 立 方 體 。這 兩 種 模 式 那 一 個 好 呢 ?主 要 看 你 會 不 會 畫 2D 圖 ,如 果 你 精 通 畫 2D 圖 ,那 就 可 以 直 接 用 Fusion 360。如 果 完 全 不 會 畫 圖 ,也 不 想 學 畫 圖 ,那 就 去 用 比 較 直 觀 的 TinkerCad。

Fusion 360 還 有 很 多 簡 單 易 用 的 功 能 ,同 樣 的 效 果 如 果 想 用 TinkerCad 來 做 ,會 要 付 出 多 很 多 倍 的 時 間 。在 這 個 模 型 ,我 用 了 2 個 這 樣 的 功 能 。第 一 個 叫 做 Chamfer ( 倒 角 ),chamfer 除 了 比 直 角 美 觀 之 外 ,還 有 很 多 好 處 ,例 如 防 界 手 ,令 榫 頭 更 容 易 插 入 卯 口 等 等 。

在 Fusion 360,你 只 要 打 開 chamfer 功 能 視 窗 ,再 用 mouse 逐 一 點 選 要 倒 角 的 邊 ,設 定 好 例 角 的 大 小 ( 距 離 ),再 按 一 下 OK 就 得 。以 我 這 個 模 型 ,有 廿 幾 三 十 條 邊 要 做 chamfer,在 TinkerCad 真 係 要 做 到 天 黑 ,而 在 Fusion 360 就 一 分 鐘 都 唔 洗 。

第 二 個 就 是 shell ( 外 殼 ) 功 能 ,但 我 喜 歡 叫 它 做 挖 空 。要 使 用 Shell 功 能 ,你 只 要 打 開 shell 視 窗 ,然 後 點 選 一 個 立 體 的 其 中 一 個 面 ,再 輸 入 外 殼 的 厚 度 ,Fusion 360 就 會 全 自 動 的 幫 你 把 模 型 挖 空 ,剩 下 一 個 指 定 厚 度 的 外 殼 。同 樣 的 凹 凸 不 平 的 模 型 ,如 果 要 在 TinkerCad 做 挖 空 ,肯 定 得 花 好 多 時 間 ,而 且 效 果 還 不 會 好 。

同 類 的 功 能 在 Fusion 360 其 實 還 有 很 多 很 多 。很 多 初 學 者 用 慣 了 TinkerCad,不 習 慣 Fusion 360,就 會 以 為 Fusion 360 很 「難 用 」,害 怕 建 立 模 型 會 要 多 花 很 多 時 間 。殊 不 知 ,當 設 計 比 較 複 雜 的 模 型 ,用 Fusion 360 反 而 會 快 很 多 很 多 呢 。

好 不 容 易 把 第 一 稿 打 印 出 來 ,發 現 竟 然 有 好 多 個 錯 處 !作 者 果 然 是 個 未 入 門 的 初 學 低 手 。最 嚴 重 的 錯 處 ,是 左 右 側 板 中 間 的 間 隔 ,竟 然 短 了 1cm ( 老 花 可 能 又 加 重 了 )。其 他 minor 的 部 分 ,包 括 頂 部 的 凹 槽 開 得 唔 夠 深 ,兩 個 榫 頭 太 大 ,插 唔 入 卯 口 等 等 。其 實 初 初 玩 3D 打 印 ,模 型 出 問 題 是 十 分 正 常 的 。同 一 個 模 型 ,要 修 改 好 幾 十 次 都 完 全 是 正 常 的 ,這 個 過 程 就 叫 prototyping。

而 要 修 修 改 改 ,正 正 就 是 Fusion 360 比 TinkerCad 強 的 地 方 ,而 且 不 只 是 強 一 點 點 。Fusion 360 有 一 條 Timeline,記 載 了 你 之 前 建 立 模 型 的 每 一 個 步 驟 。

左 右 側 板 中 間 的 間 隔 短 了 1cm,就 可 以 在 Timeline 打 開 最 初 的 sketch 來 改 圖 。修 改 的 話 也 很 簡 單 ,也 不 用 重 新 畫 圖 ,只 要 在 dimensions 改 2 個 值 就 得 了 ( 1 個 是 間 隔 的 長 度 ,另 一 個 數 值 是 令 到 間 隔 置 中 )。這 還 不 是 最 神 奇 的 。

改 完 sketch 之 後 ,只 要 把 Timeline 拖 回 去 最 後 ,就 會 自 動 生 成 新 的 3D 模 型 。所 有 的 其 他 步 驟 ,包 括 chamfer,包 括 shell,通 通 都 會 自 動 按 照 新 的 sketch 來 做 ,真 的 是 十 分 爽 。很 多 的 其 他 軟 件 ,如 果 是 最 早 的 步 驟 出 錯 ,就 只 能 back back back,然 後 由 出 錯 的 步 驟 開 始 人 手 重 做 。

模 型 頂 部 的 凹 槽 深 度 不 夠 ,也 同 樣 是 回 到 那 一 個 步 驟 ,再 修 改 一 個 參 數 的 事 。2 個 榫 頭 太 大 ,也 只 是 要 修 改 2 個 sketch 就 完 事 。這 種 可 以 回 到 過 去 ,修 改 其 中 一 個 步 驟 ,然 後 變 更 就 會 全 自 動 帶 去 後 面 的 修 改 方 式 ,真 的 是 讓 我 感 激 流 涕 ,不 知 道 在 做 prototyping 時 為 我 節 省 了 多 少 時 間 !

修 改 好 了 模 型 ,再 一 次 打 印 出 來 ,打 印 件 比 我 想 像 中 的 還 要 好 ,不 用 再 做 修 改 。第 二 稿 就 不 用 再 改 ,實 在 是 太 爽 了 。

終 於 可 以 把 膠 櫃 桶 組 裝 好 ,真 是 太 完 美 了 !唔 係 我 自 誇 ,盲 既 都 一 定 睇 唔 出 有 分 別 !突 然 一 道 鋒 利 的 眼 光 切 割 而 來 ,咳 咳 咳 ,繼 續 回 去 大 掃 除 !

Posted in GRBL + 3D 打印 | Tagged , , , , , , , | Leave a comment