具有用户定义功能的VBA数据验证

本文介绍了具有用户定义功能的VBA数据验证的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有一个用户定义的函数，希望在自定义数据验证中使用.我的功能正常运行，但是当我在数据验证中使用它时，每次都会出错...

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...

有代码:

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:

推荐答案

您不能在数据验证中直接使用UDF.但是，您可以通过命名公式来使用它.

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

选择A1，然后在名称管理器中定义一个名为IsAlphaNum的名称，其引用为:

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

=alphanumeric(A1)

(注意:单元格引用中没有$符号)

(Note: no $ signs in the cell reference)

然后在数据验证中使用= IsAlphaNum并取消选中"Ignorer si vide"选项.

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