6

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))

What is the strange magic of VBA braces?

Tested in 2007 and 2010 versions.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
Yumashka
  • 63
  • 2

1 Answers1

6

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

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.

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).

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
GSerg
  • 76,472
  • 17
  • 159
  • 346
  • Thanks for answer and for clarification. Finally I will do it the way you suggest because the source of coordinates will be easly parseable text file. – Yumashka Oct 07 '18 at 16:10