我可以使用VBA函数将可接受值的（动态）列表返回到Excel的数据验证中吗？ [英] Can I use VBA function to return a (dynamic) list of acceptable values into Excel's data validation?

问题描述

对于给定的单元格，我选择数据/验证，并将允许设置为列表。我现在希望设置Source，如下所示：

For a given cell, I select Data/Validation and set Allow to "List". I now wish to set Source like so:

= rNames（REGS）

=rNames(REGS)

但不起作用名称未找到）。所以我去插入/命名/定义并创建REGNAMES通过简单地分配上面的公式（无单元格范围）。然后我返回到数据/验证，当我设置Source像这样：

but that does not work (name not found). So I go Insert/Name/Define and create "REGNAMES" by simply assigning the formula above (no cell range). I then return to the Data/Validation and when I set Source like so:

= REGNAMES

=REGNAMES

现在我得到源当前评估为错误。不幸的是，即使我无视它，这个错误也不会消失。我可以在表格中创建一个范围公式：

Now I get "Source currently evaluates to error". Unfortunately, this error does not go away even after I ignore it. I can create a range formula in the sheet like so:

{= REGNAMES}

{=REGNAMES}

并将其拖动到右边的几个单元格和rNames函数忠实地返回

and drag this to the right across a couple cells and the rNames function faithfully returns

选项＃1 |选项＃2 | ...

Option #1 | Options #2 | ...

就是这个函数返回一个范围。

That is, the function returns a range as intended.

我知道我可以使用宏代码来操作该单元格的VBA列表设置。我不喜欢这些副作用。我更喜欢一个基于功能的干净依赖树。任何想法如何使数据/验证接受从rNames返回的数组值？

I know that I can use macro code to manipulate the List setting for that cell out of VBA. I don't like these side-effects much. I would prefer a clean dependency tree built on functions. Any ideas how to get the Data/Validation to accept the array values returned from rNames?

谢谢。

PS：rNames将结果范围作为Variant返回，如果有任何方向。

PS: rNames returns the result range as a Variant, if that has any bearing.

推荐答案

我认为问题是数据验证对话框仅接受以下列表：

I think the problem is that data validation dialog only accepts the following "lists":

• 直接输入到源字段的事物的实际列表

• an actual list of things entered directly into the Source field

一个文字范围引用（如$ Q $ 42：$ Q $ 50）

a literal range reference (like $Q$42:$Q$50)

解决一个范围参考

最后一个是关键 - 没有办法让VBA函数返回数组，可用于验证，即使您从命名公式中调用它。

That last one is key - there is no way to have a VBA function just return an array that can be used for validation, even if you call it from a named formula.

您可以编写返回范围的VBA函数参考，并从指定的公式调用 。这可以作为以下技术的一部分，这些技术近似于您实际想要的功能。

You can write a VBA function that returns a range reference, though, and call that from a named formula. This can be useful as part of the following technique that approximates the ability to do what you actually want.

首先，在某个地方调用任意数组 - 返回VBA UDF。假设你有这个功能：

First, have an actual range somewhere that calls your arbitrary-array-returning VBA UDF. Say you had this function:

Public Function validationList(someArg, someOtherArg)

    'Pretend this got calculated somehow based on the above args...
    validationList = Array("a", "b", "c")
End Function

并且您从$ Q $ 42：$ Q $ 50作为数组公式调用它。你会得到三个具有a，b和c的单元格，其余的单元格将具有＃N / A错误，因为返回的数组小于调用UDF的范围。到目前为止这么好。

And you called it from $Q$42:$Q$50 as an array formula. You'd get three cells with "a", "b", and "c" in them, and the rest of the cells would have #N/A errors because the returned array was smaller than the range that called the UDF. So far so good.

现在，有另一个VBA UDF只返回范围的被占用部分，忽略＃N / A错误单元格：

Now, have another VBA UDF that returns just the "occupied" part of a range, ignoring the #N/A error cells:

Public Function extractSeq(rng As Range)

    'On Error GoTo EH stuff omitted...

    'Also omitting validation - is range only one row or column, etc.

    Dim posLast As Long
    For posLast = rng.Count To 1 Step -1
        If Not IsError(rng(posLast)) Then
            Exit For
        End If

        If rng(posLast) <> CVErr(xlErrNA) Then
            Exit For
        End If
    Next posLast

    If posLast < 1 Then
        extractSeq = CVErr(xlErrRef)
    Else
        Set extractSeq = Range(rng(1), rng(posLast))
    End If
End Function

然后，您可以使用如下命名的公式调用：

You can then call this from a named formula like so:

=extractSeq($Q$42:$Q$50)

并且命名的公式将返回范围引用，Excel将接受允许的验证列表。笨拙，但副作用免费！

and the named formula will return a range reference that Excel will accept an allowable validation list. Clunky, but side-effect free!

请注意在上述代码中使用关键字Set。你的问题不清楚，但这可能是整个答案的唯一重要部分。如果在尝试返回范围引用时不使用Set，VBA将返回范围的值，不能用作验证列表。

Note the use of the keyword 'Set' in the above code. It's not clear from your question, but this might be the only part of this whole answer that matters to you. If you don't use 'Set' when trying to return a range reference, VBA will instead return the value of the range, which can't be used as a validation list.

这篇关于我可以使用VBA函数将可接受值的（动态）列表返回到Excel的数据验证中吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！