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

Question

excel vba(函数或子)可以返回“数据验证>>列表>>源”的数据列表吗？
来自我的 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 是列表验证单元格，从哪里提取列表...