1

I coded an if statement as seen below:

enter image description here

You can see here that I hovered over all three values in this statement and all of them should be the equivalent of a True value and thus the contents inside the if statement should run. However, for some reason when I execute this statement, it skips straight to the End If part of the code.

Does anyone know why just by looking at this?

jlcv
  • 1,688
  • 5
  • 21
  • 50
  • 1
    This is odd. Non-zero should equate to a True value. Could you post the rest of the If block? @hnk has a good point about the potential Null situation – Jimmy Smith Jul 10 '14 at 19:58

2 Answers2

5

In VB, the And and Or operators are bitwise, not logical. They perform bitwise operation on the numeric operands, and only in the end the result of the bit operation may be considered in the logical context.

In your example, you have True AND 13 AND 18, which is -1 AND 13 AND 18 (in VB True equals -1), which results in 0, because numbers 13 and 18 do not have any common bits. The zero is in the end implicitly understood as False in the logical context.

Normally you get away with it when you write things like If Len(str) Then instead of If Len(str) > 0 Then - because there are no And or Or to mess up the result.
If you do have them in the condition, you should always use the explicit comparison. Then the bitwise operators will have boolean operands to operate on, in which case the result of their work will be the same as if they were logical operators.
True logical operators, AndAlso and OrElse, were only introduced in VB.NET. If you could replace your Ands with AndAlsos, the condition would evaluate to True (with Option Strict Off, because with Option Strict On the compiler would make you to use explicit comparison).

Note that Null has nothing to do with it. It is acceptable in VB to have Null in the logical context: If Null Then ... Else... is a valid construct. Null in this case evaluates to False.

GSerg
  • 76,472
  • 17
  • 159
  • 346
0

You will need to rewrite them as

If InStr(...) > 0 Then
 '....
 ' I will run smoothly
 '
End If

instead of simply

If InStr(...) Then
' .... 
'  I will cause problems when run
'
End If

VBA will not be able to correctly interpret the second sample I've written. The comparison construction should be explicitly stated.

A Note on NULL InStr returns Null if either of the String inputs are NULL. Hence, if it better to put this enclosed in a function wrapper that returns a boolean value and performs all validation internally to avoid any nasty run-time surprises.

Ref: http://office.microsoft.com/en-us/access-help/instr-function-HA001228857.aspx

That doesn't mean it will necessarily cause problems, but it's one of those things to consider.

In this particular question, the problem isn't null and can be corrected easily with just rewriting the line with comparison explicitly specified, so the information is for the community in general.

A Note on Type conversions Btw, unlike other languages, in VBA, while False translates to 0, True doesn't translate to 0, but to 255 (2s complement for -1, all 1s). Just to note.

Edit: corrected the bit mixup

hnk
  • 2,216
  • 1
  • 13
  • 18
  • It will interpret the 2nd statement as true if the string is found(1+), false if not (0), but bomb if you feed it a Null which I did not know about Instr! I think I've never encountered that , because if you try to assign Null to a string it will error in advance of the comparison. – Jimmy Smith Jul 10 '14 at 20:07
  • Well, that'll depend on the use case. Say, if the string is a global variable and code execution is interrupted for whatever reason, it could get initialized to NULL, Also, if this is to be deployed as an addin for other users, it's better to do error-checking since they may try to feed in an uninitialized element from an Array of strings, etc. (No telling what users can do! But they will defy all expectations for ingenuity when creating bugs :) ) – hnk Jul 10 '14 at 20:09
  • Wow thanks hnk, it fixed the issue. Not sure why it did that, reading through my code it seemed pretty clean ie. array elements were initialized. – jlcv Jul 10 '14 at 20:35
  • I think it's the explicit comparisons. Best to use those unless very clearly specified that not using it would be fine. – hnk Jul 10 '14 at 20:36
  • Does this issue arise in other languages? Should I always use explicit comparisons from now on to be on the safe side? Or is it just an issue with VBA? – jlcv Jul 10 '14 at 20:38
  • Don't know, but in C++ you're safe as the returned value is 1 or 0. In VBA I'm never sure. – hnk Jul 10 '14 at 20:39
  • Btw, unlike other languages, in VBA, while True translates to 1, False doesn't translate to 0, but to 255. Just to note. – hnk Jul 10 '14 at 20:40
  • 1
    I meant the bit-level arrangement. It's `11111111` so unsigned it is 255 and signed it'll be -1 – hnk Jul 10 '14 at 20:45
  • 1
    lol @hnk why are you down voting all my posts, is it because I unaccepted your answer? – jlcv Jul 11 '14 at 18:31
  • @hnk Anyways I am disappointed in what you did, I decided to change the answer because it had more upvotes, also I did not want other people who came across this question to be misinformed about the `NULL` aspect of your post. Nonetheless the code you provided worked and I thank you for that. – jlcv Jul 11 '14 at 18:52
  • @JustinLiang hey dude? you should choose whichever answer works for you man, that's up to you only. btw, regarding my answer, i actually looked for the NULL thing on microsoft's help site, and it says the same (try opening VB editor, typeing InStr and clicking F1, you'll see the full function description). Either case here's the reference: http://office.microsoft.com/en-us/access-help/instr-function-HA001228857.aspx . I'll probably take it off myself if it's incorrect, as I do with other similar posts . Anyways, all the best :) – hnk Jul 11 '14 at 19:08
  • @hnk Right, but 8 of my posts got down voted RIGHT after choosing the answer that I felt would work for me... Would you kindly reupvote them? – jlcv Jul 11 '14 at 19:15
  • 1
    @JustinLiang http://meta.stackoverflow.com/q/252270/11683, http://meta.stackexchange.com/q/126829/147640. – GSerg Jul 11 '14 at 21:26