Excel - DotNet - Variant Arrays as Params

[Paul S Bryant]

Is someone able to confirm the following behaviour in relation to the
following two UDFs:

Public Function BadFunction(avVariant() as Variant) as String
BadFunction= TypeName(avVariant)
End Function

Public Function GoodFunction(ParamArray avVariant() as Variant) as
String
GoodFunction= TypeName(avVariant)
End Function

Public Function GetTypeName(vVariant as Variant) as String
GetTypeName= TypeName(avVariant)
End Function


Test Harness:
Call the above three functions from the worksheet level.
=BadFunction(A1:B2)
=GoodFunction(A1:B2)
=GetTypeName(A1:B2)


Behaviour:
BadFunction returns #Value (and the function never gets called)
GoodFunction returns "Variant()" (ie., it works)
GetTypeName returns "Range"
The same is true if the implement these functions in a COM DLL, and
reference this DLL as a COM Addin through Excel XP (ie., you call the
DLL directly from the function bar on the worksheet level.


Interpretation:
Excel is able to turn range references into ParamArray parameter
types, and these end up inside the VBA function as an array of
Variants() (a variant array).
However, Excel is NOT able to turn range references into an array of
Variants() directly (there is a difference, and Excel can only cope
with the first case). BadFunction is never called, probably due to a
parameter type mismatch error.
Even though the ParamArray ends up as TypeName=Variant() inside the
function, the signature for this function is clearly different to a
function with a pure avVariant() parameter.



Why?:
It looks like the black box sitting between the Excel function bar and
the eventual COM call is not able to turn range references in function
calls into Variant() arrays (ie., an array of Variants). It CAN
interact with a ParamArray parameter, but it can't interact with a
parameter expecting and array of Variants. This is a very fine
distinction.


Why do I care:
When you are writing a COM DLL in C#, and exposing this DLL to Excel
using Excel XP "COM Addins" functionality, you might want to take an
array of cells as in an argument to a function:
eg. =MyCSharpExcelCOMAddinFunction(A1, A11)
Turns out you can't (?) do this without using the Primary Interop
Assembly (PIA) for Excel in C#. Firstly, the COM interop layer has no
idea with an Excel Range object reference is, so there is no point
defining a parameter of this type. However, Excel is smart enough to
convert cell/range references into primatives if the target COM
parameter type is a primitive. We also know that Excel can translate
range references into ParamArray-compatible parameter types (which
them. You would hope, therefore, that it could interact with the
DotNet COM Interop layer where the target method expects and array of
variants. But it can't. You can DEFINE and array of Variants in the
C# method signature, but Excel doesn't know how to pass these from the
function bar. And you CAN'T define a parameter in a C# method
signature that corresponds with the PARAMARRAY type in COM speak
(varargs in IDL?), but this is the only thing Excel's formula bar
know's how to speak.

Anyone have any interest in this?

 

[Tom Ogilvy]

I believe your interpretation of GoodFunction is incorrect. If you test
the typename of the first element of avVariant, it will be Range. No
conversion has taken place. Replace the argument with a single number, and
as written you will get the same result.

=GoodFunction(21)


Your interpretation of BadFunction is correct in that you have a mismatch
with argument type and the function is never entered.

 

[Charles Williams]

Hi Paul,

Try using a straight variant that can contain an array or a range, that is
the most general way of handling arguments that I know of:

Public Function GoodBadFunction(avVariant as Variant) as String
goodBadFunction= TypeName(avVariant)
End Function

this returns Range when called from Excel as =GoodBadFunction(A1:B2)
and returns Variant() when called from Excel as =GoodBadFunction({1,2})

(This is using VBA so YMMMV with .net)

regds
Charles
______________________
Decision Models
FastExcel Version 2 now available.
www.DecisionModels.com

 

[Paul S Bryant]

I agree that the elements in the Variant() array in the GoodFunction()
are indeed of type Range. This wouldn't help me pass data to DotNet
without the PIA, but it does confirm the thing I am immediately
interested in. That is, the Excel function bar can't pass parameters
to functions requiring variant arrays unless you use the ParamArray
modifier.

 

[Charles Williams]

It works OK if you use as variant rather than as variant(), and also handles
non-range arguments.

Public Function GoodBadFunction(avVariant as Variant) as String
goodBadFunction= TypeName(avVariant)
End Function

regds
Charles
______________________
Decision Models
FastExcel Version 2 now available.
www.DecisionModels.com

 

[onedaywhen]

How about using a (large) number of optional variant arguments e.g.

Function Indifferent(ByRef Arg1 as Variant, _
Optional ByRef Arg2 as Variant, _
Optional ByRef Arg3 as Variant, _
...
Optional ByRef Arg30 as Variant)

I think this is how the SUM worksheet function is written. If you
*type* this in the VBE Immediate Window:

Application.WorksheetFunction.Sum(

when you type the parenthesis the IntelliSense kicks in to show one
mandatory and 29 option arguments.

 

[onedaywhen]

How about using a (large) number of optional variant arguments e.g.

Function Indifferent(ByRef Arg1 as Variant, _
Optional ByRef Arg2 as Variant, _
Optional ByRef Arg3 as Variant, _
...
Optional ByRef Arg30 as Variant)

I think this is how the SUM worksheet function is written. If you
*type* this in the VBE Immediate Window:

Application.WorksheetFunction.Sum(

when you type the parenthesis the IntelliSense kick in to show one
mandatory and 29 option arguments.

--

 

[Charles Williams]

Hi Paul,

To explain whats happening: A variant containing an array (or a range) is
not the same as an array of variants.

If you want to access the parameter as a variant array just assign it to a
variant.

Public Function GoodBadFunction(avVariant As Variant) As Variant
Dim vVar As Variant
Debug.Print TypeName(avVariant)
vVar = avVariant
Debug.Print TypeName(vVar)
GoodBadFunction = vVar
Debug.Print TypeName(GoodBadFunction)
End Function

What happens is that when a variant parameter is passed a range, the variant
is treated as containing a range,
and when that variant containing a range is assigned to another variant, the
range is converted to a variant containing an array of variants.
(the exact treatment differs slightly by VBA version).
However Excel will not let you pass a range to a variant array parameter
(avVariant() as variant).

This is also important for performance reasons:
converting a range to a variant containing an array of variants with a
single assignment statement is very efficient compared to reading the
elements of the range one--by-one,
but if you only want a few elements of the range (typically things like
binary search or excel functions like MATCH and VLOOKUP) then it is much
faster not to assign it to a variant but to handle it as a variant
containing a range.

I assume that using a paramarray involves each individual parameter being
handled internally as a variant which is then assigned to a single element
(as an array of variants) of the variant array that the function sees. so
the paramarray winds up as an array of variants each element of which can
contain an array.

Charles
______________________
Decision Models
FastExcel Version 2 now available.
www.DecisionModels.com