I wish to validate the data in cell B3. I can readily set up a custom
validation using a formula such as =AND(B3=0, B3<=100)

I would like to use a VBA function that I have written called IsValid().
However, if I attempt to enter a custom validation formula of =IsValid(B3)
I receive the message "a named range you specified cannot be found".

Where am I going wrong? Can't I reference a VBA function in this way?

The function appears to work if I use it in a normal Worksheet formula. For
example, if I enter =IsValid(A3) in cell A4 then A4 correctly displays
either TRUE or FALSE depending on the value in A3.

Andrew,
You don't show your code but I knocked up this little function which is
similar to what you describe

Function IsValid(rng As Range)
If rng.Count 1 Then
IsValid = CVErr(xlValue)
Else
IsValid = (rng.Value = 0 And rng.Value <= 100 And Not
IsEmpty(rng.Value))
End If

End Function

If you put =IsValid(B3) in A3 it reruns TRUE or FALSE depending upon the
value.

How does it compare to yours?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Andrew" wrote in message
...
I wish to validate the data in cell B3. I can readily set up a custom
validation using a formula such as =AND(B3=0, B3<=100)

I would like to use a VBA function that I have written called IsValid().
However, if I attempt to enter a custom validation formula of
=IsValid(B3)
I receive the message "a named range you specified cannot be found".

Where am I going wrong? Can't I reference a VBA function in this way?

The function appears to work if I use it in a normal Worksheet formula.
For
example, if I enter =IsValid(A3) in cell A4 then A4 correctly displays
either TRUE or FALSE depending on the value in A3.

Bob Phillips wrote:
Andrew,
You don't show your code but I knocked up this little function which
is similar to what you describe

Function IsValid(rng As Range)
If rng.Count 1 Then
IsValid = CVErr(xlValue)
Else
IsValid = (rng.Value = 0 And rng.Value <= 100 And Not
IsEmpty(rng.Value))
End If

End Function

If you put =IsValid(B3) in A3 it reruns TRUE or FALSE depending upon
the value.

How does it compare to yours?


My function is:

Function IsValid(Num As Variant) As Boolean
IsValid = (Num = 0 And Num <= 100)
End Function
(the final version will check for various text values as alternatives to
0..100, hence use of Variant)

I have no problem using the function in a formula in a cell (eg.
=IsValid(B3) in cell A3). My problem occurs when I attempt to use the same
formula for Data Validation in as cell. eg in Cell B3 I select:

Data | Validation | Settings | Allow Custom | Formula =IsValid(B3)

This ought(?) to do the same as the worksheet function:

Data | Validation | Settings | Allow Custom | Formula =AND(B3=0,
B3<=100)

Can you post your IsValid function cod

-----------------------------------------------
~~ Message posted from http://www.ExcelTip.com
~~View and post usenet messages directly from http://www.ExcelForum.com

mudraker wrote:
Can you post your IsValid function code


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from
http://www.ExcelForum.com/

My function is:

Function IsValid(Num As Variant) As Boolean
IsValid = (Num = 0 And Num <= 100)
End Function
(the final version will check for various text values as alternatives to
0..100, hence use of Variant)

I have no problem using the function in a formula in a cell (eg.
=IsValid(B3) in cell A3). My problem occurs when I attempt to use the same
formula for Data Validation in as cell. eg in Cell B3 I select:

Data | Validation | Settings | Allow Custom | Formula =IsValid(B3)

This ought(?) to do the same as the worksheet function:

Data | Validation | Settings | Allow Custom | Formula =AND(B3=0,
B3<=100)

Function IsValid(Num As Variant) As Boolean
select case num
case 0 to 100
isValid = true
case else
isValid =false
end select
End Function


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

Andrew

You guessed it. You can't use a UDF in Data Validation (at least not that
I've ever seen). You can use theWorksheet_Change event to validate the data
using your UDF and pretty much emulate what you can do with DV.

--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.

"Andrew" wrote in message
...
mudraker wrote:
Can you post your IsValid function code


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from
http://www.ExcelForum.com/

My function is:

Function IsValid(Num As Variant) As Boolean
IsValid = (Num = 0 And Num <= 100)
End Function
(the final version will check for various text values as alternatives to
0..100, hence use of Variant)

I have no problem using the function in a formula in a cell (eg.
=IsValid(B3) in cell A3). My problem occurs when I attempt to use the
same
formula for Data Validation in as cell. eg in Cell B3 I select:

Data | Validation | Settings | Allow Custom | Formula =IsValid(B3)

This ought(?) to do the same as the worksheet function:

Data | Validation | Settings | Allow Custom | Formula =AND(B3=0,
B3<=100)

mudraker wrote:
Function IsValid(Num As Variant) As Boolean
select case num
case 0 to 100
isValid = true
case else
isValid =false
end select
End Function


Thanks. However, it's not writing the function that's my problem. It's the
fact that you appear to not be able to use a user define VBA function in
Data Validation.