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.

---

Clarification of Question by nowhitenoise-ga on 24 Mar 2006 14:40 PST

To clarify.  At a minimum, I need it to work for the listed indirect,
but it still needs to work across the different columns / rows.

Ie the macro can't just convert every indirect using the same inputs
on file/sheet/row/column, for each cell in the sheet, it would have to
convert based on the appropriate relative link for the input to the
indirect (e.g. E$11 might be "F", but F$11 might be "G", same for the
other inputs.)

Thanks,

Matt

---

Request for Question Clarification by maniac-ga on 11 Apr 2006 18:13 PDT

Hello Nowhitenoise,

Have the comments answered your question?

If so, I suggest you close the question so you are not charged for the
freely offered answer.
If not, please clarify the problem you are having / the solution that is needed.

Thanks.

  --Maniac

Hi nowhitenoise,

I have tried to solve your problem. Following is a solution to conver
indirect functions to direct.

Example:
In this example macro will convert an indirect function 
[=IF(COUNT(INDIRECT("'"&E$3&E$4&"'"&"!"&E$5&$A13&":"&E$6&$A13))>0,SUM(INDIRECT("'"&E$3&E$4&"'"&"!"&E$5&$A13&":"&E$6&$A13)),0)]
to direct one.
The converted formula will look like,
[=IF(COUNT([book2.xls]sheet2!D10:F10)>0,SUM([book2.xls]sheet2!D10:F10),0)]

The code is generic to all the functions with INDIRECT.

Code:
--------------------------------------------------------------------------------
Sub main()
sstring = Sheet1.Cells(1, 1).Formula
sstring = Trim(sstring)
While InStr(sstring, "INDIRECT(") > 0
    sstring = test(sstring)
Wend
Sheet1.Cells(1, 2) = sstring
End Sub
Function test(sstring) As String
sstring = Mid(sstring, 2, Len(sstring) - 1)
start_pos = InStr(1, sstring, "INDIRECT(") + 9
start1_pos = InStr(1, sstring, "INDIRECT(")
head = Mid(sstring, 1, start1_pos - 1)
'MsgBox head
end_pos = InStr(start_pos, sstring, ")")
start2_pos = end_pos
tail = Mid(sstring, start2_pos + 1, Len(sstring) - start2_pos)
'MsgBox tail
'MsgBox start_pos
'MsgBox end_pos
sstring = Mid(sstring, start_pos, end_pos - start_pos)
sstring = Replace(sstring, """", "")
sstring = Replace(sstring, "$", "")
sstring = Replace(sstring, "&", "+")
'MsgBox Sstring
Cpbooktext = sstring + "+"
sstring = ""
Do Until Trim(Cpbooktext) = ""
    last = InStr(Cpbooktext, "+")
    first = Trim(Mid(Cpbooktext, 1, last - 1))
    'MsgBox first
    If first = "'" Or first = "!" Or first = ":" Then
        sstring = sstring + first
    Else
        'MsgBox Sheet1.Range(first)
        sstring = Trim(sstring) + Trim(conv(Sheet1.Range(first)))
        
    End If
    Cpbooktext = Trim(Mid(Cpbooktext, last + 1, Len(Cpbooktext) - last))
Loop
'MsgBox sstring
test = "=" + Trim(head) + Trim(sstring) + Trim(tail)
End Function

Function conv(x) As String
If IsNumeric(x) Then
    x = Str(x)
Else
    x = x
End If
conv = x
End Function
--------------------------------------------------------------------------------

You can contact me on: nayan.ambali@gmail.com

Regards,
Nayan

Hi Boss,

  I think i have given you the correct ANSWER. In this MACRO if write
your Indirect Ref. Function in Sheet1.Cells (1, 1), then you get
result in Sheet1.Cells (1, 2).

  And one more thing, it can be used as function and can be performed
on selected cells as you wish, If you need that part also, tell me it
doesn?t take 5minutes


Regards
Nayan GA

Code:
--------------------------------------------------------------------------------
Sub main()
sstring = Sheet1.Cells(1, 1).Formula
sstring = Trim(sstring)
While InStr(sstring, "INDIRECT(") > 0
    sstring = test(sstring)
Wend
Sheet1.Cells(1, 2) = sstring
End Sub
Function test(sstring) As String
sstring = Mid(sstring, 2, Len(sstring) - 1)
start_pos = InStr(1, sstring, "INDIRECT(") + 9
start1_pos = InStr(1, sstring, "INDIRECT(")
head = Mid(sstring, 1, start1_pos - 1)
'MsgBox head
end_pos = InStr(start_pos, sstring, ")")
start2_pos = end_pos
tail = Mid(sstring, start2_pos + 1, Len(sstring) - start2_pos)
'MsgBox tail
'MsgBox start_pos
'MsgBox end_pos
sstring = Mid(sstring, start_pos, end_pos - start_pos)
sstring = Replace(sstring, """", "")
sstring = Replace(sstring, "$", "")
sstring = Replace(sstring, "&", "+")
'MsgBox Sstring
Cpbooktext = sstring + "+"
sstring = ""
Do Until Trim(Cpbooktext) = ""
    last = InStr(Cpbooktext, "+")
    first = Trim(Mid(Cpbooktext, 1, last - 1))
    'MsgBox first
    If first = "'" Or first = "!" Or first = ":" Then
        sstring = sstring + first
    Else
        'MsgBox Sheet1.Range(first)
        sstring = Trim(sstring) + Trim(conv(Sheet1.Range(first)))
        
    End If
    Cpbooktext = Trim(Mid(Cpbooktext, last + 1, Len(Cpbooktext) - last))
Loop
'MsgBox sstring
test = "=" + Trim(head) + Trim(sstring) + Trim(tail)
End Function

Function conv(x) As String
If IsNumeric(x) Then
    x = Str(x)
Else
    x = x
End If
conv = x
End Function
--------------------------------------------------------------------------------