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
next
' 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()
aFields()=oFilterDesc.getFilterFields
n=-1
sAlt=""
Do while sAlt="" and n<ubound(aFields())
n=n+1
if aFields(n).Field=Column - oRange.ReferredCells.RangeAddress.StartColumn then
'column is actually filtered
sAlt=aFields(n).StringValue
aFields(n)=oNeu 'filter "off"
end if
loop
' filter without filtering actual column
oRange.AutoFilter = True
oFilterDesc.setFilterFields(aFields())
oFilterDesc.ContainsHeader=true
oRange.referredcells.filter(oFilterDesc)
' some presets
if sAlt="" then
n=n+1
Redim Preserve aFields(n)
aFields(n)=oNeu
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
sNeu=""
sMax=sAlt
zellen.nextelement
do while zellen.hasmoreelements
sTemp=zellen.nextelement.string
if sTemp>sMax then sMax=sTemp ' find max
if sNeu="" then
if sTemp<sAlt then sNeu=sTemp '
else
if sTemp<sAlt and sTemp>sNeu then sNeu=sTemp
end if
loop
if sNeu="" then sNeu=sMax
' set new filter criteria
aFields(n).StringValue=sNeu
aFields(n).Operator = com.sun.star.sheet.FilterOperator.EQUAL
' apply filtering
oFilterDesc.setFilterFields(aFields())
oRange.referredcells.filter(oFilterDesc)
End Sub 'PreviousFilterShortcut
code end here, inserting it as a block is still somewhat difficult for me,
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 …
tia for any help