如何编写Excel VBA等效的INDIRECT函数？ [英] How to code Excel VBA equivalent of INDIRECT function?

问题描述

我的工作簿中有很多使用INDIRECT功能，导致性能问题。我需要用一些能给我相同结果的东西来代替它们。所有的INDIRECTS在任何事情发生变化时都会重新计算，导致工作簿滞后。

I have many uses of the INDIRECT function in my workbook, and it is causing performance issues. I need to replace them with something that will give me the same results. All the INDIRECTS recalculate anytime anything is changed, causing the workbook to lag.

我想知道是否有一种在VBA中编码INDIRECT的方法，而不实际使用INDIRECT函数，并删除代码中功能的波动。

I was wondering if there is a way to code INDIRECT in VBA without actually using the INDIRECT function, and take away the volatility of the function in the code.

 =INDIRECT("'" & $AC$9 & "'!" & AC26)

这是一个例子。我需要删除INDIRECT，但为这个单元格获得相同的结果。有没有办法在VBA中完成这个工作？

This is an example. I need to remove INDIRECT but get the same results for this cell. Is there a way to accomplish this in VBA?

推荐答案

你可以试试这个。

将以下例程放在标准代码模块中：

Place the following routines in a standard code module:

Public Function INDIRECTVBA(ref_text As String)
    INDIRECTVBA = Range(ref_text)
End Function

Public Sub FullCalc()
    Application.CalculateFull
End Sub

将 INDIRECT 函数替换为 INDIRECTVBA 。

这些将是静态的。如果您的工作簿缓慢，因为您的INDIRECTs不断评估，那么这将结束。

These will be static. If the slowness of your workbook is because your INDIRECTs are constantly evaluating, then this will put an end to that.

重要信息：所有单元格包含使用 INDIRECTVBA 的公式将是静态的。每个公式将在您确认时计算，但在先例更改时不会重新计算。

IMPORTANT: all cells that contain a formula using INDIRECTVBA will be static. Each formula will calculate when you confirm it, but it will not recalculate when precedents change.

然后，您将需要一种方法来迫使他们在方便的时间重新计算。你可以从功能区做到这一点。或者，您可以运行 FullCalc 。

You will then need a way to force them to recalculate at a convenient time. You can do that from the Ribbon. Or, you can run FullCalc.

这篇关于如何编写Excel VBA等效的INDIRECT函数？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！