VBA Excel中的魔术括号 [英] Magic braces in VBA Excel

问题描述

我写了一些函数向Excel工作表中添加一些折线.然后我发现了奇怪的牙套行为. 我声明并定义点数组为:

I wrote some function to add some polylines to Excel sheet. Then I've discovered strange braces behavior. I declare and define points array as this:

Dim points As Variant
Dim sh As Shape

points = Array(Array(10.5, 10.5), Array(20.4, 20.4), Array(5.1, 30.3), Array(10.5, 10.5))

' These both do not work and I get error about wrong type (error 1004) in 2007
' and application defined error 1004 on 2010:

ActiveWorkbook.ActiveSheet.Shapes.AddPolyline points
Set sh = ActiveWorkbook.ActiveSheet.Shapes.AddPolyline(points)

' These work fine:

ActiveWorkbook.ActiveSheet.Shapes.AddPolyline (points)
Set sh = ActiveWorkbook.ActiveSheet.Shapes.AddPolyline((points))

VBA括号的奇异之处是什么?

What is the strange magic of VBA braces?

在2007和2010版本中进行了测试.

Tested in 2007 and 2010 versions.

推荐答案

points周围的附加括号会导致对参数进行评估作为表达式，然后被传递给ByVal.

The additional parentheses around points cause the argument to be evaluated as an expression and consequently be passed ByVal.

评估数组的行为可以准确更改数据在包含数组的Variant中的打包方式(例如，请参见 VBA:当使用变量传递列数组作为示例时，删除重复项失败)，并且如果被调用的过程不太宽容它可以接受的数组类型(应该是)，那么它将引发错误

The act of evaluating an array can change exactly how the data is packed inside the Variant that contains it (e.g. see VBA: Remove duplicates fails when columns array is passed using a variable as an example), and if the called procedure is not very lenient about what types of arrays it can accept (which it should be), then it will raise an error.

在您的情况下，让评估的(points)甚至可以通过实际上使我感到惊讶，因为文档中提到期望使用Single s的2D数组，并且Array(Array(...), Array(...), ...)是锯齿状的数组而不是2D的数组.看起来AddPolyline也是为了处理锯齿状数组而编写的，但是只有当包含数组的Variant中有一组特定的标志(似乎会产生评估)时，它才能识别它们(例如，

In your case I am actually surprised that passing an evaluated (points) even works, because the documentation mentions that a 2D array of Singles is expected, and Array(Array(...), Array(...), ...) is a jagged array as opposed to a 2D array. It would appear AddPolyline is written to cope with jagged arrays too, but it only recognizes them when the Variant containing the array has a particular set of flags in it which evaluating seems to produce (e.g. it might be that the presence or absence of VT_BYREF trips its flag comparison so it fails to recognize the passed array as supported).

我会称它为AddPolyline中的错误，并且我会明确定义并填充Single的2D数组以避免出现这种情况:

I would call it a bug in AddPolyline, and I would explicitly define and fill a 2D array of Single to avoid it:

Dim points(1 To 4, 1 To 2) As Single
points(1, 1) = 10.5: points(1, 2) = 10.5
points(2, 1) = 20.4: points(2, 2) = 20.4
points(3, 1) = 5.1: points(3, 2) = 30.3
points(4, 1) = 10.5: points(4, 2) = 10.5

这篇关于VBA Excel中的魔术括号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！