[Solved][Calc] Bugged duplicates detector requires expertise

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
ooGuillaume
Posts: 16
Joined: Sat Jul 20, 2019 10:03 am

[Solved][Calc] Bugged duplicates detector requires expertise

Post by ooGuillaume »

Hello,

I'm using LibreOffice 6.2.7.1 Calc on Xubuntu 18.04.

An association I'm a volunteer for has a spreadsheet "database" for subscribers to their workshop. I understand it's not good practice to use Calc for this, but at the moment we don't have the knowledge and/or taskforce to learn and use better tools.
I'd managed, thanks to the help from people on these boards, to add a duplicates detector for first name, last name and email, using Similarity Search.

It worked for a while, but a bug now makes it unusable. I've narrowed the issue down in a test file with easy steps to reproduce the bug.
I'm very bad at debugging, hence the need for help. :) I'm also a beginner in Basic code, and I'm pretty sure my macros require modifications in order to make them "safe".

Here it goes:
  1. make sure macros are enabled before or on opening the attached file BaseAdherentsTest.ods
  2. open the file
  3. in sheet "Adhérents", fill columns A, B and C on row 5 with eg "Jack", "Smith" and "jack@smith.com"
  4. see that columns D and E get populated automatically (these columns are usually hidden and used for the duplicates search)
  5. select cell C5 ("jack@smith.com")
  6. press Delete
  7. LibreOffice Calc should crash with unexpected error
The bug doesn't occur when deleting the second sheet in the file, named "Comptages annuels", before deleting cell C5 on step 5.

Hoping this is enough information to help you help me, please ask if more is needed. :)

Thank you.
Attachments
BaseAdherentsTest.ods
(47.81 KiB) Downloaded 180 times
Last edited by ooGuillaume on Sat Aug 07, 2021 9:55 am, edited 1 time in total.
LibreOffice 6.2.7.1 on Xubuntu 18.04
JeJe
Volunteer
Posts: 2764
Joined: Wed Mar 09, 2016 2:40 pm

Re: [Calc] Bugged duplicates detector requires expertise. :)

Post by JeJe »

Nope. Followed your instructions in OO and LO (7.1) and no crash.
Windows 10, Openoffice 4.1.11, LibreOffice 7.4.0.3 (x64)
ooGuillaume
Posts: 16
Joined: Sat Jul 20, 2019 10:03 am

Re: [Calc] Bugged duplicates detector requires expertise. :)

Post by ooGuillaume »

Hello JeJe, thanks for testing.

Could you try and copy & paste "john@doe.com" from cell C6 to C7?

A dialog box should tell you that a duplicate has been detected on line 6.
After you click OK, if it works, cell C7 is deleted, and selection moves back to line 6 where the duplicate is.
In my case though, LO crashes with unexpected error after I click OK.
LibreOffice 6.2.7.1 on Xubuntu 18.04
JeJe
Volunteer
Posts: 2764
Joined: Wed Mar 09, 2016 2:40 pm

Re: [Calc] Bugged duplicates detector requires expertise. :)

Post by JeJe »

No crash, I get what's in the picture after clicking ok
untitled.JPG
Windows 10, Openoffice 4.1.11, LibreOffice 7.4.0.3 (x64)
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Calc] Bugged duplicates detector requires expertise. :)

Post by Villeroy »

Tested with AOO 4.1.10, LO 6.0 and LO 7.1.
Crashes with both versions of LibreOffice.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
MrProgrammer
Moderator
Posts: 4895
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: [Calc] Bugged duplicates detector requires expertise. :)

Post by MrProgrammer »

ooGuillaume wrote:… a duplicates detector for first name, last name and email …
Conditional formatting can detect duplicates without an evil macro. Enter, successively, AAA, BBB, CCC, and DDD in cell C6 of my attachment to see the effect. I suppose you could add your NormalizeName function from [Solved] First and last name duplicates detector if you feel that is necessary.
202107270704.ods
(13.03 KiB) Downloaded 167 times
[Tutorial] The SUMPRODUCT function
ooGuillaume wrote:I'm very bad at debugging …
This shows a lack of proficiency in programming. Debugging is an essential skill for a programmer. People waste hours of their time trying to use macros when they don't have the proper experience for them. Macros are an "advanced topic". It will be easier to learn about conditional formatting than about macros and since that is a standard feature of Calc, used by thousands for decades, it is less likely to fail. Another non-macro solution is here.

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the subject field. Select the green checkmark icon at the same time.

[Tutorial] Ten concepts that every Calc user should know
Mr. Programmer
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Calc] Bugged duplicates detector requires expertise. :)

Post by Villeroy »

Basic/VBA coders hate spreadsheets that are not programmed all by themselves.

And as always: You store text on sheets and try to avoid duplicates. This means that you are trying to mimic a database. A spreadsheet will never be a database, no matter how many Basic code you throw at it. A database would not store any duplicate, so you would not have to detect them.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
ooGuillaume
Posts: 16
Joined: Sat Jul 20, 2019 10:03 am

Re: [Calc] Bugged duplicates detector requires expertise. :)

Post by ooGuillaume »

Hello all,

@JeJe
Thanks again, the picture you attached is indeed what happens for me if the bug doesn't occur.

@MrProgrammer
In the thread you link to, I originally used conditional formatting. As mentioned there, I wasn't able to use it and avoid slowing down the sheet loading and use, or add a similarity search to detect typos.
I'm a former C++ programmer, used to work with Xcode and Visual Studio. I must admit my lack of time and interest in learning how to set up and use debugging in LO Basic. I tried and set breakpoints here and there, couldn't see any data displayed, went back to MsgBox. Maybe "very bad at debugging" wasn't an honest description. I never was a very good programmer, though. :)

@Villeroy
Am I the Basic/VBA coder? Not sure what you meant, but I'd be very happy just ticking boxes to achieve what I want. :)
As mentioned in my first post, I know I'm using the wrong tool. You offered a sample DB file in the other thread I posted a while back, and maybe, someday, if I find the time, I'll learn how to use Base & SQL. So, thank you! :)

Meanwhile, I again used the solution which was the fastest and easiest for me: I moved the other sheets from the bugged file to a new one, for now the bug doesn't occur anymore.

Thank you everyone.
LibreOffice 6.2.7.1 on Xubuntu 18.04
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved][Calc] Bugged duplicates detector requires exper

Post by Villeroy »

someday, if I find the time, I'll learn how to use Base & SQL. So, thank you!
You already spent a lot more time with an arithmetic software trying to handle text with the help of Basic code. Tell us when this project is finished in a useable state.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Post Reply