如何使VBA宏在整个工作簿的公式中执行搜索/替换？ [英] How can I have a VBA macro perform a Search/Replace in formulas across an entire workbook?

问题描述

我将Excel工作簿分发给多个用户，并且他们应该将特定的宏文件预先安装在他们的XLSTART文件夹中。

如果他们没有正确安装宏，并且将工作簿发回给我，则根据它的任何公式都包括宏的完整路径，例如：

 'C：\Documents and Settings\richard.tallent\Application Data\ 
 Microsoft\Excel\XLSTART\pcs.xls'！ MyMacroFunction（）
  

我想创建一个快速宏，我可以用来从工作簿中的每个公式。

我已经使用GetSpecialFolder（一个正常工作的外部函数）成功检索了特殊文件夹，但是替换下面显示的自身会抛出应用程序定义或对象定义的错误。

  Public Sub FixBrokenMacroFormulas（）
 Dim badpath As String 
 badpath ='& GetSpecialFolder（AppDataFolder）& \Microsoft\Excel\XLSTART\mymacro.xls’！ 
 Dim Sheet As Worksheet 
对于ActiveWorkbook.Sheets中的每个工作表
调用Sheet.Activate 
调用Sheet.Select（True）
调用Selection.Replace（What：= badpath，Replacement：=，LookIn：= xlFormulas）
''// LookAt：= xlPart，MatchCase：= False，SearchFormat：= False，ReplaceFormat：= False 
下一个
结束Sub 
  

自动搜索和替换不完全是我的伪造，我做错了什么？ >

我已经评论了一些似乎不重要的参数。

解决方案

尝试以下操作：

  Sub FormulaFindAndReplace（phrase As String）
对于每个Sheet_Select在ActiveWorkbook.Worksheets 
 Sheet_Select.Activate 
 Set Found_Link = Cells.Find（what：= phrase，After：= ActiveCell，_ 
 LookIn：= xlFormulas，lookat：= xlPart，searchorder：= xlByRows，_ 
 searchdirection：= xlNext，MatchCase：= False）
虽然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 
下一张Sheet_Select 
 End Sub 
  

我用： / p>

  FormulaFindAndReplace'& GetSpecialFolder（AppDataFolder）& \Microsoft\Excel\XLSTART\mymacro.xls’！ 
  

这被黑客入侵：

a href =http://support.microsoft.com/kb/126093 =nofollow noreferrer>要删除公式链接的宏

I distribute an Excel workbook to a number of users, and they are supposed to have a particular macro file pre-installed in their XLSTART folder.

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".

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.

解决方案

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:

Macros to Delete Formula Links

这篇关于如何使VBA宏在整个工作簿的公式中执行搜索/替换？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！