如何让 VBA 宏在整个工作簿的公式中执行搜索/替换？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/534522/
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
How can I have a VBA macro perform a Search/Replace in formulas across an entire workbook?
提问by richardtallent
I distribute an Excel workbook to a number of users, and they are supposedto have a particular macro file pre-installed in their XLSTART folder.
我向许多用户分发了一个 Excel 工作簿，他们应该在他们的 XLSTART 文件夹中预先安装了一个特定的宏文件。
If they don't have the macro installed correctly, and they send the workbook back to me, any formulas depending on it include the full path of the macro, e.g.:
如果他们没有正确安装宏，并且他们将工作簿发回给我，则任何依赖于它的公式都包含宏的完整路径，例如：
'C:\Documents and Settings\richard.tallent\Application Data\
Microsoft\Excel\XLSTART\pcs.xls'!MyMacroFunction()
I want to create a quick macro I can use to remove the improper path from every formula in the workbook.
我想创建一个快速宏，我可以使用它从工作簿中的每个公式中删除不正确的路径。
I've successfully retrieved the special folder using GetSpecialFolder (an external function that is working just fine), but the Replace call itself shown below throws an "Application-defined or object-defined error".
我已经使用 GetSpecialFolder（一个运行良好的外部函数）成功检索了特殊文件夹，但是下面显示的 Replace 调用本身会引发“应用程序定义或对象定义错误”。
Public Sub FixBrokenMacroFormulas()
Dim badpath As String
badpath = "'" & GetSpecialFolder(AppDataFolder) & "\Microsoft\Excel\XLSTART\mymacro.xls'!"
Dim Sheet As Worksheet
For Each Sheet In ActiveWorkbook.Sheets
Call Sheet.Activate
Call Sheet.Select(True)
Call Selection.Replace(What:=badpath, Replacement:="", LookIn:=xlFormulas)
''//LookAt:=xlPart, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Next
End Sub
Automating search and replace isn't exactly my forte, what am I doing wrong?
自动化搜索和替换并不是我的强项，我做错了什么？
I've already commented out some of the parameters that don't seem essential.
我已经注释掉了一些看起来并不重要的参数。
采纳答案by Bravax
Try the following:
请尝试以下操作：
Sub FormulaFindAndReplace(phrase As String)
For Each Sheet_Select In ActiveWorkbook.Worksheets
Sheet_Select.Activate
Set Found_Link = Cells.Find(what:=phrase, After:=ActiveCell, _
LookIn:=xlFormulas, lookat:=xlPart, searchorder:=xlByRows, _
searchdirection:=xlNext, MatchCase:=False)
While UCase(TypeName(Found_Link)) <> UCase("Nothing")
Found_Link.Activate
Found_Link.Formula = Replace(Found_Link.Formula, phrase, "")
Set Found_Link = Cells.FindNext(After:=ActiveCell)
Wend
Next Sheet_Select
End Sub
I called this with:
我这样称呼它：
FormulaFindAndReplace "'" & GetSpecialFolder(AppDataFolder) & "\Microsoft\Excel\XLSTART\mymacro.xls'!"
This was hacked from:
这是从以下地址窃取的：
回答by jpv
There is no need to use such a function, you could instead just use the following statement:
不需要使用这样的函数，您可以使用以下语句：
'Replace with a reference to other workbook
xlBook.ChangeLink Name:= _
"C:\LISTADOS\reporte_gestion_sucursales\modelo_vaciado\modelo_reporte.xlsx", _
NewName:=fileIn2, Type:=xlExcelLinks
