XLL & Automation functions

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

XLL & Automation functions
Writting anywhere in spreadsheet 
J Phil




Posts: 20
 Joined: 2008-09-25
Is it possible to write a function, that when called from a cell, will write information in another cell somewhere in the sheet, and could this other cell be received as argument to the function?

I've been trying to do this all day using an XLL Add and re-doing the same thing with COM Excel Add-In Module...

Example (With COM Excel Add-In Module): 

public string SetValue(object otherCell)
{
    Excel.Range otherCellToWriteTo = otherCell as Excel.Range;

    otherCellToWriteTo.Value2 = "SecondCell";

    Marshal.ReleaseComObject(otherCellToWriteTo);

    return "FirstCell";
}


My code always crashes on the line:
otherCellToWriteTo.Value2 = "SecondCell";

with the error: 
Exception de HRESULT : 0x800A03EC

System.RuntimeType.ForwardCallToInvokeMember(
	String memberName, 
	BindingFlags flags, 
	Object target, 
	Int32[] aWrapperTypes, 
	MessageData& msgData)
   at Excel.Range.set_Value2()



After googling for a while, I thought this error was caused by the fact that Excel is running in the "fr-CA"(French) culture. But after trying every conceivable way to change the culture to "en-US" I still failed to make that code run...

So here I am :)

Thanks
Posted 09 Oct, 2008 15:45:46 Top
Andrei Smolin


Add-in Express team


Posts: 18819
 Joined: 2006-05-11
Hello Jean-Philippe,

That's because a UDF cannot change any cell without a trick. To change a cell you need to call a special public method in the code of the UDF. The method must accept no parameters and its return type must be void (in VB, it must be a sub, not a function). You call the method using ExcelApp.ExecuteExcel4Macro("MethodName"). In the method's code you get the ADXExcelRef and use its SetValue method.


Andrei Smolin
Add-in Express Team Leader
Posted 10 Oct, 2008 05:33:05 Top
J Phil




Posts: 20
 Joined: 2008-09-25
Could you post a piece of code that works?

Cause I just can't make this work...

Thanks
Posted 10 Oct, 2008 11:40:51 Top
J Phil




Posts: 20
 Joined: 2008-09-25
I was finally able to make something work, but then I encountered another problem...

public static string DoStuff()
{
try
{
Thread.CurrentThread.CurrentCulture = new CultureInfo("en-US");
Module.ExcelApp.ExecuteExcel4Macro("RUN(\"DoSomething\")");
Thread.CurrentThread.CurrentCulture = new CultureInfo("fr-CA");
}
catch (Exception ex)
{
System.Windows.Forms.MessageBox.Show(ex.ToString());
}

return "Doing stuff";
}

public static void DoSomething()
{
System.Windows.Forms.MessageBox.Show("Doing Something more");

Thread.CurrentThread.CurrentCulture = new CultureInfo("en-US");
red
Thread.CurrentThread.CurrentCulture = new CultureInfo("fr-CA");
}


If you remove the code in red, everything works fine.

But if you leave the code in red there, once the function DoStuff is done Excel will hang and be non-responsive forever...

Any idea?

Thanks
Posted 10 Oct, 2008 12:59:50 Top
Andrei Smolin


Add-in Express team


Posts: 18819
 Joined: 2006-05-11
Hello Jean-Philippe,

I've created a test project for you. See {NOTE. This download isn't available any longer because this approach doesn't work for Excel 2007.}

When debugging Office extensions, don't use messageboxes. Use System.Diagnostics.Debug instead. See DebugView (http://technet.microsoft.com/en-us/sysinternals/bb896647.aspx)


Andrei Smolin
Add-in Express Team Leader
Posted 13 Oct, 2008 07:04:22 Top
J Phil




Posts: 20
 Joined: 2008-09-25
Hi, I get the same behavior from your code.

Everything works fine, the function DoSomething is called normally, and the execution returns normally to DoStuff. The value in the cell is changed to 1. I can follow all of the code through the debugger, but once I get to the last "}" of the DoStuff function, Excel hangs, and never recovers...

Any idea?

I imagine something is wrong with my setup since this code most likely works on your side...

Thanks
Posted 14 Oct, 2008 11:35:13 Top
Andrei Smolin


Add-in Express team


Posts: 18819
 Joined: 2006-05-11
Hi Jean-Philippe,

Yes, on my PC, it works correctly.


Andrei Smolin
Add-in Express Team Leader
Posted 15 Oct, 2008 08:05:25 Top