在 vba 中使用变体对性能不利吗? [英] Is using variants in vba bad for performance?

问题描述

我想标题很简单，将变量声明为变体而不是特定数据类型对 VBA 中的性能有什么影响?

I guess the title is pretty straightforward, what impact does declaring variables as variants, instead of a specific datatype, has on performance in VBA?

也许是因为我没有编程经验，但直觉上它应该会降低性能，因为计算机必须检查分配了哪种数据类型，然后将变体更改为分配的数据类型，而不是立即为其分配值.我没能找到这方面的一些文献.

Maybe it's because I am not that experienced with programming, but intuitively it should decrease performance due to the computer having to check which datatype is assigned, and then changing the variant to the assigned datatype, instead of immediately assigning the value to it. I have not been able to find some literature on this.

推荐答案

视情况而定.变体比原生类型慢，但在大多数程序中，它根本无关紧要.大多数宏都很小，在运行时会被编译，差异可能只有几微秒，您无法察觉.

It depends. Variants are slower then native types, but in most programs, it doesn't matter at all. Most macros are small and get compiled when run and the diference may be a few microseconds, that you can't perceive.

变体有其优点，我喜欢它们.

Variants have their advantages, I love them.

所以这取决于你在做什么.如果你的程序在眨眼间运行，那么避免变体没有任何好处.如果需要时间，请强烈键入您的变量(并正确声明对象 - 见下文).

So it depends on what you are doing. If your program runs in a blink of an eye, there is no advantage in avoiding variants. If it takes time strongly type your variables (and declare objects correctly - see below).

如果性能是一个问题，除此之外我将在下面提到其他事情.

If performance is an issue there are other things apart from this that I'll mention below.

一般问题

在设置属性或调用方法时，每一个都是CPU中的一个函数调用.这意味着堆栈设置开销.函数调用比内联代码慢.出于同样的原因，在 VBA 中使用循环而不是函数.

When setting properties or calling methods, each is a function call in the CPU. That means stack setup overhead. Function calls are slower than inline code. Use loops rather than functions in VBA forthe same reason.

F 或开始不会一遍又一遍地指定所有这些属性.除非你改变它们，否则它们不会改变.

F or 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/

这篇关于在 vba 中使用变体对性能不利吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！