Check if variable contains a string

[Ted M H]

I'm trying to write VBA code that will look at a String variable and tell me
whether or not that String contains another String — without erroring out.
Variable X contains this string: =SUM(AAAA,BBBB)
Variable Y contains this string: PPPP
I want something that works sort of like this:
If Variable X contains Variable Y, Then Variable W = 1
If Variable X does not contain Variable Y, Then Variable W = 2
In the example above, W will equal 2, since X does not contain Y
If Y is BBBB then W will equal 1, because X does contain Y
Many thanks.

 

[Don Guillett]

Have a look in the vba help index for INSTR and incorporate into your IF

 

[Rick Rothstein]

If I understand your question correctly...

W = 2 + (InStr(VariableX, VariableY) > 0)

The InStr function, as I used it above, is case sensitive. If you want a
case insensitive test...

W = 2 + (InStr(1, VariableX, VariableY, vbTextCompare) > 0)

 

[Ted M H]

This works perfectly (I used the case sensitive test). I don't yet
understand why, but my immediate problem is solved.

I spent a little time with VBA help on the InStr function, and I think I
understand how it works. What puzzles me is the 2 +.... and the > 0....
additions to the statement. You've already solved my problem, but if you're
inclined to help educate a struggling programming hacker further I'd sure be
interested in understanding why the solution works.

Many thanks.

 

[Rick Rothstein]

W = 2 + (InStr(VariableX, VariableY) > 0)

if you're inclined to help educate a struggling programming
hacker further I'd sure be interested in understanding why
the solution works.

I'd be more than happy to help you. First off, the InStr function looks at a
text string (your VariableY) to see if it is a substring of another text
string (your VariableX)... if it is, the InStr function returns the position
that VariableY is located at within VariableX (this will be a positive
number greater than 0); and if it isn't, the InStr function returns 0. Okay,
now let's look at the part of my statement in parentheses...

(InStr(VariableX, VariableY) > 0)

This is known as a logical expression... one item (the result from the InStr
function) is being tested to see if it is greater than 0. If the result from
the InStr function is greater than 0, then the logical expression evaluates
to True, otherwise it evaluates to False. In other words, if VariableY is
contained as a substring within VariableX, the logical expression will
evaluate to True. This logical expression stuff should be familiar to you as
you see it all the time in If..Then statements; for example...

If InStr(VariableX, VariableY) > 0 Then
W = 1
Else
W = 2
End If

By the way, this If..Then construction is the long way of doing what my
one-line statement does. Okay, back to the explanation. There are two
possible results from evaluating the logical expression... True or False...
in VB (note this will be different than when logical expressions are
evaluated on a worksheet), when used in a mathematical expression, True
becomes -1 (minus one) and False becomes 0 (zero). When these values are
added to 2, the results will be...

Logical Expression False... W = 2 + False = 2 + 0 = 2

Logical Expression True... W = 2 + True = 2 + (-1) = 1

I hope the above makes what I did clearer for you.

 

[Ted M H]

Hi Rick,

Very clear explanation. Very, very helpful. Thanks again for helping me.

Ted