在 VBA 中比较字符串的最佳方法？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/45215786/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Best way to compare strings in VBA?
提问by FunThomas
I've been using VBA for the past month now (for my job) and since I've begun using this language/script, there have been several occurances where I have issues comparing strings.. the ways that I've compared strings so far are:
过去一个月我一直在使用 VBA（为了我的工作），自从我开始使用这种语言/脚本以来，有几次我在比较字符串时遇到问题......我比较字符串的方式远的是：
- str1 = str2and str1 <> str2
- StrComp(str1, str2, comparisonMethod)
- InStr(str1, str2)
- str1 = str2和str1 <> str2
- StrComp(str1, str2, 比较方法)
- InStr(str1, str2)
Sometimes, before using one of these comparison methods, I'll first apply UCase(str1), or even UCase(Trim(str1)). From my experience so far though, comparing strings in VBA seems to be a rather fickle thing, because I feel like I get inconsistent results.
有时，在使用这些比较方法之一之前，我会先应用 UCase(str1) ，甚至 UCase(Trim(str1))。不过，从我目前的经验来看，在 VBA 中比较字符串似乎是一件相当善变的事情，因为我觉得我得到的结果不一致。
QUESTION:What is the best procedurefor comparing two strings in VBA?
问题：在 VBA 中比较两个字符串的最佳程序是什么？
For example: Imagine there are two lists.. one list contains laptop models, the other contains parts numbers. The goal is to associate all part numbers with a model. My results produce only partial associations, meaning that if I have 10 part numbers that should associate with a model, only 5 or 6 actually are. I then take the strings that don't properly compare and compare again in seperate code, and will then get the correct result. This kind of behavior has been somewhat consistent throughout my use of VBA, and is where the "feeling" comes from.
例如：想象一下有两个列表。一个列表包含笔记本电脑型号，另一个包含部件号。目标是将所有零件编号与模型相关联。我的结果仅产生部分关联，这意味着如果我有 10 个应该与模型关联的零件号，实际上只有 5 或 6 个。然后我将没有正确比较的字符串在单独的代码中再次比较，然后将得到正确的结果。这种行为在我使用 VBA 的整个过程中一直有些一致，这就是“感觉”的来源。
回答by FunThomas
First question is if you want to compare case sensitive or insensitive - with other words: is "ABC" = "abc".
第一个问题是，如果您想比较区分大小写或不区分大小写 - 换句话说： is "ABC" = "abc"。
The behavior of the =-operator is defined by Option Compare- the default is binary.
=-operator的行为由Option Compare- 默认为binary.
Option Compare text
sub foo1
Debug.print "ABC" = "abc" ' Prints "True"
end sub
Option Compare binary ' <-- This is the default!
sub foo2
Debug.print "ABC" = "abc" ' Prints "False"
end sub
The StrComp-function gets the same results, but without the need of setting the compare option at the top of the modul. Instead, you can give the desired way to compare as parameter:
本StrComp-函数得到相同的结果，但无需设置在模件的顶部比较选项。相反，您可以提供所需的比较方式作为参数：
sub foo3
Debug.Print StrComp("ABC", "abc", vbBinaryCompare) = 0 ' <-- Prints "False"
Debug.Print StrComp("ABC", "abc", vbTextCompare) = 0 ' <-- Prints "True"
end sub
if you have leading or trailing blanks in your strings, you always have to use trim- that's the case in any programming language I know.
如果您的字符串中有前导或尾随空格，则必须始终使用trim- 在我知道的任何编程语言中都是如此。
Using Instris a rather bad idea to check if two strings as identical, you can use it for substring search. Note that InStr(and also its counterpart InstrRev) are using the option comparesetting:
使用Instr检查两个字符串是否相同是一个相当糟糕的主意，您可以将其用于子字符串搜索。请注意InStr（及其对应物InstrRev）正在使用option compare设置：
Option Compare text
sub foo4
Debug.print Instr("ABCDE", "cd") ' Prints "3"
end sub
Option Compare binary
sub foo5
Debug.print Instr("ABCDE", "cd") ' Prints "0"
end sub
