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

Creating a macro - Writing a Script - Using the API

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

Postby Lupp » Wed Sep 16, 2015 1:33 pm

(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   Expand viewCollapse view
(_.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.)
On Windows 10: LibreOffice 7.0 and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 2896
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

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

Postby FJCC » Wed Sep 16, 2015 2:13 pm

I think the only way to do that is to create a special number format. What kind of string are you trying to use?
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 7680
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

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

Postby Lupp » Wed Sep 16, 2015 3:08 pm

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.
Attachments
lof11492ProcessingHexSpecials003.ods
(16.7 KiB) Downloaded 108 times
On Windows 10: LibreOffice 7.0 and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 2896
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany


Return to Macros and UNO API

Who is online

Users browsing this forum: No registered users and 5 guests