vba PULL 函数加速
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7325335/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
vba PULL function speed up
提问by tuj
I am using the PULL function (http://groups.google.com/group/microsoft.public.excel.worksheet.functions/msg/e249f6c074a3adfd?pli=1) which is like INDIRECT except that it can reference a closed workbook.
我正在使用 PULL 函数（http://groups.google.com/group/microsoft.public.excel.worksheet.functions/msg/e249f6c074a3adfd?pli=1），它类似于 INDIRECT ，除了它可以引用关闭的工作簿。
The PULL function works fine, except that it creates an excel instance every time it is called and that makes calculation of the workbook with many calls extremely slow.
PULL 函数工作正常，除了它每次被调用时都会创建一个 excel 实例，这使得具有许多调用的工作簿的计算速度非常慢。
Is there a way to re-write this function such that it will run significantly faster?
有没有办法重写这个函数，让它运行得更快？
回答by Harlan Grove
Pull is SLOW. It's intentionally nonvolatile. It could be improved by making the Application instance effectively static (I'd make it a private, module-level variable), but that could lead to reentrance issues which I didn't want to tackle. At that point, it'd be better to make the guts a class and the udf merely a wrapper around one of the methods, again something I didn't want to tackle.
拉动很慢。它是故意非易失性的。可以通过使 Application 实例有效地静态化（我将其设为私有的模块级变量）来改进它，但这可能会导致我不想解决的重入问题。在这一点上，最好让胆量成为一个类，而 udf 只是其中一种方法的包装器，这也是我不想解决的问题。
If you have many formulas calling pull and most refer to different workbooks, you may be better off using Laurent Longre's MOREFUNC.XLL add-in, which provides a function named INDIRECT.EXT. It also uses a second Excel application instance to dereference ranges in closed workbooks, but because it's an XLL rather than VBA, it may be a bit faster.
如果您有许多调用 pull 的公式并且大多数公式引用不同的工作簿，则最好使用 Laurent Longre 的 MOREFUNC.XLL 加载项，它提供了一个名为 INDIRECT.EXT 的函数。它还使用第二个 Excel 应用程序实例来取消引用封闭工作簿中的范围，但因为它是 XLL 而不是 VBA，所以它可能会快一点。
OTOH, if you're referring to multiple cells in a few workbooks, at the cost of storage you could use pull as an array formula, pulling in large ranges in a single call.
OTOH，如果您指的是几个工作簿中的多个单元格，则以存储为代价，您可以使用 pull 作为数组公式，在一次调用中拉入大范围。
回答by Fionnuala
Is there a reason you do not wish to use:
您是否有不想使用的原因：
='C:\Full\Path\To\ExcelFile\[Book1.xls]Sheet1'!$B
回答by Jon49
It looks like they may be opening a workbook every time for some reason when using the Execute4Macro. I haven't use it much but maybe you could experiment with it and try something directly like this (instead of using the persons function):
看起来他们在使用 Execute4Macro 时可能出于某种原因每次都打开工作簿。我没有经常使用它，但也许你可以试验它并直接尝试这样的东西（而不是使用 people 函数）：
arg = "'" & wbPath & "[" & wbName & "]" & _
wsName & "'!" & Range(cellRef).Address(True, True, xlR1C1)
GetInfoFromClosedFile = ExecuteExcel4Macro(arg)
As found at http://www.exceltip.com/st/Read_information_from_a_closed_workbook_using_VBA_in_Microsoft_Excel/473.html
如在http://www.exceltip.com/st/Read_information_from_a_closed_workbook_using_VBA_in_Microsoft_Excel/473.html 中找到
There are functions that work with closed workbooks so maybe you could use something similar to that too, like, SUMPRODUCT, I believe can operate on a closed workbook, or maybe one of the Database functions.
有些功能适用于封闭的工作簿，所以也许您也可以使用类似的东西，例如 SUMPRODUCT，我相信可以对封闭的工作簿进行操作，或者可能是数据库功能之一。
I'm not exactly sure what you are trying to do so you'll have to be more specific if you want a more specific answer.
我不确定您要做什么，因此如果您想要更具体的答案，则必须更具体。
Try some of the solutions offered here: http://www.mrexcel.com/forum/showthread.php?t=14353
尝试这里提供的一些解决方案：http: //www.mrexcel.com/forum/showthread.php?t=14353
回答by CharlesChuckieCharles
A small macro to change the LINK entry is all that is required :
只需要一个用于更改 LINK 条目的小宏即可：
ActiveWorkbook.ChangeLink Name:="C:\Temp\DeleteMe1.xlsx", NewName:= _
"C:\Temp\DeleteMe.xlsx", Type:=xlExcelLinks
