Read the Manual

Posted on by Dick Kusleika

My friend Harald is putting together a utility that identifies common problems. The idea is that the utility would identify these easy-to-fix, hard-to-spot errors and eliminate the need for a support call. On the list so far are

  • Calculation set to manual
  • R1C1 style option selected
  • Extraneous files in the XLSTART folder
  • Circular references
  • Array formulas entered as non-array
  • SUM function on hidden cells instead of SUBTOTAL

What are some other common problems that you experience or are asked to fix?

Posted in Uncategorized

37 thoughts on “Read the Manual”

  2. Why is this file so big? Blank cells beyond the end of the data that have been created and not deleted.

    Vlookup into a range that is mixed text and numeric returns #NA / lookup into a range of text that is padded at the end with blank characters returns #NA…

  3. Page break view vs. Normal view (usually in page break preview and they don’t know how to switch it)

    Formulas that are showing up as text (instead of calculating. We use and AS400 system and people pull info regularly. This is typical when they insert a column.)

    Mixed Zip codes – 5 digit vs 9 digit in the same column and not formating properly

  6. I agree with Jim regarding the spaces at the end or beginning of text
    I also see a lot of users “delete” the data in a cell by hitting space bar and enter
    So adding to the utility the ability to celar the contents of all cells containing only a space would be good

    When adding time – need to use the [h]:mm format to ensure that time that adds to more than 24 hours shows the total time in hours not in days and hours.
    This isn’t a default date format in Excel though it can be found in the custom format list – but should be available as a time format

    Edit directly in Cell – turning this off (default has it on) allows double click to go to cell precendents, and also makes it easier to edit large formulas. I turn it off on all my clients pcs.

    Turn of the reviewing toolbar.
    It persists in returning no matter how often I turn it off – anybody know how to stop that?
    I don’t know anybody who uses this feature as it involves sharing workbooks and my forays inthat regard with office 2000 discovered lots of problems with attempting to make this work successfully. It also appeared to cause an inordinate number of “Excel has just crashed – hope you saved recently – tough luck if you didnt!” errors – The tool bar takes up valuable screen space

    A show all errors utility is good too.
    I have built a routine for some users that searches their spreadsheets and builds a page showing the cell reference of all errors enabling them to go to the error. Probably should post it up on my website… one day when i have time!

    Locating Named ranges referring to #REF! or to external sheets.
    A lot of users dont know how to break the inadvertent links created by copying formulas with name ranges in them to other work books and thus creating links that cannot be located using Find or the Edit Links (break links) menu option

  7. “Make sure ‘Always show full menus’ is selected.”
    Also, show Standard and Formatting toolbars on two rows, so all the buttons are visible.

    In the VBE: Require variable declaration

    Another common error: numbers stored as text.

  10. I wanted to also mention displayed Page Breaks.
    When these are showing, I find they slow everything down.
    I have a helper macro for turning them off:

    ActiveSheet.DisplayPageBreaks = False

  12. What are some other common problems that you experience?

    Numbers stored as text
    Formulas with only numbers ( like “= 234+567?)
    Mixed formulas and values
    Circular references
    Vlookups (Hlookups) expecting an ordered list
    Vlookups (Hlookups) expecting absolute addressing
    Pivot tables without updating
    Auto calculation turns off

    …and some users ;)

  16. A simpler one…

    User switches on auto-filter but row immediately below is totally blank so nothing appears in drop-down lists.

  17. 1. “I hit the tab key and the cursor moves several columns”…Tools > Options > Transition tab and uncheck transition navigation keys.

    2. “I can’t see my file”… try Windows > Unhide

    3. “The leading zero disappears when I enter a number such as US zip code”…Preformat the cell as text or precede the number with a single quote (‘)

    4. “I tells me I already have the file open”…see Debra’s site:

    http://www.contextures.com/xlfaqApp.html#AlreadyOpen

  19. – confused by multiple files after using New window
    – show placeholders ticked on Tools > Options > View > Objects

  22. How exactly does one tell whether formulas should be entered as array formulas without building a very sophisticated formula parser? Just using multiple cell ranges as arithmetic arguments isn’t necessarily a sure sign that formulas need to be array formulas. There are many things that don’t require array entry in SUMPRODUCT and LOOKUP, and some things that do even using those functions. Or would this cover only the functions that always return multiple entry arrays, e.g., FREQUENCY, LINEST and LOGEST?

    And to add to the list, the other Options, Transition tab entries:
    -transition formula entry
    -transition formula evaluation

  23. Just for emphasis I would have to second the Analysis Toolpack that Debra mentioned. To add on to Dennis’s post Error Trapping is probably better set to Break in Class Module…

  25. a) Dont paste-special on a filtered list.
    b) Dont allow deletion(or any other operation) on >8192 discontinuous cells selected

    Regards
    Sam

  27. Thanks everybody. Lots of “d’oh, why didn’t I think of that”. I do now.

    This is unfortunately work, as in Work, so I’m not allowed to share the result on the internet. But this is an easy thing to build, feel free to use the concept.

    Best wishes Harald

  28. John,

    Make sure ‘Always show full menus’ is selected.

    My kingdom for this option’s location (disclosure: very small kingdom)! I use the keyboard almost exclusively and commands not in the custom short menus won’t respond to mnemonic key strokes. Drives me nuts!!

    Thanks,
    Brett

  30. Number Precision = 15: first encountered after users had entered hundreds of 16-digit credit card numbers and found that the last digit had been changed to 0 on all of them. Not fun.

  31. Dianne Butterworth: “Number Precision = 15: first encountered after users had entered hundreds of 16-digit credit card numbers and found that the last digit had been changed to 0 on all of them.”

    Wrong data type: a credit card number is fixed-width text!

    But I take you point and would add: all numerics = double precision floating point (the only fixed point type is CURRENCY, with scale limited to four).

  32. Response 29 should read View…Toolbars…Customize…Options tab of Customize dialogue box. Sorry about that.

    Brett

  33. When user entered a date: 5/29/07 it was automatically changed into a formula. The resulting number, 0.024630542,
    when date formatted, ended up with a date format of Jan00. The problem was that the Transition Formula Entry option was set in the Tools, Options, Transition tab. Unchecking this option fixed the problem. The setting was designed to transition Lotus users into Excel, however in this case the file was never originally Lotus. Not sure how the option was set.

  34. Hi all, first comment here for me. Wonderful blog Dick and friends!

    Harald, here’s an advanced one and perhaps not that useful, but still. What about checking whether variables have a good variable type. For instance, I just saw on a forum:
    Dim dtoh1, dtoh2, dtoh3, dtoh4, dtoh5, dtoh6, dtoh7, dtoh8, dtoh9, dtoh10 As Date
    Dim dt1, dt2, dt3, dt4, dt5, dt6, dt7, dt8, dt9, dt10 As Date

    Don’t know if this is desirable / feasible.

    Cheers

    Wim Gielis

  35. >> Dim dtoh1, dtoh2, dtoh3, dtoh4, dtoh5, dtoh6, dtoh7, dtoh8, dtoh9, dtoh10 As Date

    I was under the impression that doesn’t work as one would expect. Each comma separates a full definition, so if you want them all to be type Date, you’d need to code it as:

    Dim dtoh1 As Date, dtoh2 As Date, dtoh3 As Date, dtoh4 As Date, dtoh5 As Date,
    Dim dtoh6 As Date, dtoh7 As Date, dtoh8 As Date, dtoh9 As Date, dtoh10 As Date

    The original coding would define dtoh1 thru dtoh9 as type Variant

  36. I am trying to write a simple macro to turn off auto-calculation on hitting the macro button. I recorded hitting Tools/Options/ Calculation and hitting the Manual calc radio button, but the resultant causes a runtime error [1004].
    Any ideas? Macro code:
    With Application
    .Calculation = xlManual ‘(THIS IS THE LINE THAT FAILED)
    .MaxChange = 0.001
    End With
    ActiveWorkbook.PrecisionAsDisplayed = False
    Thanks

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.