• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Excel Functions and Commands that should come with Warnings

K

kchiba

Active Member
Hi,
Are there any functions and commands in Excel that you feel should be used with extreme caution.

I will start the list of with:
Merge
Cut and Paste

As for Cut and Paste, I usually disable this if I develop a spreadsheet for others to use.

Do you have any "Favourites"?
 
I think you are better to teach people skills like when to recognise that they are about to do something potentially harmful, like deleting pages or ranges which can result in the #REF! errors and to backup before they do these things

Mind you I learned the hard way, as in the 80's even printing would often cause Word Perfect to simply crash, so you just learned to always save before doing things like that.
 
Yes, I remember, the warning "Save Before Printing" for Lotus123,

I was hoping that this thread would develop into a "Tricks and traps"
 
User-defined vba functions are hell.

I implemented the formula on this website http://investexcel.net/implied-volatility-vba

Then let this run for a table of options. Changes to the workbook were really slow after using this.

I hope to sometime over the Christmas break show how I translated this formula into a Power Query Formula on my blog - dbexcelaccounting.blogspot.com

Refactoring this user-defined formula to a formula that calculates in power query saved me tons of time because of how it calculated in a volatile manner.

Other functions that are hell to slow your workbooks are array formulas that are entered with ctrl+shift+enter, but these can be really powerful and for any pre-excel 2007 user still around, these are the only alternative for implementing some more advanced functions that now can be done much easier with structured references.

Occasionally, I need to use array formulas with structured references which provides some truly awesome calculation capabilities
 
I love macros, but I learned the hard way that you can't undo them. Beware having a macro edit large chunks of data without copying the sheet(s)!

Also, as cool as it is to record macros, Excel seems to record VBA code in the most convoluted way possible.
 
Recorded macros are the devil.

I only use the record function when I want to see what some action might be called, and even then the recorded may record something different from what you think you are recording.

When I used to record, I would always go back and delete out the parts that looked recorded and replaced with logical steps and relative or hard-coded cell range values.

For example, selecting and activating cells is rarely efficient and operating on selected cells will leave you open to user actions screwing up your code by selecting a cell mid-operation.
 
@DBExcel
Hi!
If I don't copy a template yet written, or I don't import an existing module, unless it's something really easy I usually use the built-in recorder when having to deal with borders, fonts, backgrounds, shapes..., page setup..., how would I ever remember all those parameters? :eek:
Then I cut, shrink, crop, and maybe I only get a couple of lines... from tenths of them :(
Regards!
 
You must log in or register to reply here.
Back
Top