I seem to have started something. Mailing the Microsoft Excel Developers
List EXCEL-L this morning about John Meyer’s “Off Topic” humour posting
which recommended Don McMillan’s hilarious video How NOT
to use Powerpoint, I said it would be good to see something similar for Excel. Nobody made a video, but huge numbers of readers posted about their pet hates: merged cells; “deleting” cell contents by entering a space; formulae like =SUM(A3+A4); leaving #DIV/0! in cells; ghastly colours…

It seemed a shame to waste the momentum, so I set up this blog wherein people could discuss the topic. This first posting contains all the EXCEL-L mails on
the topic [up to June 18th, when I revised it]: I’ve removed names and other identification, because EXCEL-L isn’t a
public mailing list. For what they show about Excel and its defects as a programming language, these mails ought to interest visual-language designers and
spreadsheet researchers, and people designing Excel courses, and so on. In the remaining postings, I’ve blogged some links that Patrick
O’Beirne sent me, summarising what they show about how not to use Excel.

I don’t have much to add at the moment, but it would be good to see others writing
in the blog. I’ve set it up so that anybody can comment. Also, if anybody wants to
post an entry, they can send it to me as rich text or HTML, and I can submit it.
(The blog is MoveableType.) I should also be able to give prospective bloggers the
blog URL so they can create their own entries, though I’d have to trust people
here, because one could then destroy the entire blog.

Here are the EXCEL-L postings:

• This has got me thinking – because I train Excel and it would be easy
  and
  great to create a sequence that contains all of the bad habits we see.
  It would be great to compile a list to include things like:
  Formulae like =SUM(A3+A4).
  Or even worse +=SUM(A3+A4) for ex lotus users.
  Clicking and dragging a formula down the side of a list – instead of
  double-clicking the fill handle.
  Building graphs without F11.
  Not ALT+ENTER’ing sections in a large formula.
  Linking to workbooks without using a range name.

• Merged cells.

• “Deleting” cell contents by entering a space.

• Shared workbooks.
  Ghastly colours.
  Too many rows.
  Too many array/SUMPRODUCT formulas.
  Not using pivots.
  Not using named ranges.
  Pie charts.
  3D charts.
  Childish CF icons in 2007+.

• I’d disagree about F11 for graphs – the wizard does (for pre-2007) stop
  and make you think about the graph you are creating (mind you, it
  doesn’t stop the little beggars from putting in negative numbers in %
  bar charts! Aaaaagh! ). My pet hate is the blank line between headers
  and the data, with merged cells coming a very close second.

• (at least in part):
  >Merged cells.
  Horizontal can go use “center across…” but vertical needs to stay
  untill
  there is a vertical version of “CA…”

• You brought back memories of an old(er) lady I once taught “basic” IT
  to, who, even after working thru all the standard “build up” exercises
  C&G, BTec, RSA, Clait and a few of my own (to fill the gaps left by
  the others) still
  preceded EVERY equation with =SUM on the grounds that “we are doing
  Sums”. It would have been easier if Excel could have objected to any of
  her weird formulas like:
  =SUM(B3%)
  =SUM(B5/B4%)
  =SUM(B3/B4*B5)
  =SUM(B4^B3).
  All gave the result you would expect (without the SUM() that is), so the
  question “Why?” came up a lot.

• There is always something on everyone’s list that someone will disagree
  with. I disagreed with =SUM(A3+A4), I have no problem with it. Now,
  =SUM(A3+A4+A5+A6+A7+A8) might be a little silly. And
  =SUM(A24:B24+C24:D24)
  just plain won’t work – but Excel will tell you that.

  My peeves off top of my head:
  Leaving #DIV/0! In cells.
  Distributing worksheets whose cells contain data validation
  or other formulae dependent on items in closed (local) workbooks.
  Leaving Calculation set to Manual in a workbook you give someone else.
  Failing to turn off GetPivotTableData [hate it hate it hate it].
  Failing to set these:
  # worksheets to 1… and leaving empty sheets in a workbook.
  Recently Used Files to 9 (or whatever is max).
  Always use Full Menus.
  Put the Web address bar in the tool bars.
  Buying and using Excel 2007 without a gun to your head or a client
  likewise.
  Forgetting to reset the Delimiter when using Text to Columns.
  Not partnering your Excelling experience with a good book and Google.
  Using IsEmpty to test a Cell instead of testing the cell’s formula
  length.
  Leaving Circular Reference in the status bar unattended.
  Mixing too many formats of various kinds.
  Naming files with a single quote in the file name (legal but silly).
  Using Range(“A1”) without s worksheet qualification.
  Likewise for .Cells.
  Creating workbook level names for ranges on a sheet (that might get
  replicated on another sheet) … makes coding very difficult, always
  include
  the sheet name.
  Leaving macro recorder code uninspected / without being made generic.
  Deleting rows in a forward counting loop (VBA).
  Messing with OWC 10.0 or OWC 11.0 spreadsheet
  tool AT ALL. (undocumented and lost a month of my life and
  crashed Excel numerous times).

• Disagree. I put the value in all the cells then use CF to determine the
  middle one and hide all others. Merged cells are evil.

