Excel VBA - 替换单元格公式中的文本

I have faced a strange thing in Excel VBA coding.

我在Excel VBA编码中遇到了一件奇怪的事情。

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:

这是单元格A1中的示例公式,例如:

=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 个解决方案

#1

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.

A1中的单元格被格式化为文本并包含',但打印的值没有它。

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:

编辑:通常,只有当工作表名称包含空格时才需要',例如,通用输入和摘要。名称中没有空格,如test,它不是很需要,因此这个例子也可以:

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

#2

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

感谢Vityata的精彩解释,这让我得到了答案!

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:

例如,如果在sheet1中设置此公式,则单元格A1:

=test2!A1

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

(请注意,您还没有创建test2表)

So you obviously get #REF! error.

所以你显然得到#REF!错误。

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)

但是,如果你那么创建test2选项卡(通过宏或手),并返回并检查sheet1中的单元格A1公式,(也通过保存文件刷新),它仍然显示#REF!错误,即使现在test2选项卡确实存在! (您需要双击单元格,然后按Enter键,以便更新)

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

结果,我更改了我的宏,以便它首先创建测试选项卡,然后操纵这些公式。

#1