Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel - DotNet - Variant Arrays as Params
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel - DotNet - Variant Arrays as Params
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. -- Regards, Tom Ogilvy "Paul S Bryant" wrote in message om... 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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel - DotNet - Variant Arrays as Params
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" wrote in message om... 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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel - DotNet - Variant Arrays as Params
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. "Tom Ogilvy" wrote in message ... 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel - DotNet - Variant Arrays as Params
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 "Paul S Bryant" wrote in message om... 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. "Tom Ogilvy" wrote in message ... 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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel - DotNet - Variant Arrays as Params
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. -- (Paul S Bryant) wrote in message . com... 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. "Tom Ogilvy" wrote in message ... 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. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel - DotNet - Variant Arrays as Params
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 "Charles Williams" wrote in message ... 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 "Paul S Bryant" wrote in message om... 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. "Tom Ogilvy" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Opening XLS file directly and getting params | Excel Discussion (Misc queries) | |||
OWC load add-ins programmatically in dotnet? | Excel Discussion (Misc queries) | |||
Variant to String | Excel Programming | |||
Excel Workbook opened twice with dotnet interop | Excel Programming | |||
DLLs and VBA: Who free's a variant? | Excel Programming |