Text highlighting problem

English
#1

Please help to solve the problem of resetting attributes and highlighting the entire text when using a formula (UDF) on the same sheet. It looks like a bug.
The attached example is specially designed for the problem being solved and is presented in a simplified form.
bug-highlight-substrings.ods (19.5 KB)

When calling the Module1.HIGHLIGTH_ALL_ONE_BY_ONE() function from the sheet on which the data is located, both resetting the font color in the range (oRange.CharColor = -1) and highlighting a text fragment with color, if this fragment is collectively equal to the entire string (that is, the concatenation of substrings makes up the entire string or the desired substring corresponds to the entire string) does not work.
#5

Is tdf#143792 related? (didn’t actually look closely to the sample)

#6

No. #143792 refers to multiple selections of whole cells. This one is about text snippets within cells to be formatted by a macro. This works as expected unless the macro is triggered by a user-defined cell function. All contents and formattings are locked while the calculation cascade is running.

#8

tdf#143792 is not about “multiple selections of whole cells”. It is about the difference between whole-cell format vs text run format, and it is reproducible with a single cell selected vs focused. And here, it seems, the problem is OP applies a whole-cell formatting (using oCell) vs text run selection (using oTextCursor).

#19

ok, sorry.

#2

meanwhile Execution of an UDF (UserDefinedFunction) everything outside the cell with the formula on the same sheet is locked and therefore immutable.
The execution from other sheets works, also the procedure-call via =HYPERLINK(…)

Rule of thumb: Dont create UDFs for the intention doing something else except returning to the Cell from where its called.

1 Like
#3

It’s not quite accurate here. Resetting attributes works if you use the com.sun.star.text.XTextCursor object instead of the Cell object. And as for the text highlighting, why does the highlighting of part of the string work and only the highlighting of the entire string does not work?

Following the rule that you have given will require some reworking of the code from me, but in principle it is permissible. However, in some cases, it is convenient to have the formula on the same sheet.

#7

It is indeed wrong that the content is locked while executing UDF - unfortunately. It is only not locked because it’s technically difficult to do. But one must consider everything locked when UDF is executing - including own cell (which is, by the way, unknown to UDF).

See Can a function write data into a function selected cell - #2 by mikekaganski for some background. UDFs must only calculate and return result, nothing more.

#9

Should probably read
wrong that the content is not locked
?

#10

@erAck both :wink:

It is factually incorrect when @karolus wrote:

In fact, it is not locked. Unfortunately. (There are pieces that may be changed; there are pieces that can’t for unclear reasons - my link above also mentions one such thing.)

And now that - that it is not properly locked - is also wrong :slight_smile: conceptually.

#11

Correcting this situation will require a lot of effort and anyway there will be a “craftsman” who will bypass all the defenses. It seems to me easier to agree “Let’s assume that UDF is intended for returning the result of calculations, just this and not anything else”
And every time we come across a decision that violates this agreement, we will write “Hey guy! You’re wrong! It’s not customary in our office to do that!”

1 Like
#12

It is said in the Excel documentation:

A user-defined function called by a formula in a worksheet cell cannot change the environment of Microsoft Excel. This means that such a function cannot do any of the following:

  1. Insert, delete, or format cells on the spreadsheet.
  2. Change another cell’s value.
  3. Move, rename, delete, or add sheets to a workbook.
  4. Change any of the environment options, such as calculation mode or screen views.
  5. Add names to a workbook.
  6. Set properties or execute most methods.
    Any environmental changes should be made through the use of a Visual Basic subroutine.

Do we agree with this?

#13

Basically the same for LibreOffice Calc.

#14

For each of these points, we can come up with situations where the imposed constraint interferes with the implementation of a simple and elegant solution to a complex problem … There are no rules without exceptions, there shouldn’t be any.

  1. Take the entered value and add it to the corresponding list on the adjacent sheet, if it is not already there, let the function write in its cell “Added” or “Already been”
  2. Let the function completely mix the data on the adjacent sheet if the project needs it
  3. Let the function add sheets to a spreadsheet and even (very neatly) Move, rename, delete - if necessary, warn the user about what is about to happen. Why not?
    Etc.
    My opinion is - warn, but don’t forbid.
#15

For each of your points I could come up with explanations why it would be a bad idea and could destroy your calculation, but it’s too late and I’m too lazy now.

1 Like
#16

Me too

Me too

We know all “pro” and “contra” in this dispute. We know that the right position is somewhere in the middle. I think the best option would be something like what is said in PE8: “[We tried different options and came to the conclusion that it] will be good if you follow these rules. You can break these rules at any time if you think it is better that way. [But if something goes wrong and the result does not meet your expectations, then try to do it in accordance with these rules …]”

#4

Cross posting [Calc] Text highlighting problem (View topic) • Apache OpenOffice Community Forum

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.