這裡將介紹 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
這裡我們定義了兩個字串變數 text1
與 text2
,接著使用 &
把它們接在一起,並且在兩個英文單字之間加入一個逗號與空白。
執行之後,就會輸出三個字串連接起來的結果。
在 VBA 中若要取得一個字串的長度,可以使用 Len
函數:
MsgBox Len("Hello, world.")
Len
函數的參數只有一個字串變數,呼叫 Len
之後它就會傳回該字串的長度:
Len
傳回的數值是一個整數,我們也可以把它儲存在變數中做其他的運算:
Dim text As String text = "Hello World" Dim textLen As Integer textLen = Len(text) MsgBox "textLen = " & textLen
若要尋找一個字串中某個子字串的位置,可以使用 InStr
函數:
Dim pos As Integer pos = InStr("Hello, world.", "world") MsgBox "pos = " & pos
InStr
第一個參數是放一個比較長的字串,而第二個參數則是放要搜尋的關鍵字,以這個例子來說它就會在 Hello, world.
這一個字串中尋找 world
出現的位置。
InStr
預設在搜尋時,英文的大小寫是視為不同的,若希望不分大小寫,可將比對方式
參數設定為 vbTextCompare
:
Dim pos As Integer pos = InStr(1, "Hello, World.", "world", vbTextCompare) MsgBox "pos = " & pos
這裡的第一個參數是用來指定搜尋的起始位置,在上面的範例中我們將這個參數省略掉了,在這裡使用比對方式
參數時就要加進來,這樣執行之後 InStr
在比對字串時就會將英文字母的大小寫視為相同的。
在 Excel VBA 程式碼編輯器中輸入函數時,通常都會有這樣的參數用法提示。
有些函數的參數非常多,若是忘記參數的詳細位置與順序時,就可以善用這個小技巧。
如果想從字串的結尾處開始往左搜尋,則可改用 InStrRev
:
Dim pos As Integer pos = InStrRev("Hello, World.", "l") MsgBox "pos = " & pos
若要讓 InStrRev
搜尋時不分大小寫,可以這樣寫:
Dim pos As Integer pos = InStrRev("Hello, WORLD.", "l", -1, vbTextCompare) MsgBox "pos = " & pos
第三個參數是搜尋起始點,預設值是 -1
(從最右方開始搜尋),最後一個參數就是比對方式
,用法同上。
如果要從一段字串中取出部分的子字串,依據不同的狀況會有好幾種作法,以下是各種取出子字串的函數與用法。
如果想要取出一段文字中開頭的部分,可以使用 Left
函數:
MsgBox Left("Hello, world.", 5)
Left
的意思就是從左邊開始擷取子字串的意思,第一個參數是完整的字串,而第二個參數則是要擷取的子字串長度,執行結果如下:
如果想要取出一段文字中結尾的部分,可以使用 Right
函數,其用法跟 Left
類似,只是換成從右邊開始擷取子字串而已:
MsgBox Right("Hello, world.", 6)
執行結果如下:
如果要擷取的子字串位置不在文字的兩端,而是在文字的中間,則可使用 Mid
函數:
MsgBox Mid("This is a message.", 6, 2)
Mid
函數的第一個參數是完整的字串,第二個參數是要擷取的子字串位置(從左邊算起),而第三個參數則是要擷取的子字串長度,這個例子就會從 This is a message.
這段文字的第 6
個字元開始擷取,取出 2
個字元,執行結果如下。
如果使用 Mid
時不指定擷取的字串長度,則 Mid
就會從指定的位置開始擷取直到整個字串的結尾,例如:
MsgBox Mid("This is a message.", 6)