Automation Add In, functions parameters as Range

[ilhamik]

I have created a function library in VB.Net and C# like described here :
www.cpearson.com/Excel/CreatingNETFunctionLib.aspx

But i have a problem with function prameters. I can call the function like
"=DivideBy2(10)" in a Cell and it is ok. if i call the function with a cell
parameter like "=DivideBy2(A1)", it says "#VALUE!". I located a breakpoint
and look for the prameter value, so the prameter is a System.__ComObject
object with null value..

I have:
Visual studio 2005 with VSTO 2005 SE
Windows XP German
Office 2007 English

How can i solve this problem?
Any idea?

 

[Tony Gravagno]

I don't believe VSTO is related to this since it doesn't have anything
to do with excel formulas. Your solution is that the number 10 is an
integer which is a simple data type that can be marshalled, but "A1"
is a reference to a Range object. Change the "ByVal D As Double" to
"ByVal D As Object", try to cast it to a Range in the method, then get
the Value from that and cast it to a Double or some other type.

HTH

Were you able to deploy to a non-development system as Chip Pearson
suggests at the bottom of that article?

I haven't been able to deploy even after using regasm on the
deployment system. I have a complete tutorial on my blog using a COM
Shim which is highly recommended. I just can't get it to run on a
deployment workstation.
remove.this.munge.pleaseNebula-RnD.com/
blog/tech/2007/11/excel-tools5.html

Thanks.

 

[ilhamik]

I have tryed to cast the prameter to Range object and get the Value2 property
but it is not possible because the parameter is null (Nothing).
I did not deploy it since it did not work correct as development.

I just found something;
If I enter DivideBy2(A1*1) then it works correctly. It just seems like the
function doesn't recognize cell references when they are passed to the
function as parameters.

 

[ilhamik]

I have just found that my Regional settings was problem. I have English
Office but Deutsh Localization so excel can not convert the values correctly
i think.
Is there a workaround for that?

 

[Tony Gravagno]

I haven't done too much playing with this yet - got stuck on the
deployment thing. I might have mis-spoke earlier - I believe the
following is correct:

A Range object can't be passed in as a scalar Object, it must be an
array, either of the following:

Object[] for a row, not a column. The index is the relative column,
so if you pass in A5 then this is Object[0] and A5:A6 would put A6 in
Object[1].
Object[,] can be used for a 2D Range array.

For jagged arrays, you need to use params (C#):
Object MyFunc(params Object[][,] YourCells)

There are specific rules about errors that get passed back when you
pass the wrong data type to various method signatures, like bool to
string, etc. You should be able to find a table somewhere.
Unfortunately this means the user may need to be somewhat aware of
data typing when passing data in.

Like I said, I'm still just starting to play in this area myself.

HTH