How NOT to Use Excel

In 2010, John Meyer posted to the Microsoft Excel Developers List EXCEL-L about Don McMillan's hilarious video How NOT to use Powerpoint. I replied that it would be good to see something similar for Excel. In response, 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; and so on. I thought it would be a shame to waste the momentum, so I set up a blog where people could discuss the topic. Few did, so to reduce clutter, I eventually deleted it. However, I thought the original mails to EXCEL-L should remain visible, because they ought to interest visual-language designers and spreadsheet researchers, and people designing Excel courses, and so on. So this posting contains all the EXCEL-L mails on the topic up to June 18th 2010. I've removed names and other identification, because EXCEL-L isn't a public mailing list.

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 ... http://www.amazon.com/dp/190540400X <GD&R>

  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! :-D

• >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.

• <<<Wiping egg off face>>>

  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

How to Code Recursion in Excel

In my posting "Earth falls toward a black hole and everyone dies", I said that the science-fiction generator it describes works by traversing a story flowchart, and that the natural way to program this is by recursion. I'm now going to explain. How did I code a recursive Excel spreadsheet?

Here's an example I've written to demonstrate. The spreadsheet is here. It has three columns. The first two, headed input and output, are one row each. Type a number into input, and its factorial will appear in output.

And here is the Excelsior program I generated this spreadsheet from:

  table input : -> general.

  table output : -> general.

  output[ ] = fac[ input[] ].


  type fac_range = 0:100.

  table fac : fac_range -> general.

  fac[ 0 ] = 1.

  fac[ n>0 ] = n * fac[ n-1 ].


  layout( 'Sheet 1'
        , rows( heading
              , row( input as null
                   , output as null
                   , fac as y
                   )  
              )
        ).

The key to understanding this is that Excelsior thinks of a spreadsheet as a collection of tables. The Excelsior keyword for these is table. When Excelsior generates a spreadsheet, it maps each table onto one or more cells, as dictated by the layout statement. In this program, that statement puts fac to the right of output to the right of input, and runs fac down the sheet. Since fac has one dimension — as indicated by the type statement for fac_range, and by the table declaration for fac — that makes it occupy a single column.

The key to understanding the recursion is to think of tables as functions. Indeed, my syntax for table declarations imitates the notation used by mathematicians to specify a function's argument and result types. The two equations for fac then become analogous to the usual recursive definition of factorial.

This, clearly, is recursion. But it's laid out — spread out, you might say — in space rather than in time.

So that generates the factorials. The remaining part of the program is that which gets the right factorial from the table and puts it into the output cell. Namely, the first three statements. The first two declare the input and output cells as tables that have no subscripts, and are therefore  one cell each. The third statement uses the input as a subscript to fac, and puts the result into the output.

And that's how to program recursion in Excel.

Kaprekar's Constant in Excel and Excelsior

The integer 6174 is also known as Kaprekar's constant. According to Wikipedia, it is notable for the following property:

1. Take any four-digit number which contains at least two different digits (so not 1111, 2222, and so on). Leading zeros are allowed.
2. Arrange the digits in ascending and then in descending order to get two four-digit numbers, adding leading zeros if necessary.
3. Subtract the smaller number from the bigger number.
4. Go back to step 2.

The above process, known as Kaprekar's routine, will always reach its fixed point, 6174, in at most 7 iterations.

I'm going to use it as an example. In my last posting, about literate programming and my science-fiction generator spreadsheet, I explained that I made the spreadsheet by writing a program in a language I call Excelsior, and compiling this to Excel. I want now to demonstrate Excelsior, by using it to write a Kaprekar-constant spreadsheet.

I was inspired by Excel developer Jeff Lutes, who posted to the Microsoft Excel Developers List in July 2012, asking for an elegant way to calculate Kaprekar's constant in Excel, preferably without using Visual Basic. I tried this in Excelsior, and generated this spreadsheet. Here below is the Excelsior program, in the HTML documentation format output by Literate Excelsior. Program text is on a grey background: the rest is commentary. To use the spreadsheet, type a four-digit number into cell A2. You can also see the program here, which may be better if the blog styling interferes with this posting.

Program structure

I'm laying the spreadsheet out in columns. Each row corresponds to one iteration, and I shall define a constant called max_iterations to give the depth of each column:

type column = 1:max_iterations.

constant max_iterations = 20.

I hope this will be big enough.

Briefly stated, the columns hold the following tables:

• the original number;
• its digits, calculated by converting it to text and extracting a substring;
• the digits in ascending order, calculated by using the function SMALL, which returns the k-th smallest value in an array or range;
• the number in ascending order, calculated by concatenating these;
• the number in descending order;
• their difference. I feed this back into the first column.

The tables

number

number[1] holds the original number. number[n], where n>1, contains the absolute value of difference calculated in the n-1'th iteration. I made it absolute because I hit problems with the minus sign otherwise.

table number : column -> general.

number[ 1 ] = 4973.
number[ n>1 ] = abs( difference[ n-1 ] ).

number_as_text

number_as_text[n] is number[n] converted to text.

table number_as_text : column -> text.

number_as_text[ n ] = text( number[n], "0000" ).

digits

digits is a four-column table. The d'th column holds the d'th digit of number.

type digit_range = 1:4.

table digits : digit_range column -> general.

digits[ d, n ] = value( mid( number_as_text[n], d, 1 ) ).

digits_ascending

digits_ascending is also a four-column table. The d'th column holds the d smallest digit.

table digits_ascending : digit_range column -> general.

digits_ascending[ d, n ] = small( digits[all,n], d ).

number_ascending

number_ascending[n] concatenates the digits from digits_ascending[n], giving us them as a single number.

table number_ascending : column -> general.

number_ascending[ n ] =
  value( digits_ascending[ 1, n ] &
         digits_ascending[ 2, n ] &
         digits_ascending[ 3, n ] &
         digits_ascending[ 4, n ]
       ).

number_descending

Similarly, number_descending[n] is a single number with the digits in descending order.

table number_descending : column -> general.

number_descending[ n ] =
  value( digits_ascending[ 4, n ] &
         digits_ascending[ 3, n ] &
         digits_ascending[ 2, n ] &
         digits_ascending[ 1, n ]
       ).

difference

difference[n] is the difference between number_ascending[n] and number_descending[n].

table difference : column -> general.

difference[ n ] = number_ascending[ n ] - number_descending[ n ].

layout

This arranges the tables from left to right as named below, and puts an automatically-generated heading above each giving its name.

layout( 'Sheet 1'
      , rows( heading
            , row( number as y
                 , number_as_text as y
                 , digits as xy
                 , digits_ascending as xy
                 , number_ascending as y
                 , number_descending as y
                 , difference as y
                 )
            )
      ).

And that is how to calculate 6174 in Excel. It's the most useless integer in the mathematical universe, but at least the calculations are easy to read.

"Earth falls toward a black hole and everyone dies"

In my post "The Programmer as Essayist", I quoted the famous computer scientist Donald Knuth on literate programming. I now want to give an example from my own work. It's a spreadsheet that generates science-fiction plots. Try it! Clicking on that link will open Excel if you're in Windows, and you can then run the spreadsheet immediately or save it for later. To run it, scroll to the top. The pale yellow column headed Story contains the output. To change it, click on cell B4, below the label saying Recalculate. The cell will then display as a menu containing one option, also called Recalculate. Select this option (even though it's already selected). That will activate a chain of random-number generators, and you'll get a new story in column A.

I wrote this spreadsheet using literate programming. This is where it came from.

In the anthology The Year's Best Science Fiction No. 5 (edited by Harry Harrison and Brian Aldiss, Sphere, 1972), there are stories. That's usual for anthologies. There is also a story generator. That's less usual. The story generator is called "The Science Fiction Horror Movie Pocket Computer" and is written by Gahan Wilson.

Or perhaps I should say drawn, because the SFHMPC is a flowchart. It consists of boxes containing phrases such as "Earth", "is struck by a giant comet and", "destroyed", "is attacked by", "bug(s)", "which (who)", "look upon us only as a source of nourishment", "and are", "radioactive", "and", "can be killed by", "the atomic bomb (The End)". The boxes are connected by lines. Start at the top, follow it round, choose an exit at each box, and you end up with a cheesy SF plot that Hollywood would be proud of.

Wilson's SFHMPC went through several incarnations in my hands before I got it working in Excel. The first was in felt-pen and card. I used to teach Artificial Intelligence at Oxford, and I also used to publicise AI at Freshers' Fair. There's not actually much AI in the SFHMPC, but it's fun and it demonstrates that computers can do interesting things with data other than numbers. So I drew a huge copy of it on a monster piece of card bought from the Broad Canvas art shop, and used it as one of my Freshers' Fair props, together with such tantalisers as the Dennett and Hofstadter "A Conversation with Einstein's Brain", a wodge of output from Eliza, and a photocopy of John Varley's short story "Overdrawn at the Memory Bank".

A bit later, I implemented the SFHMPC in Prolog. That's because Prolog was my main language for teaching AI, and I wanted an amusing demo to show how Prolog can represent networks.

Then I put that up on my Web site, as a PHP script running SWI-Prolog. Go here to try it, to see the Prolog source, or to read about how to call Prolog from PHP.

Then even later, as part of my spreadsheet research, I implemented the SFHMPC in Excel. Now, this is an unusual spreadsheet. That's because the natural way of generating a story from the flowchart would be by recursion. Like this:

To generate a story, go to the box at the top of the flowchart, then generate the rest of the story.

To generate the rest of the story, output the contents of the box you're at, then choose one of its exits, go to the corresponding box, and generate the rest of the story. Stop if there are no exits.

That, indeed, is how the Prolog version works.But you can't do recursion in Excel, unless you descend into Visual Basic, so how did I make the spreadsheet version work? That's a topic for another posting. What I want to say in this one is that I wrote it in Excelsior, a language I've implemented that compiles into Excel.

And you can write Excelsior programs in two modes. One is similar to the way you'd write in a language such as Pascal or Java. Everything is assumed to be source code unless you precede it by comment delimiters, in which case it's comment. When you feed this into Excelsior, it generates a spreadsheet.

The other mode is for literate programming. Everything is assumed to be comment unless you indent it, in which case it's code. When you ask Excelsior to compile this, it generates two pieces of output. One is a spreadsheet. The other, from Excelsior's documentation engine, is a nicely formatted Web page. Here's the documentation engine's output for the SFHMPC.

In this Web page, commentary is left alone. Code is rendered in a monospace font, on a lightly coloured background to make it stand out. I wrote the code and commentary for the SFHMPC as a mathematical essay, thinking of the code as inserts in the same way that equations would be in a normal maths essay. I introduced the data structures in an order which, I hope, makes it easy to understand how the code works. I included some simple examples. And that is literate programming.


.

The Programmer as Essayist

I believe that the time is ripe for significantly better documentation of programs, and that we can best achieve this by considering programs to be works of literature. Hence, my title: "Literate Programming."

Let us change our traditional attitude to the construction of programs: Instead of imagining that our main task is to instruct a computer what to do, let us concentrate rather on explaining to human beings what we want a computer to do.

The practitioner of literate programming can be regarded as an essayist, whose main concern is with exposition and excellence of style. Such an author, with thesaurus in hand, chooses the names of variables carefully and explains what each variable means. He or she strives for a program that is comprehensible because its concepts have been introduced in an order that is best for human understanding, using a mixture of formal and informal methods that reinforce each other.

— Donald Knuth. "Literate Programming (1984)" in Literate Programming. CSLI, 1992, pg. 99. This quote is at http://www.literateprogramming.com/ .

Happy Spreadsheet Christmas

Cartoon Description

Santa and his reindeer are silhouetted against the moon, overlooking the Sphinx and two pyramids, one with a palm tree in front of it. The nearest reindeer is saying, "Er, Santa? Was it wise to program the route-finder using a spreadsheet?".

Spreadsheet Evolution, Flared Trousers, and Colour in the Depths of Winter

This post wasn't going to be about spreadsheets, and then I realised that it would be. This is the first time I've created a blog in Blogger, so I was pleased to remember that there's a Blogger blog that I like, and could use as a guide to designing my own blog and arranging its layout "gadgets".

The blog in question is called Saaibestrijding, written by Paul Tieman from Maastricht. His blog's name is a Dutch coinage meaning "struggle against boredom", and his blog is mainly photos showing unusual and colourful ways for men to dress. I like it because I like colour, especially in the depths of a British winter when everyone is wearing brown, beige, grey, and black. I also like comfort, and some of his clothes look very comfortable.

So Paul's blog helped me lay out my own, but surely it has nothing to do with spreadsheets. Then I remembered that I once cited it in an essay called "Dress Code", which I wrote for the Dr Dobbs computer magazine after the 2009 European Spreadsheet Risks Interest Group conference in Paris. It had been very hot, and yet some of the people at the conference didn't like me wearing shorts, because it didn't convey a professional image. In  "Dress Code", I was commenting on ergonomics versus business.

And I was also writing about an earlier EuSpRIG conference, the 2002 one in Cardiff. Now, the first modern spreadsheet was created in 1979. Since then, technology has made spreadsheets look much nicer, but it is still hard to build reliable software with them. As you can tell from this blog and my Indiegogo campaign, that's one of my research interests, and it's also the problem that EuSpRIG was founded to attack.

So in the 2002 conference, to emphasise how bad spreadsheets still are, I wandered around Cardiff's arcades until I found a retro clothing shop, bought a pair of extremely flared 1970s bellbottoms, and with marker pens and fluorescent yellow card given me by the shop's owner, made a lapel badge reading Spreadsheets have not evolved since flares were ^last in fashion. The "^last" was hastily inserted when I looked around and realised that flares were now in fashion for the second time.

I wore this badge to the rest of the conference. And I could still wear it today, because although Microsoft have made Excel bigger, they still haven't equipped it with the features that programmers of other languages expect to use for building reliable programs.