• =SUM(A3+A4) is dumb, you are over-working Excel.

• Cell merging is not a “don’t do” I claim it is a bug.

  Password protecting internal documents that will be used over
  years and not letting someone else know the password.

  Sharing large WBs that also have links.

  VBA solutions for where simple formulas would work

  Using complex array formulas where simple VBA could be used.

• Trying to use Excel as a database when a database would be the better
  solution.

• I see you’re in the UK, so this may be difficult to comprehend:
  If CA is Canada (on the Internet), it’s horizontal.
  If CA is California (in the USA), it’s vertical.
  So you see that there is a vertical version of CA…Just check out a
  map.
  😉

• hard-coding variables in formulas

• > Formulae like =SUM(A3+A4)
  >
  Well, =SUM(A3,A5) has some application compared to simple =A3+A5 when
  the
  data can contain mixed text and numbers.

  > Or even worse +=SUM(A3+A4) for ex Lotus users.
  >
  =+SUM(A3+A4)
  I still come across workbooks with Lotus compatibility setting on
  worksheets.
  AFAIK it only affects lookups, but it may have other effects – can
  anyone
  remember?

  And financial modellers still use +name in a formula when they want it
  to be
  interpreted by implied intersection.

  > Clicking and dragging a formula down the side of a list – instead
  of
  > double-clicking the fill handle.
  >
  Needed when the list has gaps or is just in the wrong place for Excel to
  know
  where to fill to.

  > Building graphs without F11.
  >
  What’s so bad about that?

  > Not ALT+ENTER’ing sections in a large formula.
  >
  Nice .. easier to read in 2007+ than earlier.

  > Linking to workbooks without using a range name.
  >
  Deffo.

  Ahem …
  
  

  Although I think the OP was looking more for scenarios when Excel is
  being
  used inappropriately for analysis and communication rather than
  technical
  flaws.
  e.g.
  http://caseelse.net/2008/08/06/why-not-to-use-excel-for-data-gathering/.

  There’s some discussion on the Eusprig yahoogroup about its use in
  statistics
  and there are a few skeptics in this field.
  Patrick Burns’ Spreadsheet Addiction page and
  THE
  UNRELIABLITY OF EXCEL’S STATISTICAL PROCEDURES

• Dumb or not, I use it from time to time when I want all formulas to
  appear
  (in terms of formula text) identically in a column, for easy eye-scan /
  auditing when I turn Formulas on, and other formulas begin with SUM for a
  not-so-simple reason, so everything appears uniform and I can see
  symmetry where I want to see it. That way I know for sure that all my
  formulas are similar. My methods have been called worse than dumb
  before!

• Enough already !!!!
  Wow – this is certainly a sure-fired way to press some react buttons !
  I didn’t know we were so passionate about certain things in Excel – and
  the
  hatred towards merged cells – I love them!
  I’ve compiled a list and I’m sure it will keep growing – so when I get
  the
  time……

  I don’t understand the CA Canada thing – yes I’m in the UK so I must be
  missing something obvious.

  Thanks for the contributions guys…

• Someone used the abbreviation CA for
  Centre Across and then said there was no vertical equivalent. CA is the
  domain name country element for Canada but is also the abbreviation (zip
  code etc) for the state of California. California is definitely
  vertically stacked where as Canada is Horizontal when you take into
  account their ratios of depth (latitude) to width (longitude).

• And they both have a place called Ontario!

  For my own peeves:
  Recording a macro without understanding what it does, and then claiming
  to be an expert in Excel macros (the same goes for PowerPoint and Word,
  I guess).
  Trying to use more columns than be comfortably scrolled or viewed on a
  single screen.
  As per any electronic publication, unattractive fonts such as Comic Sans
  Serif, or a mishmash of fonts that I think of as “ransom note
  typography”.

• As a trainer/developer/Jack of all trades, 3 of the things that I always
  emphasise (so in essence is reverse-bad habits) are:
  1) Don’t be reliant on mouse, i.e. LEARN the short-cut keys (I’m not
  talking about the usual suspects c,x v, but ones like CTRL+ or CTRL*).
  2) understand the basic relationships between Word And Excel (lines,
  paragraphs, tabs, cells).

  As to databases, I agree that Excel shouldn’t be used as a DB, but
  should be always considered as the front-end to a DB.
  (Most companies nowadays prohibit their users from using Access – if
  they
  even have it installed at all!)

• > As to databases …
  >
  There’s my top tip just there. Don’t be tempted to use Excel as a
  database,
  but don’t think that means you need to use A****s.

• But you can still use .db even without Access.

• Great topic, love the answers. Don’t think I saw: MEGA-FORMULAS.

• Formatting whole row/s and/or column/s when only a few cells would
  suffice
  and ending with huge files as a result and then complaining about the
  size.

• Writing a 200 line macro when a built-in Excel function is available to
  do
  the same thing.

  Blank columns between data columns.

• I’d disagree about F11 for graphs – the wizard does (for pre-2007) stop
  and make you think about the graph you are creating
  (mind you it doesn’t stop the little beggars from putting in negative
  numbers in % bar charts! Aaaaaghg!)
  My pet hate is the blank line between headers and the data, with merged
  cells coming a very close second.

