由於最近公司要辦活動，必須統計每堂課的報名狀況，
無奈報名系統產出的 Excel 太過基本，無法快速產出老闆想要的報表，
因此開始試圖用 VBA 自動完成一些重複性高的手動作業，
也是我第一次撰寫簡單的 VBA 應用程式，過程還蠻好玩的 (雖然害我少睡很多)，
因此以下按照報表產生的步驟來介紹一些我用到的方法和心得：
- 首先我做了一張 sheet (Settings)，裡面放了一些 Report 的相關設定，
未來可以繼續擴充，目前最重要的設定是課程名稱和 Worksheet Name的對應，
例如「A_Very_Long_Name_About_SaaS」對應到「SaaS」，像下面這樣：
- 接下來是將報名系統產生的 Excel 資料貼到 "Original Data"中，
並且調整適當的標題，做好資料的排序，再加上「統計資訊」的區塊：
其中在統計資訊的部分，公式長的像這樣：COUNTIF(D:D,J2)
這樣的原始資料的問題在於，由於在報名過程中每天的報名人數都會變化，
如果要用這張 Worksheet 來統計每日 (或每堂課)的報名人數，
就必須每天調整公式中的 Range，
因此最好是能將每堂課的資訊各自獨立到一張 Worksheet 中，
如此統計資訊的公式中的 Range 就可以使用整個「欄」， (像上面那樣)
而不用根據每天的資料筆數來調整公式中的 Range。
而上述這個「將每堂課的資訊各自獨立到一張 Worksheet 中」的動作，
如果要每天手動去複製就相當的麻煩，而且複製前還必須要先刪掉舊資料，
因此如果能透過 VBA 自動將每天報表中的每堂課的資料抽出來放到相對應的 Worksheet 中，統計的公式就會自動計算出最後結果，省時省力。
- 在主要的 VB Sub Routine 中，首先會先 loop 過整個 Workbook 內的 Worksheet，
並且將目前 Worksheet 中已經存在的舊資料刪除，程式像下面這樣：
- 若是第一次執行這個 Sub，要把以下的註解取消，以便根據 Worksheet 名稱來新增 Worksheet，如同註解中說的，看來 VBA 不支援 Try Catch：
- 接下來就會抓取步驟一中設定的對應關係，並且將"Original Data"的標題以及統計資料列複製到每堂課程的 sheet中 (如此每張 sheet 的 Layout 都會是相同的)：
其實以上就是這個 Sub 最主要的內容了，接下來會解釋「CopyTitleAndStatistics」和「ProcessData」做的事情。
- 實際上執行「複製標題列及統計資訊」的是「CopyTitleAndStatistics」，
主要的程式像這樣：
- 而「ProcessData」則是分成兩段，首先要根據課程名稱，在 HR 原始報表中找出該課程的相關資料(With 區塊主要參考 Excel 內建的範例程式)：
- 接下來則是計算要負制的範圍，並且根據資料筆數來計算 Target Worksheet 要貼上的範圍：
- 最後產出的報表長的像這樣：
在 "Original Data" 之後已經依據 "Settings" 內的設定自動產生好相對應的 Worksheet，並且將該堂課程的資訊貼進去，這樣就可以檢視每堂課的報名人數統計了。
- 另外我還手動作了一張 "Report Overview" 的 Worksheet，有 summary 的功效：
- 首先要先想好最終要產出的文件到底長甚麼樣子，才能分析裡面有哪些東西可以透過程式自動幫你完成 (或者透過痛苦的反覆人工作業來了解XD)。
- 其實 VBA 還蠻方便的，最大的好處是卡住的時候只要錄個巨集，就能很快的了解狀況，因此說明文件雖然不如 MSDN 好用，但還不致於會造成太大困擾。
- VBA 也提供方便的 Debug 功能，像是即時運算視窗、監看式等等，
雖然用起來不太習慣，也有一些限制，但還是比 ASP 好太多了。
- 註解裡面有些記錄到嘗試失敗的部分，有空的時候可以再努力看看
- 目前的 Report_Overview 中的儲存格內容是手動一格格設定的，應該VBA也辦的到
- 在產生每堂課的 Worksheet 時，可以間隔的設定索引標籤顏色，這樣可讀性更高
沒有留言:
張貼留言