How to add erroneous list-formula validation in VBA?

[jasmith4]

In the Excel UI you can enter a list-type validation whose list is a formula that results in an error. If you do, a dialog box comes up saying "The Source currently evaluates to an error. Do you want to continue?", and if you say Yes, it will preserve the validation. For example, the Source might be "=OFFSET(A1,0,0,NumValues,1)" where NumValues is the name of a cell that right now is blank or has a value less than 1, but later might be set to 1 or more.

But in VBA, if you encode this, even after an On Error Resume Next statement, the validation doesn't get added.

Range("NumValues") = 0
On Error Resume Next
Range("B1").Validation.Add xlValidateList, xlValidAlertStop, , "=OFFSET(A1,0,0,NumValues,1)"

How can I make that validation stick?

 

[jasmith4]

Jerry: Please understand, NumValues is made up. It's not real. I have no such workbook.

I'm not asking for a workaround, I'm asking for VBA code that duplicates what the UI can do: store a list-type validation despite its Source formula being syntactically correct, invalid when I set up the validation, but possibly valid when the user uses my workbook.

 

[RoryA]

Great. But how in general do you report Excel bugs? Is it best to report them here?

Not really, as this site is not actually affiliated to Microsoft - though it is owned by an MVP and there are several MVPs that post here, who may be able to report the bug. I think you would have to go through Microsoft Support - I am assured they do not charge for actual bugs, or security issues, though whether they would class this as one I don't know. I suspect they would simply argue this is a feature that hasn't been implemented yet.

In any event, there is no way to get the code to do what you want, as far as I know. You need to supply a formula that is always valid.

 

[jasmith4]

It seems the simplest and most general workaround is to add error-catching to the original formula:

=IFERROR(<originalFormula>,"") or =IFERROR(<originalFormula>,<emptyCell>), where <emptyCell> is one cell that's always empty.

Thus the formula's always valid even if the original is not, and if it isn't, blank is the only valid value.

 

[Mech0T1]

Hi, I signed up for an account here just to say thanks - 4 years later! This solution idea worked perfectly for a similar issue I was having. Trying to create dependent data validation lists, but because the dependent cell had not yet had a value chosen, I would get errors (the same error you get when entering DV formulas manually - "the source currently evaluates to an error, continue?" except in VBA you can't continue, it just errors and crashes). So I put a value in one of the cells (oddly, just populating a single cell worked - I thought I would have to do all the cells in the range) and then deleting it after I've put the DV formula in the next column in worked perfectly. Thanks!