加速多次更换 [英] Speed up multiple replacement

问题描述

我正在编写一个 Word 宏，它从 Excel 文件中读取大约 1000 个单词对，并相应地替换 Word 文件.一开始我使用下面的代码进行替换:

I'm writing a Word macro that reads about 1000 word pairs from an Excel file and does replacement accordingly for a Word file. At the very beginning I was using the code below for the replacement:

    Selection.Find.ClearFormatting
    Selection.Find.Replacement.ClearFormatting
    With Selection.Find
        .Text = SrcText
        .Replacement.Text = DestText
        .Forward = True
        .Wrap = wdFindContinue
        .Format = False
        .MatchCase = False
        .MatchWholeWord = False
        .MatchByte = False
        .MatchAllWordForms = False
        .MatchSoundsLike = False
        .MatchWildcards = False
        .MatchFuzzy = False
    End With
    Selection.Find.Execute Replace:=wdReplaceAll

它有效，但对于大型 Word 文件似乎有点慢.然后我尝试首先将整个内容读入一个字符串变量，在那里找到，然后在找到任何替换目标时更改 Word 文件(尽管我实际上不确定这会更快).

It worked but appeared a bit slow for large Word files. Then I tried to first read the whole content into a string variable, do finding there, then alter the Word file when any replacement target was found (although I'm actually not sure that this can be faster).

这样做时，我遇到了有点奇怪的不一致.例如，如果您创建的 Word 文件的第一行只有一个单单元格表格:

When doing that I encountered a somewhat weird inconsistency. For example, if you create a Word file with only a single-cell table in the first row:

然后在 VBA 编辑器中，下面的代码会给你 2.

then in the VBA editor, the code below will give you 2.

    Debug.Print Len(ActiveDocument.Characters(2))

我不知道是否存在其他类似的不一致并卡在那里.

I don't know whether there exist other inconsistencies of this sort and got stuck there.

是否有任何解决方法或是否有其他方法可以加快替换速度?

推荐答案

首先不要一遍遍地指定所有这些属性.除非你改变它们，否则它们不会改变.

For a start don't specify all those properties over and over again. Unless you change them they don't change.

With Selection.Find
    .ClearFormatting
    .Replacement.ClearFormatting
    .Forward = True
    .Wrap = wdFindContinue
    .Format = False
    .MatchCase = False
    .MatchWholeWord = False
    .MatchByte = False
    .MatchAllWordForms = False
    .MatchSoundsLike = False
    .MatchWildcards = False
    .MatchFuzzy = False

    For loop to go through each word pair
        .Text = SrcText
        .Replacement.Text = DestText
        .Find.Execute Replace:=wdReplaceAll
    Next

End With

最小化点

因此，如果您对性能感兴趣，请尽量减少点(每个点都是一个查找)，尤其是在循环中.

So if you are interested in performance minimise dots (each dot is a lookup), especially in loops.

有两种方法.一种是如果您要访问多次，请将对象设置为最低对象.

There are two ways. One is to set objects to the lowest object if you are going to access more than once.

例如(较慢)

set xlapp = CreateObject("Excel.Application")
msgbox xlapp.worksheets(0).name 

(更快，因为每次使用对象时都省略了一个点)

(faster because you omitt a dot every time you use the object)

set xlapp = CreateObject("Excel.Application")
set wsheet = xlapp.worksheets(0)
msgbox wsheet.name

第二种方式是用.一次只能激活一个.

The second way is with. You can only have one with active at a time.

这会跳过 100 次查找.

This skips 100 lookups.

with wsheet
For x = 1 to 100
 msgbox .name
Next
end with

字符串连接

并且不要一次连接一个字符.从 VBScript 程序员那里看到这个.制作一个100个字符的字符串需要50​​,000个字节和多次分配和释放.

And don't join strings one character at a time. See this from a VBScript programmer. It requires 50,000 bytes and many allocation and deallocation to make a 100 character string.

http://blogs.msdn.com/b/ericlippert/archive/2003/10/20/53248.aspx

阅读属性

不要重读不会改变的属性，尤其是在进程外或后期绑定的情况下.将它们放入一个变量中.

Don't reread properties that don't change especially if out of process or late bound. Put them into a variable.

对象类型

这里有两个概念 - 进程内或进程外以及早期或晚期绑定.

Two concepts here - in or out of process and early or late binding.

exe 文件连接到进程外.所有调用都通过 RPC(一种网络协议)进行编组.Dllfile 正在处理中，函数调用通过跳转直接进行.

exefiles are connected to out of process. All calls are marshalled over RPC (a networking protocol). Dllfiles are in process and function calls are made direct with a jump.

早期绑定是set x = objecttype.编写程序时会查找函数.在执行时，程序被硬编码以跳转到存储在该函数的 vtable 中的地址.

Early binding is set x = objecttype. Functions are looked up when you write the program. On execution the program is hard coded to jump to address stored in the vtable for that function.

设置后期绑定x = createobject("objecttype").每个函数调用都是这样的.对象，你有打印命令吗".是"，它回答说，命令编号 3".对象，你能做3号命令吗".当然，这是结果".

Late binding is set x = createobject("objecttype"). Each function call goes like this. "Hi object do you have a print command". "Yes", it replies, "command number 3". "Hi object can you please do command number 3". "Sure, here's the result".

来自 Visual Basic 概念(帮助的一部分)

From Visual Basic Concepts (part of Help)

您可以通过优化 Visual Basic 解析对象引用的方式来使您的 Visual Basic 应用程序运行得更快.Visual Basic 处理对象引用的速度可能受以下因素影响:

You can make your Visual Basic applications run faster by optimizing the way Visual Basic resolves object references. The speed with which Visual Basic handles object references can be affected by:

ActiveX 组件是否已实现为进程内服务器或进程外服务器.

Whether or not the ActiveX component has been implemented as an in-process server or an out-of-process server.

对象引用是早绑定还是晚绑定.通常，如果组件已作为可执行文件(.exe 文件)的一部分实现，则它是进程外服务器并在其自己的进程中运行.如果它已作为动态链接库实现，则它是进程内服务器并与客户端应用程序在同一进程中运行.

Whether an object reference is early-bound or late-bound. In general, if a component has been implemented as part of an executable file (.exe file), it is an out-of-process server and runs in its own process. If it has been implemented as a dynamic-link library, it is an in-process server and runs in the same process as the client application.

使用进程内服务器的应用程序通常比使用进程外服务器的应用程序运行得更快，因为应用程序不必跨越进程边界来使用对象的属性、方法和事件.有关进程内和进程外服务器的详细信息，请参阅进程内和进程外服务器".

Applications that use in-process servers usually run faster than those that use out-of-process servers because the application doesn't have to cross process boundaries to use an object's properties, methods, and events. For more information about in-process and out-of-process servers, see "In-Process and Out-of-Process Servers."

如果对象引用使用声明为特定类的变量的对象变量，则对象引用是早期绑定的.如果对象引用使用声明为通用 Object 类变量的对象变量，则对象引用是后期绑定的.使用早期绑定变量的对象引用通常比使用后期绑定变量的对象引用运行得更快.

Object references are early-bound if they use object variables declared as variables of a specific class. Object references are late-bound if they use object variables declared as variables of the generic Object class. Object references that use early-bound variables usually run faster than those that use late-bound variables.

Excel 特定

请参阅来自 Microsoft 人员的此链接.这是excel特定的，而不是VBA.自动计算和其他计算选项/屏幕更新等

See this link from a Microsoft person. This is excel specific rather than VBA. Autocalc and other calc options/screenupdating etc.

http://blogs.office.com/2009/03/12/excel-vba-performance-coding-best-practices/

这篇关于加速多次更换的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！