标签：VBA，数据验证
这是在forum.ozgrid.com上找到的一个工作簿，能够实现多层级联数据验证。当你在单元格A1的下拉列表中选取某项目后，单元格B1中下拉列表项会相应改变；选择单元格B1中的下拉列表项后，单元格C1中的下拉列表项会相应改变，如下图1所示。
图1
这个工作表在数据验证中应用了公式，在工作表中应用了VBA自定义函数。两个VBA自定义函数分别为：
代码语言：javascript复制Option Compare Text
Function BowLeg(s1 As String, _
Optional s2 As String = "", _
Optional s3 As String = "", _
Optional s4 As String = "", _
Optional s5 As String = "") As String
' 使用 "|" 作为联结符
BowLeg = "|" & Replace(s1, "|", "") & "|"
If s2 <> "" Then BowLeg = BowLeg & Replace(s2, "|", "") & "|"
If s3 <> "" Then BowLeg = BowLeg & Replace(s2, "|", "") & "|"
If s5 <> "" Then BowLeg = BowLeg & Replace(s2, "|", "") & "|"
If s5 <> "" Then BowLeg = BowLeg & Replace(s2, "|", "") & "|"
End Function
Function ValRange(r As Range, Optional s As String = "*") As String
Dim r1 As Range
Dim r2 As Range
Set r1 = r.Find(What:=s, After:=r.Cells(r.Rows.Count), _
LookIn:=xlValues, LookAt:=xlWhole)
If r1 Is Nothing Then Exit Function
If s = "*" Then
ValRange = Range(r1, r1.End(xlDown)).Address(False, False)
Else
ValRange = Range(r1.Offset(1), r1.End(xlDown)).Address(False, False)
End If
End Function
