Apply "Formula to Value" to all cells in a selection

[najevi]

Version: 7.5.3.2 (X86_64) / LibreOffice Community
Build ID: 9f56dff12ba03b9acd7730a5a481eea045e468f3
CPU threads: 12; OS: Windows 10.0 Build 22621; UI render: Skia/Raster; VCL: win
Locale: en-AU (en_AU); UI: en-GB
Calc: threaded

The problem:

Data >> Calculate >> Formula to Value

works as you would expect on a single selected cell OR on a contiguous/adjacent range of cells that have been selected.

Indeed the same can be said of Copy (Ctrl+c) followed by Paste Special (Ctrl+Shift+c) … it works as you would expect so long as the selected cells are adjacent to one another. When even one cell is disjoint from the others the now disjoint cells can still be copied as one selection but none of them can be (Paste-Special)ed onto themselves as one batch operation.

What I have is a disjoint selection of cells selected using

Edit >> Find and Replace (
        "target_string"; 
        [Current selection only]; 
        [Search in(Formulae)] 
        ) >> [Find All]

• Is there a simple macro (or better) way to iteratively apply the “Formula to Value” operation on each cell in a selected range?

As a clumsy workaround I could rearrange my spreadsheet so that all cells with matching formulae are guaranteed to be adjacent (just to make Formula to Value and/or Copy&PasteSpecial function as needed) but before taking that step I am asking here if there is a more elegant solution.

CONTEXT
(Not strictly relevant but … who knows? … I don’t know what I don’t yet know!!)

I am using Calc as a very simple database in an accounting Ledger style of application. When creating a new record (a single row) in that Ledger I prefill data into various cells in that one row by using the VLOOKUP function and indexing into columns of a predefined Database Range that I have named “db_Vendor_prefill”. So you can probably imagine how easy it is to select that entire row/record and then use the name of that Database Range as a argument to very easily select all cells that have been prefilled.

• Is there some other method to prefill data into many disjoint fields of a new record using values directly?

[schiavinatto]

Feed the database already without the formulas, fill in the data in a “form” and save data values in the database.

Post an example file, it’s easier to suggest more precisely.

[najevi]

I am attaching an anonymized version of the Ledger spreadsheet application that I am developing in LO Calc.

I don’t fully understand @schiavinatto 's remark but they now have me wondering if I’m approaching this Ledger from the wrong direction. I rather loosely use the term “database” even when I’m thinking of a simple named range that I use to VLOOKUP values or enumerate allowed values for Data Validation.

What I am now pondering is the idea of having just a small number (<10) of rows of Ledger records that are “work in progress” and then once all steps of the workflow are completed (usually a matter of days, not weeks) then pushing just the minimal set of cell values (not the formulae and not repeated fields) from each completed record into a true Database that becomes the Ledger-actual or Ledger-proper.
That database of values then becomes the nexus I can query seven ways from sunday to provide at-a-glance insight into year-to-date spending versus target “burn-rate” per period until end of year.
Such an approach should completely sidestep my currently perceived need to convert “Formula to Value” , n-est-ce pas?

The more I think of it, the more I think that is the safer direction I ought to proceed. However, I do remain open to further input especially viz a viz macro based solutions.

The devil is usually in the details, and by sharing the attached you will realize some detail omitted from my original post:

1. the targeted primary user of this sheet is a vision impaired person (VIP) using a Screen Reader (JAWS) to navigate the spreadsheet in a Windows + MS Excel environment. (i.e. not LO Calc)
2. the secondary target user is a sighted support worker who might be assisting the VIP either in person or remotely (via a video call or face-time).

So when you see a plethora of comments, color-coded columns, numbered headers and “seemingly blank” partitioning columns, etc. … well, all of that busy verbosity are deliberate efforts to provide navigation markers that a screen reader (JAWS in this case, not NVDA) will make audible and thereby assist the vision impaired primary user to navigate the spreadsheet efficiently.

Data Validation is leveraged so as to mitigate the occurrence of typo errors by enumerating allowed values and thereby facilitating the correction of input errors in real time.

• Are there known features that do not transfer well to MS Excel?

