VBA speed select case/if else
Solved
ed555
Posted messages
61
Registration date
Status
Member
Last intervention
-
ed555 Posted messages 61 Registration date Status Member Last intervention -
ed555 Posted messages 61 Registration date Status Member Last intervention -
Hello forum,
I have a small question regarding the speed of conditional tests in VBA. In a fairly large program, I call a function many times, which takes a certain value based on the value of another parameter.
I would like to speed up the execution of the code as much as possible. Is one of the two methods (select-case or if-else) faster than the other? In doing some research online, I couldn't find an answer to my question (both sides seem to have their arguments...).
Thanks in advance!
ed
Configuration: Windows 7 / Chrome 19.0.1084.52
I have a small question regarding the speed of conditional tests in VBA. In a fairly large program, I call a function many times, which takes a certain value based on the value of another parameter.
I would like to speed up the execution of the code as much as possible. Is one of the two methods (select-case or if-else) faster than the other? In doing some research online, I couldn't find an answer to my question (both sides seem to have their arguments...).
Thanks in advance!
ed
Configuration: Windows 7 / Chrome 19.0.1084.52
2 answers
Good evening,
I would tend to say that it’s much of a muchness, although only a test could answer that.
On average, you will do half the tests before finding the right one.
The Select Case has the advantage of being easier to read.
What you could do, if possible, is to find large groups.
For example, if you have to test numbers from 1 to 100 (50 tests on average)
You do 4 tests <=25, 26 to 50, etc. And inside, you test the concerned values individually. You would go down to an average of 2+13=15 tests, which is more than a 60% gain.
Or you could determine the probability of True for each test and do them in that order. If 60% of your database is "ok", 30% "in progress" and the next 10% have 50 various anomaly messages, it makes sense to test "ok" first, then "in progress"; in 90% of cases, you would only do 2 tests.
This can be done with either if or select.
There are also classic techniques to speed things up: declare and type your variables (not varying if possible, in long as much as we can for numerics), work in memory rather than on sheets, and use dictionaries if needed.
And there might be another way to approach the problem. But without elements…
Eric
Never respond to an unsolicited private message...
Well, that’s done.
I would tend to say that it’s much of a muchness, although only a test could answer that.
On average, you will do half the tests before finding the right one.
The Select Case has the advantage of being easier to read.
What you could do, if possible, is to find large groups.
For example, if you have to test numbers from 1 to 100 (50 tests on average)
You do 4 tests <=25, 26 to 50, etc. And inside, you test the concerned values individually. You would go down to an average of 2+13=15 tests, which is more than a 60% gain.
Or you could determine the probability of True for each test and do them in that order. If 60% of your database is "ok", 30% "in progress" and the next 10% have 50 various anomaly messages, it makes sense to test "ok" first, then "in progress"; in 90% of cases, you would only do 2 tests.
This can be done with either if or select.
There are also classic techniques to speed things up: declare and type your variables (not varying if possible, in long as much as we can for numerics), work in memory rather than on sheets, and use dictionaries if needed.
And there might be another way to approach the problem. But without elements…
Eric
Never respond to an unsolicited private message...
Well, that’s done.
eriiic
Posted messages
24581
Registration date
Status
Contributor
Last intervention
7 281
You're welcome!!!
ed555
Posted messages
61
Registration date
Status
Member
Last intervention
Oops, my apologies! I thought I had responded... Thank you!
ed555
Posted messages
61
Registration date
Status
Member
Last intervention
Indeed, there must have been a bug because I clearly remember thanking you and appreciating the cleverness of your technique (which, unfortunately, does not apply in my case). Have a good day!
Hello,
The simplest thing is to test it yourself.
At the beginning of the function, you put
And just before the function exits
You test on your function with IF/else and with select case.
And that way... you’ll only have one clear answer. :D
See you later
--
If you bump into a pot and it sounds hollow, it's not necessarily the pot that's empty. ;-)(Confucius)
NOTE: I do not respond to private messages for technical questions.
The simplest thing is to test it yourself.
At the beginning of the function, you put
Dim T T=Timer
And just before the function exits
MsgBox Timer - T
You test on your function with IF/else and with select case.
And that way... you’ll only have one clear answer. :D
See you later
--
If you bump into a pot and it sounds hollow, it's not necessarily the pot that's empty. ;-)(Confucius)
NOTE: I do not respond to private messages for technical questions.