1 字符串常量

字符串常量分符号常量、字面常量、内置常量。

字符串的字面常量要求使用双引号界定（为了和变量名称相区别）。

Private Const str = "Const String"

以上表达式str就是符号常量，右值就是字面常量。

内置常量表示特殊字符，如BA.Constants.vbTab就是表示一个制表符；

如果字符串分为多行，使用vbNewLine即可。

2 字符串变量

Sub strings()
 Dim str As String ' 字符串变量声明
 Dim strn As String * 6 '定长字符串变量
 str = "VBA"
 strn = "Excel" & str ‘字符串连接
 Debug.Print strn
 Dim str2 As Variant '变体型，可以接受包括字符串在内的任意类型
 str2 = "变体型"
 Debug.Print TypeName(str2) '类型判断
 Debug.Print VBA.varType(str2) = VBA.VbVarType.vbString
 Dim str3 As String
 Debug.Print str3 '初始默认值为空
 Debug.Print VBA.Information.IsNull(str3)
 Debug.Print VBA.Information.IsEmpty(str3)
 Dim str4$ '变量声明的简写
 Debug.Print TypeName(str4)
 End Sub

运行结果：

ExcelV

String

True

False

String

3 用ASCII码生成字符串

Sub Test1()
 Dim i As Integer
 For i = 0 To 127
 Range("A" & (i + 1)).Value = i + 1
 Range("B" & (i + 1)).Value = Chr(i + 1)
 Next i
End Sub

4 把字符转换为数值

Left(CStr(13579), CLng("3"))

Asc(s)则可以把字符转换为数值；

如：Asc("45") * 3

Val函数可以把以数字开头的字符串中提取出连续数字并返回为数值：

Val("6.5元每斤") * 10

5 字符串比较

模块默认的方式是二进制比较方式：Option compare Bianry，也就是默认区分大小写，也可以在模块顶部声明为Option Compare Text文本比较方式，这是在文本比较时不再区分大小写：

Option Compare Text
Sub Test1()
 Debug.Print "VBA" > "excel"
 Debug.Print "VBA" = "vba"
End Sub
' 都返回为True，如果第一行注释掉，则返回为False;

6 模式匹配

模式匹配格式：Result = String like Pattern

如以下匹配都返回True

 "vba" Like "vb?"
 "vba" Like "v*"
 "vb6" Like "vb#"
 "vba" Like "[!abcde]ba"

7 字符串数组

 Sub test()
Dim v As Variant
 v = Array("sn", "书名", "作者", "借书日期", "应还日期")
 For i = LBound(v) To UBound(v)
 Debug.Print v(i)
 Next i
 Range("A1:E1").Value = v
End Sub

8 VBA字符串处理函数

可以在代码窗口通过代码提示查看：

excelvba字符串处理,excelvba分析表格数据

Sub Test7()
 Dim s As String, t As String
 s = "高手就是高手"
 t = Replace(s, "高手", "HighHand")
 Debug.Print t
End Sub

9 分割字符串为数组

利用VBA内置全局函数Split()。

Sub Test1()
 Dim s As String
 Dim v As Variant
 s = "excel word outlook access"
 v = Split(s)
 Debug.Print v(0), v(3)
 ActiveSheet.Range("B3:E3").Value = v
End Sub

10 将字符串数组各元素连接为一个字符串

利用VBA内置全局函数Join()。

Sub Test4()
 Dim arr(2 To 5) As String
 Dim s As String
 arr(2) = "excel"
 arr(3) = "word"
 arr(4) = "outlook"
 arr(5) = "access"
 s = Join(arr)
 Debug.Print s
End Sub

11 字符串数组筛选

利用VBA内置全局函数Filter()。

Sub Test5()
 Dim arr(2 To 5) As String
 Dim v As Variant
 arr(2) = "excel"
 arr(3) = "word"
 arr(4) = "outlook"
 arr(5) = "access"
 v = Filter(arr, "e", True)
 Stop
End Sub

12 工作表字符串处理函数

excelvba字符串处理,excelvba分析表格数据

Sub Test7()
 Dim s As String, t As String
 s = "高手就是高手"
 t = Application.WorksheetFunction.Replace(s, 1, 2, "HighHand")
 Debug.Print t
End Sub

将英文文本的第一个字符大写，其它保持不变：

Range("D2").Formula = "=LEFT(PROPER(C2),1)&LEFT(C2,LEN(C2)-1)"

13 字符串子串提取

Sub Test8()

Dim s As String, t As String

s = "大江东去浪淘尽"

t = Mid(s, 3, 4)

Debug.Print t

Mid(s, 3, 3) = "DQL"

Debug.Print s

End Sub

14 检索子串位置

Sub Test9()
 Dim s1 As String, s2 As String, pos As Integer
 s1 = "ExcelVBAWordVBAOutlookVBA"
 s2 = "VBA"
 pos = InStr(s1, s2)
 Debug.Print pos
End Sub
'输出6
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Sub Test7()
 Dim path As String
 Dim file As String
 Dim pos As Integer
 path = "E:\ADOSQLwizard\示例数据源\data.txt"
 pos = InStrRev(path, "\")
 file = Right(path, Len(path) - pos)
 Debug.Print file
End Sub
'输出data.txt

15 使用正则表达式

正则表达式是绝大多数编程语言最强大的字符串处理工具。

Sub test123()
Dim mh, s$
s = "Hypertext <a>first</a> and <a>second</a>"
With CreateObject("vbscript.regexp")
.Pattern = "<.*>"
.Global = True
Set mh = .Execute(s)
End With
Debug.Print mh(0)
With CreateObject("vbscript.regexp")
.Pattern = "<.*?>" '非贪婪
.Global = True
Set mh = .Execute(s)
End With
Debug.Print mh(0)
End Sub

output:

<a>first</a> and <a>second</a>
<a>

－End－

excel vba字符串处理 (vba excel字符串操作)