My macro for turning annotations ("comments") into footnotes

It’s could sometimes be desirable to explain the value of a spreadsheet cell with some kind of footnote, and I’ve recently been battling the apparent lack of such functionality in LibreOffice Calc. By hijacking the annotations functionality (aka “comments”) with the help of a small macro it is possible to turn cell annotations into a numbered list of footnotes which updates automagically as these are added, edited or removed. Writing LO Basic is not my day-job, so forgive me if my solution is unnecessarily clumsy or contains outright errors - let me know if you see any obvious ways this can be improved! Here’s the macro code:

Function ClearOrphans(oDraw As Object)
	' This helper function loops through all shapes on the provided DrawPage and removes
	' any TextShapes that belong to a cell that has no annotation (comment). It takes care
	' of removing footnote symbols from cells which had an annotation that has been removed. 
	
	Dim oShape As Object
	Dim vAddr  As Variant 
	Dim sSheet As String
	Dim oSheet As Object
	Dim oCell  As Object
	
	' Loop through all shapes on the DrawPage
	For Each oShape in oDraw
		' Check if the shape has a name (our footnote symbols always do) and is a TextShape
		If oShape.Name <> "" And oShape.SupportsService("com.sun.star.drawing.TextShape") Then
			' Split the (absolute) name into sheet name and cell address
			vAddr = Split(oShape.Name,".") 
			' Remove the initial $ in the sheet name
			sSheet = Right(vAddr(0), Len(vAddr(0))-1)
			' Get the sheet with that name (if any)
			oSheet = ThisComponent.Sheets.getByName(sSheet)
			' Find the matching cell on the sheet
			oCell = oSheet.getCellRangeByName(vAddr(1))
			' If the cell doesn't have any annotaion, delete the TextShape (symbol)
			If oCell.Annotation.String = "" Then
				oDraw.remove(oShape)
			End If
		End If
	Next
End Function

Function FindShape(oDraw As Object, sName As String) As Object
	' This helper function searches a given DrawPage for shapes with the name sName. 
	' It is called from the Annotate sub to find an existing TextShape for a given 
	' annotation, if one exisist. Returns Nothing if none found. 
	
	' Loop through all shapes in the given DrawPage
	For Each oShape in oDraw
		' Check if the shape's name matches the sName search string
		If oShape.Name = sName Then
			' We found a matching shape, return it
			FindShape = oShape
			Exit Function
		End If
	Next
	' No matching shape could be found, return Nothing 
	FindShape = Nothing 
End Function