Now that you understand that development is happening in LO Calc but that the final use environment will be MS Excel any advice you can offer as to what LO Calc features are known not to migrate cleanly to MS Excel will be greatly appreciated. To date I have only needed to share unsophisticated spreadsheets and those have migrated without any loss of information, function or format.

Regards,
VIP Plan Ledger.ods (49.4 KB)

[schiavinatto]

If the environment to be used is in Excel, I advise you to make the spreadsheet in Excel, because any automation using a Macro in Basic, nothing will work in Excel.

[najevi]

I am thinking that I will no longer need to mess around with converting FormulaToValue (i.e. not need any macros) if I stop trying to use a spreadsheet (with formulae) as a database and instead use a Database Range of only the values produced by those formulae.

I need to sleep on this and come back with a clear head.

[Lupp]

No comments concerning your special case! No guarantee of any kind.
Situations may always be more complicatd than how they were described.

@najevi

… who knows? … I don’t know what I don’t yet know!!

I also don’t.

What I call “castrating formula ranges” is pretty crude surgery.
In the attached example I give a solution, and demonstrate a few unexpected effects that need to be expected.
The code is simple. Sniffing for problematic cases would be an AI task possibly.

Sub replaceFormulaByResultForSelection()
REM This is very raw: If the selection contains cells with internal
REM text formating or URL fields, unwanted effects are to be expected.
REM Assert that the CurrentSelection was made ba a F&R into formulas
REM before you use this code.
selectionAsRanges = sheetCellSetAsRanges(ThisComponent.CurrentSelection)
For Each range In selectionAsRanges
 range.setDataArray(range.getDataArray())
Next range
End Sub

REM Helper function:
Function sheetCellSetAsRanges(pDiverse As Object) As Object
On Local Error Goto fail
If pDiverse.supportsService("com.sun.star.sheet.SheetCellRanges") Then
  sheetCellSetAsRanges = pDiverse
Else
Dim calcDoc As Object, unified As Object
  calcDoc = pDiverse.Spreadsheet.DrawPage.Forms.Parent REM Funny detour; thanks to Andreas Säger.
  unified = calcDoc.CreateInstance("com.sun.star.sheet.SheetCellRanges")
  unified.addRangeAddress(pDiverse.RangeAddress, False)
  sheetCellSetAsRanges = unified
EndIf
fail:
End Function

See also attached example:
disask93400castrateFormulaRanges.ods (17.8 KB)

[sokol92]

@Lupp

text formating or URL fields, unwanted effects are to be expected.

Hello, @Lupp!
Perhaps there will be fewer side effects.

' Changes formulas to formula values in CurrentSelection
Sub Selection_FormulaToValue
  Range_FormulaToValue ThisComponent.CurrentSelection
End Sub

' lang:en
' Changes formulas to formula values.
' arg - one or more rectangular cell ranges.
Sub Range_FormulaToValue (ByVal arg As Object)
  Static oDisp As Object
  Dim oDoc as Object, oRange as Object
  Dim t As Long
  t=Range_Type(arg)
  If t=0 Then Exit Sub 
  If t=3 Then
     For Each oRange In arg
        Range_FormulaToValue oRange
     Next oRange
     Exit Sub
  End If
  
  oRange=arg
  oDoc=oRange.Spreadsheet.DrawPage.Forms.Parent
  If oDisp Is Nothing Then oDisp = createUnoService("com.sun.star.frame.DispatchHelper")
  oDoc.CurrentController.Select oRange   
  oDisp.executeDispatch oDoc.CurrentController.Frame,".uno:ConvertFormulaToValue", "", 0, Array()
End Sub

' lang:en
' Returns the type of the oRange object.
' Return codes:
' 1 one cell (the "com.sun.star.sheet.SheetCell" interface is supported).
' 2 rectangular range of cells (the "com.sun.star.sheet.SheetCellRange" interface is supported).
' 3 multiple rectangular cell ranges (the "com.sun.star.sheet.SheetCellRanges" interface is supported).
' 0 none of the above.
Function Range_Type(Byval oRange) As Long
  Dim oAdr
  On Error GoTo ErrLabel
  If oRange.supportsService("com.sun.star.sheet.SheetCell") Then
    Range_Type=1
  ElseIf oRange.supportsService("com.sun.star.sheet.SheetCellRanges") Then  
    Range_Type=3
  ElseIf oRange.supportsService("com.sun.star.sheet.SheetCellRange") Then
    Range_Type=2
  Else
    Range_Type=0
  End If
  
