Bug When Pasting Validation in VBA

June 08, 2005 - by Bill Jelen

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.