Sub Annotate(oEvent)
	' This function loops through all annotations (aka "comments") on a given spreadsheet
	' and turns them into footnotes. Each annotated cell gets a background symbol with a 
	' number that references the footnote. The sub should be called from the ContentChanged
	' sheet event, which can be set with "Sheet" > "Sheet Events..." > "Content Changed". 

	Dim oSheet  As Object	' A spreadsheet containing annotations
	Dim oDraw   As Object	' The spreadsheet's "DrawPage", in which we will draw the symbols
	Dim oAnnos  As Object	' The spreadsheet's annotations (aka "comments")
	Dim oFoot   As Object	' The cell on the spreadsheet where footnotes should be printed
	Dim aFoot() As String	' An array of strings that will contain the footnotes 
	Dim sFoot   As String	' A string that will contain the footnotes 
	Dim oShape  As Object   ' The TextShape used to draw the symbols
	Dim iCount  As Integer	' The annotation counter, which is used for the symbols 
	
	' Get the first spreadsheet
	oSheet = ThisComponent.Sheets.getByIndex(0)
	' Get the spreadsheet's "DrawPage"
	oDraw  = oSheet.getDrawPage()
	' Get the spreadsheet's annotations (aka "comments")
	oAnnos = oSheet.getAnnotations()
	' Get the cell defined for the footnotes (set this with "Data" > "Define Range") 
	oFoot  = oSheet.getCellRangeByName("Footnotes")
	' Initialise the annotation counter 
	iCount = 1
	
	' Loop through all annotations on the sheet 
	For Each oAnno in oAnnos
		' Try to find an existing symbol for this annotation
		oShape = FindShape(oDraw, oAnno.Parent.AbsoluteName)
		' If no symbol was found, create a new one 
		If oShape Is Nothing Then
			' Create a new instance of a TextShape, to be the symbol for this annotation
			oShape = ThisComponent.createInstance("com.sun.star.drawing.TextShape")
			' Set the symbol's name to the cell's absolute name, so we can find it again
			oShape.Name = oAnno.Parent.AbsoluteName
			' Add the symbol to the DrawPage
			oDraw.add(oShape)
			' Set the symbol to the annotation's number, with some right padding
			oShape.setString(iCount & "  ")
			' Anchor the symbol to the parent cell
			oShape.Anchor = oAnno.Parent
			' Set the symbol to the same size as the cell
			oShape.setSize(oAnno.Parent.Size)
			' Resize the symbol when the cell is resized
			oShape.ResizeWithCell = true 
			' We want the symbol on the right hand side of the cell
			oShape.setPropertyValue("ParaAdjust", com.sun.star.style.ParagraphAdjust.RIGHT)
			' Set the font size of the symbol
			oShape.CharHeight = 9
			' Move the symbol to the cell's background, so it can't be selected
			oShape.LayerID = 1 
		' An existing symbol was found, update it's contents (in case numbers have changed)
		Else
			oShape.setString(iCount & "  ")
		End If
		' Add the annotation's text to the list of footnotes
		ReDim Preserve aFoot(iCount-1)
		aFoot(iCount-1) = sFoot & iCount & ": " & oAnno.String
		' Increment annotation number 
		iCount = iCount + 1
	Next
	
	' Remove orhpaned symbols
	ClearOrphans(oDraw)
	
	' Print out the list of footnotes in the oFoot cell 
	oFoot.setString(Join(aFoot, Chr(13)))
End Sub

To use this macro you need to copy the code into the macro editor for your spreadsheet:

Then you need to tell it to run the macro on the Content Changed event:

Finally you need to define a cell range as “Footnotes”, where your comments will be shown as footnotes:

Some caveats:

  • The Content Changed event does not fire when a comment is inserted, and therefore the footnotes do not update either - you need to change the value of some cell for this to happen*. The same is true for when you modify the contents of a comment. Strangely the event does fire when you delete a comment, so that works at least.

  • The footnote indicator symbols are placed below the cell’s background layer, so they will be hidden if you set a cell background colour. I would love to find a way to place them above the background without them taking over the select action when you click on a cell with the mouse (which makes it impossible to edit cells with the mouse).

  • The ClearOrphans() function in particular is pretty rough; it has no checks of any kind and will blindly try to do stuff that will cause an error if the necessary objects do not exist. This is particularly likely if your spreadsheet contains other TextShape drawing objects that have been named. I tried to add some basic checks but my LO Basic fu is very weak and the checks themselves ended up causing errors :smiley: It also rather stupidly fetches the sheet for each DrawPage object, all though they’re all on the same sheet.

  • You might think you should be able to edit a footnote in the cell where they are all listed, and I agree that would be nice - but my macro currently does not allow this. If you try to edit the footnote text that will just trigger a Content Changed event and the text will be regenerated. Instead you need to edit the contents of each cell’s comment to change the footnote, and then trigger a Content Changed event with some other edit*.

  • The footnote indicator (which I call “symbol” in the macro) needs space to live on the right of the cell; easiest way to provide this is to increase the cell padding for the whole sheet.

  • The comment/annotation indicator (small triangle in the top right cell corner) cannot be moved, hidden or styled; you’ll need to adjust the cell padding and footnote indicator size and padding (in the macro code) so they coexist peacefully.

  • I have not been able to get rid of the redundant “Author” and “Date” fields in the comment popups, which looks a bit meh.

  • This forum does not appear to have colour coding support for any programming languages. I had to use the “preformatted text” option to display the code - apologies if it looks a bit odd.

