这些和函数之间的可靠性是否存在差异？

So the other day I was getting some strange sum values and I was completely stumped. I gave up on using a sum function in VBA and just added the values the long way (through loops), but then I read somewhere that using the sum functions in VBA isn't always reliable for developers? (I can't find the post anymore, but I'm still looking for it).

所以有一天我得到了一些奇怪的总和值,我完全被难倒了。我放弃在VBA中使用sum函数,只是添加了很长的值(通过循环),但后来我在某处读到使用VBA中的sum函数并不总是对开发人员可靠? (我找不到帖子了,但我还在找它)。

Is there any truth to this? I know many people have different ways to get the sum from a range of cells - without being too opinionated, which one of these will return the most exact result?

有没有道理呢?我知道很多人有不同的方法可以从一系列细胞中获得总和 - 而不是过于自以为是,其中一个会返回最准确的结果?

Sub testsums()

Dim metric1 As Integer, metric2 As Integer, metric3 As Integer

metric1 = Application.Sum(Range(("A1"), ("Z1")))

metric2 = Application.WorksheetFunction.Sum(Range(("A1"), ("Z1")))

metric3 = WorksheetFunction.Sum(Range(("A1"), ("Z1")))

End Sub

I'm working to reproduce my error - basically when looping through many rows (15,000+) and getting sums, some were returning zeroes where they shouldn't have been.

我正在努力重现我的错误 - 基本上当循环通过许多行(15,000+)并获得总和时,有些人返回的零点应该不存在。

1 个解决方案

#1

• Application.Sum(Range(("A1"), ("Z1")))

  That's a late-bound call against Excel.Application, resolved at run-time; as with any late-bound call (e.g. against Object or Variant), you get no IntelliSense, no auto-completion, and no compile-time validation, be it for typos in the name or for order or number of parameters. If the call is invalid or the function otherwise results in an error, this will return an Error value that you can validate with the IsError VBA function (of course if there's a typo in the function name what you'll get is a run-time error #438 "Object doesn't support this property or method").

  这是对Excel.Application的后期调用,在运行时解决;与任何后期绑定调用一样(例如针对Object或Variant),您没有获得IntelliSense,没有自动完成,也没有编译时验证,无论是名称中的拼写错误还是订单或参数数量。如果调用无效或函数否则会导致错误,这将返回一个Error值,您可以使用IsError VBA函数进行验证(当然,如果函数名中有拼写错误,您将得到的是运行时)错误#438“对象不支持此属性或方法”)。

  What makes this syntax work is the fact that the Excel.Application COM interface has a flag that makes it extensible - I'm not sure if it's extended with straight-up WorksheetFunction interface or if it's merely doubling-up the members, but anyway that's what's going on: you're calling members that don't exist on the Application interface at compile-time.

  使这种语法有效的原因是Excel.Application COM接口有一个标志,使其可扩展 - 我不确定它是否通过直接的WorksheetFunction接口扩展,或者它只是使成员加倍,但无论如何发生了什么:你在编译时调用Application接口上不存在的成员。

• Application.WorksheetFunction.Sum(Range(("A1"), ("Z1")))

  That's an early-bound call against Excel.WorksheetFunction, resolved at compile-time; you get IntelliSense, auto-completion, and compile-time validation. A typo will fail to compile, as will missing required parameters. If the call is invalid or the function otherwise results in an error, this will raise a VBA run-time error that you can handle with an On Error statement using standard, VBA-idiomatic error handling.

  这是对Excel.WorksheetFunction的早期调用,在编译时解析;您将获得IntelliSense,自动完成和编译时验证。输入错误将无法编译,因为缺少必需的参数。如果调用无效或函数导致错误,则会引发VBA运行时错误,您可以使用标准的VBA惯用错误处理使用On Error语句处理该错误。

• WorksheetFunction.Sum(Range(("A1"), ("Z1")))

  That's exactly the same as Application.WorksheetFunction.Sum, except it's not fully-qualified. If your project has a WorksheetFunction class, or if there's a WorksheetFunction object variable in scope, it will take precedence over a fully-qualified Application.WorksheetFunction call as far as resolution goes (which may result in compile errors). Otherwise, identical.

  这与Application.WorksheetFunction.Sum完全相同,只是它不是完全合格的。如果你的项目有一个WorksheetFunction类,或者如果在作用域中有一个WorksheetFunction对象变量,那么就分辨率而言,它将优先于完全限定的Application.WorksheetFunction调用(这可能导致编译错误)。否则,相同。

---

Which one is "more reliable" depends on exactly what you deem "reliable". Personally I consider compile-time resolution completely worth it, so the "most reliable" would be the fully-qualified early-bound version.

哪一个“更可靠”取决于您认为“可靠”的确切内容。我个人认为编译时解析完全值得,所以“最可靠”将是完全合格的早期版本。