来自单元格的自定义Excel VBA功能（修改的VLOOKUP）引用不同文件中的范围会给出错误 [英] Custom Excel VBA Function (Modified VLOOKUP) from Cell referring to a range in different file gives an error

问题描述

我第一次写一个stackOverflow问题，所以请让我知道，如果我做错了。

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).

基本上我正在尝试编写一个功能类似于VLOOKUP的修改后的VLOOKUP函数，除了返回下一个较小的值，而不是默认的前一个较小的值。我知道索引/匹配方法不幸的是，我需要仔细地替换已经存在于我正在工作中清理的工作簿中的数以千计的VLOOKUP。因此，我使用了一个VLOOKUPnew函数，所以我可以使用VLOOKUPnew查找/替换所有的VLOOKUP。

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.

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

我成功编写了函数但打了一个sna G。因为我将table_array声明为一个范围，所以vba无法识别其他工作簿的范围引用

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

= VLOOKUPnew（$ A432，'reallyLongFilepath / [filename.xlsx] tablename'！$ B $ 6：$ N $ 35，columnNumber，0），FALSE）解析为#VALUE错误

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

真的很奇怪的是，如果我打开文件，那么文件路径将退出公式（成为只是= VLOOKUPnew（$ A432，'[filename.xlsx] tablename'！$ B $ 6：$ N $ 35，columnNumber，0），FALSE）），然后我的自定义函数正常返回正确的值。

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.

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

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?

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

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.

提前感谢！

Andrew

推荐答案

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

You face quite a dilemma here!

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

The root problem is that while VLOOKUP can look into closed workbooks, a Range parameter in a UDF cannot. The range reference resolves to an error, so the function call fails with a type mismatch. If you change the table_array parameter type to Variant and put a break on the function header, you will see the parameter value as Error 2036.

虽然有办法研究封闭的工作簿，但所有这些工作簿（AFAIK）都相当缓慢。因为你提到 ...我需要仔细替换成千上万的VLOOKUPs ... 我怀疑这些方面的任何解决方案将会是无法接受的缓慢。

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.

我的建议是去 INDEX / MATCH 路由，并写一个 VBA 宏来为您做公式更新。

My reccomendation would be to go the INDEX/MATCH route, and write a VBA macro to do the formula updates for you.

这篇关于来自单元格的自定义Excel VBA功能（修改的VLOOKUP）引用不同文件中的范围会给出错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！