Excel UDF, volatile and Indirect

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

Excel UDF, volatile and Indirect
 
nwein




Posts: 577
 Joined: 2011-03-28
This one is a doozie.
I have an XLL UDF that takes in a parameter marked as AcceptAsArray = false, I need that in order to get the parameter as a Range object in my code.
now, due to the fact that when marking a parameter with AcceptAsArray = false it immediately causes the function to become volatile, I've applied http://www.add-in-express.com/forum/read.php?FID=5&TID=9658, this works great; the function doesn't get called every time there's a calculation not affecting the aforementioned parameter.

However if I use Excel's function INDIRECT in the parameter (e.g. MyFunction(INDIRECT(A1))) it messes everything up. What happens next is a bad combination: the function refuses to trigger upon any parameter change (my function have more than one parameter, only one is marked as AcceptAsArray = false, the other parameters are standard ones: string, bool, etc.)
There are two cases to consider -
1) Changes to the indirect reference (i.e. if Ihad INDIRECT(A1) and A1 value is B2 and B2 has changed)
2) Normal changes to other parameters (e.g. MyFunction(INDIRECT(A1), C1) and I change the value of C1 manually)

None of these happen.
Of course removing the 237 trick solves the problem, but I don't want my function to be volatile. I can see that if it's not volatile I can't ask case 1 to apply (too much to ask), but why won't case 2 work?
Note that if the range parameter doesn't have INDIRECT in it it also works as designed, it's only a combination of the two that fails.

Here's a sample such function in case you didn't fully understand the issue: 

public static string IndirectTest(bool evaluate, dynamic range) // range: AcceptAsArray = False
{
	Module.CallWorksheetFunction((ADXExcelWorksheetFunction)237, new object[] { false });

	if (evaluate)
	{
		if ((range is ADXExcelRef))
		{
			Range rng = Module.ExcelApp.Range[((ADXExcelRef)range).ConvertToA1Style()];
			string ret = rng == null ? range.ToString() : rng.Value2.ToString();
			return ret;
		}
		return "range is not an ADXExcelRef";
	}
	return "Evaluate is false";
}


Any ideas?
Posted 10 May, 2012 11:12:38 Top
Eugene Astafiev


Guest


Hi Nir,

Thank for providing us with the detailed description of the issue you faced with. We will research it and let you know our results tomorrow (now is 21.40).
Posted 10 May, 2012 13:41:42 Top
Andrei Smolin


Add-in Express team


Posts: 18829
 Joined: 2006-05-11
Nir,

Your UDF is volatile. When it is executed, you mark it non-volatile. INDIRECT() is volatile. I suppose Excel's brains get broken trying to find a way out.

I see two variants:
- you don't use INDIRECT()
- or, you can mark your function non-volatile only if it is used without INDIRECT()

Is any of these possible?


Andrei Smolin
Add-in Express Team Leader
Posted 11 May, 2012 10:03:41 Top
nwein




Posts: 577
 Joined: 2011-03-28
Thanks Andrei,
I was looking into the second option, but I don't think it works.
I've approached it as follows: 


public static string IndirectTest(bool evaluate, dynamic range) // range: AcceptAsArray = False 
{ 
	var callerRef = (ADXExcelRef)Module.CallWorksheetFunction(ADXExcelWorksheetFunction.Caller);
	var caller = callerRef.ConvertToA1Style();
	var range = Module.ExcelApp.Range[caller];
	if (range.HasFormula && ((string)range.Formula.ToString()).Trim().ToUpper().Contains("INDIRECT("))
	{
		// Don't mark as non-volatile
	}
	else
    {
		Module.CallWorksheetFunction((ADXExcelWorksheetFunction)237, new object[] { false }); 
	}
 
    if (evaluate) 
    { 
        if ((range is ADXExcelRef)) 
        { 
            Range rng = Module.ExcelApp.Range[((ADXExcelRef)range).ConvertToA1Style()]; 
            string ret = rng == null ? range.ToString() : rng.Value2.ToString(); 
            return ret; 
        } 
        return "range is not an ADXExcelRef"; 
    } 
    return "Evaluate is false"; 
}

But this doesn't seem to work. It think the 237 is global, and not per formula/cell.
Were you thinking of something else?
Other than that I told the client not to use Indirect in this formula, but if a solution can be found it would be preferable.
Also thanks for enlightening me - I didn't know Indirect is de facto volatile, I mean, it made sense to me that it would, but I never really thought about it.
Thanks!
Posted 11 May, 2012 10:14:27 Top
Andrei Smolin


Add-in Express team


Posts: 18829
 Joined: 2006-05-11
Instead of non-marking it as non-volatile, mark it as volatile:

Module.CallWorksheetFunction((ADXExcelWorksheetFunction)237, new object[] { true });

Does it work?


Andrei Smolin
Add-in Express Team Leader
Posted 11 May, 2012 10:16:44 Top
nwein




Posts: 577
 Joined: 2011-03-28
Of course... how silly of me - It does work!
Thanks Andrei!
Posted 11 May, 2012 12:32:52 Top