Excel VBA程式專案到了設計完成階段，是將所有程式和資料整合在一起，可以利用Call呼叫方式，建立子執行模組及輔助副模組，並且依照需求將報表另存新檔。

本書一到三章為第一篇「建立分析資料庫」，主要介紹利用VBA程式網路爬蟲取得想分析的資料，建立Excel表格資料庫。本章開始第二篇「資料統計分析」，一開始先彙總第一篇所取得資料，接著介紹善用Excel執行統計分析，重點在於表格和樞紐分析表的工具應用。

先前第二章已經將每天排行榜100筆資料彙總，第三章是以個別書籍為對象，取得補充的出版社及分類資訊，在這一節要介紹利用VBA的Call指令，將這兩章的內容整合成一個大型的程式專案，呼叫之前寫好的程式，一次完整執行，達到自動化的效果。

一、Excel資料整合

將第二章的「匯總報表」和第三章的「書籍補充資訊列表」兩張工作表整合在同一個Excel檔案。

二、VBA Call程式整合

沿用第二章第五節的程式，這裡主要多了「Call 彙總書籍補充資訊(當天日期)」和「Call 取得出版社及分類(當天日期)」，用意是利用VBA Call指令整合所有程式。

Sub 取得網路資料()

=>建立Sub程序

Dim 當天日期 As String

=>宣告文字變數

當天日期 = Format(Date, “yyyymmdd”)

=>利用Format函數定義變數

Call 新增工作表並取得網路資料(當天日期)

=>呼叫副程式

Call 多餘資料刪除(當天日期)

=>呼叫副程式

Call 報表格式整理(當天日期)

=>呼叫副程式

Call 彙總書籍補充資訊(當天日期)

=>呼叫副程式

Call 取得出版社及分類(當天日期)

=>呼叫副程式

Call 分析欄位設置(當天日期)

=>呼叫副程式

Call 工作表匯總(當天日期)

=>呼叫副程式

MsgBox “程式執行完畢”

=>顯示訊息視窗

End Sub

=>結束Sub程序

三、VBA輔助副模組

配合本書章節及整體程式專案架構，「Module1」模組是工作表「匯總報表」更新所需要執行的主模組，其中涉及到取得各書籍出版社及分類的部份，由於程式較為複雜，另外放在「Module2」模組，等於是工作表「書籍補充資訊列表」更新所需要執行的副模組。

Sub 彙總書籍補充資訊(當天日期)

=>建立Sub程序，取得Call呼叫所傳遞的參數值

當天日期附網址 = 當天日期 & “-附網址”

=>宣告變數，以「&」符號合併參數及文字

Call 取得網頁網址(當天日期附網址)

=>呼叫其他Sub程序並傳遞參數值

Call 標記當天新書(當天日期附網址)

=>呼叫其他Sub程序並傳遞參數值

Call 取得出版社資料(當天日期附網址)

=>呼叫其他Sub程序並傳遞參數值

Call 取得分類資料(當天日期附網址)

=>呼叫其他Sub程序並傳遞參數值

Call 非當日新書刪除(當天日期附網址)

=>呼叫其他Sub程序並傳遞參數值

Call 工作表匯總(當天日期附網址)

=>呼叫其他Sub程序並傳遞參數值

End Sub

=>結束Sub程序

四、CurrentRegion優化

這裡的重點是將「資料筆數」從「UsedRange.Rows.Count」優化為「CurrentRegion.Rows.Count」，這是因為UsedRange是使用範圍，雖然資料被刪除了，在工作表上已經沒有了，但仍然算是使用過的範圍，在進行「UsedRange.Rows.Count」列數計算時仍然會納入，實際執行有可能會導致錯誤，因此改為CurrentRegion目前範圍，這樣就不用擔心已刪除資料的問題，因為它不會在目前範圍中。

Public Sub 工作表匯總(當天日期附網址)

=>這裡的「Public Sub」多了一個「Public」，意思是公共程序，可以被其他程序（Sub）呼叫引用。前面的程式只有一個「Sub」而不是「Public Sub」，其實結果是一樣的，因為VBA對於「Sub」的預設值便是「Public」，因為沒有特地寫的話，「Sub」一樣會是「Public Sub」，本節重點是Call呼叫指令，所以加以標註並說明，讓讀者更加清楚。

‘定義變數

Dim 資料筆數 As Integer

=>定義「資料筆數」為整數型態的變數

