VBA Value of text

[Giordano Bruno]

I have the following code line which I am using to distinguish between similar files.

If Workbooks(s).Sheets("Accounts").Range("A1").Value > 1 Then

A marker for the two file types is contained in a cell on the "Accounts" tab. This will be either the text "Password" or the value 2.

When the code find the correct workbook, it checks the contents of A1 and will find either "Password" or 2 depending upon which file it has found.

If it finds "Password", it should skip to the 'Else' code lines, but instead it runs the next line of code after 'Then'. Does this mean that the text string "Password" is greater than the value 2

Given that the values in the cells are either the word "Password" or the value 2, how should I distinguish between them if I cannot use > 2

 

[Sektor]

Sub G()
    With Workbooks(s).Sheets("Accounts").Range("A1")
        If .Value = "Password" Then
            ' Process
        ElseIf .Value = "2" Then
            ' Process
        End If
    End With
End Sub

 

[Giordano Bruno]

Thanks Sektor,

That certainly looks more robust, but do you know what is wrong with my code?

 

[Sektor]

Check this:

Sub Check()
    MsgBox """Password"" > 1: " & ("Password" > "1") & vbNewLine & _
           "2 > 1: " & (2 > 1)
End Sub

P.S. When comparing "Password" and 1, I used 1 in quotes, 'cause this same thing VBA does. If you omit quotes, you'll get error.

 

[Giordano Bruno]

Thanks for that Sektor.

Unfortunately I've peppered my code with that test. Now it looks like I have to start again.

 

[Sektor]

I apologize, what does "peppered" mean? Abused? Just can't apply correct translation to this word.

 

[Giordano Bruno]

Peppered means "sprinkled", like pepper, i.e. spread everywhere

 

[Sektor]

Now I get it.

 

[Ruddles]

Unfortunately I've peppered my code with that test. Now it looks like I have to start again.

In that case, as you're faced with the task of revisiting every occurrence of that code segment anyway, take the opportunity of replacing them with a call to a function, then write a function to return TRUE or FALSE based on that same test.

That way you'll only have one place to edit the code if you need to tweak it in the future.

 

[Giordano Bruno]

Excellent advice Ruddles. I've done just that.

And thanks again for your help Sektor. Problem solved.