• Reminds me of the shared workbook that was growing to insane size,
  because one of the muppets was highlighting whole rows for formatting ,
  then his co-muppet was selecting said rows and ALL THE WAY DOWN TO 65536
  then deleting the rows, thus storing 60,000+ rows in the tracked
  changes. Repeat a few times and see how big that sucker got!!!
  Users should not be trusted to write their names! With a 2 inch brush!
  On a barn door!

• Yeap, that’s the trick. My boss loves it. And he includes formulas. If he
  needs a formula for eleven rows and a little formatting he copies it to the
  end of the sheet. Then he calls me when the file is 18 Mb and hangs up his
  runs and tells me “will you take a look at this? It doesn’t work any more.”
  Then he shakes his head and goes “maaaan . . . this Excel thingy . . .”

• Tell him it’s either a wetware problem or a PBKAC error
  🙂

• I’ve been told a number of times that Excel and Access are the same
  program. Yes, they were all in management.

• That’s what I call job security. Something going on here with the “Excel
  macro thingy”. Nobody knows or wants to know and that makes us all happy.

• And this is the other half of the reason good programmers never document
  their work. If it was hard to write, it should be hard to read! 😀

• >Disagree. I put the value in all the cells then use CF to determine the
  >middle one and hide all others. Merged cells are evil.

  Nice work-around, but not what I meant, I rotate text 90′ and then center
  across a number of rows, your solution would just leave my text centered in
  one
  (large height row).

• >I see you’re in the UK, so this may be difficult to comprehend:

  > If CA is Canada (on the Internet), it’s horizontal.
  > If CA is California (in the USA), it’s vertical.

  >So you see that there is a vertical version of CA…Just check out a map.
  >;-)

  I meant CenterAcross (vertically).

• I have . . . I have . . . together with a few other things that can not be
  repeated in mixed company.

• Don’t forget ID10T and ESTO.

• >Great topic, love the answers. Don’t think I saw: MEGA-FORMULAS.

  Oh go on we all love the odd mega-formula even if only to keep our brains
  working.

• >Reminds me of the shared workbook that was growing to insane size,
  >because one of the muppets was highlighting whole rows for formatting ,
  >then his co-muppet was selecting said rows and ALL THE WAY DOWN TO 65536
  >then deleting the rows, thus storing 60,000+ rows in the tracked
  >changes. Repeat a few times and see how big that sucker got!!!
  >Users should not be trusted to write their names! With a 2 inch brush!
  >On a barn door!

  Nice idea but the EndUser does need some input, now if only they were
  consulted
  at the planning stage in place of
  their bosses who may not (don’t) know what
  is
  required…

• >And they both have a place called Ontario!
  >
  >For my own peeves:
  >…
  >As per any electronic publication, unattractive fonts such as Comic Sans
  >Serif, or a mishmash of fonts that I think of as “ransom note
  >typography”.

  Ahh no not the Font Police, Some users react better to a serif font (and
  some
  don’t).

• >>
  >> As to databases, I agree that Excel shouldn’t be used as
  >> a DB, but
  >> should be always considered as the front-end to a DB
  >> (Most companies nowadays prohibit their user from using
  >> Access – if they
  >> even have it installed at all!)
  >>
  >

  >There’s my top tip just there. Don’t be tempted to use Excel as a
  database,
  >but don’t think that means you need to use A****s.

  I found it a good starting point (once you understand NormalForm(s))
  certainly
  gave me a foot up on SQL.

  And that from someone who sneers on the use of DreamWaver, FrontPage and
  the
  like in favour of a good PlainText editor…

• I had a client that did that, formatted all rows to allow for growth. It
  was
  a multiple sheet spreadsheet and also got huge. When they migrated to 2007,
  they found some of the workbooks just didn’t open. Nice little job fixing
  that.

• Well as Nick Hodge maintains, Access is an Excel addin.

• How about “Accept labels in formulas” aka “natural language formulas”,
  so if a column heading is, say, 2010, and xlColumnLabels option is set,
  when you enter =SUM(2010) it silently changes it to =SUM(‘2010’) and shows
  the sum of that column.

• We can’t document it, we don’t understand it. Don’t you realise it is all
  magic?

• Refer to specific cell references in the VBA like
  .Range(“C10”)
  “Why not? The recorder does…”

  Use the same “text string” in lots of places inside the VBA
  Put lots of code in the _SelectionChange event.

  Use lots and lots of borders, especially different kinds and colors.

• >> Use lots and lots of borders, especially different kinds and colors.

  OK, I did not want to write in again because I have already done my bit
  here, but had to add to someone’s point above.

  … So – What not to do: what someone wrote about *plus* keep every legacy
  sheet you’ve copied from itself so that whatever format changes you’re
  making on the current sheet is sure to yield different formats than the
  legacy sheets, just *asking* for Excel’s 4000-ish style limitation to come
  round and bite ya.

  And then there is making Comment shapes grow and shrink with the workbook,
  I
  love that one.

  And let’s not forget leave little shapes with 0-height so that no one can
  see them — especially hanging around areas with no data, that one might
  otherwise like to delete.

• Thanks to all for the advice.

  So many things to include in my next project (before I change employers)
  and Zero height shapes -neat0
  🙂

