Excel Macro to Convert indirect function to direct links

[Paula]

I am looking for a visual basic macro that would do the following:

Convert an indirect function into a direct link. I have a variety of
indirect functions in a file that link to external files, and as the
indirect function is volatile, these fields "#ref" out when the files
are closed. Rather than redo all of these to be direct links, I would
like a macro that goes through and converts the indirects to direct
links. Since all the information is on the sheet and in the formula,
it seems like it should be straightforward, but is beyong my simple
VBA programming abilities.


For greater clarity, I am using the following types of indirect
functions:

SUM(INDIRECT("'"&E$8&E$9&"'"&"!"&E$10&$A18&":"&E$11&$A18))

Where E$8 is filename (e.g. "File")
E$9 is the sheet name (e.g. "Sheet1")
E$10 is the beginning column (e.g. "D")
E$11 is the ending column (e.g. "F")
$A18 is the row (e.g. 10)

So the macro would convert the above macro to:

SUM('[File]Sheet1'!$D$10:$F$10)

I would like this macro to be made generic so it applies to different
indirect functions (e.g. some are sums, some are single links), and to
be set up so I can run it for all indirects that are currently
selected (e.g. I highlight a range of cells, hit the macro shortcut
key, and all indirects in those cells are converted to direct links,
and cells with other formulas are not affected.

I put this in as a google answers post too, if anyone wants the money!

http://answers.google.com/answers/threadview?id=711588

Thanks in advance

PC

 

[stevenilsen]

the following code should do the trick. Dump in module, select range of cells you would like converted, run code. This code will convert all formulas that have indirect functions within them.

Sub ConvertIndirectFormulas()
'variables
Dim TheFormula As String
Dim iPosition As Integer
Dim afterIndirect As String
Dim refLength As String
Dim iRef As String
Dim beforeIndirect As String
Dim afterRef As String

Application.Calculation = xlCalculationManual
Application.DisplayStatusBar = True

numCells = Selection.Cells.Count

C = 1
For Each Cell In Selection

Application.StatusBar = "Converting Indirect Formulas..." & Format(C / numCells, "0%")

' Cell.Activate
TheFormula = Cell.formula
Do While InStr(TheFormula, "INDIRECT") > 0

'get the cell reference
iPosition = InStr(TheFormula, "INDIRECT")
afterIndirect = Mid(TheFormula, iPosition + 10, Len(TheFormula))
refLength = InStr(afterIndirect, """") - 1
iRef = Mid(TheFormula, iPosition + 10, refLength)

'replace the formula
beforeIndirect = Mid(TheFormula, 1, iPosition - 1)
afterRef = Mid(afterIndirect, refLength + 3, Len(afterIndirect))
TheFormula = beforeIndirect & iRef & afterRef
Loop

Cell.formula = TheFormula
C = C + 1

Next Cell

Application.Calculation = xlCalculationManual
Application.StatusBar = False

End Sub

 

[Nicky005]

Hey,
I tried to use this code but it doesn't work. I suppose it is due to the fact that I use a concatenate function between the indirect brackets.
=INDIRECT(CONCATENATE($A$16;H$17;$G18))
How could I convert this into a direct link?