Bug 149325 - VBA reference to a sheet by name, not index, crashes Calc
Summary: VBA reference to a sheet by name, not index, crashes Calc
Status: VERIFIED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
6.0.7.3 release
Hardware: All All
: medium normal
Assignee: Eike Rathke
URL: https://crashreport.libreoffice.org/s...
Whiteboard: target:7.5.0 target:7.4.0.0.beta2 tar...
Keywords: bibisected, bisected, haveBacktrace, regression
Depends on:
Blocks: Macro-VBA
  Show dependency treegraph
 
Reported: 2022-05-27 03:07 UTC by Óvári
Modified: 2022-11-11 12:19 UTC (History)
10 users (show)

See Also:
Crash report or crash signature: ["ScRange::ScRange(ScRange const &)"]
Regression By:


Attachments
Excel spreadsheet created with Excel 2000. Tested and works with Excel 2000/2003/2007/2010 (180.50 KB, application/vnd.ms-excel)
2022-05-27 03:15 UTC, Óvári
Details
Excel spreadsheet automatic output after “Generate Statement” button is pressed (26.34 KB, application/pdf)
2022-05-27 03:16 UTC, Óvári
Details
bt with debug symbols (4.93 KB, text/plain)
2022-05-29 21:03 UTC, Julien Nabet
Details
Statement Modified 2.xls (143.00 KB, application/vnd.ms-excel)
2022-06-13 21:41 UTC, Óvári
Details
LO Calc output from Statement Modified 2.xls (19.83 KB, application/pdf)
2022-06-13 21:44 UTC, Óvári
Details
MS Excel output from Statement Modified 2.xls (26.20 KB, application/pdf)
2022-06-13 21:44 UTC, Óvári
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Óvári 2022-05-27 03:07:59 UTC
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
Comment 1 Óvári 2022-05-27 03:15:01 UTC
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
Comment 2 Óvári 2022-05-27 03:16:35 UTC
Created attachment 180421 [details]
Excel spreadsheet automatic output after “Generate Statement” button is pressed
Comment 3 Óvári 2022-05-27 03:21:12 UTC
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
Comment 4 Xisco Faulí 2022-05-27 08:29:47 UTC
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
Comment 5 Xisco Faulí 2022-05-27 08:31:18 UTC
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
Comment 6 raal 2022-05-27 19:03:19 UTC
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 sha:a962f1465a8db9b6c4dfdfd8fef9141f07d7fd06

https://git.libreoffice.org/core/+/a962f1465a8db9b6c4dfdfd8fef9141f07d7fd06
    Resolves: tdf#108788 update references for deletions at end of sheet
Comment 7 himajin100000 2022-05-29 19:01:27 UTC
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
Comment 8 Julien Nabet 2022-05-29 21:03:57 UTC
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> >;
    }
Comment 9 Eike Rathke 2022-06-13 15:21:51 UTC
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.
Comment 10 Óvári 2022-06-13 21:41:37 UTC
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
Comment 11 Óvári 2022-06-13 21:44:00 UTC
Created attachment 180740 [details]
LO Calc output from Statement Modified 2.xls
Comment 12 Óvári 2022-06-13 21:44:29 UTC
Created attachment 180741 [details]
MS Excel output from Statement Modified 2.xls
Comment 13 Commit Notification 2022-06-13 23:09:02 UTC
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.
Comment 14 Commit Notification 2022-06-14 00:13:39 UTC
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.
Comment 15 Commit Notification 2022-06-14 09:22:09 UTC
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.
Comment 16 Eike Rathke 2022-06-14 09:26:58 UTC
(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.
Comment 17 Commit Notification 2022-06-14 21:01:07 UTC
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.
Comment 18 Commit Notification 2022-06-15 06:28:48 UTC
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.
Comment 19 Commit Notification 2022-06-15 09:04:55 UTC
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.
Comment 20 Commit Notification 2022-06-15 09:19:40 UTC
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.
Comment 21 Commit Notification 2022-06-15 12:06:53 UTC
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.
Comment 22 Commit Notification 2022-06-16 08:05:16 UTC
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.
Comment 23 Commit Notification 2022-06-16 09:22:57 UTC
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.
Comment 24 Commit Notification 2022-06-16 12:54:41 UTC
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.
Comment 25 Óvári 2022-06-20 02:19:04 UTC
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
Comment 26 johnks 2022-11-11 05:51:06 UTC Comment hidden (off-topic)
Comment 27 Óvári 2022-11-11 06:04:37 UTC Comment hidden (off-topic)
Comment 28 Óvári 2022-11-11 06:11:23 UTC Comment hidden (off-topic)
Comment 29 Eike Rathke 2022-11-11 12:18:59 UTC Comment hidden (off-topic)