Finally, some screenshots:

Have fun!

*) One easy way to trigger the Content Changed event is to select an empty cell and press the [Delete] key on your keyboard.

I beg to differ:

  • I am not a LibreOffice extension developer, and my post is as much a question as it is an answer.
  • My code is not ready to be packaged as an extension.
  • I was hoping that more experienced LibreOffice developers might help make it more better.
  • This is the site that comes up when you search for a way to do what my macro does.
  • There is no other community forum for LibreOffice.

I find it odd how intense the push-back is against my participation here. It would be nice if someone would engage with what I’m trying to do rather than try so hard to give me reasons to go away. How to create footnotes in Calc is a fairly common question for which I haven’t been able to find a satisfactory answer, and I’ve spent a considerable amount of time trying to come up with a nice way to do so. The fact that I’ve made the effort to write this up into a detailed post for the benefit of the LO community should perhaps be welcomed rather than attacked. What precious resource am I wasting? Strange and disappointing. :worried:

There was no push-back intended. I tried to seriously critisise the approach (not having read the guides?) and aspects of the post.

“Fairly common”? “Footnote” isn’t actually an applicable term in spreadsheets. It’s a term only known and specified for texts - not only in “office” software, but also in printed media. This will be the reason for what you neither found a clearly related question nor a usable answer here.
What’s actually known as footnote in Writer is linked to in the text and has a back-link to the anchor. To implement something roughly similar for Calc would require lots of programming and the automatic adaptions you talked of would surely be complicated even for an experienced developer. Where bo place the “footnotes”? How to handle forward links and back-links e.g. if the structure of the sheet is changed? ( I have to admit that I didn’t study your posted code. It had probably taken many hours.)
If you want to list the textual contents of the comments existing in a specific state of the development of a Calc document in a ListBox control or some shape e.g. that’s not too complicated, but it doesn’t support any automatisms of the kind you hoped for - and it will not be very useful.
If you want the long version of code you posted to be accepted as a valuable contribution you must embed it into a working example sheet which you attach then. I would assume you will get then useful comments and suggestion how to improve the sketch by other users.

1 Like

but it would not cost much to be, and you may get some feedback from posting it.

but maybe more appropriate channels, since you mean to get dev feedback.
e.g. see 165725 – Allow targeting of styles and formatting via regular expressions in Find and Replace

as said by Lupp, don’t expect people to just jump in with hours of reverse engineering some pasted code (and some screenshots). Narrowing down your question(s) would get you more (constructive) feedback :neutral_face:


And from a general standpoint : coding style - When are comments "too much", and when are they not enough? - Stack Overflow

Dim iCount  As Integer	' The annotation counter, which is used for the symbols  type or 

Dim annotationSymbolCounter ... :wink:

@fpy: You may know the saying "It is easy to get results from a spreadsheet. It's much less easy to get correct results."
Adapted to the given case: "It's easy to create somethingt looking like a list of footnotes in a spreadsheet by user code. It's much less easy, if not impossible, to get something that provides the expected functionality."
It surely is impossible as long as the “expected functionality” isn’t clearly described.
For the easy part there is code in the attachment.
annotationCollectionFor_fpy.ods (13.7 KB)

I would not normally comment every single line of code the way I have (laboriously) done in the code I’ve shared here; I did so only for the benefit of any less experienced LibreOffice Calc / LO Basic user who might end up here while searching for a way to print cell comments as footnotes. The intention being to make it crystal clear what my macro does and how, though I agree that the naming of some of my variables could be clearer.

