Efficiency -- Not a how to , just an info question

I would guess that each solution has its own degree of efficiency, depending on the specific problem.

Each is different and each applicable to the particular problem, with the particular data and particular scenarios.

I suppose a 4-page macro that takes 0.04 seconds to run and solves the problem is just as efficient as a conditional format, as they both replace 20 minutes manual work.

But to a different user who only has 2 meg free, a 4-page macro that swells his workbook by 1.8megs may not see it as efficient.

But also to the guy who gets nervous about conditional formatting as he doesn't like colours in his spreadsheet, it's the other way round.

Sometimes you can solve only by VBA, sometimes only by functions and other times only by conditional formatting. Sometimes it's any 2 from 3 and other times all 3 will solve your problem.

You'd have to have a specific problem and know your solution options in order to judge to your own scenario which is more efficient in execution terms....

HTH
Chris

Hi Steve,

The answer to this question depends on many variables. But the simple answer is that there is no "most efficient" solution for all applications. For example, in general built-in Excel functions are many times faster--typically a factor of 10--than VBA macros. This is mainly because VBA is interpreted while Excel functions are compiled--most likely in the C programming language. However, there are cases where a VBA solution will run circles around the Excel solution, usually because the VBA solution can be tailored to the problem whereas the Excel solution uses general-purpose tools. In addition, many mathematical functions(e.g., trig functions and Excel worksheet functions called from VBA) are just as efficient in VBA because they, too are compiled and just called from VBA. Usually though, the reason for using VBA is simply that the problem is difficult or impossible to solve using Excel functions.

Much macro "programming" is done by recording macros, then editing them slightly to make them work for the specific problem at hand. This works, usually quite well, but often generates obscenely inefficient code and also lacks the generality to work under varying starting conditions. This is because a recorded macro must implement the solution exactly as it was performed manually, whereas most of the operations performed manually are unnecessary when performed programmatically. Learning to program in VBA rather than record is a must if efficiency is any kind of an issue at all.

One of the problems with Excel functions is that when the formulas get long and the number of them gets large, the size of the workbook can get pretty large. In general, the equivalent VBA solutions are MUCH smaller and more compact when they put the values directly in the cells, eliminating the need for cell formulas. Of course they also have the advantage of being able to do things that cannot be done with worksheet functions.

The situation is similar with conditional formatting. Conditional formatting of a few cells is much more efficient than doing the same thing with VBA. But if you have hundreds or thousands of cells that are conditional formatted, the VBA way is at least more efficient from the memory standpoint. And again, the VBA macro provides the capability to do formatting-related things that can't be done with conditional formatting. For example, suppose you want every cell that contains the string "Blork" in its comment to continuously blink green, then yellow, then red. Try doing that with Conditional Formatting (I know that you've dreamed of doing this).

Well, I got a bit carried away with this answer. Let's hear from some of the VBA programmers out there.

Damon

It is dependent on what's being done. If you can do it with an internal function it is faster than VBA code. VBA code requires many steps, Get compiled code, access libraries, allocate workspaces, get sheet information, formatting, close code, do house keeping, etc. So code is slower, but conditional formatting can also be slow depending on the complexity of the criteria. Some sheet functions can also be slow as most can take many variations on the properties. So if you write good code optimized for the data you will be working with you may see a speed improvement?

I build test files testing different ways of doing the same thing and pick the one that is the fastest. As, you can't always know which will be the faster. I also test for file size. Code increases file size rapidly with each additional sub. Yet most sheet functions hardly increase file size at all!

The trick with code is to know which way is the quickest way to a solution. "IF" statments are slow, loops are slow, the way you define a range can slow you down, as can the way a object is used. With new fast computers even slow code runs fast and with cheep storage and memory, many of the code economics of the past are a side note now. Many times we are just glad to get the thing to work! So, sloppy code is the norm.

Every application project should be updated from time to time. We learn new things all the time and many older projects can benefit from a new look. I use version numbers in the file name of applications to indicate such changes and additions.

The number one tip: Keep a test path full of working ideas and lable the sheet of each with instructions, pick a discriptive name for the file. This way if you need to do something do not reinvent the wheel each time use your test library. Many times it is easer to play with one aspect of something and less intimidating than a full application project. You need a form that gets user data in the project you are building?
Go to your library pull the code,taylor it to your needs and be done with it. JSW

Thanks folks, that was what I was looking for.