Creating UDF in .Net which requires an array as a parameter

[Alan Moseley]

I have an xla addin for Excel for which I can pass an array to the function
(as it requires a varaible number of parameters). It is declared:-

Public Function MyFunction(StaticVariable1 as Long, ParamArray
VariableVariables as Variant) As Variant

This works absolutely fine, but I would like to port this to a Shared
Add-in. I am using VS2005 (not VSTO). I have created my addin and all of my
other functions work fine with the exception of this one. I cannot seem to
find the correct declaration for my array variable. What should it be?
Thank in advance.

 

[Cindy M.]

Hi =?Utf-8?B?QWxhbiBNb3NlbGV5?=,

I have an xla addin for Excel for which I can pass an array to the function
(as it requires a varaible number of parameters). It is declared:-

Public Function MyFunction(StaticVariable1 as Long, ParamArray
VariableVariables as Variant) As Variant

This works absolutely fine, but I would like to port this to a Shared
Add-in. I am using VS2005 (not VSTO). I have created my addin and all of my
other functions work fine with the exception of this one. I cannot seem to
find the correct declaration for my array variable. What should it be?

Mostly, "Variant" in classic VB translates to the type "Object" in the .NET
Framework. Have you tried that?

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 17 2005)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or reply
in the newsgroup and not by e-mail

 

[vince]

I have an xla addin for Excel for which I can pass an array to the function
(as it requires a varaible number of parameters).  It is declared:-

Public Function MyFunction(StaticVariable1 as Long, ParamArray
VariableVariables as Variant) As Variant

This works absolutely fine, but I would like to port this to a Shared
Add-in.  I am using VS2005 (not VSTO).  I have created my addin and all of my
other functions work fine with the exception of this one.  I cannot seem to
find the correct declaration for my array variable.  What should it be? 
Thank in advance.

Yes, you should use objet as a type:

object MyXLLFunction(object in_range....

object[,] input_range;

if (in_range is object[,])
{
input_range = (object[,])in_range;
}
else
{
throw new Exception("whatever");
}
int i= input_range.GetLength(0);
:. :. :.

Vincent Boilay. LVP (Less Valuable... )

 

[Alan Moseley]

Vince (and Cindy)

Object is indeed correct. My issue was with how to declare it in my UDF.
You have answered this for me. I was originally incorrectly declaring my
function:-

Public Function MyFunction(ByVal MyArray() As Object) As Object

I am not a C programmer but I could see from your code that I should have
been declaring it:-

Public Function MyFunction(ByVal MyArray As Object) As Object

As a footnote to this, I was also trying to call my function from Excel with:-

=MyArray(1,2,3)

This was wrong, and should be:-

=MyArray({1,2,3})

Thanks for your help people. Regards.

--
Alan Moseley IT Consultancy
http://www.amitc.co.uk

I have an xla addin for Excel for which I can pass an array to the function
(as it requires a varaible number of parameters). It is declared:-

Public Function MyFunction(StaticVariable1 as Long, ParamArray
VariableVariables as Variant) As Variant

This works absolutely fine, but I would like to port this to a Shared
Add-in. I am using VS2005 (not VSTO). I have created my addin and all of my
other functions work fine with the exception of this one. I cannot seem to
find the correct declaration for my array variable. What should it be?
Thank in advance.

Yes, you should use objet as a type:

object MyXLLFunction(object in_range....

object[,] input_range;

if (in_range is object[,])
{
input_range = (object[,])in_range;
}
else
{
throw new Exception("whatever");
}
int i= input_range.GetLength(0);
:. :. :.

Vincent Boilay. LVP (Less Valuable... )