Is this true? Was it ever true??

[abesimpson]

Many years ago I recall reading that one should never use a macro where a formula would do. I am curious as to users opinions of this rather broad admonition.

Thanks

a

 

[hatman]

There a lot of variables implicit in your statement. But the bottom line is the KISS rule: Keep It Simple Stupid. The more complex your solution, the more opportunity for potential issues. And of course, they get multiplied if you will be distributing your file. I like to weigh the pros and cons of each approach and decide on a case by case basis, myself. If the workbook will be saying on my PC to be used by myself only, I usually bang out macros left and right if they are the fastest way of getting something done. On the other hand, if I will be distributing the file, I try to avoid macros at all costs: as sonn as I employ one, the end user will have potential security issues, and that can be an annoyance for them. Not to mention the additional time I need to spend to make the macro bullet-proof: if something goes wrong with your macro, people get upset at being dumped into the debugger, on th eothe rhand, if a bug creeps in with the standard formulas, people tend to take that in stride a littl emore easily.

nyway, that;s my two-cents, and by no means is it written in stone.

 

[njimack]

For me, it's horses for courses.

I have files where the macro will insert a complex formula into a range of cells, then copy-paste special values. This would be part of a much larger procedure. This has the advantage of a much smaller and less calculation-intensive file. It also means that if the user inadvertantly makes a change to the contents of a cell (despite my best efforts to protect the cell contents), they're change won't be permanent.

But as hatman says, in most cases, writing macros takes much longer than the equivalent formula, and can often lead to unwanted results and irritated users.

Also, a formula will accomodate inserted/deleted rows or columns much better than a macro. I've lost count of the number of times a perfectly good macro has gone pearshaped because I hadn't envisaged the user doing something perfectly sensible!

 

[Scott820]

I like the idea of keeping it simple.

generally my data, and things that i do with it are so varied ive no need to learn macro's yet. Everything i do is a single piece of work, one time, and one time only. I may leave in all my working and formulas to come back to.

If ever im issueing information / analysis to someone, i never leave the base data or formulas etc.

a) makes me indispensible :D
b) annoys other people wondering how the hell i got said info :D

plus then they cant balls it up either.

I see excel as a tool for manipulating data only. Information in, 'excel it' information out, job done.

Anything more is a quick solution to problem which requires a much better system (ala access or other database / solution / front end).

Anything (in my eyes) that requires visual basic implies it shouldnt have been in a spreadsheet in the first place. admitedly it may be required for complex big one off jobs converting / normalising / summarising oddly formatted data (generally nicked from another inadequate system, be it human or computer).

I guess im of the school of thought of lots of additional colums, lots of mini processes, working a piece of data through many stages to get to my end goal. I guess my posts will reflect that, but im not ashamed!

 

[abesimpson]

Thanks for taking the time.

abe

 

[PaddyD]

I'd add:

"Many years ago I recall reading that one should never use a macro where a formula would do. I am curious as to users opinions of this rather broad admonition."

" I like to weigh the pros and cons of each approach and decide on a case by case basis, myself."

'Should' implies an obligation - it is the right thing to do. You could argue, therefore, that responses elaborating on the practical issues have somewhat missed the point.

The question could be re-phrased 'Is it appropriate to have rules about how to code that proscribe using a vba routine when a native formula exists?'.

The answer to that is clearly 'no' - one can always construct conter-examples to such a rule (as previous replies indicate).

A more pragmatic formulation might be "Is it appropriate, in general, to avoid using vba routines when a native formula exists?"

The answer to this is clearly 'yes' - simply because you are never going to write a vba version of

=sum(a1,b1)

...that's going to implement addition more efficiently than the function, if for no other reason than the extra compilation involved.

So, treated as a law, ignore it; as a guideline, follow it.