資料筆數 = Sheets(“書籍補充資訊列表”).Cells(1, 1).CurrentRegion.Rows.Count

=>以工作表「書籍補充資訊列表」的儲存格第一列第一欄為基準，Cells(1,1)指的就是第一列第一欄，Cells括號裡第一個「1」是表示列、第二個「1」表示欄，後面再加上「.CurrentRegion.Rows.Count」是以Cells1(1,1)為左上角有向右向下有資料存在的儲存格範圍，計算此範圍有多少列(Rows.Count)，等於是計算有多少筆資料。

‘工作表匯總並刪除重複的標題列

Worksheets(當天日期附網址).UsedRange.Copy Sheets(“書籍補充資訊列表”).Cells(資料筆數 + 1, 1)

=>程式新增取得資料的工作表為「當天日期附網址」，複製此工作表的已使用資料的表格範圍(Worksheets(當天日期附網址).UsedRange.Copy)，貼到工作表「書籍補充資訊列表」，從目前資料最後一筆的下一列開始添加(Sheets(“書籍補充資訊列表”).Cells(資料筆數 + 1, 1))，這裡的Cells(資料筆數 + 1, 1)先前有說明過，其中「資料筆數+1」是第幾列、「1」是第一欄。

Application.DisplayAlerts = False

=>正在在Excel刪除有資料的工作表時會提示確是否刪除，在此為加快程式自動化執行，暫時取消提醒視窗機制

Worksheets(當天日期附網址).Delete

=>刪除以「當天日期附網址」作為變數名稱的工作，也就是刪除網路爬蟲程式取得資料的工作表

Application.DisplayAlerts = True

=>恢復提醒視窗機制，和先前的「Application.DisplayAlerts = False」是相呼應的，暫時取消，程式自動刪除工作表不提示確認，再恢復機制

Sheets(“書籍補充資訊列表”).Cells.Hyperlinks.Delete

=>將工作表中所有儲存格的連結刪除

Sheets(“書籍補充資訊列表”).Cells.Font.Name = “Noto Sans CJK TC Medium” ‘

=>設定工作表儲存格字型為「Noto Sans CJK TC Medium」，這是Google所開發的免費字型，贊贊小屋習慣使用此字型，讀者也可以更改為目前Windows系統常用的「微軟正黑體」

End Sub

=>結束程序

五、多餘資料刪除

這裡要特別再提醒多餘資料刪除的程式，是以i、i+1、i+4、i+5作為條件，這是因應原始網頁的資料規則所設置的，如果在實際執行網頁格式有變更，需要同步更新。

Sub 多餘資料刪除(當天日期)

=>建立程式

Dim UsedR As Integer, KeyW As String

=>宣告整數及文字變數

UsedR = Worksheets(當天日期).UsedRange.Rows.Count

=>計算工作上使用範圍有多少列數

For i = 1 To Worksheets(當天日期).UsedRange.Rows.Count

=>以工作表使用範圍變數建立迴圈

If Left(Worksheets(當天日期).Cells(i, 1).Value, 3) = “TOP” Then

=>利用Left函數取得逐行資料左邊3個字元，以如果是TOP作為條件

Worksheets(當天日期).Cells(i, 5).Value = “保留行”

=>條件成立的話，同一列的第5欄儲存格標記「保留行」

Worksheets(當天日期).Cells(i + 1, 5).Value = “保留行”

=>條件成立的話，下一列的第5欄儲存格標記「保留行」

Worksheets(當天日期).Cells(i + 4, 5).Value = “保留行”

=>條件成立的話，下面第四列的第5欄儲存格標記「保留行」

Worksheets(當天日期).Cells(i + 5, 5).Value = “保留行”

=>條件成立的話，下面第五列的第5欄儲存格標記「保留行」

End If

=>結束if條件子句

Next i

=>執行下一筆迴圈

For i = Worksheets(當天日期).UsedRange.Rows.Count To 1 Step -1

=>建立從報表最後一行到第一行的迴圈

If Left(Worksheets(當天日期).Cells(i, 5).Value, 3) <> “保留行” Then

=>如果迴圈的某一行資料有等於「保留行」的話

Rows(i).Delete

=>將該行刪除

End If

=>結束條件式

Next i

=>執行下一筆迴圈

Columns(5).Delete

=>第五欄刪除

End Sub

=>結束程序

六、VBA儲存檔案

