Rebuilt my ExcelDNA project on a new PC at work and the "Rezize" function gets called but does not work

[Leigh Tilley]

Hi

I rebuilt my ExcelDNA project on a new PC at work and the "Rezize" function gets called but does not work.

Initially i thought there was a problem with my query, as it uses a DLL to talk to ActivePivot, get a 2D array back, and the data is definitely there. In fact if I comment out the "Resize" call it gets passed back to Excel, but i need to resize mt results.

It was working fine on my other PC, with Excel 2016.

is there a way to resize the Excel array in 2016 without the older use of the "Resize" function?

what could have caused this do we think?

        public static object Resize(object[,] array)

        {

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

            if (caller == null)

                return array;

            int rows = array.GetLength(0);

            int columns = array.GetLength(1);

            if ((caller.RowLast - caller.RowFirst + 1 != rows) ||

                (caller.ColumnLast - caller.ColumnFirst + 1 != columns))

            {

                // Size problem: enqueue job, call async update and return #N/A

                // TODO: Add guard for ever-changing result?

                EnqueueResize(caller, rows, columns);

                AsyncRunMacro("DoResizing");

                return ExcelError.ExcelErrorNA;

            }

            // Size is already OK - just return result

            return array;

            //object[,] retArray = new object[columns, rows];

            //for(int i=0;i<array.Length;i++)

            //{

            //    retArray[i, 0] = array[0, i];

            //}

            //return retArray;

        }

[Govert van Drimmelen]

--------------------------------------------------

Excel-DNA is now registered on GitHub Sponsors.

You can add Excel-DNA sponsorship to encourage support and future development, with easy billing through a corporate GitHub account.

-------------------------------------------------- 

Hi Leigh,

Is the Excel version where the 'Resize' is not working also Excel 2016, like the version where you say it works fine?

For Office 365 versions you don't need the 'Resize' workaround at all - Excel has 'Dynamic Arrays' built in, and having it in might cause trouble.

For these versions your function can just return the array directly, and Excel will do the right thing.

 Otherwise I can suggest making a simple add-in that just has the 'Resize' sample code from here:

ExcelDna/ArrayResizer.dna at master · Excel-DNA/ExcelDna (github.com)

Then you can test the new add-in on the different machines, to figure out where things went wrong.

-Govert

[Leigh Tilley]

Hey

Thanks for the prompt reply. The thing is, if I pass back the original array, it doesn't resize in excel 2016.

Or do I need to change my method signature from Object to match the underlying Object[,]?

[Govert van Drimmelen]

Hi Leigh,

I'm a bit confused by your messages.

You say " It was working fine on my other PC, with Excel 2016.", and also " it doesn't resize in excel 2016"

That's why I was asking about the Excel versions and why I suggested the other approach to figuring out what's going on.

-Govert

[Leigh Tilley]

Hey 

Sorry to confuse! :)

So here is what is happening:

PC 1 (old, desktop support told me they'd build a new PC for me): working since I made the project late 2019 into 2020, with tweaks this year. Excel 2016, and has to use the "Resize" callback function.

PC 2 (same apps and programs): rebuilt the project in Visual Studio 2019. Launched it from VS, which launches Excel 2016. opened my test workbook with all of my functions in. Run a query and get #NA all across the resultset. 

So it should behave the same on the new PC as I am using the same Visual Studio and Excel 2016 apps.

I debugged it to check if it was the call to the remote Java ActivePivot cube. No, all fine. Got it down to the "Resize" function. If I comment it out and try to return the Object[,] directly Excel 2016 does not resize it. I notice I cannot change the ExcelDNA signature either as it needs to be Object.

[Leigh Tilley]

I just tested an already deployed xll/dll and it works perfectly, as it always did.

So there must be something weird or missing on this PC. I've asked support for access to my old PC to copy up the C# folder of projects to see it and compare. I will also look at your resizer code on GitHub you shared too and plug that in to try.

[Leigh Tilley]

Hey

Interesting. I just replaced my resize function with your github one and now it is working again. So perhaps somehow it got mangled on copying from my old machine. Seems weird but overall it is now working as expected.

I will try to remove the call to Resize though as did say it should be possible!