• I really hate to see this thread keep wrapping around the world a second
  time but I have to ask.

  How is that you refer to a range when you need to do so? And, what is
  wrong with referring to a range
  like .Range(“A10”)? Is there a better way and not just a better way from a
  personal preference?

  And, depending on how the recorder options are set then you may or may not
  have the recorder record references like .Range(“A10”), right? 🙂

• Data tables that have been “prettified” in any way make me just plain
  grumpy, unless they are one-offs. Do the summarizing and beautifying on
  another sheet.

• I prefer Cells(1, 1) then Range(“A1”), but that is because I am usually
  working with variable row / column numbers 99% of the time, e.g.
  Cells(RowNum1, 1).

  I really only use Range for where I know it is always going to be the
  same chunk every single time.

• I’m with you on this one.

  I want my raw data to be exactly that: raw. No number formatting, no
  nothing. Ok, I don’t mind if there are a few unobtrusive cells with
  different colors to help me or someone else stumbling through the data
  identify specific cells or rows, but I -don’t- want to have my numbers
  truncated, etc!

• Give Microsoft 3 more versions and they will be the same. Why do you think
  we have a million rows, now? Too many people using Excel as a database and
  then complaining to MS that they do not have enough room for all their
  records.

• Raw data isn’t what Excel is about. Raw data belongs in a data store, maybe
  a database, maybe a text file, maybe another Excel workbook, but without a
  clear presentation of the data, it is impossible for data consumers to get
  the information they need.

• Just remember that that ‘capability’ was nixed as of Excel 2007.
  So at Least MS got some things right with ’07!

• Don’t pad values (especially, but not only, text) in cells with spaces.

• I preach and preach and preach about this I know, but I have to keep
  insisting that no one should post examples of code calling Range without a
  worksheet qualifier. Assuming the Activesheet is dangerous. I hope you were
  just using shorthand.

• It’s not a problem if the code follows something like
  Sheets(“Whatever”).Activate
  then a reference to Sheets(“Whatever”).Range(“A1”) is actually slower than
  simply Range(“A1”)
  Those billionths of a second add up, you know!

• Don’t cram them into a single cell either.

• As we all do, I suspect, I too work with variable row / column numbers 99% of
  the time.

  I prefer to use Range(“A1”).Offset(RowNum1, ColNum1), which, to me is really
  the same thing as using Cells(x,y). It is just a different method, at least
  to me.

  Seems to me it is 6 of one and half a dozen the other and again just a matter
  of preference and how you first learned and understood VBA. 🙂

• I’ve refrained from adding as my list was far toooo long, and I thought most
  would be covered anyway. Here are a few I think haven’t been suggested.

  Copying worksheets with range names – thereby creating hidden links.
  Using recorded code – UNMODIFIED. (macro1, macro2, macro … to infinity).
  Having oooodddles of workbooks with COPIES of same code.
  Not using Add-Ins for your code.
  And my fav – workbooks with strange properties set.

  As for the XL interface, I’ve seen sheets with data on alternate rows and
  columns.
  And a 30,000 rows of data with several columns with only 3 entries each.
  One worksheet had pink handwriting size 8 font on a red and yellow background
  with a 75% zoom – I got an instant headache!

  Lastly, ‘please forgive me’ – USING XL12.

• Is there any general benefit in Activating a worksheet before using VBA on it?

• I really thought there would be 10 replies as to the .Range(“D10”) comment.

  So maybe this is an advanced “Do Not”, but let me explain because it is a BIG
  DO NOT in any application wtih any shelf life.

  Excel is wonderful at keeping track of references so that when you add rows
  and columns, it adjusts the formula references automtically. It even
  redefines the definition of the named ranges, including when you rename the
  sheet.

  Do not be under the illusion that VBA code is as smart! If you give it
  wsInput2.range(“D10”) to get a value, It will get the value from cell “D10”
  even after you have inserted 3 rows at row 3 for neat page header.

  The proper way is the give each critical cell location a named range such as
  “IN2_PartNum”
  In the code you use

  Dim rngPartNum as range
  Set rngPartNum = Thisworkbook.Names(“In2_PartNum”).RefersToRange ‘set the
  range variable to the named range.

  vPartNum = rngPartNum ‘Assign the PartNum to a variant from the range.

  This way, no matter where you move the cell with the named range
  “In2_PartNum”, the VBA code will reference the correct cell.

  I will use .Offset(i,j) only under conditions when I know the offset is good.
  In particular, I use a range Name “Tbl6_Header” for the labeled header
  block of a data table (either from an advanced filter or .CopyFromRecordset.
  The “Tbl6_Header” give me my anchor, .offset(1,0) gives me the first row of
  data in that record.

  Back in the early ’80s, I read a programming book recounting a wise programmer
  telling a junior, “Always use named variables to hold constant values. The
  only constants that should ever appear in your code are 0 and 1… and I’m not
  those two are ok, either.”

• Especially if the code in question is in a worksheet code module!!

• I agree with critical cell ranges being named, but I’m moving more and more
  away from multiple cells in a range
  to using a single cell (named) as a datum point and using a combination of
  Offsets and Resizes, together with .currentregion to build the required
  objects before using them. (Which also negates having to mess around with
  screen updating).

• That presumes you are doing something with an existing sheet though. It is
  clearly not always unreasonable to use a fixed address in your code. :).

