How to set ThisCell.String not changing ThisCell.Value?

[Lupp]

(concerning BASIC and/or uno, API)

A cell (in short denoted by "_" here) containing a numerical value for the _.Value property, should return

Code: Select all

 (_.Type = 1) OR ((_.Type = 3) AND  (_.FormulaResultType = 1)) = True  

At the same time _.String contains the text to which the value was formatted for display.
Is there a way to replace this text by anotherone without changing the _.Type / _.FormulaResultType and the _.Value?

(Using _.String = NewText will empty the _.Formula content and set _.Value to 0 (zero). The type properties are ReadOnly.)

[FJCC]

I think the only way to do that is to create a special number format. What kind of string are you trying to use?

[Lupp]

Salve FJCC!

You may be right with ...

I think the only way to do that is to create a special number format.

...but:
My question was triggered by my considerations related to a thread (http://en.libreofficeforum.org/node/11492) in another forum. I first posted a suggestion based on custom programming there, addressed to Zizi64, who took part in that thread. Then I could spare the time to experimantally implement the concept, demonstrating it in an example. I will attach it. If you take the risk to run my "macros" you will see that it seems to work satisfyingly on the surface. Going a bit deeper you will also see the disadvantages of the approach.
Aside from the general retention concerning user code, there are mainly:

1. Every single value converted into a HEX format can only be displayed this way creating a TEXT mirroring the SPECIFIC VALUE to WORK AS THE FORMAT CODE OF THE SPECIFIC CELL. (This was the basic idea, however.)
2. This is automatically applied to cells possessing a proper NamedCellStyle based on an event handler for the 'Changed content' event of the sheet.
3. The method does not apply therefore to cells having changed their values based on recalculation.
4. The list of user defined format codes under 'Numbers' is growing inexorably. Codes no longer used are not removed automatically - and I even don't know a way to do it by my code.

A much more promising approach would be, of course, to interpret one single added format code like "0x" and have the Sub save the string for display in the cell's property _.String. Below the level of things I'm not capable of (creating an extension or even offering an enhancement to the Calc code itself) a working answer to my question would allow for a better solution. I wouldn't file a feature request in this matter. From my point of view it's playground stuff. I don't actually need it. (The 'Engineering' format was introduced with LibO 5.0, however.)

Regards
Wolfgang

Editing (2015-09-17T10:04UCT+1)
Concerning Nr 4: Inspecting the NumberFormats object in more detail I found the Method 'RemoveByKey'. I now lack knowledge about how to efficiently find out whether or not a key is still used for any cell. This must be done before removing a key in the context.