Data Validation with custom function 1

[venkman]

How do you setup Data Validation with a custom function? I'd like to pass the value of what's trying to be entered into a cell into a custom function that returns true or false depending on the entered value. How do I do this?

-Venkman

 

[Zathras]

What's the function? Perhaps there's a simpler way.

Otherwise, you can use a extra cell somewhere on the sheet (I'm using B1 in this example):
[blue]

A1: 4
B1: =MyValidation(A1)

[/color]
Set up a validation formula for cell A1 like this:
[blue]

=B1

[/color]
For example if MyValidation looks like this:
[blue]

Function MyValidation(ACell)
  If ACell Mod 2 = 0 Then
    MyValidation = True
  Else
    MyValidation = False
  End If
End Function

[/color]
Then only even numbers can be entered into cell A1.

But this trivial example could be accomplished with this validation formula:
[blue]

=MOD(A1,2)=0

[/color]

 

[venkman]

What I'm trying to validate is that cell containst a value or a range of values. A single value is written as a 10 digit number, while a range is written as 10 digit number, a dash, then another 10 digit number. For example the following are valid:

1234567890-1234567891
1234567890
1546812813-1546812850

I would think validation of this sort requires a custom function, but if I've missed a much easier solution, please let me know.


The method you give above is pretty good, but it has one major problem. What happens if previously I had info in rows 1 through 10 and now I want to add some information to 11. In order for your method to work I'll have to fill down the B column to the last row. This is going to be a real pain to do every time. Of course, I could solve it by adding some code the Worksheet_Change() event, but that's hardly elegant.

Any other suggestions?

-Venkman

 

[Zathras]

I think you can select the entire column and then enter this as a custom validation formula:
[blue]

=OR(AND(LEN(A1)=10,VALUE(LEFT(A1,10))>0),AND(LEN(A1)=21,VALUE((LEFT(A1,10)))>0,VALUE((RIGHT(A1,10)))>0))

[/color]
If any cells in the column need to have other shapes to their values, you can override single cells (or a range of cells). For example you may need to allow any value for the column heading.

The formula looks as if it could be simplified, but any simpler version will, I believe, allow some invalid entries to pass. In particular an entry like -123456789 which is 10 characters long and numeric. (Hence the test for >0.)
Similarly, an entry like 1234567890--123456789 needs to be stopped, but the second half is a valid number, and so care must be taken.

 

[venkman]

You're right I could do it that way, but the thing is that a long prefix notation formula like that (and it's actually even longer if you check to make sure the character in the middle is a "-&quot is really hard to read and thus hard to maintain later. If I were to rewrite the same in VBA, it would be much clearer what it was doing.

Is there really no way to get validation to use a custom function?

-Venkman

 

[Zathras]

I managed to get it to work the way you want, but it's tricky. First, need to assign a couple of cells in an out-of-the way part of the worksheet:
[blue]

AH1: =CELL("address")
AH2: =MyValidation(INDIRECT(AH1))

[/color]
Now, here is a UDF that should be close to what you want. Adjust it to your needs as you see fit:
[blue]

Option Explicit

Public Function MyValidation(ACell) As Boolean
Const MIN_VAL = "1000000000"
Dim sWork As String
Dim sWork1 As String
Dim sWork2 As String

If IsEmpty(ACell) Then
  MyValidation = True
  Exit Function
End If
    
sWork = ACell.Value

If Len(sWork) = 10 Then
  If IsNumeric(sWork) Then
    If sWork >= MIN_VAL Then
      MyValidation = True
      Exit Function
    End If
  End If
End If
      
If Len(sWork) = 21 Then
  If Mid(sWork, 11, 1) = "-" Then
    sWork1 = Left(sWork, 10)
    sWork2 = Right(sWork, 10)
    If IsNumeric(sWork1) And IsNumeric(sWork2) Then
      If sWork1 >= MIN_VAL And sWork2 >= MIN_VAL Then
        If sWork1 < sWork2 Then
          MyValidation = True
          Exit Function
        End If
      End If
    End If
  End If
End If

End Function

[/color]
Now select your column and put in this custom validation formula:
[blue]

=$AH$2

[/color]
As long as you never select cell [blue] AH2 [/color] (in this example), you should be ok. If that cell is selected and the worksheet is recalculated, you get a circular reference.

 

[venkman]

wow, that's really cool. I never knew about the CELL or INDIRECT functions. It's too bad we have to use a hack to get this to work, but it's a really f-ing cool hack so I'm down with it.

-Venkman