Bug 129105 - MACRO: SCRIPT: "getFilterDescriptor()" broken on fresh loaded files?
Summary: MACRO: SCRIPT: "getFilterDescriptor()" broken on fresh loaded files?
Status: RESOLVED DUPLICATE of bug 133529
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
(earliest affected)
3.5.2 release
Hardware: All Windows (All)
: medium normal
Assignee: Not Assigned
Depends on:
Reported: 2019-11-29 19:09 UTC by b.
Modified: 2020-06-05 06:21 UTC (History)
1 user (show)

See Also:
Crash report or crash signature:

macro_wrong_column_3 (11.11 KB, application/vnd.oasis.opendocument.spreadsheet)
2019-11-29 19:16 UTC, b.

Note You need to log in before you can comment on or make changes to this bug.
Description b. 2019-11-29 19:09:15 UTC
hi @all,

i have a little issue with a function? method? in a macro, i asked other users to check it out before filing a bug at https://ask.libreoffice.org/en/question/218463/macro-script-getfilterdescriptor-broken-on-fresh-loaded-files/,

in short: "getFilterDescriptor()" gets wrong values for 'field' as property of 'filterfields(x)' in the 'filterfields' part of the struct, once two conditions are met: - in a fresh loaded file where filters are set, - if the 'defined range' it works in has an 'offset' from cell A1,

in long: i have a macro that 'steps through filtering values' of the autofilter for a column in order to check subsets of the table quite fast :-) - see attached code,

it produces erroneous results with the abovementioned conditions,

to check let it run on the different sheets of the attached file - see next comment,

"getFilterDescriptor()", in this case called as "oFilterDesc = oRange.getFilterDescriptor()" gets the number of the column as value for "fields" on sheets 1,2,3, and the number of the column reduced by the 'vertical offset' (start row) of the 'defined range' it's working in on sheets 4 and 5.

but "referredcells.filter(oFilterDesc)" does other calculations for the column, thus applying it to wrong columns in sheet 2,3,4, sheet 5 looks healthy, but i'm guessing only because two mistakes cancel each other out,

after 'restart' of the autofilter - autofilter off - autofilter on - the value for 'field' is always calculated relative to the 'defined range', and everything works as intended,

file with macro to play with it:

- see next comment - 

the macro code:

sub PreviousFilterShortcut

