You can compare two strings to see if they are alike using the built-in StrComp function in VBA. When you compare two strings, the StrComp function returns a value indicating whether the strings are the same. If the strings are different, the function returns a value that shows which string is larger. Show
The StrComp function has three different arguments, of which the first two arguments are required: string1, string2, and compare. The string1 and string2 arguments indicate the strings to compare. You can use a string enclosed in quotes, a string variable, or a reference to a cell containing a string as the argument value. An optional constant value, the compare argument, determines how the function compares the strings. If you omit the argument, the function uses a binary comparison of the strings, or the comparsion specified by the Option Compare statement. The vbBinaryCompare constant indicates that you want to compare the strings based upon the ANSI character code for each character in the string. On an ANSI character chart, lowercase letters have a larger value than uppercase characters. Use the vbTextCompare constant if you want to compare strings based upon the actual text, regardless of the case. With this comparison, the StrComp function sees an M and m as the same character. You can use the vbUseCompareOption to indicate that you want to use the type of comparison specified by the Option Compare statement. If the statement does not exist, a binary comparison is performed. The StrComp function returns a value of 0 if the strings are the same. If the first string is larger, it returns 1. If the second string is larger, the function returns -1. If either string is Null, the function returns Null. COMPARE TWO STRINGS COMPARE TWO STRINGS □ Type Option Compare Text at the top of the module. Create a new subroutine. □ Type Option Compare Text at the top of the module. Create a new subroutine. < Type Dim String1 As String replacing Stringl with the first string to compare. ^H Type Dim String2 As String replacing String2 with the second string to compare. Q Assign values to Stringl and String2.When comparing strings, the StrComp function treats upper and lowercase characters differently for a binary comparison. In other words, the StrComp function considers a J and j the same. If you want to compare strings to see if they contain the same characters, you must convert the strings to the same case — either all uppercase or all lowercase — before making the comparison. VBA provides two built-in functions that you can use to convert strings. The UCase function, UCase(string), converts the lowercase characters in a string to uppercase. The LCase function, LCase(string), converts all uppercase characters in the string to lowercase. Both functions ignore numbers and symbols in the string and require one argument to convert the string. You can make the string argument an actual string enclosed in quotes, or a reference to a string such as a variable name. If the string contains a Null value, the function returns a value of Null. You can place the Option Compare statement at the top of a module to indicate the type of comparison to make between strings. To perform a binary comparison of all strings, type: Option Compare Binary If you want to compare all strings as text, type: Option Compare Text Type If StrComp(String1, String2) = 0 Then, replacing Stringl and String2 with the strings in steps 3 and 4. □ Type VBA statements to perform if strings match. • Switch to Excel and run the macro. ■ The StrComp function compares the two strings and returns a value. Type If StrComp(String1, String2) = 0 Then, replacing Stringl and String2 with the strings in steps 3 and 4. □ Type VBA statements to perform if strings match. • Switch to Excel and run the macro. ■ The StrComp function compares the two strings and returns a value. Continue reading here: Debugging Basics Was this article helpful?
Índice No VBA, você pode comparar duas strings usando o StrComp função. Neste tutorial, você aprenderá como usar essa função com diferentes métodos de comparação. Se você quiser aprender como usar o VBA Like Operator para comparar correspondências inexatas, clique aqui: VBA Like Operator. Se você quiser aprender como usar operadores de comparação, clique aqui: VBA Comparison Operators - Not Equal to & More Usando a função StrComp para comparar duas stringso StrComp função leva duas strings e método de comparação como argumentos. Existem 3 métodos de comparação:
Por padrão, a função usa o método binário. Se omitirmos esse argumento, a função fará distinção entre maiúsculas e minúsculas. A função retorna os seguintes resultados:
Aqui está o código de exemplo:
Neste exemplo, queremos verificar se as strings "Computador" e "Computador" são iguais, usando o método de comparação padrão (binário). O resultado da função está no intResult variável. Como você pode ver na imagem, ambas as strings são iguais, portanto, o intResult tem valor 0. Imagem 1. Usando a função StrComp com o método binário em VBA Usando a função StrComp com método binário e textualAgora você verá a diferença entre usar o método binário e o textual. O método binário tem um número binário para cada caractere, portanto, maiúsculas e minúsculas são caracteres diferentes neste método. Por outro lado, o método textual considera “S” e “s” como as mesmas letras. Se você quiser que a função não faça distinção entre maiúsculas e minúsculas, você precisa definir o terceiro argumento como vbBinaryCompare. Aqui está o código:
Queremos comparar strings "Computador" e "Computador", usando ambos os métodos. Na variável intResult1, obteremos o valor com o método binário, enquanto o intResult2 obterá o valor com o método textual. Você pode ver os valores dessas duas variáveis: Imagem 2. Usando a função StrComp com método binário e textual O valor de intResult1 é 1, o que significa que duas strings são diferentes para o método binário. Para o método textual, essas duas strings são iguais, então o valor de intResult2 é 0.
Você vai ajudar o desenvolvimento do site, compartilhando a página com seus amigos
In VBA or excel, we face plenty of different scenarios. One such scenario is “comparing two string values.” In a regular worksheet, we can do these multiple ways, but in VBA, how do you do this? You are free to use this image on your website, templates etc, Please provide us with an attribution link Article Link to be HyperlinkedFor eg: Source: VBA String Comparison (wallstreetmojo.com) Below is the syntax of the “StrComp” function. First, two arguments are quite simple,
We can supply three values here.
The results of the “StrComp” function do not default TRUE or FALSE but vary. Below are the different results of the “StrComp” function.
How to Perform String Comparison in VBA?Example #1We will match “Bangalore” against the string “BANGALORE.” First, declare two VBA variablesVariable declaration is necessary in VBA to define a variable for a specific data type so that it can hold values; any variable that is not defined in VBA cannot hold values.read more as the string to store two string values. Code: Sub String_Comparison_Example1() Dim Value1 As String Dim Value2 As String End SubFor these two variables, store two string values. Code: Sub String_Comparison_Example1() Dim Value1 As String Dim Value2 As String Value1 = "Bangalore" Value2 = "BANGALORE" End SubNow declare one more variable to store the result of the “StrComp” function. Code: Sub String_Comparison_Example1() Dim Value1 As String Dim Value2 As String Value1 = "Bangalore" Value2 = "BANGALORE" Dim FinalResult As String End SubFor this variable, open the “StrComp” function. Code: Sub String_Comparison_Example1() Dim Value1 As String Dim Value2 As String Value1 = "Bangalore" Value2 = "BANGALORE" Dim FinalResult As String FinalResult = StrComp( End SubFor “String1” & “String2” we have already assigned values through variables, so enter variable names, respectively. Code: Sub String_Comparison_Example1() Dim Value1 As String Dim Value2 As String Value1 = "Bangalore" Value2 = "BANGALORE" Dim FinalResult As String FinalResult = StrComp(Value1, Value2, End SubThe last part of the function is “Compare” for this choice “vbTextCompare.” Code: Sub String_Comparison_Example1() Dim Value1 As String Dim Value2 As String Value1 = "Bangalore" Value2 = "BANGALORE" Dim FinalResult As String FinalResult = StrComp(Value1, Value2, vbTextCompare) End SubNow show the “Final Result” variable in the message box in VBAVBA MsgBox function is an output function which displays the generalized message provided by the developer. This statement has no arguments and the personalized messages in this function are written under the double quotes while for the values the variable reference is provided.read more. Code: Sub String_Comparison_Example1() Dim Value1 As String Dim Value2 As String Value1 = "Bangalore" Value2 = "BANGALORE" Dim FinalResult As String FinalResult = StrComp(Value1, Value2, vbTextCompare) MsgBox FinalResult End SubOk, let’s run the code and see the result. Output: Since both the strings “Bangalore” and “BANGALORE” are the same, we got the result as 0, i.e., matching. Both the values are case sensitive since we have supplied the argument as “vbTextCompare” it has ignored case sensitive match and matched only values, so both the values are the same, and the result is 0, i.e., TRUE. Code: Example #2For the same code, we will change the compare method from “vbTextCompare” to “vbBinaryCompare.” Code: Sub String_Comparison_Example2() Dim Value1 As String Dim Value2 As String Value1 = "Bangalore" Value2 = "BANGALORE" Dim FinalResult As String FinalResult = StrComp(Value1, Value2, vbBinaryCompare) MsgBox FinalResult End SubNow run the code and see the result. Output: Even though both the strings are the same, we got the result as 1, i.e., Not Matching because we have applied the compare method as “vbBinaryCompare,” which compares two values as case sensitive. Example #3Now we will see how to compare numerical values. For the same code, we will assign different values. Code: Sub String_Comparison_Example3() Dim Value1 As String Dim Value2 As String Value1 = 500 Value2 = 500 Dim FinalResult As String FinalResult = StrComp(Value1, Value2, vbBinaryCompare) MsgBox FinalResult End SubBoth the values are 500, and we will get 0 as a result because both the values are matched. Output: Now I will change the Value1 number from 500 to 100. Code: Sub String_Comparison_Example3() Dim Value1 As String Dim Value2 As String Value1 = 1000 Value2 = 500 Dim FinalResult As String FinalResult = StrComp(Value1, Value2, vbBinaryCompare) MsgBox FinalResult End SubRun the code and see the result. Output: We know Value1 & Value2 aren’t the same, but the result is -1 instead of 1 because for numerical comparison when the String 1 value is greater than String 2, we will get this -1. Code: Sub String_Comparison_Example3() Dim Value1 As String Dim Value2 As String Value1 = 1000 Value2 = 500 Dim FinalResult As String FinalResult = StrComp(Value1, Value2, vbBinaryCompare) MsgBox FinalResult End SubNow I will reverse the values. Code: Sub String_Comparison_Example3() Dim Value1 As String Dim Value2 As String Value1 = 500 Value2 = 1000 Dim FinalResult As String FinalResult = StrComp(Value1, Value2, vbBinaryCompare) MsgBox FinalResult End SubRun the code and see the result. Output: This is not special. If not match, we will get 1 only. Things to Remember here
Recommended ArticlesThis has been a guide to the VBA string comparison. Here we discuss how to compare two string values using the StrComp function in excel VBA along with examples and download an excel template. You may also have a look at other articles related to Excel VBA – |