Ask Your Question
0

How to clear cell formatting from an if statement

asked 2017-09-29 21:23:53 +0100

DesertMan gravatar image

created a macro (clear_formatting) to clear the direct formatting of some cells in the spreadsheet. now, want to activate macro in the following way:

if(cellA="",clear_formatting(),formula)

where cellA is either populated or not, clear_formatting() is the previously recorded macro (verified to work from run macro) and formula is the value to be entered if cellA is populated.

edit retag flag offensive close merge delete

Comments

1

If you want to quote a Calc formula, please make it clear by prefixing the leading "=", by using upper case for the names of standard functions, and by explaining how pseudo-parameters should be interpreted. In addition you should use the semicolon as the parameter delimiter. (See below.)

Lupp gravatar imageLupp ( 2017-09-30 13:41:55 +0100 )edit

3 Answers

Sort by » oldest newest most voted
0

answered 2017-09-29 23:44:09 +0100

Lupp gravatar image

updated 2017-09-30 19:16:50 +0100

Did you ever write a bit of code in one of the common (imperative) programming languages? In BASIC?
Did you ever access/handle an object like a cell of a spreadsheet?
The complete alternative in BASIC, e.g, is

IF condition Then
thenStatementBlock
Else
elseStatementBlock
End If
A blank cell is characterised by oCell.Type=3

Driver's license first. Then drive.
-1- Study the BASIC guide.
-2- Study the API of LibreOffice.
-3- Try first examples.
-4- Do not start with "recorded macros".

Sub Test
REM Get Cell C12 of the fisrt sheet (leftmost tab).
oCell = ThisComponent.Sheets(0).GetCellByPosition(2, 11) 
IF oCell.Type=0 Then
  oCell.ClearContents(com.sun.star.sheet.CellFlags.HARDATTR) 
End If  
REM "Incomplete alternative" or "conditional staement" here.
REM See https://api.libreoffice.org/docs/idl/ref/namespacecom_1_1sun_1_1star_1_1sheet_1_1CellFlags.html
End Sub

(Edited regarding the comment by @DesertMan (OQ) below.)

If you want to call a routine working on any Cell / CellRange / CellRanges as objects you need to pass parameters identifying the object and making it accessible for the code.
In your case a call to the respective function, intending to apply the 'Ctrl+M' action to the one cell the formula resides in, might read:
=IF(A1="";CLEAR_FORMATTING_1CELL(SHEET(A1); ROW(A1); COLUMN(A1));A1+1)
Based on this call user code can assign the respective cell object like this:
myCell = ThisComponent.Sheets(param1-1).GetCellByPosition(param3-1, param2-1)
You surely understand that this code is just a rough sketch.

Anyway the intended action will not be performed if restrictions mentioned in my comment to the answer by @Jim K apply.

To all: This is an international forum in English language. The only parameter delimiter supported locale-independent for Calc formulae is the semicolon (not the comma).
This is different, of course, in BASIC.

edit flag offensive delete link more

Comments

This code is helpful, although it sounds like you misunderstood the question.

Jim K gravatar imageJim K ( 2017-09-30 04:34:07 +0100 )edit

OK. I considered you might mean the question as @Jim K interpreted it. However, I thought in this case you would also know that a function called from a Calc formula neither has access to the cell containing the formula nor to one of the cells referenced in parameter positions as an objct. Only values are passed for the parameters.
See amendment to my answer by editing.

Lupp gravatar imageLupp ( 2017-09-30 13:19:55 +0100 )edit
0

answered 2017-09-30 19:45:54 +0100

DesertMan gravatar image

Thanks. I have a lot of experience in VisualBasic macros with Excel, but am just learning (1) that posting requires all the information without regard for the experience of the responders and (2) the process of writing and using macros in OpenOffice Calc.

@Jim K, Great idea to put the cell range in the macro variable list. I will try it.

edit flag offensive delete link more

Comments

(?)
Did you also read my comments on side-effects of user functions?
You may end up with a well filled account of for wasted time otherwise.
Please tell us if you find a way to circumvent the restrictions.

Lupp gravatar imageLupp ( 2017-09-30 20:01:51 +0100 )edit
0

answered 2017-09-30 04:22:58 +0100

Jim K gravatar image

updated 2017-09-30 04:26:28 +0100

Create a user-defined function.

Function clear_formatting()
    REM Put your verified code here.
    clear_formatting = ""
End Function

Then call it like this.

=IF(A1="",CLEAR_FORMATTING(),A1+1)
edit flag offensive delete link more

Comments

For a long time StarOffice and its successors did not allow formulae of Calc to change anything in the same sheet by side-effects of user functions called during the evaluation.
The restriction was substantially loosened by LibreOffice some time ago.
However, I do not know a published specification telling what is allowed now, and what still is prohibited.
The action under discussion here can not be performed by a user-function call from a formula.in the same sheet.

Lupp gravatar imageLupp ( 2017-09-30 19:11:55 +0100 )edit

@Lupp: That's a good point which I did not consider. However, when running across this before, I believe there were ways of overcoming the difficulty. Maybe an event handler? @DesertMan: If you encounter the problem Lupp described, then post the code and we can look for a workaround.

Jim K gravatar imageJim K ( 2017-10-01 01:30:13 +0100 )edit

@Jim K: I am eager to learn about your ideas. See also bug tdf#100297. (I updated it a few hours ago.)

Lupp gravatar imageLupp ( 2017-10-01 01:52:45 +0100 )edit

@Lupp: The discussion I had in mind is at https://stackoverflow.com/a/39254907/.... However, this may be a different issue. For the present topic, a workaround is to set up a Python loop in a separate thread to make changes to other cells, like https://stackoverflow.com/a/44988206/.... For example, the Python loop could monitor the return value of the UDF that gets displayed in the cell. Alternatively, perhaps the Content Changed sheet event could see the result of the UDF.

Jim K gravatar imageJim K ( 2017-10-01 06:30:42 +0100 )edit

Concerning the links I didn't see a close relation to this topic. Concerning the "separate thread" idea I am somehow sceptic. If started from a function body it should also be subject to the restrictions. If running and just waiting it needs a trigger and access to information about the cell(s) to work on.
Most likely I didn't understand because I am not actally using Python, and only rarely go deeper into the LibO api. And: I do not know an efficient way to act on recalculations per cell.

Lupp gravatar imageLupp ( 2017-10-01 13:23:30 +0100 )edit

@Lupp: What you have said is correct. The thread would monitor certain cells and react accordingly. And no, it could not be started from within the function body. Perhaps this is not terribly efficient, but it would work.

Jim K gravatar imageJim K ( 2017-10-02 18:13:40 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2017-09-29 21:23:53 +0100

Seen: 263 times

Last updated: Sep 30 '17