With regard to: -
>>I think I read somewhere that whenever possible I should use Macros over VBA because they are faster to run.<<
We could look at it this way…
In order to test for speed we need to time both.
We would need to test a Macro against the exact VBA equivalent functionality.
In order to test both we would need to write both.
We can test the speed of a VBA function using an API call.
How do we test the speed of a Macro; we write VBA to call the API call?
Or, do we convert the Macro to VBA in order to time it?
Often on the web we see statements like “this is faster than that” but has it really been tested correctly? Generally not. Very often we are looking at a last resort statement by someone. It’s a last resort because whoever feels like they are loosing an argument (discussion) will often resort to “but mine is more efficient” or “mine is faster”.
Macros are seldom mentioned, normally it’s SQL verses VBA, but the same thing applies.
In order to test the difference both need to be written and tested.
Today the machine we are using is rather complex and that makes it difficult to time any process. People generally do not know how to run timing test code, be it Macros, VBA or SQL. Timing tests will vary from run to run. During long timing tests other things may happen, incoming email, virus scan, network traffic, screen update time, blah, blah, blah.
How many people will, as much as is possible, isolate the test from the testing environment?
On this matter only three things are certain: -
1. Timing code is a complex matter and it takes time to run those tests.
2. People who make the claim that “this is faster than that” have generally not timed it.
3. Don’t believe everything (anything) we read, just test it.
The thing we get from running the tests is that we learn why some particular instance is faster.
Chris.
.