VBA里的文本函数 find() search() substitute() replace() match() large() 在EXCEL工作表使用和VBA中使用差别的对比

最新推荐文章于 2023-06-14 12:38:01 发布

奔跑的犀牛先生

最新推荐文章于 2023-06-14 12:38:01 发布

阅读量1.5w

点赞数 7

本文链接：https://blog.csdn.net/xuemanqianshan/article/details/104490706

版权

1 find()

1.1 worksheetfunction.find()

工作表函数
FIND(find_text, within_text, [start_num])
FINDB(find_text, within_text, [start_num]) 中文字符等会识别为2位
返回的是要查找的内容在字符串内的位数。

局限性
区分大小写，不允许通配符
如果在VBA中使用，记得字符串，是需要"" 表达的
也是模糊查询

Sub test501()

Debug.Print WorksheetFunction.Find(a, Range("B3").Value)
Debug.Print WorksheetFunction.Find(Range("d5").Value, Range("B3").Value)
Debug.Print WorksheetFunction.Find("a", Range("B3").Value)
Debug.Print WorksheetFunction.Find(3, Range("B3").Value)

End Sub

1.2 worksheetfunction.find() 使用注意点

如果在EXCEL内使用
如果查找的内容找不到,find() 不会报错，而是返回第1个字符串的位置，一般会返回1，其实已经是算报错了！
另外，引用单元格的值，就相当于使用了变量，不需要特别加字符串也不需要特别加 ""
但如果是在公式里写字符串，也需要加""

如果在VBA中使用
首先尽量不要再VBA中用工作表函数，因为一旦健壮性差，一旦查不到会报错，如下图
字符串要加""，除非是引用单元格range的值。

1.3 application.find() 返回的是range

在VBA中用工作表函数，尽量用 application.find() 而尽量不用 worksheetfunction.find()，这两者语法和功能类似。
application.find() 虽然有些情况只会返回错误，但有些情况再VBA里也会弹出报错
application.worksheetfunction.find() 更适合工作表
application.find() 更适合VBA
虽然VBA可能自带一些函数，比如VBA.find() 就没有，但即使有，和application的功能一般都是不同的。

Sub test503()

Debug.Print Application.Find(a, Range("B3").Value)
Debug.Print Application.Find(Range("d5").Value, Range("B3").Value)
Debug.Print Application.Find("a", Range("B3").Value)
Debug.Print Application.Find(3, Range("B3").Value)

Debug.Print Application.Find("aaaa", Range("B3").Value)  '找不到会返回错误值，但不会跳出错误提示


End Sub

1.4 application.find() 的问题----主要适合在一个字符串内查找！而且类型不匹配的话也会跳出报错。

