Searching for ”empty” cell (LibreOffice BASIC macro)

users
#21

Maybe that adds to the slowness I had already, but my spreadsheet was
slow with 3.3.4 too. I upgraded to 3.5.5 earlier this week, and I'm
not sure it is slower now, but it is certainly not faster.
But I'll install 3.6 when I have some time left and see if there is
any difference.

There are so many other things that are very slow as well, for example
auto-filter and diagrams. I have one diagram in another spreadsheet.
It use data from 365 rows (one for each day of a year) and something
like 5-10 columns. If I want to change the size, for example, of that
diagram, I need to wait for several seconds, maybe 30 or so, before
anything happens after I double clicked the diagram.
I also made a similar one for a month, that is 31 rows and about 5-10
columns. It's faster than the year one of course, but still too slow
for most situations. If it was millions of cells I would understand
it, but 365? That's nothing! Right?

Kind regards

Johnny Rosenberg
ジョニー・ローゼンバーグ

#22

Johnny,

found this stuf on a French forum:

Sub PysTests
dim PysSel as object, FormulaRetour, EmptyRetour as object
dim PysFlag

PysFlag = com.sun.star.sheet.FormulaResult.VALUE + com.sun.star.sheet.FormulaResult.STRING + com.sun.star.sheet.FormulaResult.ERROR

PysSel = thiscomponent.currentSelection
FormulaRetour = PysSel.queryFormulaCells(PysFlag)
xray FormulaRetour
EmptyRetour = PysSel.queryEmptyCells(PysFlag)
xray EmptyRetour
End Sub

hope it helps also :slight_smile:

#23

Am 20.07.2012 21:37, Johnny Rosenberg wrote:

Am 20.07.2012 14:19, Andreas Säger wrote:

Try my "SpecialCells" extension which selects combinations of cell types:
http://user.services.openoffice.org/en/forum/download/file.php?id=11048

Done. Snippet recorded by the MRI inspector:

Sub Snippet(Optional oInitialTarget As Object)
   Dim oDatabaseRanges As Object
   Dim oObj_1 As Object
   Dim oFilterDescriptor As Object
   Dim oFilterFields As Object

   oDatabaseRanges = oInitialTarget.DatabaseRanges
   oObj_1 = oDatabaseRanges.getByIndex(0)
   oFilterDescriptor = oObj_1.getFilterDescriptor()

   oFilterFields = oFilterDescriptor.getFilterFields()
End Sub

And this is the output for oFilterFields:

(Name) (Value Type) (Value) (AccessMode)
(0)
Connection .sheet.FilterConnection AND [ReadWrite]
Field long 0 [ReadWrite]
Operator .sheet.FilterOperator EMPTY [ReadWrite]
IsNumeric boolean True [ReadWrite]
NumericValue double 0.0 [ReadWrite]
StringValue string "" [ReadWrite]
(1)
Connection .sheet.FilterConnection OR [ReadWrite]
Field long 0 [ReadWrite]
Operator .sheet.FilterOperator EQUAL [ReadWrite]
IsNumeric boolean False [ReadWrite]
NumericValue double 0.0 [ReadWrite]
StringValue string "" [ReadWrite]

The blank field (0) uses c.s.s.sheet.FilterOperator.EMPTY with any content.
The empty string field (1) uses c.s.s.sheet.FilterOperator.EQUAL with string
content "".

Thanks. I'll take a closer look at it tomorrow. Too tired and confused
right now.

Quite confusing, indeed. My "Done" posting refers to my other posting:

Am 20.07.2012 18:56, Johnny Rosenberg wrote:

As far as I know, the find/replace tool can not find empty strings nor
blanks.
The standard filter can. It has an "-- empty --" option for the blanks and
one empty entry at the end of the combo box for the empty strings (if any).

Can I use that in a macro? How?

Apply such filter manually and inspect the filter descriptor with its filter fields.

This is what I've done. I applied such a filter and ran MRI against the filter descriptor with the above results.