Function Foo(thiscell As Range) As Boolean Foo = thiscell.hasFormula And (InStr(1, UCase(Split(thiscell.formula, Chr(40))(0)), "bar") > 0) End Function
This function exists to test for the presence of a certain substring (bar, in this case) before the (.
The case I'm having trouble with is when the cell passed into the function is empty, the thisCell.hasFormula is false, but the statement after the and is still being evaluated. This gives me a subscript out of range error in runtime.
Does VBA actually continue evaluating the second argument to the And, even when the first was false?
Logical operators compare Boolean expressions and return a Boolean result. The And , Or , AndAlso , OrElse , and Xor operators are binary because they take two operands, while the Not operator is unary because it takes a single operand.
VBA doesn't short-circuit VBA does not support short-circuiting - apparently because it only has bitwise And/Or/Not etc operations.
Well-known Member. Excel does use short-circuit evaluation of IF()s.
What you are looking for is called "short-circuit evaluation".
VBA doesn't have it.
You can see an approach that is probably adaptable to your situation here.
The approach that was chosen there involved substituting a Select Case
for the If
. There is also an example of using nested Ifs
.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With