ArrayResize shows "You cannot change part of an array" message

[Nick Tucker]

Hi,

Has any one manged to write a array re-size method that does not show the "You cannot change part of an array" message when overwriting only part of an array?

The problem I am having is described here

http://excel-dna.net/2011/01/30/resizing-excel-udf-result-arrays/

it says

"One case I don’t know how to deal with is when there is an array that would be partially overwritten by the expended function result. In the current version Excel will display an error that says ‘You cannot change part of an array.’, and I replace the formula with a text version of it"

I have tried the version of code shown and the most recent version of the ArrayResizer method but both show a dialog box with the warning/message.

Is there any way to stop the dialog box appearing when only partially over righting an array?

Thanks for any help

Nick

[Govert van Drimmelen]

Hi Nick,

I think one would have to examine the whole target region for overlapping array formulas.

What behaviour would you like to see in this case?

You can't overwrite part of an array... 

Should it just silently fail?

-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.

[Nick Tucker]

Basically I am writing a list of dates and prices with header information so the data looks like so

HeadeInfo1   XXX

HeadeInfo2   YYY

HeadeInfo3   ZZZ

Date1           Price1

Date2           Price1

Date3           Price1

Date4           Price1

Date5           Price1

Date6           Price1

if my service that supplies this data goes off line then I just want to display slightly modified header data i.e.

HeadeInfo1   XXX

HeadeInfo2   YYY

HeadeInfo3   ZZZ1

I do this giving this by posting the new header data to modified ArrayResizer (that also allows the format of the data to be set) that i have attached.

The modified array resizer set all the previous cells to ExcelEmpty.Value and then sets the cells base on the object array passed in.

All this almost works other than once I update the array (using array resizer code) with just the header data I get the dialog box, when i ok the dialog box the correct data is shown with just the header data.

I would prefer not to saliently fail.

Thanks,

Nick

[Govert van Drimmelen]

Hi Nick,

I don't think there's a way to prevent Excel from showing that dialog box when trying to write an array formula over another one.

But in the resizing case, that should not happen. We first check whether the cell is part of an array, and clear it if needed.

Do you know which line of the code causes the dialog?

Maybe there's something wrong with the check....

-Govert

[Nick Tucker]

My ExcelFunction that is called looks like so (the curve key is a cell with a unique key in to get the data for the array to be shown in excel previously discussed in this post https://groups.google.com/forum/#!topic/exceldna/TYmnLBvKf8A)

        [ExcelFunction(Category = ExcelCategory)]

        public static object GetCurveData([ExcelArgument(Description = ExcelCurves)] string curveKey)

        {

            object obj = CurveDataCache.Instance.GetCurveData(curveKey);

            if (obj != null)

            {

                // return header and data

                // create result (with data as Item1 amd formatting as item 2)

                return ExcelHelper.SizeCallerToData(result.Item1, result.Item2);

            }

            else

            {

                // return header only

                // create result (with data as Item1 amd formatting as item 2)

                return ExcelHelper.SizeCallerToData(result.Item1, result.Item2);

            }

        }

It looks like this function returns with just the header (to overwrite the header and the data section) and it is at this point that the dialog is shown and then the macro (which runs asynchronously) is called to clear and resize the array area which, i think, is why when i do ok the dialog the data is correctly shown (because the resize has then completed).

Thanks,

Nick