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!
Thanks
Welcome!
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…
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 
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)
rep.clearContents(23)
r = 0
rg = rep.getCellRangeByPosition(0,r,5,r)
rg.setDataArray(Array(Array("CtrlName","AddListenerParam","EventMethod","ListenerType","ScriptCode","ScriptType")))
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
endif
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 |
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.
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, _
ss$
oDoc=ThisComponent
oContent=getZipContentAsByteArray(oDoc, "content.xml")
'msgbox "content.xml len: " & ubound(oContent)
'msgbox getTextInputStreamFromByteArray(oContent).readString(array(), true)
oXML=createUnoService("com.sun.star.xml.dom.DocumentBuilder")
oDOM=oXML.parse(getTextInputStreamFromByteArray(oContent))
oXMLbutton=oDOM.getElementsByTagName("button")
if oXMLbutton.Length>0 then 'there is some button
for i=0 to oXMLbutton.Length-1
oButton=oXMLbutton.item(i) 'current button
oEvents=oButton.getElementsByTagName("event-listeners")
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
o=oEvent.item(k)
'msgbox getXMLString(o)
ss=o.getAttribute("event-name")
s=s & chr(9) & Mid(ss, InStr(ss, ":")+1) & " : "
ss=o.getAttribute("href")
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
oStorage=createUnoService("com.sun.star.embed.StorageFactory").createInstance
oStream=oStorage.openStreamElement("zipStream", com.sun.star.embed.ElementModes.READWRITE)
storeProps(0).Name="OutputStream" : storeProps(0).Value=oStream
oDoc.storeToUrl("private:stream", storeProps)
args(0)=oStream
oZIP=createUnoService("com.sun.star.packages.zip.ZipFileAccess")
oZIP.initialize(args)
oContent=oZIP.getByname(sFile)
iLen=oContent.available()
dim aByte(iLen) as byte
iSuccess=oContent.readBytes(aByte, iLen)
if iSuccess<>iLen then
msgbox "bug"
exit Function
endif
getZipContentAsByteArray=aByte
End Function
Function getTextInputStreamFromByteArray(aByte) as object
dim oPipe as object, oStream as object
oPipe=createUnoService("com.sun.star.io.Pipe")
oStream=createUnoService("com.sun.star.io.TextInputStream")
oStream.setInputStream(oPipe)
with oPipe
.writeBytes(aByte)
.closeOutput()
end with
getTextInputStreamFromByteArray=oStream
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$
oDocumentBuilder=createUnoService("com.sun.star.xml.dom.DocumentBuilder")
oDOMdocumentNew=oDocumentBuilder.newDocument()
oXMLelementNew=oDOMdocumentNew.importNode(oXMLelement, true)
oDOMdocumentNew.appendChild(oXMLelementNew)
oPipe=createUnoService("com.sun.star.io.Pipe")
oStream=createUnoService("com.sun.star.io.TextInputStream")
oStream.setInputStream(oPipe)
with oDOMdocumentNew
.setOutputStream(oPipe)
.start()
end with
oPipe.closeOutput()
sXML=oStream.readString(array(), true)
getXMLString=sXML
End Function
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.