條件判斷

IF

If 條件 Then
執行
ElseIf 條件2 Then
執行
Else
執行
End If

Case Select

Select Case 變數
Case Is 判斷式(例如 == 0)
執行
Case Is 判斷式2
執行
Case Is 判斷式2
執行
Case Else (其他--非必要)
執行
End Select

迴圈

For
對於固定的數值範圍

for 變數=開始值 to 結束值
執行
next

For
用來取出陣列的內容(可應用在字串)

For Each 變數 In 陣列     
執行
Next 變數

Do While 先判斷再執行

Do While 條件
執行
Loop

Do Loop While 先執行再判斷

Do         
執行
Loop While 條件

Do Until 先判斷再執行

Do Until 條件
執行
Loop

Do Loop Until 先執行再判斷

Do         
執行
Loop Until 條件

接下來我們稍微回顧一下,在第一篇:”基本語法(上篇)”

記錄了如何選取活頁簿、如何選取Range or Cell,以及一些基礎的操作,譬如如何複製、如何更改格式或顏色。這邊的重點是在於認識Excel特殊的元素,這一塊我認為是寫VBA最具有特色的地方,因為像是變數或邏輯控制這些概念,在不同程式語言都會有。只有excel 的cell 或是 copy、paste或是設定公式等動作,是在其他程式語言比較少見的。

再往下可以建議去試想看看還有哪些常用的,舉例來說,譬如

另外一個強大的武器則是善用Excel本身的公式,這邊讓我岔題一下。寫程式有分幾種門派,一種是學術派,也就是會考量到程式結構容不容易閱讀、好不好維護、會不會遇到相容問題等等,這乍聽之下都很重要,但是相對應的是須要有夠強大的基礎知識與自律,寫一個簡單程式需要花比較多時間。

另一種門派是江湖派,在市場上走跳,講求的是實際與效率,今天可以用公式達成的,我就不寫code。改天內建的excel公式不夠用,我考慮寫vba code,最好是vba+excel內建公式,混合而成我需要的功能,效率最高。

Application.WorksheetFunction是用來直接運用內建的公式,譬如說min/max/countif/countA等等。然而可能遭遇的陷阱是,在VBA裡面呼叫excel函式有時候寫法跟你在excel直接用不一樣。譬如下方,我們可能會很直觀的寫出 CountA(A:A),但在vba必須用range指出範圍。

ActiveCell.value = Application.WorksheetFunction.CountA(Range("A:A"))

另外一種方式,則是直接把公式寫進儲存格裡。

像這樣,可以用常用的公式直接寫入

Range("B1").Select
ActiveCell.Formula = "=IF(B2>100,B2*2,0)"

最後我們來嘗試用台灣COVID-19疫情確診人數,做EXCEL自動化蒐集抓取新聞的本土案例資料。如果要透過EXCEL抓取網路資料,有幾個方法。網路也有一些教學可以用EXCEL自動抓股票資料來做分析,可以參考。

而下圖是在Office EXCEL 2013的截圖,可以看到EXCEL可以從許多方式去匯入資料,譬如SQL、XML、網頁或Access。

想透過EXCEL蒐集每天本土案例確診人數,其實最好的方式是透過API拉JSON格式的資料,不過上網簡單找了一下,似乎沒有直接的資料源可以這樣做。

另外一個常見的方式就是用RSS。

RSS的英文全稱是 Really Simple Syndication,是一種透過XML標準所制定的資料格式,可以將網頁內容抽取出來,使用者透過訂閱 RSS Feed配合RSS閱讀器,即可直接取得網站最即時的資訊,更賦予使用者隨選訂閱的功能。

簡單來說,就是會用XML來規範網站資料的格式(可能是文章、新聞、產品等等,視網站內容而定)。符合格式的資料包含title(標題)、link(網站URL)、description(資訊摘要)...等等。

我們要來試試看,可不可以從新聞自動抓取每日本土確診人數的資訊。並且盡可能的使用正確資訊來源,要抓就先從政府機關的公告或即時新聞下手,避免接觸資訊雜亂或者可能有不實資訊的新聞網站。

我們可以從衛生福利部的網站找到正確資訊來源

https://www.mohw.gov.tw/cp-2661-6125-1.html

https://www.mohw.gov.tw/rss-16-1.html

加入EXCEL後如下圖,把這個匯入RSS的sheet先命名為"newdata"

可以看到幾個重點,包含了G欄有我們要的日期,然後雖然在C欄的title裡面沒有確診人數,但是在J欄裡面有,所以我們至少需要J&G欄的資訊。同時看到有許多列的新聞,都並非與疫情直接相關,所以也要想辦法來篩選掉這些不是我們需要的資料。

下一步,我們必須先新增一個sheet命名為"summary",用來整理篩選完的資料,目標是能取出每天的日期與確診人數,可以用來做簡單的統計。

再來就可以開始寫VBA程式囉

