Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
More Efficient IF
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
More Efficient IF
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 "David" wrote in message ups.com... 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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
More Efficient IF
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 "David" a écrit dans le message de news: ... 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! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
More Efficient IF
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 "David" wrote: 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! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
More Efficient IF
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. On Sep 28, 12:04 pm, Joel wrote: 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 "David" wrote: 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!- Hide quoted text - - Show quoted text - |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
More Efficient IF
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. "Andrew Taylor" wrote: 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. On Sep 28, 12:04 pm, Joel wrote: 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 "David" wrote: 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!- Hide quoted text - - Show quoted text - |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
More Efficient IF
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. -- regards, Tom Ogilvy "David" wrote: 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! |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
More Efficient IF
No, it works fine in xl2003. You didn't replace WorksheetFunction with
Application as Andrew suggested. -- Regards, Tom Ogilvy "Joel" wrote: 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. "Andrew Taylor" wrote: 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. On Sep 28, 12:04 pm, Joel wrote: 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 "David" wrote: 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!- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
What is more efficient | Excel Discussion (Misc queries) | |||
more efficient VBA? | Excel Programming | |||
Is there a more efficient way to do this? | Excel Programming | |||
More efficient way? | Excel Programming | |||
Which is more efficient? | Excel Programming |