0

When I use the following code it works fine:

If xMin <> 0 Then
    If xMax <> 0 Then
        If xMax / xMin > 2 Then
            'some code
        End If
    End If
End If

But I want to use only one if statement to avoid division errors, however it gives me a Run-time error '11': Division by zero:

If xMin <> 0 Or xMax <> 0 Then
    If xMax / xMin > 2 Then
        'some code
    End If
End If

(If I can manage to make the above code work), I plan to do it like:

If (xMin <> 0 Or xMax <> 0) And (xMax / xMin > 2) Then
    'some code
End If

What is the reason behind this and how can I make it work in one if statement?

Happy Forever
  • 120
  • 1
  • 8

1 Answers1

4

You're out of luck.

VBA does not implement a short-circuited And, unlike C, C++, Java, &c. It evaluates every term in the expression even if the result is already known. In your case that means that xMax / xMin is evaluated even if xMin is 0.

So you need to write it as 

If xMin <> 0 And xMax <> 0 Then
    If xMax / xMin > 2 Then
        'some code
    End If
End If

Note that I've fixed a typo in your conditional: you need an And in place of your Or.

Bathsheba
  • 231,907
  • 34
  • 361
  • 483
  • Well, using `If xMin <> 0 Or xMax <> 0 Then` gives me an error as I stated in my question. Only the first code in my question is working properly. – Happy Forever Jun 26 '17 at 15:04
  • Yes I've just noticed that and have amended the answer. Note you need `And`, not `Or`. – Bathsheba Jun 26 '17 at 15:06
  • But if I use `And`, it executes the inner if statement: i.e.: `xMax = 350`, `xMin = 0` which pops a division error. – Happy Forever Jun 26 '17 at 15:08
  • It works without any error as you said, and it is beyond my understanding. Either VBA is a joke, or not capable of simple logical operations. I will accept your answer, thanks. – Happy Forever Jun 26 '17 at 15:22
  • Couldn't you just check the value of `xMin`? If `xMax` is 0 the calculation will return 0. It'll only return an error if `xMin` is 0. – Darren Bartrup-Cook Jun 26 '17 at 15:43
  • @DarrenBartrup-Cook: Indeed to obviate a division by zero, that's all you need to do. But I preserve the conditions where `'some code` will run. – Bathsheba Jun 26 '17 at 15:44
  • Yes, that bit still needs checking but I was thinking along the lines of: check if xMin <> 0, if it is then check the result of xMax/xMin > 2. – Darren Bartrup-Cook Jun 26 '17 at 15:58