常見範例
從學號產生 Email 位址
假設我們在 Excel 檔有一系列的學號,而各個學生的 Email 位址都是依據學號來建立的。
這樣我們就可以利用簡單的 VBA 程式將學號轉換成 Email 位址:
Dim sid, email As String For i = 2 To 7 sid = Cells(i, 1) email = sid & "@your.domain.com" Cells(i, 2) = email Next i
執行的結果如下:
從 Email 取出學號
假設我們有一些固定格式的 Email 位址,而這些 Email 都是以一個英文字母 u
開頭,加上學生的學號組成的,實際資料如下:
若要從中取出學號的部分(也就是數字的部分),可以這樣寫:
Dim sid, email As String For i = 2 To 7 email = Cells(i, 1) sid = Mid(email, 2, 7) Cells(i, 2) = sid Next i
執行的結果如下:
房屋實價登錄資料
這是從政府資料開放平台上下載的房屋實價登錄資料。
假設我要找出所有大安區信義路上房屋的平均單價,就可以使用一個 For
迴圈配合 InStr
來搜尋:
Dim street, price As String Dim s As Double Dim cnt As Integer s = 0 cnt = 0 For i = 2 To 948 street = Cells(i, 2) ' 門牌 ' 檢查門牌是否包含「大安區信義路」 If InStr(street, "大安區信義路") > 0 Then price = Cells(i, 5) ' 單價 If Len(price) > 0 Then ' 跳過沒有單價的資料 cnt = cnt + 1 ' 計算建物總數 s = s + Val(price) ' 計算建物單價總和 End If End If Next i MsgBox "建物總數 = " & cnt & vbCrLf _ & "平均單價 = " & Round(s / cnt)
這裡我們以一個 For
迴圈把每一筆房屋成交紀錄都跑過一次,把每一筆資料的門牌地址抓出來使用 InStr
比對,如果有包含大安區信義路
的字樣,就把該建物的單價記錄下來,最後即可求得整體的平均單價。
由於有些資料的單價欄位是空白的,所以我們在使用單價欄位資料之前還要先以 Len
來檢查一下,確認這個欄位確實有資料之後,才將該筆資料納入計算。
這裡的 Val
函數的用途是將字串資料轉換為數值,而輸出時所加入的 vbCrLf
是指換行的意思。
這個範例的資料與 VBA 程式我放在這個 Excel 檔,有興趣的人可以下載回去執行。
這裡我們只是拿房屋實價登錄資料來示範字串處理的應用方式,實際上房屋的價格評估當然沒有那麼單純,真正的重點在於熟練各種字串處理的相關函數,並結合各種迴圈與判斷結構,練習針對類型的文字資料撰寫對應的 VBA 程式碼。
更多關於 VBA 的教學文章,請參考 VBA 程式設計。
參考資料:Excel Easy、tutorialspoint、Excel VBA Programming
Chen
這邊的文章很受用!
期待版主大大接下來VBA的文章!
play
字串取代中說明Replac在不區分英文字母大小寫時要使用 vbTextCompare的說明中, 句子 “這裡的替換次數設定為 1 是代表不限制的意思” 中的1應是-1吧?
G. T. Wang
已修正,謝謝您。
sean1776
大大的語法顏色真是漂亮
不曉得能否學習如何設定?
線上小白
怎樣 Left 使用變數???
For i = 2 To 100
If Left(U & i ,1) = “找” Then
‘動作
End If
Next
我失敗了……
請問我該怎修改???
VBA初學者
請問 cnt = cnt + 1 ‘ 計算建物總數
為甚麼要cnt=0 cnt=cnt+1,cnt 代表甚麼含意
s = s + Val(price) ‘ 計算建物單價總和
為甚麼要 s=0 s = s + Val(price),其用意是甚麼?
有點不明白,求解~~ 感謝
william
請問如何改variant/double to variant/string ?
謝謝