VBA data validation with user defined function

Question

I have a user defined function that i want to use in a custom data validation. My function is working properly but when i use it in data validation, it's every time in error...

There is the code:

Public Function AlphaNumeric(pValue) As Boolean
    Dim LPos As Integer
    Dim LChar As String
    Dim LValid_Values As String

    'Start at first character in value
    LPos = 1

    'Test each character in value
    While LPos <= Len(pValue)
        'Single character in value
        LChar = Mid(pValue, LPos, 1)

        'If character is not alphanumeric, return FALSE
        If InStr(REFALPHACHAR, LChar) = 0 Then
           AlphaNumeric = False
           Exit Function
        End If

        'Increment counter
        LPos = LPos + 1
   Wend

   'Value is alphanumeric, return TRUE
   AlphaNumeric = True
End Function

And the setting of my data validation:

Please explain the error in more detail. On what line is crashing? What's the error message? — JMSilla
– JMSilla, Commented Apr 7, 2016 at 8:25

Rory · Accepted Answer · 2016-04-07 08:25:07Z

7

You cannot use a UDF directly in data validation. You can however use it via a named formula.

Select A1, then in Name Manager define a name called IsAlphaNum whose refersto is:

=alphanumeric(A1)

(Note: no $ signs in the cell reference)

Then in your data validation use =IsAlphaNum and uncheck the 'Ignorer si vide' option.

answered Apr 7, 2016 at 8:25

Rory

34.2k5 gold badges40 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

ebelair Over a year ago

How i can improve this solution to use it on an entire table column?

Rory Over a year ago

It will work on the entire column. You use the same validation for each cell in the column.

Collectives™ on Stack Overflow

VBA data validation with user defined function

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related