Highlight formulas that break from a pattern?

English
#1

I’ve got a spreadsheet that unfortunately combines the raw data with the presentation, as I find is common in spreadsheets and often a major source of errors.

After adding rows here and there, some of the formulas in the spreadsheet I’ve now realized are broken and I didn’t notice at the time. It’s not obvious anymore which ones are likely broken, so I’m finding myself yet again in spreadsheet heaven and having to check all the formulas to make sure they’re all right.

Generally the formulas follow a pattern, such as =SUM(B2:B4) (dollar format), =SUM(C2:C4) (general number format), =SUM(D2:D4) (dollar format), =SUM(E2:E4) (general number format), etc. until the pattern eventually breaks at the total columns on the far right.

The closest thing I’ve found to helping me “see” formula errors at a glance are View -> Show Formula and View -> Value Highlighting, which are both nice, but don’t help specifically with recognizing these kinds of common formula patterns.

Is there a way to tell LibreOffice to highlight when formula patterns like this change?

#2

In the programming world, this might be where a linter comes in handy – something that scans your code for common potential pitfalls or outright errors. Are there such things for LibreOffice Calc spreadsheets?

#3

Yes indeed! If you ask the question not “Does this match some pattern?”, but “Is there an error here?”, then the result will most often be the same. Please see here

#4

Can you summarize the macro code?

#5

Sorry, I didn’t understand this question. The code is open, contained in an example spreadsheet. Perhaps I didn’t understand the word “summarize” - English is not my native language (Basic is closer to me)

#6

Yes, indeed, and I do appreciate it. The thing is, some if not most people visiting this page or the other question, are more likely to try out a macro if it’s not buried in an example file, but rather explained here in the answer on the page.

#7

@CivFan: You can post your own answer here with the explanation, along with a link to the answer by @JohnSUN if that is where it comes from.

#8

It’s not my answer to give. I’m also not in the habit of opening unknown office files with macros.

#9

If you’re concerned about viruses, you can disable macros when opening the document, as long as you set LibreOffice to ask about it beforehand. Or you could even unzip the .ods file and view the macro with a text editor. Should be perfectly safe.

#10

Regarding “not my answer to give,” it sounds like you are expecting @JohnSUN to do everything for you (which he is unlikely to do). Why not take some of the responsibility yourself? This is a collaborative site.

If you don’t understand why he may not be interested in posting another answer. see Answer or comment, which one do I use?.

#11

No it’s literally not my answer, as in I don’t understand the answer so I’m not in a position to give it. I may or may not get to the point where I download the file and open it, but again I’m not in the habit of doing that as I don’t trust office format files. Knowing I can open it in a text editor and see the macro is a good suggestion, though I still probably won’t download it out of distrust.

This is pretty standard policy as far as I’m aware with QA sites. Post relevant info in the answer itself, not something off-site or off-page, as that site or object may change or go unavailable for any number of reasons.

If @JohnSUN doesn’t want to post the same answer to this question, that’s fine, but then edit the original answer. I mean I’m here ready to give upvotes, which is the name of the game to encourage people to do these things. Certainly @JohnSUN doesn’t have to, but making my ask clear also encourages others to get easy upvotes.

#12

@CivFan Thank you, now I know that “summarize” can also mean “explained” or “describe”. Or did I get it wrong again?

#13

If you agree to try only to find errors, and not to check all formulas for matching templates, you can use the queryFormulaCells method. For example like this:

REM  *****  BASIC  *****
Rem (©) Vladyslav Orlov aka JohnSUN, Ukraine, Kyiv, 2020
Option Explicit
Const ConstSheetName = "_ErrorList" 

Sub SearchErrors()
Rem Main subroutine: call it to create list of all errors in current spreadsheet
Dim oSheets As Variant
Dim oSheet As Variant
Dim oQueryResult As Variant
Dim i As Long
Dim aRes As Variant 
Dim oRange As Variant 
	If Not ThisComponent.supportsService("com.sun.star.sheet.SpreadsheetDocument") Then Exit Sub	' For Calc Only! '
	oSheets = ThisComponent.getSheets()
	aRes = Array(Array("No errors found"))
	For i = 0 To oSheets.getCount() - 1
		oSheet = oSheets.getByIndex(i)
		If oSheet.getName() <> ConstSheetName Then 
			oQueryResult = oSheet.queryFormulaCells(4)
			If oQueryResult.getCount() > 0 Then DescribeErrors(aRes, oQueryResult)
		EndIf 
	Next i
	If UBound(aRes) > 0 Then aRes(0)(0) = "There are " + UBound(aRes) + " errors found:"
	oSheet = createOrClearErrSheet(ConstSheetName)
	If IsEmpty(oSheet) Or IsNull(oSheet) Then Exit Sub 
	oRange = oSheet.getCellRangeByPosition(0,0,0,UBound(aRes))
	oRange.setFormulaArray(aRes)
	oRange.getColumns().getByIndex(0).OptimalWidth = True
	ThisComponent.getCurrentController().Select(oSheet.getCellByPosition(0,0))
	ThisComponent.CurrentController.Select(ThisComponent.createInstance("com.sun.star.sheet.SheetCellRanges"))
End Sub

Rem Several helper functions
Function createOrClearErrSheet(sSheetName As String, Optional oDoc As Variant) As Variant
Dim oSheets As Variant
On Local Error Resume Next 
	If IsMissing(oDoc) Then oDoc = ThisComponent
	oSheets = oDoc.getSheets()
	If oSheets.hasByName(sSheetName) Then 
		If  oSheets.getCount() > 1 Then
			oSheets.removeByName(sSheetName)
		Else 
			oSheets.getByName(sSheetName).clearContents(1023)
		EndIf
	EndIf
	If Not oSheets.hasByName(sSheetName) Then  oSheets.insertNewByName(sSheetName, 0)
	createOrClearErrSheet = oSheets.getByName(sSheetName)
End Function

Sub AppendToArray(oData As Variant, Optional ByVal x As Variant)
Dim iUB As Integer  'The upper bound of the array.
Dim iLB As Integer  'The lower bound of the array.
	iLB = LBound(oData())
	iUB = UBound(oData()) + 1
	ReDim Preserve oData(iLB To iUB)
	oData(iUB) = x
End Sub

Sub DescribeErrors(oData As Variant, oErrors As Variant)
Dim oCells As Variant
Dim oEnumeration As Variant
Dim oCell As Variant
Dim sAbsoluteName As String
Dim sName As String
Dim sError As String
Dim sFormula As String

	oCells = oErrors.getCells()
	oEnumeration = oCells.createEnumeration()
	While oEnumeration.hasMoreElements() 
		oCell = oEnumeration.nextElement()
		sAbsoluteName = oCell.AbsoluteName
		sName = Replace(sAbsoluteName, "$", "")
		sError = oCell.getString()
		sFormula = oCell.getFormula()
		AppendToArray(oData, Array("=HYPERLINK(""#" +sAbsoluteName + """;""Error " + _
			sError + " in cell " + sName + " with formula " + sFormula + """)"))
	Wend 
End Sub
#14

This is awesome, I’ll try it out. Thx!

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