第一部分,先設定程式需要的變數,當然不是開始寫程式的時候就會知道自己要用幾個變數,不過我會把變數都集中在前面宣告(只是整理在一起),所以是邊寫邊整理需要的變數。

Sub RUN()Dim wsNewData As Object
Set wsNewData = Worksheets("newdata")
Dim wsSummary As Object
Set wsSummary = Worksheets("summary")
Dim mat(100, 2) As String
Dim i, m, n, o As Integer
i = 0
m = 0
n = 0
o = 0
Dim regEx As New RegExp

第二部分

每次跑的時候就讓他自動更新RSS的資料

wsNewData.Activate
ActiveWorkbook.XmlMaps("rss_Map2").DataBinding.Refresh

參考

第三部分,我們就應用到這次的For Loop迴圈,主要目的是用來跑從RSS這個SHEET的資料,每一筆都代表一則新聞,我們要去篩選這則新聞是不是我們需要的,前面我們有觀察過關鍵字,發現只要寫疫情確診人數的資料,就會有xxx例本土、xxx例境外這些字眼,所以使用InStr方法來做字串的搜尋。

邏輯主要是檢查每行RSS的新聞,如果有關鍵字,就把它存進去mat這個矩陣(就是多維陣列),矩陣裡面第一欄放日期(對應RSS的M欄)、第二欄放新聞標題(對應RSS的J欄)。

For i = 1 To wsNewData.Range("B1000").End(xlUp).Row
If InStr(wsNewData.Range("J" & i), "例本土") > 0 Then
''Debug.Print wsNewData("J" & i)
mat(m, 1) = wsNewData.Range("M" & i)
mat(m, 2) = wsNewData.Range("J" & i)
m = m + 1
Else
''Debug.Print "No Match"
End If
Next

補充一下這邊用了End(xlUp),可以由下往上取得最後一列的位置。這算是VBA比較特別的,因為像是python會用填補NA或去除空值的方式讓陣列盡可能的有值,避免程式在奇怪的地方出錯。

但是就EXCEL而言,本身表格就沒有資料邊界,所以就有End(xlUp)或是End(xlDown)這個方法可以從某格往上或往下到資料邊界。

至此,大致上把RSS資料可以透過一鍵自動更新,並且取出符合關鍵字的資料,存進去一個矩陣,接下來就是要把這個矩陣的資料寫進去比較好整理的SHEET裡。同時,我在這邊也想嘗試取出這個"數字",因為就算有確診人數的新聞,要做圖表還是要把數字給取出來。

第四部份:我們會用正則表達式來取出這個數字。正則表達式(regular expression)描述了一種字符串匹配的模式,可以用來檢查一個串是否含有某種子串、將匹配的子串做替換或者從某個串中取出符合某個條件的子串等。

簡單來說
1.宣告一個新物件 Dim regEx As New RegExp
2.設定正則規則 regEx.Pattern = “葡萄”
3.使用動作(Excute/Replace/Test..等動作)
例如regEx.Execute(string)

再來,主要目標是將蒐集到的RSS資料,整理到一張新的SHEET"Summary",所以先用xlUp找到資料的最後一列。再逐列讀取mat這個矩陣裡的資料,一行一行讀,直到為空值停下來,使用DO..UNTIL迴圈

至於裡面的Set regMatches這個動作,是因為正則會將匹配到的資料按群組回吐,形成多個item,但我們這邊比較單純,所以我直接寫"如果有匹配到"(regMatches.Count <> 0),直接取出第一組匹配結果(item(0))。

regEx.Pattern = "(\d+)例本土"o = wsSummary.Range("A1000").End(xlUp).Row
Do Until mat(n, 1) = ""
wsSummary.Range("A" & o + 1) = mat(n, 1)
wsSummary.Range("B" & o + 1) = mat(n, 2)

Set regMatches = regEx.Execute(mat(n, 2))
If regMatches.Count <> 0 Then
result = regMatches.Item(0).SubMatches.Item(0)
wsSummary.Range("C" & o + 1) = result
End If
o = o + 1
n = n + 1
Loop

第五部分:我們把所有符合的資料都寫進Summary這張SHEET,但會有一個問題,就是RSS上本來就會保存過去三至四天的新聞,所以如果我每次做都會抓到一堆重複資料,必須找一個方法來移除。

EXCEL本身就有滿多移除重複的功能,但是vba的寫法就必須找一下

wsSummary.Range("A1:C1000").RemoveDuplicates Columns:=1, Header:=xlYes
MsgBox ("done")
End Sub

到這邊程式跑完,用End Sub結束

成果大概長這樣,可以自動抓出數據,方便做統計及圖表。

寫到這邊,有些人可能會問說,那我要怎麼樣使用這個巨集?

有幾種方法

第一個方法是手動執行,選擇 "開發人員"->"巨集"->執行你要跑的巨集

當然也可以在 "開發人員"->"插入"->選按鈕,綁定你的巨集

第二個方法是讓EXCEL在打開的時候就跑一次巨集,請參考:

那這邊就這樣,ㄅㄅ

--

--