Support for setting COM properties whose values are SAFEARRAYs



This feature adds support for setting COM properties whose values are SAFEARRAYs.  Previously, it was possible to set the value of only one cell at a time in an Excel spreadsheet.  It is necessary to be able to set the Range property to a value that is a SAFEARRAY of VARIANTs in order to set values for multiple cells at once.  This improves performance in Excel, particularly since performance for Excel's processing of COM requests is known to be slower on Windows 10.


Excel requires two dimensional SAFEARRAYS to set multiple cells at once. SAFEARRAYS of BSTR work, and also SAFEARRAYS or VARIANT work using various subtypes for the VARIANTs.  In order to support Excel and other possible use cases, this feature adds support for the following new syntax for setting properties:


  @array2_of_<type>([typecast]gsd1[, [typecast]gsd2, ...])


This syntax produces a two dimensional SAFEARRAY, where <type> is the uppercase name of a VARTYPE, such as VARIANT or BSTR.  Multiple comma separated GSDs may be used.  A GSD is a Generalized String Descriptor in the Panther nomenclature.  These would typically be the names of Panther array fields or JPL array variables.


An optional, typecast is supported for the GSD arguments.  It should be enclosed in parenthesis and the type name should include a leading "VT_", such as (VT_BSTR).  Note that the new syntax, including type names and typecasts are all case sensitive.


An example might look like this:




When no casts are used, Panther determines types automatically from the GSD, usually BSTR.  The chosen type must match the <type> of the SAFEARRAY, unless it is a SAFEARRAY of VARIANT. Thus the need for typecasts.


The two dimensional SAFEARRAY is created such that the first dimension, number of rows, is the size of the number of occurrences of the first GSD, if possible.  The second dimension, number of columns, has a size dependent upon how many comma separated GSDs are given, if possible.  If only a single GSD is given, as shown above, the second dimension has a size of 1.  In other words, there is but one column!


The two dimensional array is useful for sending a Panther array to Excel. This is done by setting the Value property of a Range that describes all or part of one or more columns in a spreadsheet.  Here is an example in JPL, to set values for two Excel spreadsheet columns, A and B, for rows 1 through 20, using arrays C1 and C2 in Panther, where C1 contains strings and C2 contains integers:



  call sm_obj_set_property (i_spreadsheet, \

                 'Range("A1::B20").Value', \

                 "@array2_of_VARIANT((VT_BSTR)C1, (VT_UI4)C2)")


One should use @array_of_VARIANT for sending Panther arrays to Excel, since that form is documented as being valid for Excel, and the GSD should be cast to VT_BSTR if necessary. Panther usually chooses to use VT_BSTR anyway, and Excel will accept other subtypes within a SAFEARRAY of VARIANT.


The second special array syntax is:




This syntax creates a one dimensional array of type, <type>.  The size of the array is the number of occurrences indicated by the GSD.


It should be noted that Panther is designed to determine the required type to use for setting COM properties, but it cannot always do that, as in the cases for usage of the new syntax to describe arrays.


Also note that the new syntax is supported only for setting COM properties. It is not supported for passing arguments to COM methods.