excel用pos怎么打(Excel VBA｜全面理解字符串及处理)

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

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 iEnd Sub4 把字符转换为数值

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

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

如：Asc("45") * 3

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

Val("6.5元每斤") * 10

5 字符串比较

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

Option Compare TextSub 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 = vEnd Sub8 VBA字符串处理函数

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

Sub Test7() Dim s As String, t As String s = "高手就是高手" t = Replace(s, "高手", "HighHand") Debug.Print tEnd Sub9 分割字符串为数组

利用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 = vEnd Sub10 将字符串数组各元素连接为一个字符串

利用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 sEnd Sub11 字符串数组筛选

利用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) StopEnd Sub12 工作表字符串处理函数

Sub Test7() Dim s As String, t As String s = "高手就是高手" t = Application.WorksheetFunction.Replace(s, 1, 2, "HighHand") Debug.Print tEnd 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 posEnd 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 fileEnd Sub'输出data.txt15 使用正则表达式

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

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

first and second