0

My code is as follows: 

If (InStr(1, "ALL", Itype) Or InStr(1, Uvar.Device, Itype)  _
And (InStr(1, "ALL", Isec) Or InStr(1, Uvar.Sec, Isec)) _
And (InStr(1, "ALL", Idev) Or InStr(1, Uvar.Model, Idev)) Then Useline = "TRUE"

To expand on this a bit:

Itype is a long string, e.g. -

  • Itype = All, Apple, Pear, Orange
  • Isec = dog, cat, duck
  • Idev = tree, flower, plant

Each UVar.x is a single word, e.g. -

  • UVar.Device = Apple
  • UVar.Sec = Cat
  • UVar.Model = tree

So, if Itype contains the string "All" or it contains the value of UVar.Device and Isec also contains the string "All" or it contains the value of UVar.Sec and IDev also contains the string "All" or it contains the value of UVar.Model

then I want to have the if statement = true.

My code above seems to return true whatever values are used, as long as at least one value criteria set matches.

Thus, do the strings IType, Idev and Isec each contain either the value "all" or a specific user defined value?

Scott Conover
  • 1,421
  • 1
  • 14
  • 27
DevilWAH
  • 2,553
  • 13
  • 41
  • 57

2 Answers2

2

Well, for one thing, your code doesn't compile - you either have to remove the opening parenthesis after the IF or add a matching closing parenthesis.

More importantly, though, you've got the order of the Instr() parameters mixed up. First is string to look IN, second is string to look FOR. That should solve it.

PS: While Brettdj's answer is true, VBA will interpret the position 0 as false and any other as true, so that's not necessarily the issue.

brettdj
  • 54,857
  • 16
  • 114
  • 177
Tom Juergens
  • 4,492
  • 3
  • 35
  • 32
  • opps your right, I have the extra parenthesis in my actuly code must have droped it during the copy process. I think I might have gt confuised with the =Find worksheet function, this is string to look for followed be string to look in. Cheers for the ideas I will check it out – DevilWAH Nov 29 '11 at 11:58
  • Yep that was the one! Casue it was running I was assuming it was my "if's", "And's" and "Or's" in the wrong place but swapping it all round works perfect. Kind regards and many thanks – DevilWAH Nov 29 '11 at 12:06
  • 2
    It is true that VBA will interpret any nonzero value as true. However, imo, such a practice is more of a hack than good coding practice. Comparing the position to the value of 0 is easier for us humans to understand. I suppose that is a discussion of syntax versus semantics. – Bobort Nov 29 '11 at 14:49
  • @Bobort: I agree completely - I'd also rather see the explicit check, but it does work without it. – Tom Juergens Nov 29 '11 at 15:30
  • While I had addressed the parentheses I had missed that the order was wrong given it was fiddly to test. I also don't like the coarseness of the workaround, while as you say it works, it isn't obvious and workarounds like this may not survive future software versions. – brettdj Nov 29 '11 at 22:28
  • 1
    Using VBA's "logical" operators on numbers is asking for trouble. See http://stackoverflow.com/a/8047021/58845 – jtolle Nov 30 '11 at 04:34
1

(Updated: To fix order and to return a True Boolean variable)

InStr returns a position, not True or False. So you could add both tests together to test for >0 as then at least one of the two paired conditions is true. Then test all 3 pairs, ie

Dim bVar As Boolean
bVar = (InStr(Itype, "ALL") + InStr(Itype, Uvar.Device)) > 0 _
       And (InStr(Isec, "ALL") + InStr(Isec, Uvar.Sec)) > 0 _
       And (InStr(Idev, "ALL") + InStr(Idev, Uvar.Model)) > 0
brettdj
  • 54,857
  • 16
  • 114
  • 177