More Efficient IF

[David]

Hello

I've got a convaluted IF statement:

--

If Variable = 1 or Variable = 3 or Variable = 7 or Variable = 12 Then

--

Using other languages you could for example do something like this:

--

If Variable MATCHES (1,3,7,12) Then

--

What would be the best way using VBA? Do you increase the efficiency
of the code by adding brackets etc, as in PICK Basic?

Any other general efficiency tips would be appreciated as I've been
asked to update some complex code that was written a long time ago by
one of my ex-colleagues and is deemed inefficient, but I'm not expert
when it comes to VBA, as you probably noticed!

Thanks!

 

[Rick Rothstein \(MVP - VB\)]

I don't consider your If-Then statement to be convoluted at all... it is how
I would write it my own code. If you are looking for something more
"concise", well, you could use either of these...

If InStr("*1*3*7*12*", "*" & Variable & "*") Then

If Variable Like "[137]" Or Variable = 12 Then

but personally, I think you will find them harder to understand 6 months
from now if you have to come back to maintain or modify your code.

Rick

 

[papou]

Hello David
Use Select Case instead, eg:

Select Case Variable
Case 1,3,7,12
'do some job here
Case Else
'do some other job
End Select

See help in VBA for further information.

HTH
Cordially
Pascal

 

[Joel]

You can use the worksheet functtion MATCH(). the prblem witth using this
method if the item is not found an error will occur. So you must handle the
errror

Variable = 7
On Error Resume Next
Results = WorksheetFunction.Match(Variable, Array(1, 3, 7, 12), 0)
On Error GoTo 0
If IsEmpty(Results) Then
MsgBox ("Did not find match")
End If

 

[Andrew Taylor]

You can avoid using "on error.." by using the IsError
function:

validValues = Array(1, 3, 5, 7, 11)

if IsError(Application.Match(x, validValues, 0)) then
' x is not in list
Else
' x is in list
End If

I don't expect it makes much difference either way
in terms of efficiency, but it's more maintainable and
less error-prone.

 

[Joel]

Andrew: I don't like On Error statements. I try to avoid them whenever
possible. But in this case in excel 2003 your solution doesn't work. I
tried your solution before my posting and it failed so I was forced to use
the On Error method.

 

[Tom Ogilvy]

If you are looking for efficiencies, looking at this one is pole vaulting
over mouse turds. I suspect you need to look at the logic and algorithms of
the code to harvest major improvements - unless it is using a lot of recorder
style code such as select and selection, or it does a lot of screen updating
or is slowed down by the internal calculation of formulas in the worksheet or
it has inherent recursive calls in events.

Back to the If statement, It may be cumbersome to write, but it would't be
inefficient when executed. I suspect PICK basic evaluates your Match
statement slower than or equal to the way VBA evaluates this construct.

Using the worksheet function MATCH as pointed out by Joel would be a much
slower way to do it.

 

[Tom Ogilvy]

No, it works fine in xl2003. You didn't replace WorksheetFunction with
Application as Andrew suggested.