Change Number Format using Excel-DNA

[Nadeem Akhtar]

Hi all,

I am pulling some numeric data using UDF from an API and using ArrayResizer function of Excel-DNA to transform the result for displaying in excel. Now, the number format of the returned data is "General" and Excel is not allowing me to apply the formulas like AVERAGE on the result. Any suggestion to make the result in such format that excel will allow the native formulas to run?

[Naju Mancheril]

Hi,

Can you try running the formula ISNUMBER on one of these values. I think it's more likely that the values are text or something. The format shouldn't matter.

If you do want to change the format, you can change the NumberFormat property on the range you are modifying.

[Nadeem Akhtar]

ISNUMBER is returning false. The result consists of headings and values, I can't set all of them to same format i.e. Number.

[Naju Mancheril]

What happens when you call ISNUMBER on just the values? Does it return true? If not you should probably parse the values returned by the API and return the parsed doubles to excel.

[Govert van Drimmelen]

Hi Nadeem,

The array resizer sample does not change the formatting of the cells, and if your function is returning doubles for those cells, I'd expect Excel to show them as doubles.

What are you returning in your array - is this an array with only strings, or a mixture of strings and numbers?

If you change the formatting of a cell in the number part of the result, does it change to a number, and does it stay that way for the next time you recalculate.

Also, if the target area for your formula was already formatted as 'Text', Excel will not reformat just because your array returns doubles.

In summary: The behaviour with or without the resizer should be the same. If your array function is returning doubles, they should be displayed in the same way a regular function returning a double is displayed.

If this is not what you find, I'm happy to explore further.

-Govert

[Nadeem Akhtar]

Hi Govert,

I am returning an object array that contains both string and double. Basically my data is like the value of some factors - column header as smean and the rest are values and that are in double. So, I can't return the double as result to excel. Furthermore, excel is treating all the array as text because ISNUMBER function of excel is returning false on result.

Now, I want to return mixture of string and double as in object array but want excel or Excel-DNA to differentiate between them.

Regards,

Nadeem Akhtar

[Govert van Drimmelen]

Hi Nadeem,

It works correctly in my tests - returning an object[,] array with a mixture of strings and doubles leaves the doubles correctly as numbers in the sheet.

Can you check with the Excel-DNA v 0.32 release candidate from here: https://exceldna.codeplex.com/releases/view/119190

and use the updated sample in Distribution\Samples\ArrayRasizer.dna. If you then have this problem even when using your function in a fresh Excel workbook, you can perhaps post a small example of the code you're running and I can look further.

Regards,

Govert

[Nadeem Akhtar]

Hi Govert,

Thanks a lot Govert for the help. Actually I was receiving the data from API is Json format and was converting the numbers to string and then returning them in object array.

Regards,