Ask Your Question

Calc: Howto create unique list in macro using standard filter

asked 2016-09-23 11:18:10 +0200

kribjo gravatar image


I have two columns in Calc with 11 rows including header. It's used as a reimbursement form for expenses. Colums are Amount and Category. I want it to automatically sum up each category. This is easily done creating to new columns, one with categories without duplicates created using standard filter (I have created a range for Category not including header and using it in filter), and one with sumif.

But when I try do to this in a macro I cannot get it to work. I realize I must do something wrong, but I cannot figure out what. I have done some reading on google searches and in this forum, but I'm hoping this post will help.

Looking av the BASIC code created I observe that StandardFilter is not executed, because it's commented out. I don't understand what I'm doing wrong.

Code is looking like this:

REM  *****  BASIC  *****

sub Main
rem ----------------------------------------------------------------------
rem define variables
dim document   as object
dim dispatcher as object
rem ----------------------------------------------------------------------
rem get access to the document
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("")

rem ----------------------------------------------------------------------
rem dispatcher.executeDispatch(document, ".uno:DataFilterStandardFilter", "", 0, Array())

rem ----------------------------------------------------------------------
dim args2(0) as new
args2(0).Name = "ToPoint"
args2(0).Value = "$B$7"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args2())

rem ----------------------------------------------------------------------
dim args3(0) as new
args3(0).Name = "ToPoint"
args3(0).Value = "$B$4"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args3())

rem ----------------------------------------------------------------------
rem dispatcher.executeDispatch(document, ".uno:DataFilterStandardFilter", "", 0, Array())

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:FilterExecute", "", 0, Array())

end sub

As I'm a screenshot junkie I would prefer to add screenshots, but I don't have enough points in the forum yet. :-) Appreciate the help of the community. Thanks.

Regards, Bjørn

edit retag flag offensive close merge delete


A screenshot would be no use. A relevant sample document might be useful.
You won't get anywhere with this task based on recorded macros, IMO.

Lupp gravatar imageLupp ( 2016-09-23 11:27:11 +0200 )edit

Thanks for feedback Lupp. I don't have enough point to attach files, so I'll link to them below. I don't believe that I'll get anywhere with the macro as it is now. But I'm not a programmer, nor do I know the LibreOffice API or uno. Hence macros makes it possible.

Standard Filter manually.odsStandard Filter macro.ods

kribjo gravatar imagekribjo ( 2016-09-23 12:33:19 +0200 )edit

I know standard filters and I know a bit about how to use the uno-API (in LibO BASIC).
"Hence macros makes it possible." (?) Good luck! Better read the famous texts by Andrew Pitonyak.
(My dislike of screenshots was strengthened again.)

Lupp gravatar imageLupp ( 2016-09-23 12:51:09 +0200 )edit

Starting to realize that recording macros might not be optimal in some situations. I'll follow your lead and read me up on uno-API and look at Andrew Pitonyak's examples. Thanks.

kribjo gravatar imagekribjo ( 2016-09-23 13:14:41 +0200 )edit

1 Answer

Sort by » oldest newest most voted

answered 2016-09-23 12:43:58 +0200

Lupp gravatar image

If I understood correctly, you allow for arbitrarily typed categories?
If you do so, any typo or even a trailing space will spoil the summation by categories. Why don't you use a well defined table of categories which you can easily maintain and sort differently if needed?
Based on a predefined table you may used the 'Data' > 'Validity' tool for entering categories. Typing free, a conditional format can raise an alert (colouring the respective cell, e.g.) if an unknown category is entered. You may then decide to change the entry or to add it to the table of allowed categories.

edit flag offensive delete link more


Category should only be a number. I agree that validation should be used, and I will at a later stage, a combination of vlookup and validation. But at the moment I would like to fix my issue with Standard Filter in Macro and then move on. :-)

kribjo gravatar imagekribjo ( 2016-09-23 12:55:15 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2016-09-23 11:18:10 +0200

Seen: 428 times

Last updated: Sep 23 '16