# How to clear cell formatting from an if statement

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 close merge delete

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.)

( 2017-09-30 13:41:55 +0200 )edit

Sort by » oldest newest most voted

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

-1- Study the BASIC guide.
-2- Study the API of LibreOffice.
-3- Try first examples.

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.

more

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

( 2017-09-30 04:34:07 +0200 )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.

( 2017-09-30 13:19:55 +0200 )edit

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.

more

(?)
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.

( 2017-09-30 20:01:51 +0200 )edit

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)

more

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.

( 2017-09-30 19:11:55 +0200 )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.

( 2017-10-01 01:30:13 +0200 )edit

( 2017-10-01 01:52:45 +0200 )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.

( 2017-10-01 06:30:42 +0200 )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.

( 2017-10-01 13:23:30 +0200 )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.

( 2017-10-02 18:13:40 +0200 )edit