Loading old files that where constructed without dynamic array support and using resize.

[Kamiel Vandewoude]

I'm rebuilding an AddIn in C# that was previously written in VB and was using the Resize functionality. My Addin doesn't use resize as it is build for office 365. The addin works great, but when i load excel files that where constructed using the previous addin version the excel sheet shows those array results as Crtl+Shift+Enter arrays where there are curly braces around the formula. I would like to be able to detect when the excel files opens and goes over the sheet that the current formula is of that type and was wondering how i could replace these {} arrays with the dynamic ones. I can do this in excel itself by copying the formula and removing the array and then placing the formula in the cell again, but i have a client that have a lot of those excel files that are really large so would take to much time to adapt all these files by hand. It would be very usefull to simply detect when an the result in the sheet is of the '{}' type and more specifically how to remove this and replace this with the dynamic array in C#.

Thanks in advance!

[Govert van Drimmelen]

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

Excel-DNA is now registered on GitHub Sponsors.

Your monthly contribution helps the project thrive.

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

You should know that there are some subtle differences between CSE array formulas and dynamic arrays.

I have a few links to various resources here: Dynamic Arrays · Excel-DNA/ExcelDna Wiki (github.com)

I would suggest you start with a macro that you link to a ribbon button or shortcut key.

The macro will check for such functions, and do the replace.

You'd use the COM object model for this.

Checking for array formulas throughout a large sheet might be tricky,

If you are only looking for a few names, you can use something like Worksheet.UsedRange.Find(....) to identify the cells to look at.

Otherwise, if you want to convert all the formulas, or you have many functions, you might need to go through each cell (or at least each cell with a formula, somehow).

For a particular cell, you can check Range.HasArray, which will be true for CSE array formulas, and false for Dynamic Arrays or ordinary cells.

To change you can read the formula with Range.FormulaArray, then get the whole array range with Range.CurrentArray, which you can then clear.

Finally you write the formula back to the anchor cell with Range.Formula2.

Something like that should work.

Maybe someone else has a simpler plan.

-Govert

[Kedar Kulkarni]

We are in a similar boat. Ours may not be a simpler plan than above. we are onboarding few users at a time so following is our action plan (still work in progress). We have new names for our formula so we have one that support dynamic array and one that is legacy so both can coexist. We plan to provide a ribbon option to upgrade. They can choose to not upgrade or create a new workbook with new formulas and compare with old workbook etc. 

1. Use After_Calculate event to change the cells which contain the formulas. 

2. Use https://ewbi.blogs.com/develops/2007/03/excel_formula_p.html to parse formulas (get the formulas used in a cell) (or similar library https://github.com/spreadsheetlab/XLParser - i havent used)

3. We dont usually have If in our functions so we can rely on excel calc engine to get us the cells which has our formulas with following loop when user wants to replace formulas.

application.calculation = xlcalculationmanual

 for each ws in activeworkbook.worksheets

 ws.enablecalculation = false

 ws.enablecalculation = true

next

application.calculation = xlcalculationautomatic

the above forces the active workbook calculation. and we collect the cell address in our udf and add to a dictionary. If user chooses to update the formulas we goto each formula. We already get the old formula with parser and replace with new formula. (we have renamed formulas for office 365).

May be something on above lines could help - our use case is a bit different. 

[Kamiel Vandewoude]

Thank you for the answers!

I was also trying to implement both functionalities (with and without dynamic array support udf's).

I constructed a VM with excel 2016 on for testing. The functions do exactly the same but will return using the resize function with the wanted return array as input, but when i try this on excel 2016 it only shows the top left cell of my solution and not the full array. What am i missing here? I did exactly the same as in office 365 with dynamic array support but then i added the resize in the end for excel 2016.

Thanks in advance!

Op maandag 20 december 2021 om 17:55:42 UTC+1 schreef kedarku...@gmail.com: