4

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?

Reeggiie
  • 782
  • 7
  • 16
  • 36
  • 2
    you could always set the calculation to manual, then it will only calculate when you tell it to. – Scott Craner Oct 23 '15 at 15:02
  • @ScottCraner Yes this would help, but for this task, I have to actually replace indirect – Reeggiie Oct 23 '15 at 15:05
  • 1
    I think you'd have to use code to actually alter the formulas based on a cell. Any formula version, including UDFs, would have to be volatile. – Rory Oct 23 '15 at 15:06
  • Are there any other solutions besides vba that anyone could think of? – Reeggiie Oct 23 '15 at 15:14
  • 2
    If all data must remain live, any code would need to be tied to the worksheet change event and/or worksheet calculate event. This would mean that the code would run each time a cell changed and would also cause a lag, maybe a little faster but also possibly longer. – Scott Craner Oct 23 '15 at 15:15
  • There are two things that make INDIRECT slower than other functions - (1) it is volatile, meaning it calculates every time any cell changes (because it can't tell what its own precedents are). This could only be solved with VBA if you had a fairly rigorous system to track what each precedent of each INDIRECT formula was. This would be a massive project and wouldn't really help much. The better solution is likely to do as @ScottCraner suggests and just turn calc's to manual. – Grade 'Eh' Bacon Oct 23 '15 at 15:17
  • (2) Indirect needs to calculate the address of the cells and then pull the data, instead of just pulling data. This wouldn't change with a VBA solution. – Grade 'Eh' Bacon Oct 23 '15 at 15:17

3 Answers3

5

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.

Excel Hero
  • 14,253
  • 4
  • 33
  • 40
  • I will give this a shot and let you know how it goes – Reeggiie Oct 23 '15 at 15:42
  • a question - does this require calculations to be set to manual? – Scott Holtzman Oct 23 '15 at 16:09
  • 1
    @ScottHoltzman No, it does not. The UDF is non-volatile, so everything else will calculate automatically (if calculation is set to Automatic), but the formulas using the UDF will be static. – Excel Hero Oct 23 '15 at 16:15
  • Thanks ExcelHero. I just refreshed my understanding of volatile UDFs [here](http://www.excel-easy.com/vba/examples/volatile-functions.html) for anyone else interested in doing the same. – Scott Holtzman Oct 23 '15 at 16:30
  • 1
    @ScottHoltzman This is better: https://fastexcel.wordpress.com/2012/02/02/writing-efficient-vba-udfs-part-10-volatile-functions-and-function-arguments/ – Excel Hero Oct 23 '15 at 16:38
  • @ExcelHero What would you do in the ribon to force the calculation at a convenient time? – Reeggiie Oct 23 '15 at 17:26
  • You would do... Formulas --> Calculation --> Calculate Now – Excel Hero Oct 23 '15 at 17:31
  • @ExcelHero I put the code into a module, the cell now looks like this: =INDIRECTVBA( "'" &$AC$9 & "'!" & AC26), and I am getting a "Formula contains unrecognized text" error. and the cell evaluated to #NAME? Is there something else I need to do or edit? – Reeggiie Oct 23 '15 at 17:36
  • @Reeggiie `I put the code into a module`... did you make sure it was a Standard Code Module in the same workbook as you want to use it in? If you have more than one workbook open at the same time, it is easy to put code into the wrong workbook. – Excel Hero Oct 23 '15 at 17:41
  • @Reeggiie Does cell AC9 have a value that exactly matches a sheet name? – Excel Hero Oct 23 '15 at 17:42
  • @Reeggiie Does cell AC26 have a value that is a valid cell address? – Excel Hero Oct 23 '15 at 17:43
  • @ExcelHero yes, yes, and yes. The formula bar recognizes the function. cells AC9 and AC26 are all valid. – Reeggiie Oct 23 '15 at 17:48
  • @ExcelHero The cells im referencing are valid, they work when I use indirect. The only thing im changing on the cell is indirect -> indirectvba, which is evaluating to #NAME? – Reeggiie Oct 23 '15 at 17:51
  • @ExcelHero It is a secure file so I cant send it out. ill keep messing around and see if I can get it to work sense it works on your end. – Reeggiie Oct 23 '15 at 17:56
  • @Reeggiie I uploaded a small sample workbook where you can see this solution in action: http://excelhero.com/samples/Reeggiie_excelhero.xlsm – Excel Hero Oct 23 '15 at 18:30
0

Was going to add this as a comment, but my thought process got too long.

What is the context of the problem you are trying to solve?

I am guessing you are using some kind of data validation drop-down menu in $AC$9 to select a sheet name and then all your INDIRECT formulas are providing a mirror image of a particular section of the user-specified worksheet.

If that is the case then you might consider using INDEX as an alternative. It is written as =INDEX(Range, RowNum, ColNum) E.g. if you put this in H20: =INDEX(Sheet1!A:Z,ROW()+10,COLUMN()-5) then it would reflect whatever is in sheet 1, cell C30 (H - 5 columns, 20 + 10 rows). Of course, you don't have to offset anything if you don't want to, I just wanted to demonstrate that as an option.

Now, the trickier part would still remain - assigning/updating the SheetName variable. This could be done with a UserForm instead of typing in a value in a particular input cell. For example, you could have VBA provide an input box/dropdown menu for the user to select one of the available sheet names, then take that input and use it in a quick find and replace instruction - searching for "=INDEX(*!" and replacing with "=INDEX(" & InputVariable & "!"

I've made a few assumptions about your dataset and what you're trying to achieve, so it might not be the ideal solution, but perhaps something to think about.

CactusCake
  • 986
  • 3
  • 12
  • 32
-1

The solution to volatility with the Indirect function (typical in multi-version cross platform use and partitioning to run Windows on Mac) can be absorbed by splitting its various functions with a pseudonym for Indirect I have named "Implied":

Public Function Implied(Varient)
' CREDIT: Stephen L. Rush

    On Error Resume Next

    If IsError(Range(Varient)) Then
        If IsError(Find(Varient, "&")) Then
           'Not Range, is Indirect.  "A" & Match() Style (where Match() = row).
            Implied = WorksheetFunction.Indirect(Varient)
        Else
           'Not a Range, not Indirect. "A" & B99 Reference (where B99 = row).
            Implied = Range(Left(Varient, Find(Varient, "&") - 1) & Range(Right(Varient, Len(Varient) - Find(Varient, "&"))))
        End If
    Else
       'Is pure Range
        Implied = Range(Varient)
    End If

   '[On Error GoTo 0] Conflicts with use as formula

End Function
Emmett L Brown
  • 1
  • 1