problem with Excel automation function

Question

hi folks,

i´m programming a vsto add-in combined with an automation add-in in VB.NET using MS Visual Studio 2008. The automation add-in is activated in excel and registered at the system. basically the automation add-in running very well, when you call the function with

"=<function-name>(<argument1>;<argument2>;...;<argument-n>)"

by writing it into an excel cell or using the functionwizard.

The vsto add-in is used to initiate over a commandbarbutton a windows-form, where the user can type the arguments, which are required. If the user has finished a string will be written in the cell builded and looking like the example is wrote above.

the problem i´m fighting with is when the user starts excel, just using the windows-form to generate the string and clicking "ok" to fill the cell with it, excel tries to find the function cause of the "=" in the cell, but dont find it. In the cell finally stands Name?, because after looking in the functionwizard-list, my registered function disapeared.(Btw the add-in is still listed under "extra->add-ins" and is also loaded again after the next excelstart)

But on the other way if the user first use the functionwizard to get the value of the function, types it per hand in the cell or even just open the functionwizard and close it directly after opening without using the function, my windows-form generated string does work.

The onButtonClick-event performs the following example-code:

Me.Application.ActiveCell.Value = "=FUNCTION(""value1"";""value2"")"

so now my question is, why is the automation function disapearing when the user just use the string-generator at first?

Some advices or even the solution the my problem would be very nice.

Greetings Martin

Answer 1

alright,

after one week of searching and testing of other code without success, i found the reason of my problem by my own. i don´t know why excel kicks the function out of the list, but the following explanation will did solve the problem.

I did 2 mistakes trying to call the function by code. the first is that i choosed the wrong method of writing into the cell...just use "me.application.activecell.formula".

the second and most important reason is the real cause, the whole thing didn`t work. By trying to set every argument,semicolon, brackets etc. over one string excel reads the string in calculation mode and is making something wrong when it hits at the semicolon in the formula. this caused a comexception (HRESULT: 0x800A03EC) saying that just a failure occured.

excel missreads the semicolon at this point an throws an exception. by accident i found the solution to set the semicolon into the formula to separate the parameters. you just have to write " "," " into the code instead of " ; ".

i´m happy if my solution could help other developer having the same problem.

greetings martin