' this macro - steps back - by one value in the range of possible autofilter ' values for the actual column. useful to cycle through subranges of datasets ' and e.g. check their results, e.g. in calculated subtotals of columns, ' (subtotal(9,(´range´)). ' ' the only requirements are that you have a 'database range' defined ' in the table under <data -="" define="" range="">, that you have selected a single cell, ' and that this cell, the 'focus', is within the defined range,

oDoc     = ThisComponent
oControl = oDoc.CurrentController
oSheet   = oControl.getActiveSheet
oCell    = oDoc.getCurrentSelection
Column  = oCell.CellAddress.Column

' search for database range the focus is in
oDBRanges = oDoc.DatabaseRanges
for i = 0 to oDBRanges.Count-1
    oDBrange   = oDBRanges.getByIndex(i)
    oCellrange = oDbrange.ReferredCells
    if oCellrange.queryIntersection(oCell.RangeAddress).Count > 0 then
        oRange = oDBrange
    End If

' add a messagebox for errors, 
if isempty(oRange) then msgbox "actual cell not within a database range",0,""
if isempty(oRange) then exit sub

' define new filter-description
Dim oNeu As New com.sun.star.sheet.TableFilterField
With oNeu
 .Field = Column - oRange.ReferredCells.RangeAddress.StartColumn  ' filter-column (other position in code than in datasurfer?) 
 .IsNumeric = False
 .StringValue = ""
 .Operator = com.sun.star.sheet.FilterOperator.NOT_EQUAL
End With    

'analyse existing filter     
oFilterDesc = oRange.getFilterDescriptor()
Do while sAlt="" and n<ubound(aFields())
    if aFields(n).Field=Column - oRange.ReferredCells.RangeAddress.StartColumn then
        'column is actually filtered
        aFields(n)=oNeu 'filter "off"
    end if

' filter without filtering actual column
oRange.AutoFilter = True

' some presets
if sAlt="" then 
    Redim Preserve aFields(n)
end if

' get all possible values for the actual column, respecting filtering done in other columns, 
zellen= oRange.ReferredCells.queryVisiblecells.queryintersection(oCell.columns(0).RangeAddress).cells.createenumeration

' search for max of smaller values 

do while zellen.hasmoreelements
    if sTemp>sMax then sMax=sTemp ' find max
    if sNeu="" then 
        if sTemp<sAlt then sNeu=sTemp ' 
        if sTemp<sAlt and sTemp>sNeu then sNeu=sTemp
    end if
if sNeu="" then sNeu=sMax

' set new filter criteria
aFields(n).Operator = com.sun.star.sheet.FilterOperator.EQUAL

' apply filtering

End Sub 'PreviousFilterShortcut

the macro is one of four which do quite handy things for me, (toggle autofilter on/off, modify autofilter according value of selected cell (datasurfer), step forward through the filtering values in one column, step backwards) which i'd like to give to others and 'the community' once they run well,

the last three suffer from mentioned problem, i'd provide 'backwards' as example reg. it's 'short' and 'in english', 'forward' is partly german,

credits to https://www.libreoffice-forum.de/ and esp. 'mikele' who helped coming so far ...



Steps to Reproduce:
see description above and test file in next comment, 

Actual Results:
'field' in filterfields() of filterfields is calculated relative to column 1 of the sheet in fresh loaded files, affects database ranges with an offset to A1,

Expected Results:
the value for 'field' being calculated relative to the database range the filter is defined in, 

Reproducible: Always

User Profile Reset: No

Additional Info:
different flav's from4.1.6.2 to show identical behaviour,
Comment 1 b. 2019-11-29 19:16:23 UTC
Created attachment 156188 [details]

file with macro applying autofilter to wrong columns, when they are loaded with autofilter set, 

see initial bug report for details,
Comment 2 Buovjaga 2020-04-30 17:04:38 UTC
(In reply to b. from comment #1)
> Created attachment 156188 [details]
> macro_wrong_column_3

I opened the file, went to Tools - Macros - Run Macro.
I chose PreviousFilterShortcut from the file.
Result is that all of the data was hidden.

Is this good or bad? I have no idea after reading your description and comments.

Note: please try to separate the reproduction steps from any analytical writing.

Arch Linux 64-bit
Build ID: 623d6cf06ccba392c1993a3b0ad271d508205e73
CPU threads: 8; OS: Linux 5.6; UI render: default; VCL: kf5; 
Locale: fi-FI (fi_FI.UTF-8); UI-Language: en-US
Calc: threaded
Built on 29 April 2020
Comment 3 b. 2020-05-01 14:57:29 UTC
hello @Buovjaga, 

thanks for taking care, and sorry for having been imprecise ... 

it's been some time since i wrote this, may be - i hope so - this: 


is the source of evil, i'd put it in another bug because it's not 'getfilterdescriptor' being wrong but the .field-values saved in the xml file. 

for the case it's not, or just 'for the records' a step-by-step workthrough for this sample: 

> I opened the file, went to Tools - Macros - Run Macro.

ok, to see the problem evolving take 'edit macro' instead, 

> I chose PreviousFilterShortcut from the file.


> Result is that all of the data was hidden.

that's not the intended result of the macro, it should step-back the filter to the 'next-less' value in that column, if none is found  to the largest, but it's intended from the sample / bug report to show the fault, 

> Is this good or bad? I have no idea after reading your description and comments.

'bad', let me explain the details: 

on load of the file 'as downloaded', sheet2 is displayed with the cursor in E2 (hidden), if you have another display change to sheet2, if the cursor is off from  E1:E15 place it on E3, 

in the macro editor set 'afields' as watch variable (bottom left), 

step through the macro with F8, 

in line 49: 'aFields()=oFilterDesc.getFilterFields' 
afield get's assigned the actual filtering condition, check afields(0).field (unfold afields by clicking the v-hook, within that unfold afields(0) by clicking the v-hook), see Field = '4'. 

Field should be the value 'filtered column relative to database area zero-based', in this sample column C should be 0, D be 1, and E - the filtered one - should be 2, not 4! from this point on titanic will sink. 

in line 54: 'if aFields(n).Field=Column - oRange.ReferredCells.RangeAddress.StartColumn then' 

a decision is made if the filtered field is valid for the actual column (the loop is a sort out which field is valid), the check is made against the correct calculated value for .field for column E (4-th column zero based) minus start column of the database area (2-nd col (C, zero-based)) that fails as 2 (calculated) is compared to 4 (fetched from the file), thus the 'unfiltering' of the actual column doesn't work, 

after that and based on the fault instead of changing the actual filter for col E to 'not ""' that condition is applied to col '2', the area is re-filtered, based on that filtering the 'previous value' is evaluated (to '3'), and put in the filtering condition afields(1), and then instead of assigning one changed filter to the area two conditions are applied, one for .field being 2 requiring '3' in the column and one for .field being 4 requiring '3' in the column, not met in any row, thus 'all off', 

you can see that both columns 'absolute 2 - relative 0 - C' and 'absolute 4 relative 2 - E' have filtering conditions applied at the end of the macro , check the dropdown fields in the header row, both blue, both 'dotted', and both showing irrational conditions on click, 

that's how it's 'going wrong', 

do the same on sheet1, everything fine as no start-column-value has to be respected, absolute and relative column numbers are identical, filter condition for col. E is changed to '6', 

do the same on sheet3, it tries to filter columns '2' and '6', only one of them is visible in the header, 

do the same on sheet4, it tries to filter columns '2' and '4', the value fetched from the file is 4 for col. 6, imho caused by the 'row-offset' of the database area, 

do the same on sheet5, everything looks correct, col E is re-filtered to 6, imho the result of two errors - wrong evaluation of col-offset and wrong evaluation of row offset - canceling each other out ... 

> Note: please try to separate the reproduction steps from any analytical writing.

i'll try, it is not easy (for me) to present difficult mistakes and complex correlations that I have not yet understood myself, i sometimes see 'there is something wrong' without being able to explain the reason. 

since most of the 'simple' errors in calc are solved in the meantime it will happen more and more often that you have to describe strange things with long explanations, i know that this is unpleasant for the developers ... i try my best to help instead of annoying. 

If you want to help me, give me an example how i should have presented this tricky bug better, 

and keep your fingers crossed that the analysis in #132488 is correct, and someone will soon be found who can correct it, 

if you can spend time pls. chek it and - if bug - set to 'new', 

Without anticipation, it could face a difficult situation of how to deal with all the 'old' files stored with wrong values at users ... may be they'll have new problems after a fix ... 


Comment 4 b. 2020-06-05 06:21:07 UTC
it boils down to something being unclear about the use of the flag/tag 'table:orientation="column"', described there: 


as of now that's the main problem to be solved, thus setting this duplicate,

*** This bug has been marked as a duplicate of bug 133529 ***