Excel VBA(函数或子)可以返回"Data Validation >> List >> Source"的数据列表吗？

Question

是否可以excel vba (function or Sub)返回数据列表用于"数据验证">>列表在祝辞Source"？数据验证列表(下拉列表/组合框)数据源从我的vba代码像:

= MyFunction()
return Like:
Apple,
Tool,Bag,Everything ...

更多细节:我需要搜索具有多条件

的内容

Function myFunction(Rng1 As Range)
txtSearch As String
txtSearch = Rng1.Text
' do somting 
' return
' aApple,Tool,Bag,Everything ...
End Function

谢谢你的建议。

Answer 1

请尝试使用下一个功能:

Function extractValidationList(vCell As Range) As String
Dim strFormula As String

strFormula = vCell.Validation.Formula1

If left(strFormula, 1) = "=" Then
Dim inputRange As Range, c As Range
Set inputRange = Evaluate(strFormula)
If inputRange.rows.count > inputRange.Columns.count Then
'extract a 1D array from a range with more rows and one column and Join it
extractValidationList = Join(Application.Transpose(inputRange.value), ", ")
ElseIf inputRange.Columns.count > inputRange.rows.count Then
'extract a 1D array from a range with more columns and one row and Join it
extractValidationList = Join(Application.Transpose(Application.Transpose(inputRange.value)), ", ")
End If
Else
Dim arrF, listSep As String
listSep = Application.International(xlListSeparator)
arrF = Split(strFormula, listSep)
extractValidationList = Join(arrF, ", ")
End If
End Function

可以使用下一个Sub:

进行测试

Sub testExtractValidationList()
Debug.Print extractValidationList(ActiveCell) 'previously select the validated cell...
End Sub

或者使用UDF(用户定义函数)，在单元格中编写公式:

=extractValidationList(C6)

其中，C6是列表验证单元格，其中from用于提取列表…