How to code Excel VBA equivalent of INDIRECT function?

vba excel excel-indirect
Reeggiie picture Reeggiie · Oct 23, 2015 · Viewed 17.8k times · Source

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.

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)

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?

Answer

Excel Hero picture Excel Hero · Oct 23, 2015

You can try this.

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

Replace the INDIRECT functions in your formulas with INDIRECTVBA.

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.

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.

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.

Related questions

Is there a way to crack the password on an Excel VBA Project?

I've been asked to update some Excel 2003 macros, but the VBA projects are password protected, and it seems there's a lack of documentation... no-one knows the passwords. Is there a way of removing or cracking the password on a VBA …

Read More
How do I declare a global variable in VBA?

I wrote the following code: Function find_results_idle() Public iRaw As Integer Public iColumn As Integer iRaw = 1 iColumn = 1 And I get the error message: "invalid attribute in Sub or Function" Do you know what I did wrong? I tried …

Read More
How to use Regular Expressions (Regex) in Microsoft Excel both in-cell and loops

How can I use regular expressions in Excel and take advantage of Excel's powerful grid-like setup for data manipulation? In-cell function to return a matched pattern or replaced value in a string. Sub to loop through a column of data …

Read More