vba Excel：如何加快查找和替换过程？每个工作簿更改 32,000 个链接

Excel: How can I speed up a Find & Replace process? Changing 32,000 links per workbook

提问by RocketGoal

I'm comparing a lot of data for over 30 categories. Each category workbook is saved into 'MyFolder' with it's own distinctive name (the category). The data in the workbook is found on a sheet with the same name as the category: [Category.xls]CategoryYear_Final!

我正在比较 30 多个类别的大量数据。每个类别工作簿都以自己独特的名称（类别）保存到“MyFolder”中。工作簿中的数据位于与类别同名的工作表上：[Category.xls]CategoryYear_Final！

It seemed best to create a standard template that references the data and which produced the required graphs and layouts. It all worked well. The it was time to start the engines and make graphs for all the categories by amending the reference names...

似乎最好创建一个标准模板来引用数据并生成所需的图形和布局。这一切都运作良好。是时候启动引擎并通过修改参考名称为所有类别制作图表了……

Using FIND & REPLACE it's taking over 20 mins each workbook as there are over 32,000 places (two per cell) where the updates must take occur. Crikey!

使用 FIND & REPLACE 每个工作簿需要 20 多分钟，因为有超过 32,000 个地方（每个单元格两个）必须进行更新。克里基！

Any sugestions on how this could possibly be done more quickly, or do I just need to settle in for a solid 20 hours of watching Excel struggle through.

关于如何更快地完成这项工作的任何建议，或者我是否只需要花 20 小时的时间观看 Excel 挣扎。

Many thanks Michael.

非常感谢迈克尔。

回答by Joel Goodwin

This is what I would do. Before doing the update:

这就是我要做的。在进行更新之前：

Application.EnableEvents = False
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

After doing the update:

完成更新后：

Application.EnableEvents = True
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Application.CalculateFull

You might want to make sure that if there's a problem with your update, you catch the error and go through the re-enable routine anyway. For example, if you error and fail to re-enable Excel ScreenUpdating, it makes the session unusable to a user (although it can be fixed through the VBA editor immediate window if you know what to do).

您可能希望确保如果您的更新出现问题，您会发现错误并执行重新启用例程。例如，如果您出错并且无法重新启用 Excel ScreenUpdating，则会导致用户无法使用会话（尽管如果您知道该怎么做，可以通过 VBA 编辑器即时窗口修复它）。

回答by excelly cellyson

Works in Excel 2010. This is super-fast! Made 851000 replacements in approximately 10 seconds.

在 Excel 2010 中工作。这是超快的！在大约 10 秒内完成了 851000 次更换。

Sub Macro1()

    Application.EnableEvents = False
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

    ' fill your range in here
    Range("E3:CN9254").Select
    ' choose what to search for and what to replace with here
    Selection.Replace What:="", Replacement:="0", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

    Application.EnableEvents = True
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    Application.CalculateFull

End Sub

回答by Anthony -GISCOE-

Likely you need to disable calculations (as mentioned) and possibly other things like screen updates as well.

您可能需要禁用计算（如上所述）以及其他可能的内容，例如屏幕更新。

回答by JYelton

To disable calculations:

要禁用计算：

If you have Excel 2007 or later (with the Ribbon):

如果您有 Excel 2007 或更高版本（使用功能区）：

• Go to the Ribbon menu (the circle thing at top left).
• Click "Excel Options" near the bottom right.
• Click the "Formulas" category
• Under calculation options, select "Manual"

• 转到功能区菜单（左上角的圆圈）。
• 单击右下角附近的“Excel 选项”。
• 单击“公式”类别
• 在计算选项下，选择“手动”

回答by Jon Helgason

Setting calculations to manual will not help, nor will writing a VBA code.

将计算设置为手动将无济于事，编写 VBA 代码也无济于事。

The only thing you need to do is to keep all relevant files open.

您唯一需要做的就是保持所有相关文件处于打开状态。

For Example:
Say you have 3 files: A, Band C. Ccollects info from A, but now wants info from Binstead.

例如：
假设您有 3 个文件：A、B和C。C从A收集信息，但现在想要从B获取信息。

You update the link with find/replace.

你用 更新链接find/replace。

The solution is to keep both Band Copen at the same time, then it works flawlessly.

解决方案是同时保持B和C处于打开状态，然后它就可以完美运行。

Jon Helgason
Statistics Iceland

Jon Helgason
冰岛统计局

回答by mll

Based on excelly cellyson's code, here's a hopefully quite seasoned macro that I made for myself. Works on Excel 2016 (and there's no reason it shouldn't work in previous versions).

基于 excelly cellyson 的代码，这是我为自己制作的一个很有经验的宏。适用于 Excel 2016（并且没有理由在以前的版本中不起作用）。

Option Explicit

Sub FastReplace(Optional CalculateAfterReplace As Boolean = True)

Dim SelectedRange As Range
Dim What As String, Replacement As String

    'Let's set the 3 input data in place, and allow the user to exit if he hits cancel (or if he wants to look for an emprty string)
    Set SelectedRange = Selection

    What = InputBox("This macro will work on the EXISTING selection, so please cancel and restart it if you haven't selected the desired range." _
        & vbCrLf & vbCrLf & "The selection is " & SelectedRange.Address(ReferenceStyle:=xlA1, RowAbsolute:=False, ColumnAbsolute:=False) _
        & vbCrLf & vbCrLf & "What is the text that needs to be replaced?", "Fast replace stage 1 of 2")
    If What = "" Then Exit Sub

    Replacement = InputBox("You chose to look for " _
    & vbCrLf & vbCrLf & """" & What & """" _
    & vbCrLf & vbCrLf & "Now, what is the replacement text?", "Fast replace stage 2 of 2")
    If StrPtr(Replacement) = 0 Then Exit Sub 'We want to allow an empty string for replacement, hence this StrPtr trick, source https://stackoverflow.com/questions/26264814/how-to-detect-if-user-select-cancel-inputbox-vba-excel

Dim StoreCalculation As Integer

On Error GoTo FastReplace_error 'So that we're not stuck due to the ScreenUpdating = False in case of an error

    Application.EnableEvents = False
    Application.ScreenUpdating = False
    StoreCalculation = Application.Calculation
    Application.Calculation = xlCalculationManual

    'Let's log what we're doing in the debug window, just in case
    Debug.Print "Working on " & SelectedRange.Address(ReferenceStyle:=xlA1, RowAbsolute:=False, ColumnAbsolute:=False)
    Debug.Print "Replacing """ & What & """ for """ & Replacement & """."
    Debug.Print "CalculateAfterReplace = " & CalculateAfterReplace

    'The heart of this sub
    SelectedRange.Replace What:=What, Replacement:=Replacement, LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

    'Wrapping up
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    Application.Calculation = StoreCalculation
    If CalculateAfterReplace Then Application.CalculateFull
    Beep
    Exit Sub

FastReplace_error:
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    Application.Calculation = StoreCalculation
    If CalculateAfterReplace Then Application.CalculateFull
    Err.Raise Err.Number, Err.Source, Err.Description

End Sub