来自单元格的自定义 Excel VBA 函数（修改后的 VLOOKUP）引用不同文件中的范围给出错误

Custom Excel VBA Function (Modified VLOOKUP) from Cell referring to a range in different file gives an error

提问by Andrew Lem

First time I'm writing a stackOverflow question so please let me know if I do anything wrong.

我第一次写一个 stackOverflow 问题，所以如果我做错了什么，请告诉我。

I've searched for a few hours now and haven't been able to find a solution to my problem (usually I find the answer hence why this is my first question after using stackoverflow for a few years as a lurker).

我已经搜索了几个小时，但还没有找到我的问题的解决方案（通常我找到了答案，因此为什么这是我在使用 stackoverflow 作为潜伏者几年后的第一个问题）。

Basically I'm trying to write a modified VLOOKUP function that functions similar to VLOOKUP except it return the "next smallest larger" value instead of the default "previous largest smaller" value. I'm aware of the index/match method unfortunately I would need to carefully replace literally thousands of VLOOKUPs manually that already exist in the workbook I'm currently cleaning up at work. Therefore I resorted to writing a VLOOKUPnew function so I could just "find/replace" all the VLOOKUP with VLOOKUPnew.

基本上，我正在尝试编写一个修改后的 VLOOKUP 函数，该函数的功能类似于 VLOOKUP，只是它返回“下一个最小的较大”值而不是默认的“前一个最大的较小”值。不幸的是，我知道索引/匹配方法，我需要仔细地手动替换我目前正在清理的工作簿中已经存在的数千个 VLOOKUP。因此，我求助于编写 VLOOKUPnew 函数，这样我就可以用 VLOOKUPnew“查找/替换”所有 VLOOKUP。

Function VLOOKUPnew(lookup_value As Variant, table_array As Range, _
        col_index_num As Integer, Optional exactMatch As Boolean) As Variant
    Dim row As Integer

    Debug.Print table_array.Address

    With Application
        On Error GoTo NO_ROW
        row = .Match(lookup_value, table_array.Columns(1), 0)
        On Error GoTo 0

        If row = -1 And Not exactMatch Then
            row = .Match(lookup_value, table_array.Columns(1), 1)
            row = row + 1
        End If

        VLOOKUPnew = .index(table_array.Columns(col_index_num), row, 0)
    End With

Exit Function

NO_ROW:
    row = -1
    Resume Next
End Function

And I succeeding in writing the function but hit a snag. Because I declared "table_array" as a Range, vba fails to identify range references to other workbooks

我成功地编写了该函数，但遇到了障碍。因为我将“table_array”声明为范围，所以 vba 无法识别对其他工作簿的范围引用

e.g. "=VLOOKUPnew($A432,'reallyLongFilepath/[filename.xlsx]tablename'!$B$6:$N$35,columnNumber,0),FALSE)" resolves to a #VALUE error

例如“=VLOOKUPnew($A432,'reallyLongFilepath/[filename.xlsx]tablename'!$B$6:$N$35,columnNumber,0),FALSE)”解析为#VALUE错误

The really weird thing is that if I open the file, then the filepath drops out of the formula (becoming just "=VLOOKUPnew($A432,'[filename.xlsx]tablename'!$B$6:$N$35,columnNumber,0),FALSE)") and then my custom function works just fine returning the correct value.

真正奇怪的是，如果我打开文件，那么文件路径就会从公式中删除（变成只是 "=VLOOKUPnew($A432,'[filename.xlsx]tablename'!$B$6:$N$35,columnNumber,0 ),FALSE)") 然后我的自定义函数可以正常工作，返回正确的值。

So my problem is how do I resolve not having to open the other file to use this workbook. I'm not even sure how Excel is passing the address or range to the custom formula so I'm suspecting it's breaking when the filepath is included in the range reference. Is there a way to split the filepath, filename, sheet and address (after it has been passed in)? Or possibly pass it in as a string then easily split it? Or pass it in as something that will correctly identify the range in the different workbook?

所以我的问题是如何解决不必打开其他文件即可使用此工作簿的问题。我什至不确定 Excel 如何将地址或范围传递给自定义公式，所以我怀疑当文件路径包含在范围引用中时它会中断。有没有办法拆分文件路径、文件名、工作表和地址（在传入之后）？或者可能将它作为字符串传入然后轻松拆分它？或者将它作为可以正确识别不同工作簿中的范围的东西传递？

Keep in mind that I'm trying to avoid changing the arguments of the function because I want to do the find/replace trick and that this is for work so there's a restraint on too much change in data layout. Also that the workbook is for other employees to use and I'm just setting it up for use.

请记住，我试图避免更改函数的参数，因为我想做查找/替换技巧，而且这是为了工作，因此对数据布局的太多更改有所限制。此外，该工作簿供其他员工使用，而我只是对其进行了设置以供使用。

Thanks in advance!

提前致谢！

Andrew

安德鲁

回答by chris neilsen

You face quite a dilemma here!

你在这里面临着相当大的困境！

The root problem is that while VLOOKUPcan look into closed workbooks, a Rangeparameter in a UDFcannot. The range reference resolves to an error, so the function call fails with a type mismatch. If you change the table_arrayparameter type to Variantand put a break on the function header, you will see the parameter value as Error 2036.

根本问题是虽然VLOOKUP可以查看关闭的工作簿，但不能查看Range参数UDF。范围引用解析为错误，因此函数调用因类型不匹配而失败。如果您将table_array参数类型更改为Variant并在函数头上放置一个中断，您将看到参数值为Error 2036。

While there are ways to look into closed workbooks, all of them (AFAIK) are quite slow. Since you mention ... I would need to carefully replace literally thousands of VLOOKUPs ...I suspect any solution along these lines would be unacceptably slow.

虽然有多种方法可以查看封闭的工作簿，但所有这些（AFAIK）都很慢。既然你提到... I would need to carefully replace literally thousands of VLOOKUPs ...我怀疑沿着这些路线的任何解决方案都会慢得令人无法接受。

My reccomendation would be to go the INDEX/MATCHroute, and write a VBAmacro to do the formula updates for you.

我的建议是走这INDEX/MATCH条路线，并编写一个VBA宏来为您进行公式更新。