Excel AddIn - Passing string value as formular parameter inside formular code behind

Add-in Express™ Support Service
That's what is more important than anything else

Excel AddIn - Passing string value as formular parameter inside formular code behind
 
Daniel Wosch


Guest


Hello there,
I've defined an own Excel function. The method stub looks like following: 


public static object ExecuteMyFunction(object values, object tagName)
{ // my code... }


The method receives an array of double values and an string, called tagName.
In the design view my ADXExcelFunctionDescriptor looks like following:
User added an image

I call and set the function by the following lines of code (happens code behind for sure): 

var formula = string.Format(@"={0}({1};{2})", Temp.FORMULA_NAME, this.DataRangeTextBox.Text, tagCaption);
resultRange.set_Value(Type.Missing, formula);
resultRange.Formula = resultRange.Value;


... which results in an exception.

The exception has the following content: 


System.Runtime.InteropServices.COMException occurred
  HResult=-2146827284
  Message=Ausnahme von HRESULT: 0x800A03EC
  Source=""
  ErrorCode=-2146827284
  StackTrace:
       bei System.RuntimeType.ForwardCallToInvokeMember(String memberName, BindingFlags flags, Object target, Int32[] aWrapperTypes, MessageData& msgData)
       bei Microsoft.Office.Interop.Excel.Range.set_Value(Object RangeValueDataType, Object )
       bei bb.ExcelToolbar.Controls.bbControl.ApplyFormula(Object sender, EventArgs e) in c:xxyyzzbb.ExcelToolbarControlsbControlcs:Zeile 88.
  InnerException: NULL


Further, if i don't pass the tagName parameter the function returns an result without any exception or error. 


var formula = string.Format(@"={0}({1})", Temp.FORMULA_NAME, this.DataRangeTextBox.Text, tagCaption);
resultRange.set_Value(Type.Missing, formula);
resultRange.Formula = resultRange.Value;


So i think it has something to do with the string parameter. I also tried to surround the string parameter with " or ' characters but no change so far.

If i type the function directly into excel it works without any problems. So for example if i type in the following formula in an excel in works just fine: 
=Temp.DoSomething(B2:B13;"Flow")

Maybe i miss out something or doing something wrong?

Thanks in advance!
Kind regards
Daniel
Posted 29 Jul, 2015 07:53:06 Top
Andrei Smolin


Add-in Express team


Posts: 18823
 Joined: 2006-05-11
Hello Daniel,

That HRESULT is too generic. Please try these suggestions:
- make sure there's no extra EXCEL.EXE in processes before you start Excel
- use a .xlsx workbook (I assume you use Excel 2007+)
- make sure the cell you are modifying is located on an active worksheet
- don't set range.Value, set range.Formula instead
- make sure you aren't editing a cell at this time, see https://www.add-in-express.com/creating-addins-blog/2011/03/23/excel-check-user-edit-cell/

Also check https://www.add-in-express.com/forum/read.php?FID=5&TID=9463; instead of installing a multilanguage pack, you can modify your code as we suggest at https://www.add-in-express.com/creating-addins-blog/2009/02/13/old-format-invalid-type-library/, see an example at https://www.add-in-express.com/forum/read.php?FID=5&TID=8956.

Finally, check if using comma as a separator helps.


Andrei Smolin
Add-in Express Team Leader
Posted 29 Jul, 2015 09:14:09 Top
Daniel Wosch


Guest


Hey Andrej,
thanks for fast reply!

Finally, check if using comma as a separator helps.


I don't know if i should laugh of if i should cry but this little tip did the trick.
Could you please tell me why it has to be an "," instead of an ";" as separator?
After the formula has been "published" to the excel cell the "," gets transformed into an ";" which is the known way of separating parameters in excel. Why do i have to use an "," when i set a formula code behind?

Thanks.
Kind regards
Daniel
Posted 29 Jul, 2015 09:29:47 Top
Andrei Smolin


Add-in Express team


Posts: 18823
 Joined: 2006-05-11
Daniel,

I suppose this is because the object model is English.


Andrei Smolin
Add-in Express Team Leader
Posted 29 Jul, 2015 10:01:47 Top