Possible ways of clearing stale data in sheet.

[Gregory L]

Hi,

After the user run our UDF for 2nd time, the output can be sometimes smaller (less rows). So some stale data stays in the bottom (see screenshot).

How it can be cleared?

Clearing all cells in the sheet isn't suitable, because it can delete some other saved data in other cells.

May be storing affected area at the point of time when the function is invoked (in cell metadata/hidden sheet/etc.) ? Is there such functionality in ExcelDNA?

What approach do you suggest?

[Govert van Drimmelen]

Hi Gregory,

 

What approach are you taking to get the data there in the first place?

 

I recommend only using the Dynamic Arrays support in current Excel, or using the ArrayResizer approach for legacy Excel versions:

https://github.com/Excel-DNA/ExcelDna/blob/master/Distribution/Samples/ArrayResizer.dna

 

Both of these will correctly manage the output array.

 

-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 view this discussion on the web visit https://groups.google.com/d/msgid/exceldna/0e8bb679-96d4-4fcf-ad95-47b0d8564571%40googlegroups.com.

[Gregory L]

Currently we insert table data in following way (see screenshot)

On Thursday, May 14, 2020 at 2:48:17 PM UTC+3, Govert van Drimmelen wrote:

Hi Gregory,

 

What approach are you taking to get the data there in the first place?

 

I recommend only using the Dynamic Arrays support in current Excel, or using the ArrayResizer approach for legacy Excel versions:

https://github.com/Excel-DNA/ExcelDna/blob/master/Distribution/Samples/ArrayResizer.dna

 

Both of these will correctly manage the output array.

 

-Govert

 

 

From: exce...@googlegroups.com <exce...@googlegroups.com> On Behalf Of Gregory L
Sent: 14 May 2020 13:44
To: Excel-DNA <exce...@googlegroups.com>
Subject: [ExcelDna] Possible ways of clearing stale data in sheet.

 

Hi,

 

After the user run our UDF for 2nd time, the output can be sometimes smaller (less rows). So some stale data stays in the bottom (see screenshot).

How it can be cleared?

Clearing all cells in the sheet isn't suitable, because it can delete some other saved data in other cells.

May be storing affected area at the point of time when the function is invoked (in cell metadata/hidden sheet/etc.) ? Is there such functionality in ExcelDNA?

What approach do you suggest?

--
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 exce...@googlegroups.com.

[Govert van Drimmelen]

Yes, that approach goes against the functional calculation style if Excel, and will have the kind of problems you report 

-Govert

To unsubscribe from this group and stop receiving emails from it, send an email to exceldna+u...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/exceldna/286ec766-41be-4d69-a412-84df675386a7%40googlegroups.com.

[Gregory L]

So you are suggesting to switch to the Dynamic Arrays approach.

I'm a bit worried about it, because it will demand huge refactoring, since our code depends heavily on the legacy approach.

And I still don't understand how Dynamic Arrays approach wil solve the problem of stale data. Can you please elaborate on it?

Additional question does it affect format of cells somehow? (We don't want format to be affected, because we have separate sophisticated logic for setting formats)

10x!

To view this discussion on the web visit https://groups.google.com/d/msgid/exceldna/286ec766-41be-4d69-a412-84df675386a7%40googlegroups.com.

[Govert van Drimmelen]

The Dynamic Arrays feature in new Excel is very good, if you’re able to use a version of Excel that supports this.

I have dumped a bunch of useful links here: https://github.com/Excel-DNA/ExcelDna/wiki/Dynamic-Arrays

 

To use this from your UDF you just return the array from the function directly – no need for the extra macro to run. Excel will detect the result size required, and return the values to a region of the right size. If an update of the functions returns a larger or smaller array, it dynamically resizes, clearing out the extra space if needed. Finally there is some extra syntax to indicate you want to refer to the whole array that is anchored at a cell – so you can say =SUM(A1#) to indicate you want to add up all the values in the array that is anchored at A1. Cell formatting is not affected by this.

 

It’s a well thought out feature that works very well with UDFs, and will simplify your code substantially.

 

The only snag is that it is not supported on old versions, and also not on Excel 2019. So you need an Office 365 subscription.

But it’s the future and it’s available now.

 

-Govert

 

 

From: exce...@googlegroups.com <exce...@googlegroups.com> On Behalf Of Gregory L
Sent: 14 May 2020 19:06
To: Excel-DNA <exce...@googlegroups.com>
Subject: Re: [ExcelDna] Possible ways of clearing stale data in sheet.

 

So you are suggesting to switch to the Dynamic Arrays approach.

I'm a bit worried about it, because it will demand huge refactoring, since our code depends heavily on the legacy approach.

And I still don't understand how Dynamic Arrays approach wil solve the problem of stale data. Can you please elaborate on it?

Additional question does it affect format of cells somehow? (We don't want format to be affected, because we have separate sophisticated logic for setting formats)

 

10x!

On Thursday, May 14, 2020 at 3:17:41 PM UTC+3, Govert van Drimmelen wrote:

Yes, that approach goes against the functional calculation style if Excel, and will have the kind of problems you report 

 

-Govert

 

On Thu, 14 May 2020, 14:04 Gregory L, <lev...@gmail.com> wrote:

To unsubscribe from this group and stop receiving emails from it, send an email to exceldna+u...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/exceldna/ee20016c-4c59-4295-9901-1ee33480e776%40googlegroups.com.

[guido debouver]

Govert,

I am considering to move to the Dynamic Array option,

Are you saying that I can conert by just omitting the standard Resize() function to use the dynamic Array function.

or do i need to apply other changing ( eg in the code or packing process )

<ExcelFunction(Category:="...", Description:="...")>

Public Function whatever(

        <ExcelArgument(Description:="...")> ...(,) As Object,

        ...) 

As Object(,)

        Dim x(,) As Object

        ....

        Return Resize(x)   ----> Just remove the resize here to use dynamic Functions ???

    End Function

kind regards

guido