首先，先弄清楚
application.find 和 application.worksheetfunction.find 都源于工作表
而在工作表中，多少情况下，都是查找一个单元格的内容----一般都是字符串
而 application.find(）就是查找一个单元格里的元素，只能查找一个字符串的内容

实测只适合字符串，不适合多个单元格的range，数组等
如果，查找的范围是数组，或多个单元格的range，会报错，类型不匹配

application.find() 虽然可以有些情况下，可以返回 error 2015
但是一旦查找的内容，找不到，也可能会弹出报错，VBE编辑器好像不稳定，多次运行的结果不同

Sub test505()

Debug.Print Application.Find(2, "a12345")
Debug.Print Application.Find(3, Range("b10"))
Debug.Print Application.Find("aaaa", Range("b10").Value)  '实测VBE不稳定，有时候也会弹出报错

'查不到的内容也会报类型不匹配
'Debug.Print "Application.Find(9, ""a12345"") " & Application.Find(9, "a12345")
'Debug.Print "Application.Find(""aaa"", ""a12345"") " & Application.Find("aaa", "a12345")


'下面会报错类型不匹配
'Debug.Print "Application.Find(1, Array(1, 2, 3))  " & Application.Find(1, Array(1, 2, 3))
'Debug.Print "Application.Find(2, Range(""B1: B2 "")) " & Application.Find(2, Range("B1:B2"))
End Sub

1.5 VBA.find() 和 find() ,这两个函数并没有，不存在

1.6 object.find()

语法
Find(What, After, LookIn, LookAt, SearchOrder, SearchDirection, MatchCase, MatchByte, SearchFormat)
find()一般是为了查询结果，返回的是找到的对象 range

局限性
是模糊查询
只会返回查找到的第一个range()--单元格

Sub test502()
Dim a As Object   'find返回的应该是 range对象
Set a = ActiveWorkbook.ActiveSheet.UsedRange.Find(what:=2, LookIn:=xlValues)
Debug.Print a.Address


'这句话语法上OK，但因为find() 本身的目的，不返回find()的值，这么写显得很奇怪
ActiveSheet.UsedRange.Find what:=2, LookIn:=xlValues
'ActiveSheet.UsedRange.Find（what:=2, LookIn:=xlValues） '这样会报错

End Sub

详细了find()函数的说明，以后再学习

https://blog.csdn.net/threshold1980/article/details/84869665

1.7 Application.find() 和 obejct.find() 的区别，语法和返回值都不同。

这两者的语法差别很大
application.find() 是工作表相关的，返回的是找到的字符串的位置
obejct.find() 是VBA相关的，返回的是range

2 search() ----不区分大小写，可以加通配符，其他类似find()

2.1 worksheetfunction.search()

语法
search(find_text, within_text, [start_num])
=SEARCH("n","printer")
=SEARCH("base","database") 会返回字符串首字母的位置
search()
searchB()

局限性
search() 是不区分大小写的
也是模糊查询

2.2 application.search()

Sub test503()

Debug.Print Application.Search(a, Range("B3").Value)
Debug.Print Application.Search(Range("d5").Value, Range("B3").Value)
Debug.Print Application.Search("a", Range("B3").Value)
Debug.Print Application.Search(3, Range("B3").Value)

Debug.Print Application.Search("aaaa", Range("B3").Value)  '找不到会返回错误值，但不会跳出错误提示

End Sub

2.3 object.search() 没有

2.4 search() 没有

2.5 VBA中取代search() 和 find() 的是 vba.instr()

2.6 VBA.instr() 语法,但是并没有object.instr()

InStr([start,]string1,string2[,compare])
Start - 一个可选参数。指定搜索的起始位置。搜索从第一个位置开始，从左到右。
String1 - 必需的参数。要搜索的字符串。
String2 - 必需的参数。要在String1中搜索的字符串。
Compare - 一个可选参数。指定要使用的字符串比较。它可以采取以下提到的值：
0 = vbBinaryCompare - 执行二进制比较(默认)
1 = vbTextCompare - 执行文本比较/

Sub test504()

Debug.Print InStr(1, Range("B3").Value, Range("d5").Value)


Dim a
a = InStr(1, Range("B3").Value, Range("d5").Value)
Debug.Print a

End Sub

3 substitute

3.1 worksheetfunction.substitute() 基本语法

SUBSTITUTE(text，old_text，new_text，instance_num)
可以实现
可以实现替换第几个，这个比 worksheetfunction.find()好用
局限性，不支持通配符

3.2 worksheetfunction.substitute() 和application.substitute()

worksheetfunction.substitute() 和application.substitute() 即使找不到合适的替换内容也不会报错
substitute 不一定需要返回值，所以可以采用不加括号的单独句子写法

Sub test505()

Application.WorksheetFunction.Substitute "abc123abc123abc", "abc", "AAA", 2
Debug.Print Application.WorksheetFunction.Substitute("abc123abc123abc", "abc", "AAA", 2)
Debug.Print Application.WorksheetFunction.Substitute("abc123abc123abc", "abcd", "AAA", 2)
Debug.Print

Application.Substitute "abc123abc123abc", "abc", "AAA", 2
Debug.Print Application.Substitute("abc123abc123abc", "abc", "AAA", 2)
Debug.Print Application.Substitute("abc123abc123abc", "abcd", "AAA", 2)


End Sub

3.3 VBA里好像没有 subsitute() 相关的函数

4 replace()

4.1 worksheetfunction.replace()

语法
REPLACE(old_text, start_num, num_chars, new_text)
支持通配符
和substitute() 完全不同，是按位数替换，而不是查找关键字式替换。

4.2 object.replace() 可以算是VBA中 replace() 和 substitute() 的替代功能

语法
Selection.Replace What:="￥", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ReplaceFormat:=False
replace() 返回的是 bool，除了做if判断等，一般很少调用，做替换操作才行。

Sub test302()
 
'ActiveSheet.UsedRange.Replace what:=6, replacemeent:=666
Range("a1:d10").Replace what:=6, replacement:=666
'这个是模糊查找，不是精确查找，所以替换可能不是想要的，比如66会变成666666
 
'replace()也有返回值，是布尔值，除了做判断可能调用其返还值，一般执行操作就可以
a = Range("a1:d10").Replace(what:=6, replacement:=666)
Debug.Print a

End Sub

4.3 支持通配符，但是其操作的对象，返回的缺不是对象，而返回bool

所以一般情况下， object.replace() 函数不用来返回值，只用来操作。

操作成功，修改了单元格的值
但是返回的只是bool，而没能返回结果

Sub test507()

'ActiveSheet.UsedRange.Replace what:=6, replacemeent:=666
Range("b3").Replace what:=1 * 3, replacement:=666
'这个是模糊查找，不是精确查找，所以替换可能不是想要的，比如66会变成666666
'repalce（）支持通配符
Debug.Print



a = Range("b3").Replace(what:=1 * 3, replacement:=666)
Debug.Print a
'replace()也有返回值，是布尔值，除了做判断可能调用其返还值，一般执行操作就可以
'replace虽然是操作的对象，但其返回值却不是对象...,而是是否替换成功
'可能replace() 函数设计的目的主要还是为了操作，不是为了返回值吧


End Sub

5 large() 和 small()

5.1 worksheetfunction.large() 和 worksheetfunction.small()

好像没有对应的VBA函数
worksheetfunction.large() 如果指定不合适和报错
在VBA中尽量使用 application.large()

Sub test508()

Debug.Print WorksheetFunction.Large(Range("H1:H5"), 2)
a = Application.Large(Range("H1:H5"), 2)
Debug.Print a
Debug.Print

'Debug.Print WorksheetFunction.Large(Range("H1:H5"), 10)  '这样会报错
a = Application.Large(Range("H1:H5"), 10)
Debug.Print a
Debug.Print

'b = Large(Range("H1:H5"), 2)
'Debug.Print b

End Sub

6 application.match()

worksheetfunction.match()
application.match() 随便有些情况只会返回错误，不会跳出报错，但有些情况下在VBA里也会跳出报错的

6.1 错误举例

错误的思路：没有先考虑出错处理
application.match() 查不到会报错，无法取得 match 属性

6.2 正确代码

Sub aa31()
On Error Resume Next
in1 = Int(InputBox("请输入1个要查的数字"))
a = WorksheetFunction.Match(in1, Array(1, 2, 3, 4, 5), 0)

If Err = 0 Then
   Debug.Print a
Else
   Debug.Print "没找到!"
End If
End Sub