Results 1 to 6 of 6

How to compare strings in VBA

This is a discussion on How to compare strings in VBA within the Excel Questions forums, part of the Question Forums category; Usually i use the if string1 = string 2 then .... method. but it only performs case-sensitve comparisons. so i ...

  1. #1
    Board Regular
    Join Date
    Jul 2005
    Posts
    63

    Default How to compare strings in VBA

    Usually i use the if string1 = string 2 then .... method.

    but it only performs case-sensitve comparisons. so i looked online and found this code:

    Code:
     compareResult = String.Compare(string1, string2, False, CultureInfo.InvariantCulture)
    however, when i paste it in my code, i get the error saying

    Expected "("

    and it points to the period in between String and Compare.

    what am i doing incorrectly?

  2. #2
    Board Regular hatman's Avatar
    Join Date
    Apr 2005
    Location
    Palmer, MA
    Posts
    2,610

    Default

    you want to use strcomp() rather that string.compare()... I think the syntax is correct from there...

  3. #3
    Board Regular Cbrine's Avatar
    Join Date
    Dec 2003
    Location
    Brampton
    Posts
    3,191

    Default

    Hatman,
    A quick fix for your issue with non-case sentitive values would be

    If UCase(Var1)=UCase(Var2) then


    You convert the test values both to Upper case and do the compare. That way you don't affect the values, but the case doesn't matter.

    HTH
    Cal
    Xcelerated Solutions
    Office Automation Solutions for the Toronto Area

  4. #4
    Board Regular hatman's Avatar
    Join Date
    Apr 2005
    Location
    Palmer, MA
    Posts
    2,610

    Default

    Cbrine: that's true, but strcomp also has an argument that allows you to specify whether the comparison is case sensitive or not:

    vbUseCompareOption -1 Performs a comparison using the setting of the Option Compare statement.
    vbBinaryCompare 0 Performs a binary comparison.
    vbTextCompare 1 Performs a textual comparison.
    vbDatabaseCompare 2 Microsoft Access only. Performs a comparison based on information in your database.


    the value of 1 gives a test comparison without case sensitivity...

  5. #5
    Board Regular Cbrine's Avatar
    Join Date
    Dec 2003
    Location
    Brampton
    Posts
    3,191

    Default

    hatman,
    StrComp will definitly do the job, I was just providing you with another alternative option.
    Xcelerated Solutions
    Office Automation Solutions for the Toronto Area

  6. #6
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,702

    Default

    This is all true, and here's another way:

    Code:
    Option Compare Text
    
    Sub foo()
    MsgBox "test" = "Test"
    End Sub

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com