CODEWITHSUNDEEP
excelexcel-formulavba
Mr. Blond
Mr. Blond

Reputation: 1177

Excel custom data validation use own made function

I'm trying to use my own made function inside custom data validation formula. But for some reason it does not work.

My function:

Public Function IsNumberXValid(x) As Boolean
   IsNumberXValid = IsNumeric(x) And Math.Cos(x) <> 1
End Function

When I put this function inside formula field it shows following warning:

enter image description here

What am I doing wrong?

Upvotes: 4

Views: 1260

Answers (1)

Gary&#39;s Student
Gary&#39;s Student

Reputation: 96791

With normal worksheet functions, it is easy to apply DV:

enter image description here

It is slightly more complex with a UDF() This approach uses a "helper" cell.

I begin with a tiny UDF():

Public Function IsPrime(L As Long) As Boolean
    arr = Array(5, 7, 11)
    IsPrime = False
    For Each a In arr
        If L = a Then
            IsPrime = True
            Exit Function
        End If
    Next a
End Function

The UDF() returns True for a couple of inputs. I want to apply DV to cell D1 using the udf() as a rule.

I am using cell E1 as my "helper". In cell E1 I put my UDF():

=IsPrime(D1)

enter image description here

Next I Name my "helper" cell:

enter image description here


Finally, I return to cell D1 and apply the DV:

enter image description here

Upvotes: 6

Related Questions