VBA-3--String Manipulation字符串操作

String Manipulation字符串操作

在本章中，您将找到在Excel VBA中操作字符串的最重要函数。

在工作表上放置一个命令按钮并在下面添加代码行。要执行代码行，请单击工作表上的命令按钮。

连接字符串

我们使用&运算符连接（连接）字符串。

代码：

Dim text1 As String, text2 As String

text1 = "Hi"

text2 = "Tim"

MsgBox text1 & " " & text2

结果：

 

注意：若要插入空格，请使用“”键

Left取左

要从字符串中提取最左边的字符，请使用Left。

代码：

Dim text As String

text = "example text"

MsgBox Left(text, 4)

结果：

 

同样的操作还有：取右Right、取中Mid

MsgBox Right("example text", 2)

MsgBox Mid("example text", 9, 2)

注：从位置9（t）开始，长度为2。如果要提取从字符串中间开始直到字符串结束的子字符串，可以省略第三个参数。

取长度Len

要获得字符串的长度，请使用Len。

代码：

MsgBox Len("example text")

 

找位Instr

要查找字符串中子字符串的位置，请使用Instr。

代码：

MsgBox Instr("example text", "am")

 

折分字符串

下面我们将看一个Excel VBA中分隔符拆分字符串的程序。

情况：

 

在工作表上放置命令按钮并添加以下代码行：

1首先，我们声明一个名为字符串类型全名的变量、一个名为commaposition整数类型的变量和一个名为i的整数类型变量。

Dim fullname As String, commaposition As Integer, i As Integer

我们要处理的问题是，我们需要告诉Excel VBA要在哪里分隔开字符串。对于“Smith, Mike”逗号在第6位，而对于“ Williams, Janet ”逗号在第9位。

2 我们使用一个循环对在Excel中输入的每个名称执行操作。首先，我们初始化变量fullname。接下来，我们使用Instr函数来查找逗号的位置。

For i = 2 To 7

    fullname = Cells(i, 1).Value

commaposition = InStr(fullname, ",")

3最后，我们想把逗号后面的部分写到B列，把逗号前面的部分写到C列。您可以通过添加以下行来实现：

Cells(i, 2).Value = Mid(fullname, commaposition + 2)

Cells(i, 3).Value = Left(fullname, commaposition - 1)

4别忘了结束本次循环

Next i

5添加六个由逗号和空格分隔的名称到范围（“A2:A7”）。

6测试程序。

结果：

 

反转字符串Reverse Strings

下面我们将看一个Excel VBA中可以反转字符串的程序。

情况：

在工作表上放置命令按钮并添加以下代码行：

1首先，我们声明四个变量。一个称为text 字符串类型的变量，一个称为reversedText字符串类型的变量，一个称为length整数类型的变量，以及一个称为i的整数类型的变量。

Dim text As String, reversedText As String, length As Integer, i As Integer

2我们初始化了两个变量。我们使用输入框函数从用户中获得一个文本字符串。我们在VBA外使用Len长度函数获得该文本字符串的长度。

text = InputBox("Enter the text you want to reverse")

length = Len(text)

 

3我们开始使用一个For循环。

For i = 0 To length - 1

4下面是简单的技巧。我们从文本中提取最后一个字符，并将其放在ReversedText的前面。我们可以使用Excel VBA中的Mid函数从字符串中提取字符。我们使用&运算符连接（连接）两个字符串。

reversedText = reversedText & Mid(text, (length - i), 1)

5别忘了结束本次循环

Next i

6最后，我们使用MsgBox显示reversedText。

msgbox reversedText

7测试程序，输入Car,结果：

 

转换为正确的大小写

下面我们将看看Excel VBA中的一个程序，它将文本转换为正确的大小写。也就是说，每个单词的第一个字母大写，所有其他字母小写。

情况：

 

1首先，我们声明两个Range对象。我们称之为Range对象rng和cell。

Dim rng As Range, cell As Range

2我们用选定的范围初始化Range对象rng。

Set rng = Selection

