XLL function returns #VALUE!

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

XLL function returns #VALUE!
Function returns error only on certain worksheet 
Pierre Alain Carrier




Posts: 50
 Joined: 2011-12-22
Hi,

We have a XLLModule with one simple function that takes a object[,] and returns a string.

Works well everywhere.

Except today, a customer send un a file where he uses the function. On some sheets, the function works well, on others sheets, it return #VALUE!. Same argument, nothing fancy.

when debugging, the function code doesn't get called when returning #VALUE!.

Get's better. If I copy the entire content of the sheet to another sheet, it works fine! (not when copying the sheet however)

I wrapped the function call inside a VBA function and used Application Run. When called from one of the defective sheet, the Application.Run return Error 2015.

Anyone have any idea how to fix this?
Posted 01 Mar, 2012 12:31:14 Top
Pierre Alain Carrier




Posts: 50
 Joined: 2011-12-22
OK. The function has a second string parameter. I didn't think it mattered, because it is usually left blank, but it seems that if we add an empty string (""), the function works properly.

Still, this is not necessary in other worksheets/workbooks...
Posted 01 Mar, 2012 13:00:15 Top
Eugene Astafiev


Guest


Hi Pierre Alain,

How can I reproduce the issue on my PC? Could you please send a sample add-in project (a newly created empty add-in project is much appreciated) to the support e-mail address (see readme.txt file for details)?

BTW What version of Excel do you have installed on the PC? Did you install the latest service packs for Excel and OS?
Posted 02 Mar, 2012 00:13:09 Top
Pierre Alain Carrier




Posts: 50
 Joined: 2011-12-22
Hi Eugene,

thank you for your reply.

I will prepare everything and send it to you.

The customer uses Excel 2007. I get the same result on 2007 and 2010, Windows 7 and all latest updates.
Posted 02 Mar, 2012 09:11:19 Top
Eugene Astafiev


Guest


Hi Pierre,

Thank you. I am waiting for your e-mail.
Posted 02 Mar, 2012 09:32:39 Top
Eugene Astafiev


Guest


Hi Pierre,

Thank you for providing me with a sample add-in project. I have reproduced the issue on my PC with Excel 2010 x64. The cause of the issue is a wrong order of parameters: 

static public string Test([Optional]object[,] param1, [Optional]string param2)
{
    return "It works!";
}


Note, your UDF accepts an array or optional parameters (see param1). But how can we find the last optional parameter (see param2)?

I have changed the order of parameters in your function: 

static public string Test([Optional]string param2, [Optional]object[,] param1)
{
    return "It works!";
}


And it works like a charm! I can't reproduce the issue now.
Posted 05 Mar, 2012 03:21:23 Top
Pierre Alain Carrier




Posts: 50
 Joined: 2011-12-22
Thank you Eugene.

However, why does it work on all other worksheet except that one?

Also, I added the Optional atttribute later as a test, but it is not working even without the attribute. And in this particular use case, the first argument is always given.
Posted 05 Mar, 2012 07:56:19 Top
Pierre Alain Carrier




Posts: 50
 Joined: 2011-12-22
I've changed the signature of the function to:

static public string Test(string param1, string param2)

and I'm still getting the same behavior. It works in one sheet but not in the other.

Not sure what you did to make it work, but I can't reproduce.
Posted 05 Mar, 2012 08:05:41 Top
Eugene Astafiev


Guest


Hi Pierre,

Unfortunately I wasn't able to reproduce the issue on my PC with Excel 2010 x64.

Could you please organize a remote session instead? I will take a look at the issue online.
Posted 05 Mar, 2012 09:55:20 Top