Cell formatting not possible on formula cell

Add-in Express™ Support Service
That's what is more important than anything else

Cell formatting not possible on formula cell
 
Henjo van Rees




Posts: 46
 Joined: 2018-12-10
I can't apply cell formatting on the calling cell (Range) which contains the formula to my function created with ADX.

Not within Excel itself, and not using Range.NumberFormat.

Other cells with formulas (like SUM()) etc can be formatted in Excel, but not the function from ADX.

Any idea why that is? The cell contains data like "2019-01-01" which I would to format in a specific date format, but I can't..
Posted 29 Jan, 2020 09:36:04 Top
nwein




Posts: 577
 Joined: 2011-03-28
This is definitely possible. However, you should be changing formatting not from the XLL module but from the COM module.
You can use the calculate event to apply the changes you like.
Posted 29 Jan, 2020 15:31:21 Top
Henjo van Rees




Posts: 46
 Joined: 2018-12-10
How do I apply formatting from the XLL module?
I have 1 module (DLL) which is used as COM module and XLL module.

To be clear:
When in Excel itself, changing the formatting with "Format Cells..." does nothing.
I expect that doing it in my C# code will not do the same.
Posted 31 Jan, 2020 02:48:44 Top
Andrei Smolin


Add-in Express team


Posts: 18821
 Joined: 2006-05-11
Hello Henjo,

Henjo van Rees writes:
When in Excel itself, changing the formatting with "Format Cells..." does nothing.


Start the Macro Recorder to record a VBA macro while doing this. The macro should show the classes/members involved in the process.


Andrei Smolin
Add-in Express Team Leader
Posted 31 Jan, 2020 05:25:13 Top
Henjo van Rees




Posts: 46
 Joined: 2018-12-10
I recorded the macro in Excel, which:
- Retrieved + set data in cells using my Excel Add-in function ("PRLCO") in cell C1.
- The PRLCO function writes to cells C1 - I1
- Changed cell date formatting of cell C1 to another format

I observed NO changes in display date format.
I am unsure how this macro helps, it doesn't show me anything.. 



Sub Macro1()
'
' Macro1 Macro
'

'
    ActiveCell.FormulaR1C1 = _
        "=PRLCO(R[1]C[-2],R[5]C[-2],,R[8]C[-2]:R[14]C[-2],R[21]C[-2],,0)"
    Range("C1").Select
    Selection.NumberFormat = "d/mm/yy;@"
End Sub
Posted 03 Feb, 2020 02:30:53 Top
Andrei Smolin


Add-in Express team


Posts: 18821
 Joined: 2006-05-11
Hello Henjo,

That macro shows that you set the NumberFormat. In my Excel, I get the same format applied if I open the Format Cells dialog and choose Date | 3/14/12 | English (United States) - this is the default language on my system. Here's the macro created while I change the format and value of a cell: 


Sub Macro2()
'
' Macro2 Macro
'

'
    Selection.NumberFormat = "[$-en-US]d-mmm-yyyy;@"
    Range("E7").Select
    ActiveCell.FormulaR1C1 = "2/3/2020"
    Range("E7").Select
    Selection.NumberFormat = "m/d/yy;@"
    Range("F8").Select
End Sub


Accordingly, the cell reflects the following date formats: "3-Feb-2010" and "2/3/20". Does these formats work in the same way for you?


Andrei Smolin
Add-in Express Team Leader
Posted 03 Feb, 2020 04:26:31 Top
Henjo van Rees




Posts: 46
 Joined: 2018-12-10
Hello,

No the same Cell Formatting does not work for me.

It works for cells D1-I1, not not for cell C1 which contains the PRLCO formula and the date like "01-12-2020".

Perhaps good to mention is that in our Excel plugin we set the date value as a string like "01-12-2020" and let Excel apply formatting automatically.

This works for all cells, except for the cell C1 with the formula.
Posted 03 Feb, 2020 04:49:13 Top
Andrei Smolin


Add-in Express team


Posts: 18821
 Joined: 2006-05-11
Hello Henjo,

Henjo van Rees writes:
we set the date value as a string like "01-12-2020"


This is the casue of the issue.

I have these test functions: 

public static string GetDateAsString()
{
    return DateTime.Now.ToShortDateString();
}
public static double GetDateAsDouble()
{
    return DateTime.Now.ToOADate();
}


The first one produces the issue. In fact this isn't an issue: this is expected as Excel can't format a string as date. The second function returns a double e.g. 43864.5715002893 displayed as 43864.57. Formatting that value as date works as expected.


Andrei Smolin
Add-in Express Team Leader
Posted 03 Feb, 2020 05:37:35 Top