Excel VBA - Replace a text inside cell's formula

4
votes

I have faced a strange thing in Excel VBA coding.

I have a sheet full of formulas, each refers to the cells in other sheets.

This is a sample formula in cell A1, for example:

=IF('General Inputs & Summary'!B6="","",'General Inputs & Summary'!B6)

I want to dynamically change the old tab names in formulas, with new tab names.

When I try this code:

oldStr = "'General Inputs & Summary'"
newStr = "'test'"  
Range("A1").Formula = Replace(Range("A1").Formula, oldStr, newStr)

it results in:

=IF(test!B6="","",test!B6)

instead of:

=IF('test'!B6="","",'test'!B6)

You see, single Quotes are automatically removed, so the new formula fails!

Any solutions for this, please?

2
excelvba
I tried your code and it works fine for me. In your example the removal of the quotes is actually correct, since you only need quotes if the sheet-name contains a space. – MGP
The quotes are automatically removed because test does not contain any spaces and quotes are only needed when there are spaces in your worksheet name. Your formula might fail because of other reasons but not because of the quotes here. Can you be more specific what "fails" mean? What error does it show? Is there a sheet called test? Is calculation turned to xlAutomatic? If not run calculate after changing the formula. – Pᴇʜ

2 Answers

1
votes

Thank you Vityata for your nice explanations, which led me to the answer!

The problem is, the macro was "First" replacing the name in formulas (i.e to test) and THEN creating the new tab "test".

For example, if you set this formula in sheet1, cell A1:

=test2!A1

(Note that you have not created the test2 sheet, yet)

So you obviously get #REF! error.

However, if you THEN create test2 tab (by macro or hand), and come back and check cell A1 formula in sheet1, (also refresh by saving the file), it STILL shows #REF! error, even now test2 tab is really there! (You need to Double click on the cell, and press enter, for that to UPDATE)

As a result, I changed my macro so that it FIRST creates the test tab, and THEN manipulate those formulas.

0
votes

What you can do is to read the formula to a string, replace in the string and pass the string as a new formula like this:

Sub TestMe()

    Dim oldStr$, newStr$

    oldStr = "'General Inputs & Summary'"
    newStr = "'test'"
    newStr = Replace(Range("A1").Formula, oldStr, newStr)
    Range("A1").Formula = newStr

End Sub

To illustrate the ' take a look at this example:

Sub TestMe()

    Dim a As Range
    Dim b As String
    Set a = Range("A1")

    b = "'12"
    a = b

    Debug.Print a 'prints 12
    Debug.Print b 'prints '12

End Sub

The cell in A1 is formatted as text and contains the ', but the printed value is without it.

Edit: In general, the ' is needed only when the worksheet name contains spaces, like this one - General Inputs & Summary. Without spaces in the name, like test, it is not quite needed, thus this example works as well:

Public Sub TestMe()

    Dim a As Range: Set a = Range("A1")
    Dim oldStr$, newStr$

    a.Formula = "=IF('GI S'!B6="","",'GI S'!B6)"

    oldStr = "'GI S'"
    newStr = "'test'"
    Range("A1").Formula = Replace(Range("A1").Formula, oldStr, newStr)

End Sub