removing references in excel formulas

[eager2know]

i am looking for a piece of vb code which replaces all references in a cell formula. the references may be to other cells, other worksheets or other workbooks, so that the new formula will just have variables. any help will be appreciated.

[arnie1066]

(in module)

private sub My_Sub()
dim tmpString as string
 
tmpString = ReplaceText(range("A1").formula, "[Otherworkbook.xls]Sheet1!", "")
end sub
 
Function ReplaceText(SourceString As String, ReplaceAllofThese As String, WithThese As String)
strModulePart = "Replacing Text Module"
    Dim temp As Variant
    temp = Split(SourceString, ReplaceAllofThese)
    ReplaceText = Join(temp, WithThese)
End Function

This removes references to other workbooks/sheets in a specified cell,
you would have to do a loop to do them all.


eg

for x = 1 to 20     'NO_OF_ROWS_TO_CHECK
    for y = 1 to 30       'NO_OF_COLUMNS_TO_CHECK
        tmpString = ReplaceText(cells(y, x).formula, "[Otherworkbook.xls]Sheet1!", "")
    next y
next x

[eager2know]

thanks a lot. i was actually looking for a piece of code which removes references inside the same worksheet as well as other work sheets/workbooks. so it is supposed to detect a cell reference(i.e. A19) / or worksheet reference inside a formula and go on resolving it until there is no more references.

split function certainly helps but can i use wild cards in delimiters?

[Webtest]

Eager:

What you are trying to do is going to be very tricky depending on the range of cells used on all of the sheets, and the number for substitutions you are trying to implement.

As an example, if one of your "variables" replaces a reference to cell "A1", you can't use a simple "InStr" because you need to differentiate that from cells "A10", "A11", "A199", "AA1", "BA123", etc., remembering that there can be up to 256 columns and 65,536 rows in the used range of a Worksheet. Also, remember that "$A$19", "$A19", and "A$19" all point to the same cell.

Here is a process idea that might help you for "A19" - say you want to replace it with a reference named "SalesTax":

1) Iterate through all cells in the UsedRange/ActualUsedRange of the sheet.
2) You want the aCell.Formula String to operate on.
3) Do an Instr on the String for "A19"
4) If found, make sure the character position BEFORE is NOT A-Z, and make sure there are no numerals 0-9 in the character position AFTER.
5) If your candidate formula is still valid, REPLACE the substring "A19" with "SalesTax". (REPLACE is not available in older versions of VBA - '97).

P.S. I think I read your post wrong. Are you trying to pull apart a SINGLE cell formula and replace ALL of the direct references in it with known variables? How many 'known variables' are candidates for this replacement process?