How to code Excel VBA equivalent function INDIRECT?

Question

I have many uses of the INDIRECT function in my book, and this causes performance issues. I need to replace them with one that will give me the same results. All deviations are recalculated at any time when something changes, as a result of which the workbook is behind.

I was wondering if there is a way to encode INDIRECT in VBA without actually using the INDIRECT function and remove the function volatility 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 do this in VBA?

Answer 1

You can try this.

:

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

Public Sub FullCalc()
    Application.CalculateFull
End Sub

INDIRECT INDIRECTVBA.

. , INDIRECT , .

: , INDIRECTVBA, . , , .

. . FullCalc.

Answer 2

, .

, ?

, - $AC $9 , INDIRECT .

, INDEX . =INDEX(Range, RowNum, ColNum) . H20: =INDEX(Sheet1!A:Z,ROW()+10,COLUMN()-5), , 1, C30 ( H - 5, 20 + 10 ). , , , .

- / SheetName. UserForm . , VBA / , , - "=INDEX(*!" "=INDEX(" & InputVariable & "!"

, , , , , - .