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 ca
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, A1:D1)
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?