[Excel VBA] 將重複的機械化工作自動化-自製「Mark Answers as Black」、「Mark Answers as White」按鈕

今天分享一下最近用 excel VBA 做的小功能，在整理題庫的時候發現，想要把原本有題目＆解答的word檔拆成兩份，一份仍然維持原本的格式，有題目＆解答：

另一份只要有題目就好：

這樣在練習解題的時候可以看第二份檔案，要查答案再看第一份檔案，那麼問題就來了：這份題庫一共有 15 章，每章有 60~80 題不等，總題數大約超過 1000 題！要怎樣在短時間內輕鬆的做出第二份檔案呢？

我想到幾個方法：

1. 手動用滑鼠反白選取「Answer:」那行文字，然後用滑鼠點選文字顏色的設定，設成白色 –> 真的要這樣幹的話，大概直接點到中風比較快？！
   
2. 手動用滑鼠移到「Answer:」那行文字之前，然後用「shift+end」選取整行文字，把顏色設成白色，之後再把滑鼠移到下一行「Answer:」文字之前，按F4重複上述設定顏色為白色的動作 –> 嗯，有用到 F4 來啟動 word 自動錄下的 macro，稍微有點進步，但是很不幸的，這份題庫的總題數大約超過 1000 題！所以還是放棄！
   
3. 改進第二個作法，設法讓「再把滑鼠移到下一行「Answer:」文字之前，按F4重複上述設定顏色為白色的動作」可以自動化的對整份文件的內容執行，最好是可以按一個鍵就把整份文件的格式調整好，那麼就愉快了！

經過一番嘗試，完整的程式碼如下 (範例 word 檔可至這裡下載)：

最後的成果，左邊是有答案的版本，右邊只剩下題目，超過1000題只花了我10分鐘左右：

要實作這樣的功能並不困難，可歸納為以下幾個步驟：

1. 先觀察一下要做的事情，如果是不斷重複的機械化動作，就要想到：應該可以用 excel 內建的 function / VBA 來完成
2. 整理出一個可重複執行的流程 (如上述第二點)，以程式的觀點來看就是每次跑迴圈時要執行的動作
3. 以錄製巨集 (macro) 的方式，錄下手動操作一次該流程所產生的程式碼
4. (視情況) 清掉錄到的程式碼中沒有用的東西 (可能是不小心手殘多按到無關的功能等等)
5. 在這段程式碼之外加上一個迴圈
6. test、test、test
7. 加上註解，讓下次有需要使用的時候可以快速回憶

follow以上的流程，就可以大大簡化繁瑣的重複性工作，節省可觀的時間，心情也會比較好！聽說日本的上班族很會用 excel 的 function /VBA 來簡化例行性的工作，與大家共勉之 :p

補充：設定讓 Developer (開發人員) Ribbon 永遠顯示在工具列 (以 Word 2010 為例)

在 File –> Options 中，勾選 Developer：

在 Developer 這個 ribbon 內就可以編修、錄製 macro 囉：

回頭爬了一下 blog 上面的文章，上一次寫跟 VBA 有關的文章居然已經是快要一年半之前了 (第一次認真寫 VBA - -> Report Generator)，真是時光飛逝阿！

Excel VBA 作矩陣相乘運算

雖然到最後你還是沒留下你的名字...

---

今天就來介紹一下，如何用程式撰寫多維矩陣乘法運算

首先要了解一下矩陣乘法的計算方式

• (m1 x n1) * (m2 x n2) 結果會是 (m1 x n2)的矩陣
• 上例中的 n1 = m2
• 矩陣乘法位置互換結果就會不同

(大家可以直接點上方的 wiki 連結，裡面有更詳盡的介紹)

接著就是回到 Excel 啦
首先在 Sheet 上佈置一個 CommandButton
(沒看到的話，請到工具列→點右鍵→把設計工具箱打開)
然後在 Sheet 的儲存格上隨意填入一些值，等等要來當作矩陣的輸入值

接著在 Button 上點兩下進入 VBA 編輯畫面 (記得要在設計模式)
然後就是進入 VBA 的程式撰寫啦 >w<

• 首先用 Application.InputBox 方法讓使用者選取畫面上的儲存格，Type=8表示回傳值為 Range 物件
• 取得兩個 Range 物件的 Row Count 及 Column Count (切丁備用)
• 依前面的規則判斷是否可運算 (範例裡的 M1C 要等於 M2R)
  
• 建立兩個陣列物件，並將長度指定為第一個矩陣的 Column Count (或是第二個矩陣的 Row Count，它們兩個應該要相同)

