Excel VBA 程式設計教學:基本字串處理(String)

常見範例

從學號產生 Email 位址

假設我們在 Excel 檔有一系列的學號,而各個學生的 Email 位址都是依據學號來建立的。

VBA 以學號產生 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

執行的結果如下:

VBA 以學號產生 Email

從 Email 取出學號

假設我們有一些固定格式的 Email 位址,而這些 Email 都是以一個英文字母 u 開頭,加上學生的學號組成的,實際資料如下:

VBA 從 Email 取出學號

若要從中取出學號的部分(也就是數字的部分),可以這樣寫:

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 EasytutorialspointExcel VBA Programming

Windows, 程式設計

4 留言

  1. Chen

    這邊的文章很受用!
    期待版主大大接下來VBA的文章!

  2. play

    字串取代中說明Replac在不區分英文字母大小寫時要使用 vbTextCompare的說明中, 句子 “這裡的替換次數設定為 1 是代表不限制的意思” 中的1應是-1吧?

  3. sean1776

    大大的語法顏色真是漂亮
    不曉得能否學習如何設定?

Leave a Reply