Query all contained buttons and linked macros

Hi erveryone,

I have a calc document with many sheets and in this sheets many Buttons with linked macros.

And now I would like have a macro which can query all sheets from the document for contained buttons and linked macros and list them into a msgbox.

That would be very helpfull for me to find all macros in the document which i use and more important to me all macros in the document which I don’t use and maybe can delete them.

I hope there is anyone which can help me!



This is an interesting idea. But wouldn’t it be too tedious to click OK after displaying each button and its macro? Maybe it makes sense to approach the cleaning task from a different angle? Well, for example, on a help sheet, list all the names of all the procedures in all the modules of your spreadsheet. Add a tricky procedure to the code that will find the required row in this table and in the next cell increase the call counter by 1. At the beginning of each procedure, add one line - call this tricky procedure with the parameter “module name + name of the current procedure”. Then go through the sheets of your spreadsheet several times and click each button. Voila - you have a table in which you can see procedures that have never been called…

1 Like

Hi JohnSUN,

I can’t really write macros and so I use macros from the internet, foren and try to customize them. Therefore, I have a large collection of macros and code in all my used document and sheets and would like now to clean it out a litle bit.

I am open and grateful for whatever approach or way. But unfortunately my knowledge is not enough to do this and I need help.

Your procedure sounds good to me but I can’t realize that :frowning: by myself

I find your idea much more logical and better, because I also use macros not only via buttons but also from the menu and with your method I would find out the usage behavior of all macros and can then decide which are used and which are not.

Can you help me with this?

I will try to help. It will take time. Considering this fact

it will take a little more time.

Version 3:

Sub createReport()
doc = ThisComponent
doc2 = StarDesktop.loadComponentFromURL("private:factory/scalc", 0, "_default", Array())
shs = doc.getSheets()
aNames = shs.getElementNames()
x = shs.getCount() -1
shs2 = doc2.getSheets()
for iSh = x to 0 step -1
	s = aNames(iSh)
    sh = shs.getByName(s)
    dp = sh.getDrawPage()
    if dp.Forms.getCount() > 0 then
        frm = dp.Forms.getByIndex(0)
        if not shs2.hasByName(s) then shs2.insertNewByName(s, 0)
        rep = shs2.getByName(s)
        r = 0
        rg = rep.getCellRangeByPosition(0,r,5,r)
        iUB = frm.getCount() -1
        for i = 0 to iUB
        	sName = frm.getByIndex(i).getName()
        	evs = frm.getScriptEvents(i)
        	for j = 0 to uBound(evs)
		        r = r +1
        		descr = evs(j)
        		rg = rep.getCellRangeByPosition(0, r, 5, r)
		        rg.setDataArray(Array(Array(sName, descr.AddListenerParam, descr.EventMethod, descr.ListenerType, descr.ScriptCode, descr.ScriptType)))
        	next j
        next i
next iSh
End Sub

Output from my test sheet:

CtrlName AddListenerParam EventMethod ListenerType ScriptCode ScriptType
Push Button 1 actionPerformed XActionListener vnd.sun.star.script:Standard.Module1.createReport?language=Basic&location=document Script
List Box 1 changed XChangeListener vnd.sun.star.script:Standard.Module1.Dummy?language=Basic&location=document Script
List Box 1 focusGained XFocusListener vnd.sun.star.script:Standard.Module1.Dummy?language=Basic&location=document Script
Option Button 1 actionPerformed XActionListener vnd.sun.star.script:Standard.Module1.Dummy?language=Basic&location=document Script
Numeric Field 1 textChanged XTextListener vnd.sun.star.script:Standard.Module1.Dummy?language=Basic&location=document Script
1 Like

Hi Villeroy,

sorry for my late response.

I have now been able to test it and it only works for the current table sheet. I can of course run the macro again and again for each additional table in the document, but the entries in your table “EventsReport” are always overwritten. Ideally, your macro would check all tables and then writes the result to the “EventsReport” table?

Replaced version 2 with 3. Now you get many report sheets in a new document.

Thanks I will do and try it.

Hi JohnSUN,

could you had to take a look at it?

I decided not to bind to events of controls. After all, macros can be called from the menu, right?
Therefore, my solution focuses on the call count of each subroutine or function found in the current spreadsheet.
The extension CoCa creates a menu item in Tools-Add-ons

Of course, this menu item is only visible in Calc; the extension does not work with other types of documents.
To use, simply call it again and again - the design is as simplified as possible and reduced to a single button.
When called for the first time, the extension creates a backup copy of the current spreadsheet, adds a special procedure call to each individual macro, and creates an additional sheet that lists all detected subroutines and functions. After this, you need to test the project in different modes. The call counters will change and the statistics sheet will look something like this

