Description: With Excel 2010, clicking the “Generate Statement” button in the “Range” sheet: 1. Clears all the contents in the “Statement” tab 2. Creates “Statements” tab using the information from the “Transactions”, “Interest Rates Cr”, “Interest Rates Dr” tabs. 3. Creates a PDF of using a region (not all) of the “Statements” tab. Unfortunately, LibreOffice Calc 7.3.3.2 crashes when the “Generate Statement” button is clicked. Does LibreOffice Calc have some missing VBA Macro APIs? Unsure how to add a file now and will add files in the next comments. Thank you Steps to Reproduce: 1. Open Statement.xls with LibreOffice Calc 7.3.3.2 2. Click the “Generate Statement” button in the “Range” sheet 3. LibreOffice crashes Actual Results: 1. Open Statement.xls with LibreOffice Calc 7.3.3.2 2. Click the “Generate Statement” button in the “Range” sheet 3. LibreOffice crashes Expected Results: Expected results are what happens when using Excel 2010: 1. Open Statement.xls with Excel 2010 2. Click the “Generate Statement” button in the “Range” sheet 3. VBA code clears all the contents in the “Statement” tab 4. VBA code creates “Statements” tab using the information from the “Transactions”, “Interest Rates Cr”, “Interest Rates Dr” tabs. 5. VBA code creates a PDF of using a region (not all) of the “Statements” tab. Reproducible: Always User Profile Reset: No Additional Info: Version: 7.3.3.2 / LibreOffice Community Build ID: d1d0ea68f081ee2800a922cac8f79445e4603348 CPU threads: 2; OS: Linux 5.4; UI render: default; VCL: gtk3 Locale: en-AU (en_AU.UTF-8); UI: en-US Flatpak Calc: threaded Linux Mint 20.3 Cinnamon
Created attachment 180420 [details] Excel spreadsheet created with Excel 2000. Tested and works with Excel 2000/2003/2007/2010 1. Open Statement.xls with LibreOffice Calc 7.3.3.2 2. Click the “Generate Statement” button in the “Range” sheet 3. LibreOffice crashes
Created attachment 180421 [details] Excel spreadsheet automatic output after “Generate Statement” button is pressed
cc: Hannah Meeks, Thorsten Behrens, Tomaž Vajngerl Hannah Meeks’ Google Summer of Code 2022 project. VBA Macros - Tests and missing APIs Hannah Meeks proposal is to research and make new tests for Writer (as Calc seems to already have lots of tests) as well as identify and implement some missing methods in both Calc and Writer that improve compatibility. https://summerofcode.withgoogle.com/programs/2022/projects/wMvv2tnI Hannah Meeks – VBA Macros – Tests and missing APIs : We support VBA (Visual Basic for Applications) Macros in LibreOffice, but the implemented API isn’t complete and the API functions aren’t largely tested. The consequence of this is that the VBA macros in OOXML documents don’t run as intended in LibreOffice, which causes compatibility problems. The goal of this project is to add tests for the functions already implemented and then look for what functions are missing for a method or module and add them. https://blog.documentfoundation.org/blog/2022/05/26/projects-selected-for-libreoffice-in-the-google-summer-of-code-2022/?unapproved=106262&moderation-hash=cc4b688d85ff2c090af061520d9b3b80#comment-106262 Does the attached Excel spreadsheet help identify and implement some missing methods in Calc? Thank you
Reproduced in Version: 7.4.0.0.alpha1+ / LibreOffice Community Build ID: 7bf9629d4f3e8504b5d09685d7721275b3287443 CPU threads: 8; OS: Linux 5.10; UI render: default; VCL: gtk3 Locale: es-ES (es_ES.UTF-8); UI: en-US Calc: threaded and Version: 6.0.0.0.alpha1+ Build ID: 6eeac3539ea4cac32d126c5e24141f262eb5a4d9 CPU threads: 8; OS: Linux 5.10; UI render: default; VCL: gtk3; Locale: es-ES (es_ES.UTF-8); Calc: group threaded
Not reproduced in Version: 5.3.0.0.alpha1 Build ID: f4ca1573fcf445164c068c1046ab5d084e1b005f CPU Threads: 8; OS Version: Linux 5.10; UI Render: default; VCL: gtk2; Locale: es-ES (es_ES.UTF-8); Calc: group
Crash started at the below commit. Adding Cc: to Eike Rathke; Could you possibly take a look at this one? Thanks 8484b5fb178bca2a8e569694b9896665a6b27ede is the first bad commit commit 8484b5fb178bca2a8e569694b9896665a6b27ede Author: Jenkins Build User <tdf@pollux.tdf> Date: Tue Jun 27 17:11:16 2017 +0200 source a962f1465a8db9b6c4dfdfd8fef9141f07d7fd06 https://git.libreoffice.org/core/+/a962f1465a8db9b6c4dfdfd8fef9141f07d7fd06 Resolves: tdf#108788 update references for deletions at end of sheet
crashes at Private Sub SetupStatement() ' ' SetupStatement Procedure ' Óvári 14/Feb/2001 ' With Sheets(SH_STATEMENT) '(snip) ' Set the formatting .Columns(1).NumberFormat = _ Sheets(SH_RANGE).Cells(7, 2).Value ' DATE_FORMAT ' column A '(snip) End With End Sub
Created attachment 180457 [details] bt with debug symbols On pc Debian x86-64 with master sources updated today, I could reproduce this. I also noticed these on console: ype com.sun.star.frame.XFrame! at /home/julien/lo/libreoffice/include/com/sun/star/uno/Reference.hxx:105" warn:sfx.control:233002:233002:sfx2/source/control/dispatch.cxx:1206: Childwindow slot missing: 25917 warn:sfx.control:233002:233002:sfx2/source/control/dispatch.cxx:1206: Childwindow slot missing: 26189 warn:sfx.control:233002:233002:sfx2/source/control/dispatch.cxx:1206: Childwindow slot missing: 26190 warn:sfx.control:233002:233002:sfx2/source/control/dispatch.cxx:1206: Childwindow slot missing: 25917 warn:sfx.control:233002:233002:sfx2/source/control/dispatch.cxx:1206: Childwindow slot missing: 26189 warn:sfx.control:233002:233002:sfx2/source/control/dispatch.cxx:1206: Childwindow slot missing: 26190 warn:scripting:233002:233002:scripting/source/basprov/basprov.cxx:309: LibraryName InterestChecker is part of the url InterestChecker.Sheet1.butGenerateStatement_Click /home/julien/lo/libreoffice/instdir/share/extensions/numbertext/pythonpath/org/Numbertext/Soros.py:37: DeprecationWarning: invalid escape sequence \s m = re.match("^\s*(\"[^\"]*\"|[^\s]*)\s*(.*[^\s])?\s*$", s) warn:legacy.osl:233002:233002:sc/source/ui/unoobj/cellsuno.cxx:4543: What ranges ?!?! warn:sc:233002:233002:sc/source/core/data/table2.cxx:3535: Invalid row number 1048576 /usr/bin/../lib/gcc/x86_64-linux-gnu/11/../../../../include/c++/11/debug/vector:454: In function: std::vector::reference std::vector<ScRange>::front() [_Tp = ScRange, _Allocator = std::allocator<ScRange>] Error: attempt to access an element in an empty container. Objects involved in the operation: sequence "this" @ 0x7ffe7a7049a8 { type = std::debug::vector<ScRange, std::allocator<ScRange> >; }
Fwiw, the initial crash will be fixed with the change for bug 149531, but I doubt the original code modified for that was ever correct. What will remain here is the .Columns(1).NumberFormat throwing a BASIC runtime error, and running the macro again with another click on the button will crash at a different but similar place.
Created attachment 180739 [details] Statement Modified 2.xls (In reply to Eike Rathke from comment #9) > Fwiw, the initial crash will be fixed with the change for bug 149531, but I > doubt the original code modified for that was ever correct. What will remain > here is the > > .Columns(1).NumberFormat > > throwing a BASIC runtime error, and running the macro again with another > click on the button will crash at a different but similar place. Thank you for your response. Bug 52602 comment 14 has a workaround to ensure NumberFormat can work with both MS Excel and LO Calc. LO Calc's NumberFormat is case-sensitive and string-sensitive. The NumberFormat workaround has been added to the attached `Statement Modified 2.xls`. Are you able to provide some code pointers how LO Calc supports Excel VBA Sort? Ideally it would be good if the same code would work with both MS Excel and LO Calc like the modified NumberFormat does. ```` ' Works with Excel, not LibreOffice Calc With Worksheets(intSheetIndex) .Range("A1").Sort _ Key1:=.Columns("A"), _ Order1:=xlAscending, _ Header:=xlYes, _ MatchCase:=False, _ Orientation:=xlTopToBottom End With ' Enums ' xlAscending : 1 ' xlYes : 1 ' False : 0 (guess) ' xlTopToBottom : 1 ```` Thank you
Created attachment 180740 [details] LO Calc output from Statement Modified 2.xls
Created attachment 180741 [details] MS Excel output from Statement Modified 2.xls
Eike Rathke committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/d6331fc7abe545ff0a369c41ab3f55b8f44a2cc1 Related: tdf#149325 Eliminate all unconditional ScRangeList::front() access It will be available in 7.5.0. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Eike Rathke committed a patch related to this issue. It has been pushed to "libreoffice-7-4": https://git.libreoffice.org/core/commit/7039bb3413476199448d76e3a454cf929217d7bd Related: tdf#149325 Eliminate all unconditional ScRangeList::front() access It will be available in 7.4.0.0.beta2. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Eike Rathke committed a patch related to this issue. It has been pushed to "libreoffice-7-3": https://git.libreoffice.org/core/commit/e463c3d6e1e657a2a281f3cf0ae1dc01bdc25c5b tdf#149531 tdf#149325 Eliminate unconditional ScRangeList::front() access It will be available in 7.3.5. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
(In reply to Óvári from comment #10) > LO Calc's NumberFormat is case-sensitive The number formats themselves are not, but the VBA NumberFormat implementation unfortunately is. I'm going to change that. > Are you able to provide some code pointers how LO Calc supports Excel VBA > Sort? The central place seems to be https://opengrok.libreoffice.org/xref/core/sc/source/ui/vba/vbarange.cxx?r=d6331fc7#3411 but don't ask me how that is supposed to work, I'm not the author and would have to figure it out the same as you. If you wanted to know how you can *use* Sort, then the best place to ask would be https://ask.libreoffice.org/ Anyway, please don't mix other topics into this bug report.
Eike Rathke committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/9470c5531dc928d438a6a7f4d47f2d82f2296cc1 Related: tdf#149325 Fix yet another VbaRange empty ScRangeList access It will be available in 7.5.0. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Xisco Fauli committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/4b97420c5bc72504cfbf793aaa7837ebe812399a tdf#149325, tdf#149531: sc_macros_test: Add unittest It will be available in 7.5.0. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Eike Rathke committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/8661d5579bd19a9e294ddff64bbe817b537dbd46 Resolves: tdf#149325 tdf#52602 SvNumberFormatsObj::queryKey try also uppercase It will be available in 7.5.0. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Eike Rathke committed a patch related to this issue. It has been pushed to "libreoffice-7-4": https://git.libreoffice.org/core/commit/31e47d1e76052b52a4aa058306cd17a1c0ef36aa Related: tdf#149325 Fix yet another VbaRange empty ScRangeList access It will be available in 7.4.0.0.beta2. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Eike Rathke committed a patch related to this issue. It has been pushed to "libreoffice-7-4": https://git.libreoffice.org/core/commit/c98a60742a25eb4d4526f109f9af9de391da0643 Resolves: tdf#149325 tdf#52602 SvNumberFormatsObj::queryKey try also uppercase It will be available in 7.4.0.0.beta2. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Eike Rathke committed a patch related to this issue. It has been pushed to "libreoffice-7-3": https://git.libreoffice.org/core/commit/45f2ad8f0ec4088217233132cab093180edec79c Related: tdf#149325 Fix yet another VbaRange empty ScRangeList access It will be available in 7.3.5. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Eike Rathke committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/a4fa06be4f8cd4d2584a6f3e4c5bd02786e591ad Related: tdf#149325 tdf#52602 SvNumberFormatsObj::addNew accept differing It will be available in 7.5.0. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Eike Rathke committed a patch related to this issue. It has been pushed to "libreoffice-7-4": https://git.libreoffice.org/core/commit/e5f403b7e6a5d2d99a01a2111224b5277beba0d5 Related: tdf#149325 tdf#52602 SvNumberFormatsObj::addNew accept differing It will be available in 7.4.0.0.beta2. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Verified fixed as no longer crashing with LibreOffice Version: 7.5.0.0.alpha0+ / LibreOffice Community Build ID: ca47989ad60b1414f92be22a1fbf4c1d1a92dd97 CPU threads: 2; OS: Linux 5.4; UI render: default; VCL: gtk3 Locale: en-AU (en_AU.UTF-8); UI: en-US Calc: threaded with Linux Mint 20.3 Cinnamon Thank you
i am on Version: 7.4.2.3 / LibreOffice Community Build ID: 382eef1f22670f7f4118c8c2dd222ec7ad009daf CPU threads: 4; OS: Linux 5.15; UI render: default; VCL: gtk3 Locale: en-IN (en_IN); UI: en-US Flatpak Calc: threaded and i downloaded "https://bugs.documentfoundation.org/attachment.cgi?id=180420&action=edit" and when i press on "generate statement" i get "basic runtime error'423', property or method not found:.PrintComments = xlPrintNoComments" so if i delete that line from the code, and press generate again, "423: .Draft = False" and " .BlackAndWhite = False" after that the statement does get generated but i am not sure what the data should look like but i first reopened the file after no saving any changes, removed the data from "statement" and "summary" sheets and removed the offending lines and pressed the button, i did not get any data in "summary" sheet and i get some extra fields in "statement" sheet columns, F,G,H but that isnt printinig so should be fine. also, on linux, i could not get it to print an output as is being suggested so this print output is not working on linux as well
(In reply to johnks from comment #26) Thank you for your comment. Can you please try the “Statement Generator” at https://extensions.libreoffice.org/en/extensions/show/21514
(In reply to johnks from comment #26) > also, on linux, i could not get it to print an output as is being suggested > so this print output is not working on linux as well On GNU/Linux, you need to install `Printer-driver-cups-pdf` (Printer driver for PDF writing via CUPS) to use the Print option. However, if you use “Statement Generator” at https://extensions.libreoffice.org/en/extensions/show/21514, it has been improved to `Export to PDF` so that `Printer-driver-cups-pdf` is not required and uses the PDF export feature in LibreOffice (which has many more options). Hope this helps.
(In reply to johnks from comment #26) > i get "basic runtime error'423', property or method not found:.PrintComments > = xlPrintNoComments" Please note that you are talking about something completely different than this bug was about (crash in the application) and removing VERIFIED FIXED was wrong. Setting back.