UKC

Seemingly simple bit of vba code driving me mad.

I need to test for the presence of two bits of text ("MG" and "ML") in a string of dosage text (typically "200MG/150ML", although it can vary)

thought the INSTR() function would work

If Instr("200MG/150ML", "ML") then
'do stuff
end if

Works

If Instr("200MG/150ML", "MG") then
'do stuff
end if

Also works

So why doesn't this work?

If ((InStr("200MG/150ML ", "MG")) And (InStr("200MG/150ML ", "ML"))) Then
'do stuff
end if

Instr returns the position of the match, so I assumed a positive value (4 for MG test and 10 for the ML test) would translate as true + true and the AND statement should work.....but it doesn't. Why does it work for a single test, but not for the AND comparison?

last minute tweak to make it work is:

If InStr("100MG/100ML", "MG") > 0 And InStr("100MG/100ML", "ML") > 0 Then
'do stuff
end if

In reply to ThunderCat:

I don't really do VBA but going by knowing a fair few other languages my guess would be that because the AND is operating with two integers, it's doing a bitwise AND rather than a logical one.

When you add the "> 0" bit, the AND is then working with two booleans, hence does a logical AND.

I hope that makes sense

In reply to ThunderCat:
years since I worked with VBA but some points to check.

1) Strings are sometimes addressed with the first char being at position 0 in the array not 1. In other cases position 0 holds the length of the string and myStr[1] is the first char. I can't remember how VB(A) treats this.

2)You have to consider the open ended way in which arithmetic with booleans is allowed. In many languages it is hard typed. In others it is type based around a restricted enumerated type (0 + true 1 as apples and oranges....). Typically where direct interchangeable arithmetic byvalue is permitted 0 represents false but true might be 1, or -1(here true/false is sometimes then evalutaed as the modulus of the stored value; there are reasons for this based in the expense of processor operations). I can't remember how this is done in VBA.

But looking at the documentation it looks like there are additional options

https://msdn.microsoft.com/en-us/library/8460tsh1%28v=vs.90%29.aspx

see the example.

Anyways your final snippet would be better to be calling two functions where each returned a value that you make use of elsewhere to make your life simple. If you break it up like this and step through how each works with watches you will figure it out I am sure.

In reply to ThunderCat:

My hunch is that the following will work:

If ((InStr("200MG/150ML", "ML")) And (InStr("200ML/150MG", "MG"))) Then
'do stuff
end if

In your previous example, it looks as though you've effectively been testing for equality between 4 and 10.

In reply to Clauso:




Nope, testing that both operands are true

went a bit further and tried:

if (4) then
'do stuff
end if (works fine)

if (10) then
'do stuff
end if (works fine)

but

if (4) and (10) then
'do stuff
end if

Doesn't work.

In reply to Clauso:

what Darren says . My brain short circuited and read your AND as a '+' which works in some languages.

In reply to Clauso:


Oh hold on, that does work. You git.

In reply to ThunderCat:


Have you tested my example?

It's effectively saying:

If (10) And (10)
'do stuff
End if

In reply to ThunderCat:


Oh, good... Do I get a Nobel Prize, or summat?

... I'll settle for a pint.

In reply to ThunderCat:


Haven't you answered your question within your OP?

Instr returns the position of the first occurrence of the 'searched for' character(s) in the string - and returns a 0 if they are not found. So, Instr returns an integer not a boolean - which is why adding the '>0' works.

In reply to Clauso:



It's yours. Couldn't quite work out why yours worked and mine didn't as they were near identical. Didn't spot that you'd tweaked the MG / ML

So it does indeed seem to be checking that the positions they're returning are both equal, rather than checking for boolean TRUE's in both parts (which is what I wrongly assumed)

Let me know if you're around Salford Quay or Chorlton. I'll get those beers in.

Cheers.

In reply to Dave Todd:


Got that, just couldn't work out why the original comparison wasn't happening.

I thought that a 0 did get evaluated to false, and a positive number would evaluate to true

if (instr("Hello", "e") then
'do stuff
end if

returns 2, which evaluates to true. I (wrongly) assumed that there would be some boolean comparison going on if I compared two INSTR functions. Hey ho. every day is a schoolday.

In reply to Dave Todd:



Exactly! Which is what I said in the first reply to the OP!

In reply to Phil Anderson:

Congratulations. Have a banana.

I think I just needed to fiddle with the example a bit. I've always had that assumption that both would evaluate to true and be a match. Live and learn.

In reply to Clauso:


Not quite... He's been testing whether 4 AND 10 is non-zero (note that the AND is a bitwise AND).

4 is 0100 in binary and 10 is 1010

0100 AND 1010 is 0 hence the expression returns FALSE.

In reply to Phil Anderson:



Thanks Phil - I had a much too simplistic view of what was going on with an AND comparison.

I thought 0 would simply evaluate to false (ie if the INSTR function couldn't find the text) and that any other positive value would evaulate to TRUE, so as long as both pieces of text were found (wherever they were in the string), they would both return true, and the comparison would work.

Thanks to everyone for the help!

In reply to ThunderCat:
The String.Contains method works too but just gives you a Boolean result.

String.IndexOf would do the same as InStr

Glad you have it working.

In reply to thedatastream:


so am I...

In reply to ThunderCat:

Or you could also write a function to return true or false like below.

Public Function Contains (A as string, B as String) as Boolean

If InStr(A,B) > 0 Then
Contains = True
Else
Contains = False
End If

End Function

If Contains ("100MG","MG") And Contains("200ML", "ML") Then
Do stuff.