• Never use Sheets(“Sheet1”).Dosomething
  Use Sheet1.DoSomething

  Use the code name of the sheet rather than the name.

• OK, since we are getting technical about safest way to refer to ranges. May
  I suggest never using workbook level names unless they are calculations or
  other use only pointers. And, prequalifying every named range with the sheet
  name as a variable to make sure it is at sheet level:

  When initializing the range for the first time

  sRefersTo = Thisworkbook.worksheets(Activesheet.Name).Range(“A1”)

  Set N = wb.Names.Add _
  (Name:=”‘” & Activesheet.Name & “‘!IN2_PartNum”, RefersTo:=sRefersTo)

  Later:
  Set rngPartNum = Thisworkbook.Names(Name:= _
  “‘” & Activesheet.Name & “‘!IN2_PartNum”).RefersToRange

  Otherwise woe betide the one who copies portions of the sheet (only) and
  pastes into a new sheet — expecting the formulas referring to said ranges
  to be locally consistent.

• Well, close (to err is humid)…

  WRONG >> sRefersTo = Thisworkbook.worksheets(Activesheet.Name).Range(“A1”)

  Anyway, just make sure sRefersTo is a valid address on the sheet.

• If possible…

• I hate the word never (I never use it myself) and I don’t like using code
  name of worksheets and will never change my mind. All it took was doing a
  massive search and replace on similar (code)named sheets and affecting more
  than I bargained for, to teach me a lesson I have never forgotten. And it
  was so subtle I didn’t know it happened until much later.

  With Sheet1
  Do Whatever
  Loop
  End With

  Search and Replace Sheet1 with Sheet2 and Heaven Help ya if you also have
  Sheet10 as a code name with some code in it:

  With Sheet10 <<< bye bye Sheet10, hello Sheet20
  Do Whatever else
  Loop
  End With

  Search and replace on a name inside a pair of double-quotes and you can’t go
  wrong. Ah, the beauty of it

• Oh, and since CodeName is read only, how are you going to handle deletion
  and reading of sheets on which operations are to be performed, if your VBA
  code refers to CodeName?

  [I just know some expert is gonna set me straight here! I love it when I get
  to learn, even at the price of eating crow.]

• Serves you right for using the default sheet names!

• I tend to use the codename when possible, but assign it to a variable so you’d
  only need to alter it in one place if it alters. And I try to avoid using the
  default Sheet1, Sheet2 etc. ;).

• You can change the name of the VBComponent programmatically.

• Hey, that was an example only

• Using Sheet1, Sheet2, etc may be problematic and the source of your
  frustrating experience. However, if you give your sheets meaningful codenames
  at the outset and then use those rather than the standard sheet names, your
  users can rename sheets, reposition them, etc and it will not affect your
  code. Also, by setting a local object in your code at the beginning of a
  procedure to the actual codenamed sheet then you can use that local variable
  in your with/end with constructs, limiting the problem to only where you set
  the local variable.

  Sub mySub()
  Dim myLocalSheet as Worksheet

  Set myLocalSheet = myMeaningfullyCodenamedSheet

  With myLocalSheet
  … do stuff
  End With
  End Sub

  This trivial example may seem to have extra unnecessary overhead, but in more
  complex scenarios I have found it useful. Also, if a calling sub calls
  multiple subs that would use the same sheet, then pass it as an argument
  instead, SETting it only once in the calling procedure.

• The codename CAN be changed at runtime… even though help says it cannot.

• OK, I get it already, and have explained that I was using an example.

  Obviously a very careful developer would probably not run into this
  situation. And yes, I do agree that using a worksheet’s name instead of the
  code name leads to really big problems also, but I like having a reason to
  slap a user’s wrist anyway (“I named the sheet that way for a reason!!!”).

  As for code name, nevertheless:
  Some day one might have a code name which *is* well-thought-out but happens
  to also be a substring of another well-thought-out codename … Finding it
  using the Find dialog box is pain without the benefit of quotes; the longer
  name keeps coming up as well.

  And find-and-replace, oh so dangerous.

  I will not change my coding practice, I’m just a stubbin’ mule :~!

• Aaaah, great point. I forgot about that Caveat. In my case it wasn’t a deal
  breaker, but it usually would be. I think that is one of the main reasons why
  I ended up using the sheetname instead in most instances. That and it is a
  fair bit of poking around to make it work. I also found it could make Excel
  unstable occasionally.

• Don’t put multiple disparate blocks of data into a single worksheet, rather
  place them in separate worksheets.

• I don’t disagree, but my users frequently want to have (limited) raw
  data sets available immediately with their charts, reports, etc. I
  don’t have access to a better data store like Oracle, so I’m limited in
  my options.

• Really? I thought Excel was an Access addin to crunch numbers and draw
  pretty charts…

• You mean it’s NOT?!

• So give them a pivot linked to the raw data.

• I’ve done that in many cases too, but sometimes the final presentation
  is in such a format that that’s not the best option.

