vba 在 Excel 中，使用“Range.Replace”方法替换字符，但不影响公式
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/34626788/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
In Excel, use "Range.Replace" method to substitute characters, but do not affect formulae
提问by Christian Geiselmann
I want to create a macro for Excel (2010) to substitute characters of the Latin alphabet with their equivalents from the Cyrillic alphabet.
我想为 Excel (2010) 创建一个宏，用西里尔字母表中的等效字符替换拉丁字母表的字符。
At first glance, this seems to be quite easy, e.g. like this:
乍一看，这似乎很容易，例如像这样：
ActiveSheet.Range("A1:Z500").Replace "sht", ChrW(1097) '--> щ
ActiveSheet.Range("A1:Z500").Replace "Sht", ChrW(1095) '--> Щ
'And so on for all relevant characters and character combinations
However, when I run this on my active worksheet, not only the cell values are affected but also formulae. Which makes them worthless, because e.g. Sum(B1:B3) will become Сум(Б1:Б3) [i.e. using Cyrillic letters] which, for Excel, is gibberish.
但是，当我在活动工作表上运行它时，不仅单元格值会受到影响，而且公式也会受到影响。这使得它们毫无价值，因为例如 Sum(B1:B3) 将变成 Сум(Б1:Б3) [即使用西里尔字母]，这对于 Excel 来说是胡言乱语。
Therefore the question: is there a way to tell Excel to use the Replace method only on cell values, not on formulae?
因此，问题是：有没有办法告诉 Excel 仅对单元格值而不是公式使用 Replace 方法？
Note: a dirty workaround could be to include a procedure that on each cell in the range first checks if it starts with an "=", and if so, leave the cell contents unchanged. But perhaps there is a better, less home-brewed way?
注意：一个肮脏的解决方法可能是在范围内的每个单元格上包含一个过程，首先检查它是否以“=”开头，如果是，则保持单元格内容不变。但也许有更好、更少自制的方法？
回答by Axel Richter
Range.Replacewill always search in formulas.
Range.Replace将始终在公式中搜索。
There is a HasFormulaproperty in the Rangeobject. But for using this you must iterate over all cells in the given Range. If this Rangeis quite big then this will take quite long if you are trying Replacein each of the cells. So I would only trying Replaceif the cell is not empty, is not numeric and not has a formula.
对象中有一个HasFormula属性Range。但是要使用它，您必须遍历给定Range. 如果它Range很大，那么如果您Replace在每个单元格中尝试，这将需要很长时间。所以我只会Replace在单元格不为空、不是数字并且没有公式的情况下尝试。
Sub test()
Dim oRange As Range
For Each oRange In ActiveSheet.Range("A1:Z500")
If Not IsEmpty(oRange) And Not IsNumeric(oRange.Value) And Not oRange.HasFormula Then
oRange.Replace What:="sht", Replacement:=ChrW(1097), MatchCase:=True
oRange.Replace What:="Sht", Replacement:=ChrW(1065), MatchCase:=True
End If
Next
End Sub
Oh and you must set MatchCaseif the replace shall be case sensitive.
哦，您必须设置MatchCase替换是否区分大小写。
Second approach:
第二种方法：
You also could use the VBAReplaceinstead of Range.Replaceas showed in the other answer. But not with Range.Formulabut with Range.Value.
您也可以使用其他答案中所示的VBAReplace代替 Range.Replace。但不与Range.Formula但与Range.Value。
This could be faster.
这可能会更快。
Sub test2()
Dim oRange As Range
For Each oRange In ActiveSheet.Range("A1:Z500")
If Not IsEmpty(oRange) And Not IsNumeric(oRange.Value) And Not oRange.HasFormula Then
oRange.Value = Replace(oRange.Value, "sht", ChrW(1097), , , vbBinaryCompare)
oRange.Value = Replace(oRange.Value, "Sht", ChrW(1065), , , vbBinaryCompare)
End If
Next
End Sub
回答by grominet
You have to look in .Formula properties, default Range result give the .Value (ie result), not value.
您必须查看 .Formula 属性，默认范围结果给出 .Value（即结果），而不是值。
I will suggest use a loop like :
我会建议使用像这样的循环：
For Each cell In ActiveSheet.Range("A1:Z500")
cell.Formula = Replace(cell.Formula, "sht", ChrW(1097))
cell.Formula = Replace(cell.Formula, "Sht", ChrW(1095))
Next