Based on this data, you can manually clean the modules of unnecessary code. But you can call the extension again and the program will try to do it on its own.

In this case, fragments that were not called will be moved to an additional module and provided with a comment to know the original position of the fragment. It looks something like this:

It is not possible to provide all possible variants of the processed code. Therefore, the function like as

Function TODO() As String : TODO = "This will be done by the Sub DoGood() procedure, which I will write later" : End Function

will not be processed. But if you are so advanced in programming that you can write code in this form, then you probably don’t need this extension.

The resource still does not allow files with the .oxt extension to be attached to messages. Therefore, after downloading, rename it to CoCa.oxt (35.3 KB)

Be gentle, this is a beta version, it probably still contains bugs.

1 Like

Hi JohnSUN,

Thanks for your nice work it looks really good in my first tests!
I will test it in the next time much more and give you feedback.

@german_newbie While you were testing the published version, @KamilLanda and I made several improvements to the extension code - the current version is 0.5.1
rename it to CoCa.oxt (35.5 KB)

I didn’t find the names of macros assigned to buttons via XRay, so I tried to got it directly from content.xml (ODT is renamed ZIP mainly with XMLs)
But the true is, the ODT’s XMLs have very complicated structure and I tested it only with one small ODS test.ods (10.6 kB)

Sub getButtonMacrosFromInnerXML 'get the macros assigned to buttons in ODS; source: https://ask.libreoffice.org/t/is-it-possible-to-view-and-modify-with-starbasic-xml-opendocument-code-of-current-document-writer/80798/11
	dim oDoc as object, oContent as object, oXML as object, oDOM as object, oXMLbutton as object, o as object, i&, j&, k&, s$, oButton as object, oEvents as object, oEvent as object, _
	oContent=getZipContentAsByteArray(oDoc, "content.xml")

	'msgbox "content.xml len: " & ubound(oContent)
	'msgbox getTextInputStreamFromByteArray(oContent).readString(array(), true)
	if oXMLbutton.Length>0 then 'there is some button
		for i=0 to oXMLbutton.Length-1
			oButton=oXMLbutton.item(i) 'current button
			if oEvents.Length>0 then 'button has macro(s)
				s=s & oButton.getAttribute("name") & chr(13)
				for j=0 to oEvents.Length-1
					oEvent=oEvents.item(0).getElementsByTagName("event-listener") 'single event
					if oEvent.Length>0 then
						for k=0 to oEvent.Length-1
							'msgbox getXMLString(o)
							s=s & chr(9) & Mid(ss, InStr(ss, ":")+1) & " : "
							ss=Mid(ss, InStr(ss, ":")+1)
							ss=Left(ss, InStr(ss, "?")-1)
							s=s & chr(9) & ss
							s=s & chr(13)
						next k
					end if
				next j
			end if
			s=s & chr(13)
		next i
	end if
	msgbox s
End Sub

Function getZipContentAsByteArray(oDoc as object, sFile$) as object
	dim oZIP as object, oStorage as object, oStream as object, oContent as object, iLen&, iSuccess&, _
		storeProps(0) as new com.sun.star.beans.PropertyValue, args(1) as object
	oStream=oStorage.openStreamElement("zipStream", com.sun.star.embed.ElementModes.READWRITE)
	storeProps(0).Name="OutputStream" : storeProps(0).Value=oStream
	oDoc.storeToUrl("private:stream", storeProps)

	dim aByte(iLen) as byte
	iSuccess=oContent.readBytes(aByte, iLen)
	if iSuccess<>iLen then
		msgbox "bug"
		exit Function
End Function

Function getTextInputStreamFromByteArray(aByte) as object
	dim oPipe as object, oStream as object
	with oPipe
	end with
End Function

Function getXMLString(oXMLelement as object) as string
	dim oDocumentBuilder as object, oDOMdocumentNew as object, oXMLelementNew as object, oPipe as object, oStream as object, sXML$
	oXMLelementNew=oDOMdocumentNew.importNode(oXMLelement, true)


	with oDOMdocumentNew
	end with

	sXML=oStream.readString(array(), true)
End Function

Hello @KamilLanda! See this topic.

The method .getScriptEvents(0) from @Villeroy example is the essence, I didn’t notice this method :-). I XRayed mainly the Properties and there isn’t the property ScriptEvents, only the method.

The irritating thing is that controls have no ScriptEvents. ScriptEvents belong to forms only, even if the events are assigned to controls.
Therefore, my macro does not report the right control name which should be fixed somehow.
EDIT: replaced my code with a better version.