The macro seems to handle structure changes quite well, and placement of the footnotes is very flexible since it uses a named range; the user can put this anywhere they like. I agree it would be nice if there was a link between each footnote and its corresponding comment, so that clicking a footnote takes you to the corresponding cell and editing the text of a footnote updates the corresponding comment. I mentioned this in my list of caveats. I’m thinking I might be able to achieve this by inserting a new cell for each footnote instead of printing all of them in a single cell; these cells could then be decorated with event handlers for clicking and editing. I hope to produce an updated version with this and some of the other caveats resolved.

I’m not asking that this functionality be included in Calc, it’s just a convenient macro for a very specific usage scenario. I’m really just trying to be helpful, and share with others something I myself had a need for and ended up spending many hours creating. I could have kept it to myself, and in hindsight perhaps I should have - but you were the one who suggested I should contribute something. This is it.

Guys. This is ridiculous. It’s only 65 60 lines of code. Simple code even. What’s with the attitude? If you don’t want to help then, well, don’t.

Function ClearOrphans(oDraw As Object)	
	Dim oShape  As Object
	Dim aAddr() As String 
	Dim sSheet  As String
	Dim oSheet  As Object
	Dim oCell   As Object
	For Each oShape in oDraw
		If oShape.Name <> "" And oShape.SupportsService("com.sun.star.drawing.TextShape") Then
			aAddr  = Split(oShape.Name,".") 
			sSheet = Right(aAddr(0), Len(aAddr(0))-1)
			oSheet = ThisComponent.Sheets.getByName(sSheet)
			oCell  = oSheet.getCellRangeByName(aAddr(1))
			If oCell.Annotation.String = "" Then
				oDraw.remove(oShape)
			End If
		End If
	Next
End Function

Function FindShape(oDraw As Object, sName As String) As Object
	For Each oShape in oDraw
		If oShape.Name = sName Then
			FindShape = oShape
			Exit Function
		End If
	Next
	FindShape = Nothing 
End Function

Sub Annotate(oEvent)
	Dim oSheet  As Object
	Dim oDraw   As Object
	Dim oAnnos  As Object
	Dim oFoot   As Object
	Dim aFoot() As String
	Dim oShape  As Object
	Dim iCount  As Integer
	oSheet = ThisComponent.Sheets.getByIndex(0)
	oDraw  = oSheet.getDrawPage()
	oAnnos = oSheet.getAnnotations()
	oFoot  = oSheet.getCellRangeByName("Footnotes")
	iCount = 1
	For Each oAnno in oAnnos
		oShape = FindShape(oDraw, oAnno.Parent.AbsoluteName)
		If oShape Is Nothing Then
			oShape = ThisComponent.createInstance("com.sun.star.drawing.TextShape")
			oShape.Name = oAnno.Parent.AbsoluteName
			oDraw.add(oShape)
			oShape.setString(iCount & "  ")
			oShape.Anchor = oAnno.Parent
			oShape.setSize(oAnno.Parent.Size)
			oShape.ResizeWithCell = true 
			oShape.setPropertyValue("ParaAdjust", com.sun.star.style.ParagraphAdjust.RIGHT)
			oShape.CharHeight = 9
			oShape.LayerID = 1 
		Else
			oShape.setString(iCount & "  ")
		End If
		ReDim Preserve aFoot(iCount-1)
		aFoot(iCount-1) = iCount & ": " & oAnno.String
		iCount = iCount + 1
	Next
	ClearOrphans(oDraw)
	oFoot.setString(Join(aFoot, Chr(13)))
End Sub

Here are some of the specific points I would like to improve, if anyone knows how:

I’m tired of this and will only comment on:

An annotation always is stored with values for the children giving the creator and a DateTime In strict ISO 8601 format.
If I edit the file I can remove that, but next time LibO opens it new values are created: “Unknown Author” or the like and the current DateTime.
If I edit the file again and don’t remove rhe children, but replace the contents with a fancy name and a nonsense DateTime like 1912-12-12T12:12:00 this is accepted and kept.

1 Like