到目前為止應該還算ok吧???
接著就是如何去計算結果矩陣裡的值
坎尼是用 wiki 裡提到的 這個方法

AB (結果矩陣) 中的 (1,2) 這格就是用 A 的第1列乘上 B 的第2欄

用下面這個動畫圖應該可以了解 (圖片來源：Scalar and Matrix Multiplication


所以再回到程式裡
坎尼用了兩個 For 迴圈將目標裡的值一格一格算出來
先是取得第一個矩陣的第一列(一維陣列)，再去乘上第二個矩陣裡的每一行(一維陣列)
可以得到結果矩陣第一列所有的值 (傳入 CellValue 裡計算)

接著再抓第一個矩陣的第二列去乘上第二個矩陣裡的每一行....以此類推

CellValue 是坎尼用來計算結果值的 Function
傳入兩個陣列即會把對應的 index 值相乘，最後再加總回傳 (回顧一下公式)


再來就是執行結果，先把模式改為執行模式 (點一下設計工具箱的三角板)
點「選取陣列」的按鈕，就會跳出選擇矩陣的方塊，此時可以在 Excel 儲存格上選取

矩陣1選完會再要求選擇矩陣2
記得，(m1 x n1) * (m2 x n2) 裡的 n1 = m2

選完之後，若是可以計算的兩個矩陣，會出現提示

Sheet2 裡果然有值

但是答案是否正確呢?
大家可以上 WIMS Online Matrix Multiplier 把相同的矩陣丟進去算算看
或是拿起筆來自己算吧 XDDD

由於中間省略了很多步驟，可能沒寫過 VBA 的人不知道坎尼在幹嘛吧 XD
坎尼今天上網有看到一個不錯的 Excel VBA 教學站 威廉博客
有興趣想學 Excel VBA 的人就過去看看吧 :D

矩陣這東西離坎尼好久遠了，好在有前人種好的大樹 - wiki
K了兩個晚上總算是有點成果
但是否還有更好的程式邏輯就請路過的大師們指教啦 哈哈哈
範例程式下載 要使用範例請先允許巨集執行

另外就是歡迎讀者發問
不過請留下可以連絡的方式，有時坎尼看不懂問題會睡不著覺啊啊啊~
另外要交作業的請不要問坎尼，這可是要收錢的，很貴的

自製無用小工具系列 - 利用 Excel VBA 產生大量亂數日期

這個工具起因於某日，坎尼要建立測試資料，但日期必須打散
極度懶惰的坎尼就用 Excel 做了個亂數跑日期的小程式

---

「為什麼要用 Excel 哩?」『簡單、好用、又好玩』

第一、要產生某種格式的資料很快很簡單
第二、啟動不像 visual studio 那麼久
第三、不用再重新拉UI，每個儲存格都是 TextBox

要寫 VBA 之前，先知道 VBA 編輯器要怎麼開啟，見下圖
(快速鍵為 ALT + F11)

再來是原始資料的創建
先全選 Sheet1 的儲存格，右鍵選儲存格內容，將儲存格轉換為文字格式
A1儲存格輸入 1995，B1輸入01，C1輸入01
再利用 Excel 的自動填滿選項功能建立如下圖的資料 (這時候就知道Excel的好用了)

接著一樣把 Sheet2所有的儲存格改為文字格式
以免產生出來的資料，Excel會自以為聰明的幫你改格式 :D

資料準備好之後，就按下 ALT + F11 進入 VBA 編輯器
再來就是寫程式了，當然，是要用 VB 的語法

下圖先用兩個 For 迴圈決定要產生的資料量 (I、J分別為 Column及Row)
新增三個亂數值，分別決定年月日的亂數 Index
再將亂數產生的值組合起來放進 Sheet2 的儲存格裡

寫完程式之後，直接點執行鍵即可 (編輯器上面的綠三角形)
再切到Sheet2就可以看到滿滿的亂數日期了


Excel 檔下載 (若要使用坎尼的檔案，記得打開巨集)

其實這個程式把Sheet1的資料來源改一改 (亂數index的range也可以更改)
就可以變成 電影名稱產生器、武功名稱產生器...

最後補充一下，坎尼程式裡年的 index 亂數設錯了 (應該為 14)
所以不會有 2007 及 2008 年的資料

第一次認真寫 VBA --> Report Generator

本篇範例可以到這裡下載。裡面的程式才是最正確的，下面抓的圖有點舊XD
由於最近公司要辦活動，必須統計每堂課的報名狀況，
無奈報名系統產出的 Excel 太過基本，無法快速產出老闆想要的報表，
因此開始試圖用 VBA 自動完成一些重複性高的手動作業，
也是我第一次撰寫簡單的 VBA 應用程式，過程還蠻好玩的 (雖然害我少睡很多)，
因此以下按照報表產生的步驟來介紹一些我用到的方法和心得：

1. 首先我做了一張 sheet (Settings)，裡面放了一些 Report 的相關設定，
   未來可以繼續擴充，目前最重要的設定是課程名稱和 Worksheet Name的對應，
   例如「A_Very_Long_Name_About_SaaS」對應到「SaaS」，像下面這樣：
   
   
2. 接下來是將報名系統產生的 Excel 資料貼到 "Original Data"中，
   並且調整適當的標題，做好資料的排序，再加上「統計資訊」的區塊： 
    
   其中在統計資訊的部分，公式長的像這樣：COUNTIF(D:D,J2)
   
   這樣的原始資料的問題在於，由於在報名過程中每天的報名人數都會變化，
   如果要用這張 Worksheet 來統計每日 (或每堂課)的報名人數，
   就必須每天調整公式中的 Range，
   因此最好是能將每堂課的資訊各自獨立到一張 Worksheet 中，
   如此統計資訊的公式中的 Range 就可以使用整個「欄」， (像上面那樣)
   而不用根據每天的資料筆數來調整公式中的 Range。
   
   而上述這個「將每堂課的資訊各自獨立到一張 Worksheet 中」的動作，
   如果要每天手動去複製就相當的麻煩，而且複製前還必須要先刪掉舊資料，
   因此如果能透過 VBA 自動將每天報表中的每堂課的資料抽出來放到相對應的 Worksheet 中，統計的公式就會自動計算出最後結果，省時省力。
   
   
3. 在主要的 VB Sub Routine 中，首先會先 loop 過整個 Workbook 內的 Worksheet，
   並且將目前 Worksheet 中已經存在的舊資料刪除，程式像下面這樣：
    
   
   
4. 若是第一次執行這個 Sub，要把以下的註解取消，以便根據 Worksheet 名稱來新增 Worksheet，如同註解中說的，看來 VBA 不支援 Try Catch：
   
   
5. 接下來就會抓取步驟一中設定的對應關係，並且將"Original Data"的標題以及統計資料列複製到每堂課程的 sheet中 (如此每張 sheet 的 Layout 都會是相同的)：
   
   
   其實以上就是這個 Sub 最主要的內容了，接下來會解釋「CopyTitleAndStatistics」和「ProcessData」做的事情。
   
   
6. 實際上執行「複製標題列及統計資訊」的是「CopyTitleAndStatistics」，
   主要的程式像這樣：
   
   
   
7. 而「ProcessData」則是分成兩段，首先要根據課程名稱，在 HR 原始報表中找出該課程的相關資料(With 區塊主要參考 Excel 內建的範例程式)：
   
   
8. 接下來則是計算要負制的範圍，並且根據資料筆數來計算 Target Worksheet 要貼上的範圍：
   
   
9. 最後產出的報表長的像這樣：
   
   
   在 "Original Data" 之後已經依據 "Settings" 內的設定自動產生好相對應的 Worksheet，並且將該堂課程的資訊貼進去，這樣就可以檢視每堂課的報名人數統計了。 
   
   
10. 另外我還手動作了一張 "Report Overview" 的 Worksheet，有 summary 的功效：
    

經過這次的練習，我有以下的心得：

• 首先要先想好最終要產出的文件到底長甚麼樣子，才能分析裡面有哪些東西可以透過程式自動幫你完成 (或者透過痛苦的反覆人工作業來了解XD)。
• 其實 VBA 還蠻方便的，最大的好處是卡住的時候只要錄個巨集，就能很快的了解狀況，因此說明文件雖然不如 MSDN 好用，但還不致於會造成太大困擾。
• VBA 也提供方便的 Debug 功能，像是即時運算視窗、監看式等等，
  雖然用起來不太習慣，也有一些限制，但還是比 ASP 好太多了。

Future Work:

• 註解裡面有些記錄到嘗試失敗的部分，有空的時候可以再努力看看
• 目前的 Report_Overview 中的儲存格內容是手動一格格設定的，應該VBA也辦的到
• 在產生每堂課的 Worksheet 時，可以間隔的設定索引標籤顏色，這樣可讀性更高

平常實在沒甚麼機會接觸 VBA，除非遇到像這種久久才用到一次的報表，希望下次還有其他機會可以練習 :p