Re: [libreoffice-users] Searching for ”empty” cell (LibreOffice BASIC macro)

[Johnny Rosenberg <gurus.knugum@gmail.com>]

2012/7/20 MiguelAngel <mariosv@miguelangel.mobi>:
> El 20/07/12 21:33, Johnny Rosenberg escribió:
>
>> 2012/7/20 Johnny Rosenberg <gurus.knugum@gmail.com>:
>>>
>>> 2012/7/20 Fernand Vanrie <sos@pmgroup.be>:
>>>>
>>>> Op 20/07/2012 13:31, Johnny Rosenberg schreef:
>>>>
>>>>> 2012/7/20 Fernand Vanrie<sos@pmgroup.be>:
>>>>>>
>>>>>>
>>>>>>    Johnny,
>>>>>> I suppose you have to run the check 2 Times, first SearchType = 0
>>>>>> here
>>>>>> we
>>>>>> find if there is a formula use  "."
>>>>>
>>>>>
>>>>> What do you mean? Use "." as the search string? Regular expressions
>>>>> set to what? True?
>>>>
>>>>
>>>> yep looking for not empty you need regular expressions
>>>
>>>
>>> I did some brutal experimenting and after hundreds of swearwords (the
>>> damned crap freeze all the time) I actually found something. Have a
>>> look at this descriptor:
>>>
>>>          With SearchDescriptor
>>>                  .SearchByRow=False
>>>                  .SearchRegularExpression=True
>>>                  .SearchString="^[^.]$"
>>>                  .SearchType=1 ' Search values.
>>>          End With
>>>
>>> First it didn't work as I expected, but it found another cell in
>>> another column. That cell has a semi complicated formula with nested
>>> IF's and in this case it returns F2+STYLE("Hide"). F2 in this case is
>>> an empty cell, so now I changed the formula in the cell I wanted to
>>> find, by just replacing the two quotes ("") with F2, and now it finds
>>> the cell!
>>>
>>> So "" is not good enough for making a cell empty, but I can reference
>>> to a cell that actually is empty!
>>>
>>> So what I need to do now, is to change all the formulas in one column,
>>> and the search descriptor above will work, as it seems!
>>>
>>>
>>> At least I'll try that.
>>
>>
>> Ok, I'd just better give up, I guess. LibreOffice seems to be way too
>> slow for the things I do (I am surprised that I seem to be the only
>> one who actually do something with spreadsheets, except simple tables
>> of a few rows and columns, doing close to nothing, more or less). Many
>> years ago I did similar things in Excel. It crashed now and then, but
>> it was fast, at least. Things that took maybe one second to execute in
>> Excel seems to take hours in LibreOffice these days. I don't know
>> exactly how long they take, since I can never wait for more than a
>> couple of minutes before I restart it. It's very frustrating, when
>> testing new stuff. Simple developing a spreadsheet (something that you
>> shouldn't do, obviously) takes days instead of minutes.
>>
>> I am not sure where things go slow. My current spreadsheet isn't
>> particularly big. There are currently about 2000 rows in 5 columns
>> containing formulas and there is one column with about 3300 rows of
>> formulas. Not that complicated formulas, mostly nested IF's. One of
>> the 2000 row columns use a custom formula (written in LibreOffice
>> BASIC), not very complicated: It reads the values from two cells and
>> returns a string value depending on the input values. A few IF's,
>> that's all.
>> I also use conditional formatting in all of the cells, approximately
>> 3300+6×2000=15300 cells. It's not like millions of cells or something.
>>
>> What is likely the most time consuming part in this case? My own cell
>> formula in a couple of hundred cells? The conditional formatted cells?
>> All those longer formulas with nested IF's?
>>
>> Anyway, back to the subject… The thing I wrote about that regular
>> expression above was not quite accurate. I am not sure what to think
>> any more, it all feels like a mess, kind of. It seems like cells
>> containing a number also is found with the search descriptor above. I
>> am not sure why this is considered a proper behaviour, but maybe it
>> is.
>>
>> Well, I am not sure what more to say, feel quite confused, to say the
>> least…
>>
>> What are you other guys using a spreadsheet for? It seems like
>> whatever I try to do, it all turns out as an extremely slow confusing
>> mess.
>>
>>
>> Sorry for being such a jerk.
>>
>>
>> Kind regards
>>
>> Johnny Rosenberg
>> ジョニー・ローゼンバーグ
>>
>>>
>>>
>>> Kind regards
>>>
>>> Johnny Rosenberg
>>> ジョニー・ローゼンバーグ
>>>
>>>>
>>>>>> then SearchType 1 on the found area
>>>>>
>>>>>
>>>>> Area? You mean what's found when setting ”search all”?
>>>>
>>>>
>>>> no you can define the "erea" (cells) you want to searched so when you
>>>> found
>>>> that 1 cell has a formula, you need to check only this cell on a value
>>>>
>>>>>> the SearchString has a different meaning , depending on the
>>>>>> .SearchType
>>>>>> = 1 then the SearchString is the is the result of the formula or the
>>>>>> value
>>>>>> content
>>>>>
>>>>>
>>>>> Okay, that's not very well designed… at least not in my opinion.
>>>>>
>>>>>> = 0 then the SearchString is the formula string
>>>>>
>>>>>
>>>>> Meaning what if a cell contains only a value?
>>>>>>
>>>>>>
>>>>>> hope it helps
>>>>>
>>>>>
>>>>> I don't know, will do some tests later, but it certainly feels
>>>>> confusing at the moment, because when I use the Search/Replace
>>>>> dialogue, it doesn't work like that at all. Selecting ”Values” in the
>>>>> dialogue give me the result I want, it finds the first cell with an
>>>>> empty value (in this case a cell with a formula that returns that
>>>>> empty value – ""). So one question that comes to my mind is why the
>>>>> LibreOffice Basic Search doesn't work the same way as the dialogue.
>>>>> Maybe it's just fun to confuse the users…
>>>>>
>>>>> As I said, I will do some more tests and come back here later.
>>>>>
>>>>>
>>>>> Thanks for replying.
>>>>>
>>>>> Kind regards
>>>>>
>>>>> Johnny Rosenberg
>>>>> ジョニー・ローゼンバーグ
>>>>>
>>>>>> Fernand
>>>>>>
>>>>>>> Just can't figure it out. I have a column of 2000 formulas and
>>>>>>> values.
>>>>>>> Right now, A1:A1620 contains values, and A1621:A2000 contains
>>>>>>> formulas. The formulas in A1621:A2000, at the moment, return empty
>>>>>>> strings, all of them, so it looks like only the 1620 first rows
>>>>>>> contains data.
>>>>>>> So the formulas looks something like =IF(this and that;"";something
>>>>>>> else) (but a bit more complicated). The point is that if I input
>>>>>>> something on a ”new” row in one of the other columns, the A column
>>>>>>> shall, in some cases, display something.
>>>>>>>
>>>>>>> Okay, that's what the spreadsheet looks like, roughly.
>>>>>>>
>>>>>>> Now I use a couple of macros to do things for me a lot faster than I
>>>>>>> could ever do myself. One small part of a new macro I'm trying to
>>>>>>> write needs to search for the first ”empty” row, which means the
>>>>>>> first
>>>>>>> row where the A column contains a formula that returns an empty
>>>>>>> string.
>>>>>>>
>>>>>>> Here's what I tried:
>>>>>>> Function FindCurrentRow(Sheet As Object) As Integer
>>>>>>>           Dim SearchDescriptor As Object
>>>>>>>           SearchDescriptor=Sheet.createSearchDescriptor()
>>>>>>>           With SearchDescriptor
>>>>>>>                   .SearchByRow=False ' I want to search by column,
>>>>>>> starting
>>>>>>> at A.
>>>>>>>                   .SearchRegularExpression=False
>>>>>>>                   .SearchString=""
>>>>>>>                   .SearchType=1 ' 0=Search in formulae, 1=Search
>>>>>>> values.
>>>>>>>           End With
>>>>>>>
>>>>>>>           Dim Found As Object
>>>>>>>           Found=Sheet.findFirst(SearchDescriptor)
>>>>>>>           FindCurrentRow=Found.getCellAddress().Row
>>>>>>> End Function
>>>>>>>
>>>>>>> In this example I expect the function to return 1620 (which is the
>>>>>>> row
>>>>>>> address for the cell A1621). Instead 2000 is returned, so for some
>>>>>>> reason, when my cell formula returns "", that doesn't seem to be the
>>>>>>> same as .SearchString="".
>>>>>>> I also tried different values of .SearchValue, still with the same
>>>>>>> result: 2000 instead of 1620. So it only finds the first cell in the
>>>>>>> A
>>>>>>> column that is REALLY empty – no formula, no value.
>>>>>>>
>>>>>>> To me this seems like a bug, but for someone else, hopefully, it
>>>>>>> might
>>>>>>> seem like I'm just stupid, so feel free to call me stupid and, more
>>>>>>> important, tell me what I'm doing wrong and how I should do instead…
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>> Kind regards
>>>>>>>
>>>>>>> Johnny Rosenb erg
>>>>>>> ジョニー・ローゼンバーグ
>>>>>>>
>>>>>>
>>>>>> --
>>>>>> For unsubscribe instructions e-mail to:
>>>>>> users+help@global.libreoffice.org
>>>>>> Problems?
>>>>>> http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
>>>>>> Posting guidelines + more:
>>>>>> http://wiki.documentfoundation.org/Netiquette
>>>>>> List archive: http://listarchives.libreoffice.org/global/users/
>>>>>> All messages sent to this list will be publicly archived and cannot be
>>>>>> deleted
>>>>
>>>>
>>>>
>>>>
>>>> --
>>>> For unsubscribe instructions e-mail to:
>>>> users+help@global.libreoffice.org
>>>> Problems?
>>>> http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
>>>> Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
>>>> List archive: http://listarchives.libreoffice.org/global/users/
>>>> All messages sent to this list will be publicly archived and cannot be
>>>> deleted
>>
>>
>
> Johnny,
> maybe this solved bug that I had reported,
> https://bugs.freedesktop.org/show_bug.cgi?id=49764
> now fixed (I hope) can give some light about the slowness, see the Markus
> comments.
>
> Regards.
> Miguel Ángel.
>
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
ジョニー・ローゼンバーグ

-- 
For unsubscribe instructions e-mail to: users+help@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted