Keep Score with Excel

Keep Score with Excel
On one of my first appearances on TechTV's Call for Help, Leo LaPorte mentioned that the last time he used Excel was to keep track of Gin Rummy scoring while playing cards with his daughter. On this appearance on Call for Help, I showed Leo many cool ways to use Excel when keeping score. View the show notes to see example spreadsheets:

• Little League Baseball Scoring
• 301 Dart Scoring, complete with natural out chart
• Gin Rummy scoring
• Bowling scoring

The show notes for this appearance are at http://www.mrexcel.com/tip094.shtml.

Sorting in Excel

Sorting in Excel
My manager demanded that the reports be sorted into East, Central, West sequence. I was stuck. Unless I could convince him to rename the "East" region to be called "Middle", there was no good way to get the regions to sort into the correct sequence. Luckily, Excel offers custom sort sequences and they are pretty easy to set up.

It may sound crazy, but sometimes you need a random sort. If you are a teacher and need to figure out an order for your students to present their book reports, perhaps assigning them randomly is more fair than alphabetically.

Both of these topics, plus one-click sorting are the topic of my June 10, 2005 appearance on Call for Help. Visit http://www.mrexcel.com/tip093.shtml for the show notes.

Bug When Pasting Validation in VBA

Bug When Pasting Validation in VBA
If you've read the first few chapters of VBA & Macros for Microsoft Excel, you know that I complain that the Excel macro recorder doesn't do the greatest job of recording usable code. Usually, the code works fine, but it is recorded in such a way that it may not be that useful for different size datasets. The code might work fine today, but not tomorrow.

I ran into a really strange problem where the macro recorder actually recorded code that did not work. I was writing a macro that tried to copy the validation from one cell to a range of cells. In Excel 2002, this code was as follows:
Range("E5").Copy
Range("E6:E12").PasteSpecial Paste:=xlPasteValidation
This code worked fine in Excel 2002, but failed on a client's machine with Excel 2000. One of the old computers in the office still has Excel 2000, so I tried the code there. The problem was with the xlPasteValidation. Whenever I run into something unusual, I start the macro recorder to see how the macro recorder would record the code. I set up validation in E5, turned on the macro recorder, copied E5 and used Paste Special - Validation. After stopping the macro recorder, I noted that Excel 2000 recorded the constant as:
Range("E6:E12").PasteSpecial Paste:=xlDataValidation
So, I went to the client application, changed the code to xlDataValidation and ran it again. Strangely, it produced the same error!

It turns out that the Excel 2000 macro recorder actually has a bug. It will record the xlDataValidation constant, but the macro interpreter will recognize neither xlDataValidation nor xlPasteValidation. Excel VBA Help in Excel 2000 pretends like there is no way to paste just validations.

To make this work, you need to discover the underlying value of xlPasteValidation. On my XL2002 machine, I went to the VBA Editor. Type Ctrl+G to open the immediate window and type this in the immediate pane:
Print xlPasteValidation
Hit enter and Excel 2002 will tell you that xlPasteValidation is a friendly way of saying "6". Back on the Excel 2000 machine, I tried this code:
Range("E6:E12").PasteSpecial Paste:=6

Luckily, it works. You are actually forced to use the underlying value instead of the constant. I warn against this practice in the book as it makes the program really hard to read for the next person looking at the code. In this particular case, you really have no choice. Add a comment explaining why you coded it this way:
' Bug in XL2000; need to use "6" instead of the constant
Range("E6:E12").PasteSpecial Paste:=6

So - the small lesson today is the very specific problem of how to paste special validation in Excel 2000, but the bigger lesson is the detective work required to figure out what is happening when something strange happens in Excel VBA.