Convert indirect function to direct link

[pjwayuk]

Does anyone have some VBA that does this? I have code that works for a simple indirect, but I can't fathom how to convert when the indirect is within a function and there are multiple references making up the rest of the path

eg if the workbook is in A1 and the sheet name is in A2 etc etc


=SUM(INDIRECT("'["&$A$1&"]"&$A$2&"....

help gratefully received!

Pete

 

[sergioMabres]

Hi Pete
=SUM(INDIRECT("["&A1&"]"&B1&"!"&C1))
Works fine if you have saved the file at list once
What is it that you want to do?
What is what is not working for you?
Cheers
Sergio

 

[pjwayuk]

Thanks Sergio, I was trying to write some vba that goes through the formulae and converts all the indirect formulae to their direct links, even when they're inside formula like sum or vlookup. I have code that will convert a basic =indirect(".....") but can't write something that works if there are functions with indirect inside.

 

[sergioMabres]

Hi Pete,
Here is an idea how to convert Indirect to its value
It is a macro (VBA), before calling the macro please select the range you wish to convert
Here is the code

Sub convert_indirect()
    Dim posOf, endOf As Integer
    Dim eval, cformula As String
    Dim r, c As Range
    Set r = Selection
    For Each c In r
        cformula = c.Formula
        posOf = InStr(1, cformula, "INDIRECT(", vbTextCompare)
        If (posOf > 0) Then
            endOf = InStr(porOf + 1, c.Formula, ")", vbTextCompare)
            eval = Evaluate("=" & Mid(Mid(c.Formula, posOf, endOf - posOf), 10, 100))
            cformula = Mid(cformula, 1, posOf - 1) & eval & Mid(cformula, endOf + 1, 10000)
            c.Formula = cformula
        End If
    Next c
End Sub

Cheers
Sergio

 

[asharpe]

Hi Sergio

The problem with the above solution is that it assumes that the first ")" is the end of the INDIRECT function. This will work for a basic INDIRECT function case. It is however possible that there are other function calls inside INDIRECT the brackets. Eg. to add up say every twelve colums into annual totals you'll have something like:-

=AVERAGE(INDIRECT(ADDRESS(ROW(),COLUMN($Q10)+(D$6-$D$6)*12)&":"&ADDRESS(ROW(),(COLUMN($Q10)+(D$6-$D$6)*12)+11)))

- where Q10 is the start of the monthly data
- D6 contains the first year you want to summarize (D7..D17 the subsequent years)

A slightly more defensive approach would be to ensure that open and closed brackets are matched and to extract up to the last closed bracket. Here is the code:

Sub convert_indirect()
    Dim posOf, endOf, nestLevel As Integer
    Dim eval, cformula As String
    Dim r, c As Range
    Set r = Selection
    For Each c In r
        cformula = c.formula
        posOf = InStr(1, cformula, "INDIRECT(", vbTextCompare)
        If (posOf > 0) Then
            endOf = 1
            nestLevel = 1
            Do While nestLevel > 0
                Select Case Mid(cformula, posOf + 10 + endOf, 1)
                Case "("
                    nestLevel = nestLevel + 1
                Case ")"
                    nestLevel = nestLevel - 1
                End Select
                endOf = endOf + 1
            Loop
            eval = Evaluate("=" & Mid(c.formula, posOf + 9, endOf))
            cformula = Mid(cformula, 1, posOf - 1) & eval & Mid(cformula, posOf + endOf + 9 + 1)
            c.formula = cformula
        End If
    Next c
End Sub

Andre

 

[AmFa]

Hi,

First, thank you Andre as your code has proven very helpful. I'm wondering if it's possible to make it even more defensive, that is if it could be written to work for formulas that have indirect references within indirect references. like here:
=INDIRECT("'["&$B21&"]"&$C21&"'!"&XLOOKUP(MATCH($D21,INDIRECT("'["&$B21&"]"&$C21&"'!10:10"),0),$AD:$AD,$AE:$AE)&MATCH(INDIRECT(O$18&"19"),INDIRECT("'["&$B21&"]"&$C21&"'!R:R"),0))

When using your code above, it seems that it runs into a Type Mismatch error at line 22:
cformula = Mid(cformula, 1, posOf - 1) & eval & Mid(cformula, posOf + endOf + 9 + 1)

I realise it's a very convoluted query for a very convoluted formula, but if you could help that would be great.
Thank you!