我有一个用户定义的函数,希望在自定义数据验证中使用.我的功能正常运行,但是当我在数据验证中使用它时,每次都会出错...
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.
更多推荐
具有用户定义功能的VBA数据验证
发布评论