這裡基本上是沿用本書第二章第五節的程式範例，不過先是設計像「Filnam = “C:\Users\b8810\Downloads\” & Today & “.xlsx”」這樣固定路徑的資料夾，跨電腦執行容易出問題，在此更改為「ThisWorkbook.Path」，表示是目前活頁簿路徑，如此較為靈活，在任何電腦上執行程式都不會出錯。

這裡使用到了VBA Dir命令查詢特定資料夾名稱，Dir的第一個參數為查詢對象，第二個參數vbDirectory表示查詢類型是目錄或資料夾，利用條件式判斷，如果查詢長度為零，表示這個資料夾不存在（If Len(Dir(檔案資料夾, vbDirectory)) = 0），那麼就以MkDir命令新增資料夾（Then MkDir 檔案資料夾），等於是加入自動偵錯的機制。

Public Sub 工作表匯總(當天日期)

=>建立程序

Worksheets(當天日期).Copy

=>複製工作表到新活頁簿

檔案資料夾 = ThisWorkbook.Path & “\原始檔案”

=>設定存檔路徑為目前活頁簿資料夾中的「原始檔案」子資料夾

If Len(Dir(檔案資料夾, vbDirectory)) = 0 Then MkDir 檔案資料夾

=>如果資料夾不存在，建立資料夾

Application.DisplayAlerts = False

=>取消提醒視窗機制

ActiveWorkbook.SaveAs 檔案資料夾 & “\” & 當天日期 & “.xlsx”

=>將新增的活頁簿以當天日期為名稱儲存

Application.DisplayAlerts = True

=>恢復提醒視窗機制

ActiveWorkbook.Close

=>關閉目前活頁簿

Dim SumRan As Integer, SumSht As Worksheet

=>宣告整數及工作表變數

Set SumSht = Sheets(“匯總報表”)

=>定義SumSht變數為「匯總報表」工作表

SumRan = SumSht.UsedRange.Rows.Count

=>計算工作表使用範圍有多少列

Worksheets(當天日期).UsedRange.Copy SumSht.Cells(SumRan + 1, 1)

=>將「當天日期」工作表的使用範圍複製到SumSht的報表下面

SumSht.Rows(SumRan + 1).Delete

=>刪除新複製資料的標題列

Application.DisplayAlerts = False

=>取消提醒視窗機制

Worksheets(當天日期).Delete

=>刪除工作表

Application.DisplayAlerts = True

=>恢復提醒視窗機制

SumSht.Select

=>選取工作表

Columns(“A”).ColumnWidth = 10

Columns(“B”).ColumnWidth = 5

Columns(“C”).ColumnWidth = 40

Columns(“D”).ColumnWidth = 15

Columns(“E”).ColumnWidth = 5

Columns(“F”).ColumnWidth = 45

Columns(“G”).ColumnWidth = 15

Columns(“H”).ColumnWidth = 35

=>以上程式皆為設定欄寬

Rows(“1:1”).HorizontalAlignment = xlCenter

=>特定列水平置中對齊

Rows(“1:1”).Font.Bold = True

=>特定列字型粗體

Columns(“B:B”).HorizontalAlignment = xlCenter

Columns(“E:E”).HorizontalAlignment = xlCenter

=>以上兩行程式碼為特定欄垂直置對齊

Cells.Font.Name = “Noto Sans CJK TC Medium”

=>設定工作表所有儲存格的字型

End Sub

=>結束程式

七、程式專案資料

這一節程式全部都結合成單一的「取得網路資料」巨集，執行後果然得到新增當日資料的報表，包括出版社及分類，這裡注意到日期是10/30和11/14，表示即使中間有幾天斷掉了，程式仍然會自動前後彙總在一起。

實務專案優化VBA程式

這一節程式範例算是比較大型的專案了，不過其實都是沿用先前章節內容，讀者應該不致於有太大的障礙。這裡剛好是很好的範例，可以看到如何利用Call呼叫程式並傳遞參數的方式，將各個模組程序整合成一個專案。

本節有將「資料筆數」從「UsedRange.Rows.Count」改為「CurrentRegion.Rows.Count」，同時也將檔案儲存從固定路徑的資料夾，更改為「ThisWorkbook.Path」，這個是在實際執行好幾天的程式之後，發現程式可以優化的地方。本書到這裡大致已經介紹相當完整的VBA指令，讀者也可以依照需求自行修改，作者在此是起到拋磚引玉的作用。

每天學習，每天充電：VBA爬蟲專案系列文章。