3我们要检查任意选择的范围内每个单元格（此范围可以是任意大小）。在Excel VBA中，您可以为此使用For Each Next循环。添加以下代码行：

For Each cell In rng

Next cell

4为了忽略包含公式的单元格，在For Each和Next之间添加以下代码行（仅当cell.hasfula为false时，我们继续）。

If Not cell.HasFormula Then

End If

5接下来，我们要将这个范围内的每个单词转换为“正确的大小写”。您可以使用适合此任务的工作表函数WorksheetFunction.Proper。在if语句中添加以下代码行。

cell.Value = WorksheetFunction.Proper(cell.Value)

6测试程序，结果如下：

 

字数

下面我们将看一个Excel VBA中的程序，它计算选定范围内的字数。假设一个或多个空格分隔单词。

 

1首先，我们声明两个Range对象和三个变量。我们称之为Range对象rng和cell。一个整型变量我们称之为cellWords，一个整型变量我们称之为totalWords，还有一个字符串变量我们称之为content。

Dim rng As Range, cell As Range

Dim cellWords, totalWords As Integer, content As String

2我们用0初始化两个整数类型变量，用选定的范围初始化Range对象rng。

Set rng = Selection

cellWords = 0

totalWords = 0

3我们要检查所选范围内的每个单元格（此范围可以是任意大小）。在Excel VBA中，您可以为此使用For Each Next循环。添加以下代码行：

For Each cell In rng

Next cell

4为了忽略包含公式的单元格，在For Each和Next之间添加以下代码行（仅当cell.hasfula为false时，我们继续）。

If Not cell.HasFormula Then

End If

5首先，我们将单元格的内容写入变量内容。接下来，我们移除开头和结尾的空格（如果有的话）。在Excel VBA中，可以使用Trim函数来完成此操作。例如，“ excel vba”将转换为“excel vba”。在If语句中添加以下代码行。

content = cell.Value

content = Trim(content)

注意：Excel VBA中的trim函数不会删除单词之间的多余空格，但在本例中这是可以的。

6此时，单元格仍然可以为空。如果单元格为空，我们将值0赋给变量cellWords。否则，它至少包含一个单词，我们将值1赋给变量cellWords。在If语句中添加以下代码行。

If content = "" Then

    cellWords = 0

Else

    cellWords = 1

End If

当然，一个单元格可以包含多个单词。这正是我们现在想知道的。以“excel vba”为例。如果一个单元格在此阶段至少包含一个空格，则它至少包含一个以上的单词。可以使用Excel VBA中的Instr函数查找空间。Instr（content，“”）查找内容中第一个空格的位置。

7我们将使用Do-While循环结构。放置在这些单词之间的代码（在步骤8、9和10）将重复，只要Do While后面的部分是真的。只要Instr（content，“ ”>0为真（只要content包含空格和更多单词），我们就要重复这些步骤。在If语句中添加Do While循环。

Do While InStr(content, " ") > 0

Loop

8. 接下来，我们从第一个空格的位置开始获取单元格中的部分内容。我们使用Mid函数。

content = Mid(content, InStr(content, " "))

例如: Mid("excel vba", InStr("excel vba", " ")) 将会获得 " vba".

9. 消除前后空格

content = Trim(content)

结果: "vba"

10. 增加cellWords 加 1.

cellWords = cellWords + 1

这个Do While循环将被重复，只要截取得到的内容包含一个空格，从而包含更多的单词。在我们的例子中，我们退出了DO while循环，因为“VBA”不再包含一个空间了！结果：此单元格包含两个单词。

11. 检查完一个单元格后，我们将cellWords添加到变量totalWords中。此代码行应放在Do While循环之外，但在If语句中。

totalWords = totalWords + cellWords

下一个单元格又重新开始整个查空格的过程，直到检查完所有单元格。

12. 最后，我们使用msgbox显示totalWords的值。此代码行应放置在For-Each-Next循环的外部。

MsgBox totalWords & " words found in the selected range."

13. 测试程序。

结果如下：