具有用户定义功能的VBA数据验证 [英] VBA data validation with user defined function
问题描述
我有一个用户定义的函数,希望在自定义数据验证中使用.我的功能正常运行,但是当我在数据验证中使用它时,每次都会出错...
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数据验证的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!