current community

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

I am looking for a method of comparing two string variables in VBA. The current statement I am using (which is not producing the desired result) is:

  • If Variable1 = Variable2 Then...

I have a VBA macro that assigns two variables in the first part of my code:

  • Dim Variable1 as String Variable1 = Left(Range("$F$3").Value, 4)
  • Dim Variable2 as String Variable2 = ("SheetName.B15")

I have tried using the following statement, but it still does not work correctly:

  • If Variable1 Like Variable2 Then...

Previously, the statement was working with a string comparison to the first variable, but it required that I hard-coded the "ABC" string. It looked like the below statement:

  • If Variable1 = "ABC" Then...

Does anybody know the syntax of a statement to compare two string variables, or could recommend a method for comparing them in a sub and return a binary result, which I could then compare?

Thanks!

Running VBA for Excel 2013 on Windows 8

share|improve this question
3  
If you Debug.Print the value of each variable what do you see? –  Tim Williams Jun 5 '13 at 22:28
 
@Tim-Williams Great advice, Debug.Print shows that I was incorrectly assigning Variable2. Variable2 came back as "SheetName.B15". Using that information, I corrected my variable assignment to: Dim Variable2 As String Variable2 = Worksheets("SheetName").Cells(15,"B").Value Thanks for the push! –  zacc206 Jun 5 '13 at 22:51
 
like this –  mehow Jun 6 '13 at 7:27
add comment

2 Answers

up vote 2 down vote

You can use StrComp

 Sub compare()
        MsgBox StrComp("Stack", "stack") 'alerts -1 
    End Sub

Sub compare()
    MsgBox StrComp("Stack", "stack", 1) 'Compare as text while ignoring case
End Sub

The first one is case sensitive where the 2nd is not, of course you can replace the hardcoded values with variables

share|improve this answer
 
Thanks for providing this. It is a good method for comparing string variable in a sub. Alternatively, I was able to achieve this without having to code a new sub by using If Variable1 = Variable2 Then...I wasn't correctly assigning Variable2 in my test pass. –  zacc206 Jun 6 '13 at 17:13
add comment
up vote 0 down vote accepted

Here is the solution:

I wasn't correctly assigning Variable2 and therefore, the function wasn't producing the desired result. Thanks everyone for their contributions, through the reply's - here's what we've come up with:

To compare two string variables in an existing sub, use:

If Variable1 = Variable 2 Then...

To compare two string variable in it's own sub, use: 

Sub compare()
    MsgBox StrComp("Stack", "stack") 'alerts -1 
End Sub

Sub compare()
    MsgBox StrComp("Stack", "stack", 1) 'Compare as text while ignoring case
End Sub
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.