• That’s one I was going to pick up on too. We are forever preaching non
  selection wherever possible by setting objects to an object variable and
  manipulating the variable or using the With Sheets(“Whatever”)
  construct.

• I asked someone this yesterday (offline) and he mentioned these two:

  Freezepanes and setting CF formulas with relative references are the
  two I can think of offhand.

  I seem to think you have to have a sheet Active in order to Paste to it (or
  maybe it was PasteSpecial.

  Not really sure.

• Yes, I was just using shorthand. I ALWAYS fully qualify a range even to the
  workbook level.

  But, since I don’t use Cells that much then I have to ask, wouldn’t you still
  fall into the same pit if you don’t fully qualify Cells and will Cells not
  also assume the activeworksheet if not qualified?

• re:”Finding it using the Find dialog box is pain without the benefit of
  quotes;”:

  Doesn’t ‘Find whole word only’ deal properly with this?

• No, because the codename might be just a qualifier with a property next to
  it.

  Again, FOR ILLUSTRATION ONLY – NO BARBS PLS!!!

  With Sheet1
  Blah Blah
  End With

  With Sheet10
  Blah Blah
  End With

  Sheet1.Name = ” Sample1″
  Sheet10.Name = “Sample2”

  Now try to find all instances of Sheet1 without also finding Sheet10, using
  Whole Word Only.

• <<>>

  OK, I had just tested that before I posted it … and I swear VBA did not
  find sheet1 in
  Sheet1.Name = ” Sample1″

  Now I tested it again and it did.

  OK, Find Whole Word Only pauses for periods, I thought it would not – and
  tested it and it (seemingly) did not … but has decided to, after all!

• Use the Find/Replace of MZTools?

• Hahahaha!! I have seen some pukey UI results when people have changed their
  colour index around, put in lots of candy colours to show stuff and then
  emailed the workbook to someone using the default (or different) colour grid.
  UGLY-A-RAMA.

  Whilst on “Stupid things people do with Excel”…

  I am always amazed by folks who have their font size set to 12 points (or
  bigger), but then put the zoom on 75% (or less) so they can see all the data
  on the screen better. They often also print stuff zoomed down (or fit to
  page) otherwise “it won’t fit on the page”… I see this *all the time* at
  work.

  Why don’t they just don’t make their default font (say) 9pt and leave the zoom
  at 100%? As an added bonus it will also fit on the darn page!! I mean, that is
  not rocket science folks.

  The other one that made me laugh. Got a call from a woman wanting to know who
  to “insert a table into Excel”. I told her the whole thing *is* a table to
  start with and what did she mean? She was adamant she need to insert a table
  of x row and y columns as she did in MS Word to make Excel work. I showed her
  how it worked but she seemed hopeless out of her depth – No hope for that
  one… Actually she quit not long after that episode.

• I would say generally no, however there have been one or two things in Excel
  where I have need the sheet to be active and visible for the code to work.
  Can’t recall for the life of me exactly what they were, but I know that
  occasionally that has been the only way to get the code to work.

  As a rule though, Activating and selecting is completely redundant and (IMHO)
  slows things down a lot – especially if screenupdating is not turned off.

• Agreed 99% of the time using sheet code names absolutely is the way to go.
  There have been some rare instances where using the sheet name has been a
  better approach.
  besides if you really need the sheet name you can call it back anyway from the
  sheetcodename.

  Dim strShtName as string

  strShtName = Sheet1.Name
  Worksheets(strShtName).DoSomething

  Of course you wouldn’t really use it that way, but you get the idea.

• Cheeky!… Generally this (codically removing and inserting sheets) is one of
  the issues where using the sheet name can work better (or at least easier),
  however, I have used code from Chip Pearson to remove an existing worksheet
  and replace it with a new worksheet and then programically reset the
  sheetcodename of the inserted sheet to be what I need it to be. 🙂 Can be
  done but it is a bit more stuffing around.
  See: http://www.cpearson.com/excel/vbe.aspx if you are curious.

• > I have used code from Chip Pearson to remove an existing
  > worksheet and replace it with a new worksheet and then
  > programically reset the sheetcodename of the inserted
  > sheet to be what I need it to be. 🙂 Can be done but it
  > is a bit more stuffing around.
  > See: http://www.cpearson.com/excel/vbe.aspx if you are
  > curious.

  Boilerplate warning: Doesn’t work if the VB project is protected (as it should
  be).

• I have always had trouble using the sheets code name when I am doing things
  between 2 workbooks like:
  ThisWorkbook.Bk3Sheet1.Range(“D6”).Copy
  Workbooks(“Book2”).Bk2sheet1.Range(“D6”).PasteSpecial Paste:=xlPasteValues
  I am sure there is a logical explanation, but I don’t know it.

• You would need to set a reference to the other workbook’s project before you
  could use codenames for its sheets. (at least, that’s the only way I know of)

• Are Bk2sheet1 and Bk3Sheet1 worksheet objects? IF so, why are you using the
  workbook qualifier as well?

• Sounds like referring to codename’d sheets in two different workbooks will
  be a problem, generally speaking, if they have the same codename?

  And quite frankly, even if they have different codenames, without the
  reference of which someone
  speaks, to the projects in question … using the
  codename of an object outside of the active project would fail?

  Maybe this is one-half of someone’s
  1 percent leftover in favor of the Name
  property?

• I would put using the “sheet code name” in the “Not to use” category.
  Anytime you code on the bare metal you are asking for trouble.
  Abstract your concepts

  Dim wsBk3Sheet1 as Worksheet
  Dim wbBook3 as workbook

  Set wbBook3 = Workbooks(“Book3.xlsm”)
  Set wsBk3Sheet1 = wbBook3.Worksheets(“Sheet1”) ‘or = wbBook3.Sheet1 if you want to commit the sin only once

  now wsBk3Sheet1 is a valid reference to a specific sheet (assuming the above executes without error)
  wsBk3Sheet1.DoSomething ‘works

  Now abstract further: why use Excel’s name for the sheet. Use your own so you know what you (and others) are talking about.
  Dim wsOilGasYield as worksheet
  Set wsOilGasYield = wbBook3.Sheet1 ‘or as I prefer = wbBook3.Worksheets(“OilGasYield”)

  At this point, I want it issue another NOT to Use point.

  >> DO NOT FORGET to use Option Explicit at the top of each module. <<

  Declare your variables. It might be a pain, but it beats searching for misspellings deep in your code.

  Finally, let Intellisense work for you.
  Type in “wsO” then Ctrl-Space and you get the list of all variables begining with wsO.
  Select wsOilGasYield .
  Type a “.” and now you have the list of all Worksheet Properties and methods.
  If you don’t get the list, you know you have a bug.

  If you use “Workbooks(“Book2”).Bk2sheet1. “
  Intellisense cannot assist.

  >>
  Agreed 99% of the time using sheet code names absolutely is the way to go.
  There have been some rare instances where using the sheet name has been a better approach.
  besides if you really need the sheet name you can call it back anyway from the sheetcodename.

  Dim strShtName as string

  strShtName = Sheet1.Name
  Worksheets(strShtName).DoSomething

  Of course you wouldn’t really use it that way, but you get the idea.

• “Set wsOilGasYield = wbBook3.Sheet1”.

  Yes… This is exactly the technique I usually take when dealing with
  Xlsheets in the VBE.
  As for why I use prefer sheetcodename. Simply the code will not fail if the
  user changes the sheet name. Use SET as per above is the optimal way IMHO
  and recommended.

  The issue with using “wbBook3.Worksheets(“OilGasYield”)” rather than “Set
  wsOilGasYield = wbBook3.Sheet1” is this code will fail if a user changes the
  sheet name – and that happens a lot in my experience.

• Never Call another sub without using Call.
  Call SubName is better than SubName.

• Good point,
  I get around that with defining the range first, then the sheet.

  Set rngGasYield = wbBook3.names(“GasYield”).refersToRange
  Set wsOilGasYield = rngGasYield.Parent

  (I just didn’t add the extra step in the previous post)

  However, I now see the benefit in your method. I’m not sure I’d adopt it,
  but it has advantages.
  It beats a For Each ws1 in Worksheets() looking for something key on a
  renamed sheet.

• > Never Call another sub without using Call.
  > Call SubName is better than SubName.
  >
  Why on earth?

• Because Mr Phelps said so….

  I’ve always thought, getting everything right with XL was a mission
  imposible.

• I agree with you on why you prefer sheet code names (I prefer too for that
  same reason), but I am still unable to use sheet code names when I want to
  do something between 2 workbooks i.e. Copy Test1 (below) does not work, but
  Test2 does

  Stephen I believe you said Test1 would work (even if you wouldn’t use it),
  but I cannot get it to work

  Sub Test1()
  Dim Sht1 As Worksheet, Sht2 As Worksheet
  Set Sht1 = Workbooks(“Book2”).Sheet1
  Set Sht2 = Workbooks(“Book3”).Sheet1
  Sht1.[D6].Copy
  Sht2.[D6].PasteSpecial Paste:=xlPasteValues
  End Sub

  Sub Test2()
  Dim Sht1 As Worksheet, Sht2 As Worksheet
  Set Sht1 = Workbooks(“Book2”).Sheets(“Sheet1”)
  Set Sht2 = Workbooks(“Book3”).Sheets(“Sheet1”)
  Sht1.[D6].Copy
  Sht2.[D6].PasteSpecial Paste:=xlPasteValues
  End Sub

• >> I believe you said Test1 would work (even if you wouldn’t use
  it),
  but I cannot get it to work.

  Sub Test1()
  Dim Sht1 As Worksheet, Sht2 As Worksheet
  Set Sht1 = Workbooks(“Book2”).Sheet1
  Set Sht2 = Workbooks(“Book3”).Sheet1
  Sht1.[D6].Copy
  Sht2.[D6].PasteSpecial Paste:=xlPasteValues
  End Sub
  <<

  Don’t look at me. I was taking him at his word.
  Using XL2007 SP2, I cannot get it to work either.

  With Set wb2 = Workbooks(“Book2.xlsm”)
  In the Watch Window for
  wb2.Sheet1 I get “Object doesn’t support this property or method”
  wb2.Sheets(1) resolves as a worksheet and
  wb2.Sheets(1).codename = “Sheet1”

  wb2.Sheets(“Sheet1”) works fine, of course.
  strTargetSheet = “Sheet1”
  wb2.Sheets(strTargetSheet) resolves to “Sheet1”, and I prefer this
  symmetry.

  should I delete Sheet1 then
  wb2.sheets(2).codename = “Sheet3”

  Personally, for me wbX.Sheet11 syntax is a Do NOT Use.

  Two more issues to bring up:

  I have learned that .Copy; .PasteSpecial xlValues is unnecessary.

  I believe that your
  Sht1.[D6].Copy
  Sht2.[D6].PasteSpecial Paste:=xlPasteValues

  could be rewritten as
  sht2.[D6] = Sht1[D6].values

  which has the following advantages:
  1. Only 1 line of code without breaking a sweat
  2. It doesn’t use the Clipboard which can be extreamly useful in
  multitasking
  3. it is far faster
  4. PasteSpecial requires the target sheet to be active, this method doesn’t
  5. It works on arrays, too. sht2.[D6:E10] = Sht1[D7:E11].values

  It’s been a long time since I’ve used wsX.[cellrange] syntax.
  I have to put that in the Not to Use category for me:

  1. It is putting specific cell references in the code,
  I’m a stickler on using range names to protect myself from later added rows
  and columns.

  2. You cannot use the syntax for strings carrying range names or addresses
  str2 = “D6”
  ws2.[str2] doesn’t refer to column 4 row 6,
  it refers to column 13382, row 2… ws2.Range(“STR2”) ‘welcome to XL07!

  3. That [address] syntax is commonly used in Access for FieldNames,
  though I prefer using the syntax: rsX!FieldName or rsX.Fields(“FieldName”)
  I want to keep that mental barrier.

  This has been a good topic. I’ve learned a few things and have reexamined
  others.

• Try the below

  Sub CallTest()
  Call Test(Sheet1, Sheet1.Range(“A1”))
  ‘Try without the Call statement
  End Sub

  Sub Test(wSht As Worksheet, Rng As Range)
  MsgBox wSht.Name MsgBox Rng.Value
  End Sub

• Yes I copy using range = range.value also. I was just using copy as an
  example of simple code that didn’t work for using sheet code names between
  workbooks.

• BksSheet1 and Bk3Sheet1 are the sheet code names of 2 different workbooks.
  Thanks

• Sorry, I should have answered the rest of your question as well. I use
  the workbook qualifier in case the sheet code names in 2 different books are
  the same. It doesn’t really matter because neither work.

• Correction (left out a “.” )

  sht2.[D6] = Sht1[D6].values

  should be
  Sht2.[D6] = Sht1.[D6].Values

  but regardless of syntax

  rngB = rngA.Values ‘will work to “paste values”

• I’m an idiot. Excuse my error.

  rngB = rngA.Value ‘Not ValueS Although it works for arrays of
  thousands of values.

• Not allowed to call yourself an idiot. Violates Listserv rules!

• > Try the below
  >
  >
  > Sub CallTest()
  > Call Test(Sheet1, Sheet1.Range(“A1”))
  > ‘Try without the Call statement
  > End Sub
  >
  > Sub Test(wSht As Worksheet, Rng As Range)
  > MsgBox wSht.Name MsgBox Rng.Value
  > End Sub
  >

  Sub NoNeedForCallTest()
  Test Sheet1, Sheet1.Range(“A1”)
  End Sub

  Sub Test(wSht As Worksheet, Rng As Range)
  MsgBox wSht.Name & ” ” & Rng.Value
  End Sub

  HTH

• Well first of all this line won’t work anyway:

  MsgBox wSht.Name MsgBox Rng.Value

  And next, telling someone that there is one particular value somewhere on a
  sheet is not very good information, so let’s add the address,

  MsgBox “sheet: ” & wSht.Name & _
  ” Value of Cell: ” & rng.address & ” = ” & Rng.Value

  And next, better test that Rng is only one cell, or take just its first cell

  MsgBox “sheet: ” & wSht.Name & _
  ” Value of Cell: ” & rng.Cells(1,1).address & _
  ” = ” & Rng.Cells(1,1).Value

  And last: well, now we come full circle back to the thread which says you
  never need to use SUB in the first place:

  Function CallTest()
  MsgBox Test(Sheet1, Sheet1.Range(“A1”))
  ‘Try without the Call statement? Just did!
  End Function

  Function Test(wSht As Worksheet, Rng As Range) as string
  Test = “sheet: ” & wSht.Name & _
  ” Value of Cell: ” & rng.Cells(1,1).address & _
  ” = ” & Rng.Cells(1,1).Value
  End Function

• This works as well

  Sub CopyBtwBooks()

  Dim SorSht As Worksheet, DestSht As Worksheet Dim SorWb As Workbook, DestWb
  As Workbook

  Set SorWb = ThisWorkbook
  Set SorSht = Sheet1

  Workbooks.Add

  Set DestWb = ActiveWorkbook
  Set DestSht = DestWb.Sheets(“Sheet1”)

  SorSht.Range(“A1:C10”).Copy DestSht.Range(“A1”)

  End Sub