macro script "getFilterDescriptor()" broken on fresh loaded files?

asked 2019-11-22 15:28:05 +0200

newbie-02 gravatar image

updated 2020-07-20 10:12:49 +0200

Alex Kemp gravatar image

hi @all,

i have a little issue with a function? method? in a macro and ask other users to check it out before i 'file a bug' because of it,

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,

"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:


the macro code:

code starts here, inserting it as a block is still somewhat difficult for me:

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
With oNeu
 .Field = Column - oRange.ReferredCells.RangeAddress.StartColumn  ' filter-column (other position in code than in datasurfer?) 
 .IsNumeric = False
 .StringValue = ""
 .Operator =
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 ...
edit retag flag offensive close merge delete


as of now i think it's resulting from that:

filesave: fileopen: macro: xml: calc filtered ranges wrongly saved in .ods format?


newbie-02 gravatar imagenewbie-02 ( 2020-04-30 14:06:12 +0200 )edit