Skip excecution of UDF but keep old cell values

[R. H.]

Hi experts,

Is it possible to skip the complete execution of the UDF in the Excel-DNA AddIn?

I have a switch for choosing, if skip or not.

If skip, all resulting cells should keep the old values.

What is the correct return value for my UDF, that all resulting cells holding the old values.

(throwing an Exception would also be acceptable)

I work with NetOfficeApplication.Caller(1) that gives me the range for the result.

After normal execution, I build an object[x,y] with the resulting values, that works fine.

But if  skip, I would have the old values in the result

If that range has IsFormula or IsArray as true, than I can not get access to the Value or Value2 properties of that range cells.

(An exception will be thrown and no value is returned)

Only the Text property is possible for each cell, but this is not the correct raw value for the result in most cases...

Tnx 4 your help.

Roman

[Govert van Drimmelen]

Hi Roman,

 

Excel does not have a way for a UDF to abandon the calculation, and not return a value.

Your UDF can read the old value of the cell, but that would not be an appropriate result to return if your function is used in a compound formula (e.g. =MYFUNC()+1).

 

You might cache your function results yourself, and then return the value that you have cached if the ‘skip’ switch is on.

 

-Govert

--
You received this message because you are subscribed to the Google Groups "Excel-DNA" group.
To unsubscribe from this group and stop receiving emails from it, send an email to exceldna+u...@googlegroups.com.
To post to this group, send email to exce...@googlegroups.com.
Visit this group at https://groups.google.com/group/exceldna.
For more options, visit https://groups.google.com/d/optout.

[R. H.]

Hi Govert,

that's I am afraid of...

Caching the results is not an option...

Only recreating the values from .Text value seems to be a solution.

But this can only be done with some hurts.

Tnx

Roman

[Govert van Drimmelen]

You can get the current values (not text) from the cell calling your UDF.

But how does that help you?

 

Consider this function:

    [ExcelFunction(IsMacroType=true)]

    public static object GetNewValueIfGreaterThanCurrentValue(double newValue)

    {

        var caller = XlCall.Excel(XlCall.xlfCaller) as ExcelReference;

        if (caller == null)

            return newValue;

 

        var currentValue = (double)caller.GetValue();

        if (newValue > currentValue)

            return newValue;

 

        return currentValue;

    }

 

Try this by putting “5” into cell A1, and the formula = GetNewValueIfGreaterThanCurrentValue(A1) into cell B1.

Then change the values in cell A2 to by higher or lower…

 

But this breaks if you have a compound formula, since the relationship between the function return and the cell value is not fixed – there can be a compound formula.

 

Why is caching the results not an option?

[R. H.]

Hi Govert,

Tnx 4 Response!

it is, as I mentioned, the same issue.

The GetValue() does only work, if you are not using Array or Formula based caller for UDF and I do!

GetValue throws the same Exception as in NetOfficeApplicaton wrapper...

Caching will be expensive, for each full caller address for all workbooks for all worksheets for all UDF...

I have here sometimes > 1GB data (if Excel64)

And the not solvable problem is on open of the workbook, where is my cache? It will be empty, not filled....

Or is it possible, to put all my cached data as a "DataTable" inside the workbook and save it, so I can use it after startup?

(I do not know how to do this, is there an example available?

One data tabe for each workbook would be enough, ke is the caller address, value is the object data)

This would be a nice solution, it could be used caller based, so expensive calls could be cached and not so expensive calls going through.

What do you think about such a solution?

Roman

[FastExcel]

Note that you have to use IsMacroType=True to make this work (otherwise you get a circular ref) and this means that the function will be single-threaded.

It works OK for me with multi-cell array formulas if coded to handle caller as returning an array, and I don't understand what problem you get with a formula based UDF.

[R. H.]

Hi all,

this was the solution!!!

With IsMacroType=True you will no longer get an Exception and you can use Goverts solution also for Array based UDF

if(skip)

{

    var caller = XlCall.Excel(XlCall.xlfCaller) as ExcelReference;

     return caller != null ? caller.GetValue() : DefaultValue;

}

(Keep in mind, that IsTheradSave now is no longer supported, as Govert explained in a different thread) 

Roman

Am Donnerstag, 30. März 2017 14:19:32 UTC+2 schrieb FastExcel:

[FastExcel]

<(Keep in mind, that IsTheradSave now is no longer supported, as Govert explained in a different thread) >

Is this a misunderstanding? - Could you point me at the thread where Govert withdraws support for multi-threading?

[R. H.]

What is the use of IsMacroType= true

You can also look into the source code...

if (!mi.IsMacroType && mi.IsThreadSafe && XlAddIn.XlCallVersion >= 12)

functionType += "$";

if (mi.IsMacroType)

functionType += "#";

Am Donnerstag, 30. März 2017 18:11:52 UTC+2 schrieb FastExcel:

[Govert van Drimmelen]

Nothing changed in this regard - multi-threaded functions (marked IsThreadSafe=true) are supported as before.

Some features don't work well in thread-safe functions, for example using the COM object model.
C API calls are also limited by Excel in this context.

-Govert

________________________________________
From: exce...@googlegroups.com [exce...@googlegroups.com] on behalf of FastExcel [cha...@decisionmodels.com]
Sent: 30 March 2017 06:11 PM
To: Excel-DNA

Subject: Re: [ExcelDna] Skip excecution of UDF but keep old cell values

<(Keep in mind, that IsTheradSave now is no longer supported, as Govert explained in a different thread) >

Is this a misunderstanding? - Could you point me at the thread where Govert withdraws support for multi-threading?

--
You received this message because you are subscribed to the Google Groups "Excel-DNA" group.

To unsubscribe from this group and stop receiving emails from it, send an email to exceldna+u...@googlegroups.com<mailto:exceldna+u...@googlegroups.com>.
To post to this group, send email to exce...@googlegroups.com<mailto:exce...@googlegroups.com>.

[R. H.]

Up to now, one problem keeps unsolved....

If I open the Excel-Formula-Editor, than the caller is not the whole Array, it seems to be only the first cell.

Now the caller.GetValue() will only return the single cell value!

The getter from range.CurrentArray does not work, it throws an exception...

How can I get the reference to the array-range of this cell???

Romen

[R. H.]

Now I found a solution also for this ugly behav...

First, I register the Application.SheetSelectionChangeEvent and the SheetActivateEvent

There I cache the current cell values for the current selection,
and if the selection is part of an array,

than the content of the CurrentArray range!

key is the sheet and the workbook, value is the address and the range content.

Second, If the formula editor is open, than I try to return the cached value,

if it is the correct one for the Caller.

The caller must be inside the "key" address.

I check with Goverts code

IsInFormulaEditMode()

if the editor is open.

(Govert, please make this public in the next release...)

This makes the formula editor smooth,
(no longer invalid calls with missing or wrong or half filled arguments nor ugly waiting after each key stroke)

ok it does no longer preview any new results...

So my SkipCall works now!

Roman