ErrLabel:
End Function

[Lupp]

@sokol92

Hello, @Lupp!
Perhaps there will be fewer side effects.

I have no problems with side effects, but just wanted to tell the OriginalPoster that there may be some under conditions that are only incidentally disclaimed by the OriginalQuestion.
Problematic effects concerning the result of a formula being converted itself, and also refering to results of formulas eventually converted, is next to unavoidable, imo, and the usage of setDataArray().should in many cases be an acceptable solution. There are exceptions.
A slightly enhanced version of the code posted in my above answer would be:

Sub replaceFormulaByResultForSheetCellRangesEnhanced(Optional pDiverse)
If IsMissing(pDiverse) Then
 cRgs = ThisComponent.CurrentSelection
Else
 cRgs = pDiverse
EndIf
cRgs = sheetCellSetAsRanges(cRgs)
 For Each rg In cRgs
 foRgs = rg.queryContentCells(16) REM Flag 16 meaning 'Formula'.
 For Each foRg In foRgs
  foRg.setDataArray(foRg.getDataArray())
 Next foRg
Next rg
End Sub
REM The helper function *sheetCellSetAsRanges()* is found in my first post.
REM This doesn't try to treat the FORMULA-formula exceptions (and similar ones).

BTW: What advantages do you expect of stone age syntax without argument parentheses?

[najevi]

Thank you @Lupp , I do agree with the starting point being a selection of cells chosen using Find&Replace and Searching in Formulae. In my case I can go one step further because the keyword is explicitly a table used for prefill of data. That is key to avoiding unintended collateral damage!

[najevi]

Thank you @sokol92 , I can definitely follow your code excerpt more easily than I could @Lupp 's. The “For Each” loop in both cases is what I zeroed in on and I could understand your use of
ConvertFormulaToValue more easily than the .set/get DataArray() code.

[sokol92]

@Lupp

What advantages do you expect of stone age syntax without argument parentheses?

Offtop. I got to know LIbreOffice in 2019 (not earlier, unfortunately), so I don’t know the difference between the old and new syntax.
Some of my modules are for both VBA and LO Basic, so I “automatically” apply compatible syntax.

[Lupp]

@sokol92: Sorry.
I simply was surprised. There wasn’t yet much Basic code posted here using that notation. But the questioner may even prefer the way you presented Basic if he comes from VBA. Donno. I would prefer a harmonized/unambiguous/clear syntax.
I didn’t compare to older StarOffice / OpenOffice / LibreOffice Basic, but to some old versions of standalone Basic I had to use decades ago despite my dislike because there was no practicable alternative for me if I wanted to program something at all. (I also dislike VBA. A born disliker?)
@najevi: Any object supporting the service com.sun.star.sheet.SheetCellRange has the methods .getDataArray() and .setDataArray(). The .DataArray generally keeps the values of entered data (actual content) and of calculated results (if the content is a formula) for the cells as well. Any usage of .setDataArray(), however, clears the .FormulaArray in advance. Knowing this you will easily understand the idea behind my code.
@all: A pettiness.
The .uno: command used with the DispatchHelper by sokol92 replaces error results with empty strings. My way to do it replaces all of them with the error value #N/A
The elements of a DataArray can’t represent error values. The displayed error values are managed using the .String property of every single cell.

[najevi]

@Lupp

Any object supporting the service com.sun.star.sheet.SheetCellRange has the methods .getDataArray() and .setDataArray(). The .DataArray generally keeps the values of entered data (actual content) and of calculated results (if the content is a formula) for the cells as well. Any usage of .setDataArray(), however, clears the .FormulaArray in advance. Knowing this you will easily understand the idea behind my code.

Yes. That does help, thank you!

…and yes, given that my spreadsheet will ultimately be used in MS Excel; if I do use a macro for this (or any other purpose) I probably would prefer to use code that is going to work in both LO Calc and MS Excel. (That is just a little intimidating at the present time!)