[SOLVED] How to code Excel VBA equivalent of INDIRECT function?

How to code Excel VBA equivalent of INDIRECT function?

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?

Solution

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.