Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automation Add In, functions parameters as Range
I have created a function library in VB.Net and C# like described here :
http://www.cpearson.com/Excel/Creati...nctionLib.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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automation Add In, functions parameters as Range
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 wrote: I have created a function library in VB.Net and C# like described here : http://www.cpearson.com/Excel/Creati...nctionLib.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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automation Add In, functions parameters as Range
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. "Tony Gravagno" wrote: 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 wrote: I have created a function library in VB.Net and C# like described here : http://www.cpearson.com/Excel/Creati...nctionLib.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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automation Add In, functions parameters as Range
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 ilhamik wrote: 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. "Tony Gravagno" wrote: 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 wrote: I have created a function library in VB.Net and C# like described here : http://www.cpearson.com/Excel/Creati...nctionLib.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? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automation Add In, functions parameters as Range
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? "ilhamik" wrote: I have created a function library in VB.Net and C# like described here : http://www.cpearson.com/Excel/Creati...nctionLib.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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
using functions as parameters | Excel Programming | |||
Problem with optional parameters in Excel Automation Add-in worksheet function | Excel Programming | |||
XLL Functions with no parameters | Excel Programming | |||
RP - Office 2000 automation parameters Access and excel | Excel Programming | |||
Office 2000: excel to access automation and parameters | Excel Programming |