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

這裡將介紹 Excel VBA 中各種字串的操作方式與相關的功能函數,並且提供各式常見的使用範例程式碼。

在使用 Excel VBA 處理資料時,通常除了數值的資料之外,文字的資料處理也是很常會遇到的工作,而相較於運算很單純的數值,文字的處理又更複雜了一些,例如轉換大小寫、取出部分的文字、比較或取代文字等,通常文字在處理時,都需要根據不同的問題來撰寫 VBA 程式碼,以下我們會說明各種常見的字串處理函數,並且提供各種常見的範例程式給大家參考。

字串的宣告與定義

VBA 中的 String 就是用來儲存字串(文字)的變數類型,其宣告與定義的方式跟一般的數值變數類似,而字串的內容在指定時要用雙引號包起來,例如:

Dim mystr As String
mystr = "This is a message!!!"

上面這兩行宣告了一個 mystr 字串變數,並且把該變數的內容設定為 This is a message!!!

另外我們也可以利用 String 函數來建立重複字元的字串,例如:

Dim text1, text2 As String
text1 = String(6, "A") ' 產生 AAAAAA
text2 = String(3, 100) ' 產生 ddd

String 第一個參數是指定要產生的字串長度,而第二個參數則是指定重複的字元,除了直接用雙引號包起來的方式指定字元之外,也可以直接用 ASCII 的編碼指定,像這裡的 100 就是指 d 的 ASCII 編碼。

連接字串

如果要將多個字串連接起來,可以使用 & 這個運算子,以下是將三個字串連接在一起的範例。

Dim text1 As String, text2 As String
text1 = "Hello"
text2 = "World"

' 使用 & 連接字串
MsgBox text1 & ", " & text2

這裡我們定義了兩個字串變數 text1text2,接著使用 & 把它們接在一起,並且在兩個英文單字之間加入一個逗號與空白。

VBA 連接字串

執行之後,就會輸出三個字串連接起來的結果。

VBA 輸出訊息

字串長度

在 VBA 中若要取得一個字串的長度,可以使用 Len 函數:

MsgBox Len("Hello, world.")

Len 函數的參數只有一個字串變數,呼叫 Len 之後它就會傳回該字串的長度:

VBA 取得字串長度

VBA 取得字串長度

Len 傳回的數值是一個整數,我們也可以把它儲存在變數中做其他的運算:

Dim text As String
text = "Hello World"

Dim textLen As Integer
textLen = Len(text)

MsgBox "textLen = " & textLen

VBA 取得字串長度

VBA 取得字串長度

搜尋字串位置

若要尋找一個字串中某個子字串的位置,可以使用 InStr 函數:

Dim pos As Integer
pos = InStr("Hello, world.", "world")
MsgBox "pos = " & pos

InStr 第一個參數是放一個比較長的字串,而第二個參數則是放要搜尋的關鍵字,以這個例子來說它就會在 Hello, world. 這一個字串中尋找 world 出現的位置。

VBA 搜尋字串位置

VBA 搜尋字串位置

InStr 預設在搜尋時,英文的大小寫是視為不同的,若希望不分大小寫,可將比對方式參數設定為 vbTextCompare

Dim pos As Integer
pos = InStr(1, "Hello, World.", "world", vbTextCompare)
MsgBox "pos = " & pos

這裡的第一個參數是用來指定搜尋的起始位置,在上面的範例中我們將這個參數省略掉了,在這裡使用比對方式參數時就要加進來,這樣執行之後 InStr 在比對字串時就會將英文字母的大小寫視為相同的。

在 Excel VBA 程式碼編輯器中輸入函數時,通常都會有這樣的參數用法提示。

VBA 參數用法提示

有些函數的參數非常多,若是忘記參數的詳細位置與順序時,就可以善用這個小技巧。

如果想從字串的結尾處開始往左搜尋,則可改用 InStrRev

Dim pos As Integer
pos = InStrRev("Hello, World.", "l")
MsgBox "pos = " & pos

VBA 搜尋字串位置

若要讓 InStrRev 搜尋時不分大小寫,可以這樣寫:

Dim pos As Integer
pos = InStrRev("Hello, WORLD.", "l", -1, vbTextCompare)
MsgBox "pos = " & pos

第三個參數是搜尋起始點,預設值是 -1(從最右方開始搜尋),最後一個參數就是比對方式,用法同上。

取出子字串

如果要從一段字串中取出部分的子字串,依據不同的狀況會有好幾種作法,以下是各種取出子字串的函數與用法。

開頭子字串

如果想要取出一段文字中開頭的部分,可以使用 Left 函數:

MsgBox Left("Hello, world.", 5)

Left 的意思就是從左邊開始擷取子字串的意思,第一個參數是完整的字串,而第二個參數則是要擷取的子字串長度,執行結果如下:

VBA 取出開頭子字串

結尾子字串

如果想要取出一段文字中結尾的部分,可以使用 Right 函數,其用法跟 Left 類似,只是換成從右邊開始擷取子字串而已:

MsgBox Right("Hello, world.", 6)

執行結果如下:

VBA 取出結尾子字串

任意位置子字串

如果要擷取的子字串位置不在文字的兩端,而是在文字的中間,則可使用 Mid 函數:

MsgBox Mid("This is a message.", 6, 2)

Mid 函數的第一個參數是完整的字串,第二個參數是要擷取的子字串位置(從左邊算起),而第三個參數則是要擷取的子字串長度,這個例子就會從 This is a message. 這段文字的第 6 個字元開始擷取,取出 2 個字元,執行結果如下。

VBA 取出任意位置子字串

如果使用 Mid 時不指定擷取的字串長度,則 Mid 就會從指定的位置開始擷取直到整個字串的結尾,例如:

MsgBox Mid("This is a message.", 6)

VBA 取出任意位置子字串

Windows, 程式設計

2 Comments

  1. Chen

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

  2. play

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

Leave a Reply