Bug 158386 - Excel File with VBA macro errors with "Basic Syntax Error Code" because of function used in constant expression
Summary: Excel File with VBA macro errors with "Basic Syntax Error Code" because of fu...
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
4.3.0.4 release
Hardware: All All
: medium normal
Assignee: Not Assigned
URL: https://learn.microsoft.com/en-us/ope...
Whiteboard:
Keywords:
Depends on:
Blocks: Macro-VBA
  Show dependency treegraph
 
Reported: 2023-11-26 21:47 UTC by Jeffry
Modified: 2024-07-06 17:12 UTC (History)
4 users (show)

See Also:
Crash report or crash signature:


Attachments
The file (version 2.909) from https://neverwintervault.org/project/nwn1/other/tool/characterbuildcalculator-cbc (https://neverwintervault.org/sites/all/modules/pubdlcnt/pubdlcnt.php?fid=262052) (6.96 MB, application/vnd.ms-excel.sheet.macroEnabled.12)
2024-04-14 05:37 UTC, Mike Kaganski
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Jeffry 2023-11-26 21:47:43 UTC
When trying to open a Windows Excel file CBC2.909 using LibreOffice Calc version 7.6.2.1 you get a "Basic Syntax Error Code" that locks up the program and Windows Task Manager is the only way to end the program. Windows Environment: CPU Threads 8, Os Windows 10.0 build 19045. I managed to convert the CBC2.909 file via the Aspose website and that allowed it to open up in libre Calc.  https://products.aspose.app/cells/conversion/xlsm-to-ods.

The File can be downloaded from The Neverwinter Vault here https://neverwintervault.org/project/nwn1/other/tool/characterbuildcalculator-cbc#comment-71626 

Make sure you grab version 2.909
Comment 1 m_a_riosv 2023-11-26 22:32:21 UTC
The file has excel macros, that seems not compatible with LibreOffice.

In Menu/Tools/Options/LibreOffice/Security/Macro Security/Macro Security/ to medium.
LO ask to enable or not the macros at opening the file. Disable it and you can open the file, and take a look to the macros.
Comment 2 Mike Kaganski 2023-11-27 04:48:38 UTC
(In reply to Jeffry from comment #0)
> you get a "Basic Syntax Error Code" that locks up the
> program and Windows Task Manager is the only way to end the program.

It doesn't lock up, it just shows it tens of times. Pressing Esc and holding it for a minute would dismiss all those message boxes, and finally allow you to use the document. The suggestion of m.a.riosv is best.

The offending code is

Function VLOOKUP2(ByRef values As range, ByRef matrix As range, column As Integer, Optional default As Variant = CVErr(xlErrNA)) As Variant

where 'Optional default As Variant = CVErr(xlErrNA)' seems to be unsupported, we may not allow defaults to be defined by a function, only by a literal.

Andreas, what do you think?
Comment 3 Andreas Heinisch 2023-11-27 19:50:38 UTC
The documentation states that "The default value for an optional parameter must be a constant expression."

Does Excel allow other functions apart from the CVErr function?
Comment 4 Stéphane Guillou (stragu) 2023-12-13 21:30:34 UTC
For reference, the Visual Basic documentation that Andreas quoted is: https://learn.microsoft.com/en-us/dotnet/visual-basic/programming-guide/language-features/procedures/optional-parameters

Also in VBA documentation[1]:

"defaultvalue: Any constant or constant expression."

So even MS Office shouldn't allow an argument default that uses a function, right?

[1]: https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/function-statement
Comment 5 Mike Kaganski 2023-12-14 05:55:59 UTC
Right; so it seems that the question is, should we allow the non-standard extension that Excel allows; and if yes, then to what extent (question in comment 3).

See also:
https://learn.microsoft.com/en-us/office/vba/Language/Reference/user-interface-help/constant-expression-required
Comment 6 Armondo Lopez 2024-04-13 21:17:55 UTC Comment hidden (obsolete)
Comment 7 Mike Kaganski 2024-04-14 05:37:33 UTC
Created attachment 193670 [details]
The file (version 2.909) from https://neverwintervault.org/project/nwn1/other/tool/characterbuildcalculator-cbc (https://neverwintervault.org/sites/all/modules/pubdlcnt/pubdlcnt.php?fid=262052)

(In reply to Armondo Lopez from comment #6)
> Just chiming in to say that I was able to successfully open the file

Attaching the correct version of problematic file, still giving the same problem both

in Version: 24.2.2.2 (X86_64) / LibreOffice Community
Build ID: d56cc158d8a96260b836f100ef4b4ef25d6f1a01
CPU threads: 24; OS: Windows 10.0 Build 22631; UI render: Skia/Raster; VCL: win
Locale: en-GB (ru_RU); UI: en-GB
Calc: CL threaded

and in Version: 24.8.0.0.alpha0+ (X86_64) / LibreOffice Community
Build ID: fce1fb034d282dc6e0434bf16c49d5f602d813bd
CPU threads: 24; OS: Windows 10.0 Build 22631; UI render: Skia/Vulkan; VCL: win
Locale: ru-RU (ru_RU); UI: en-US
Calc: CL threaded

(of course, you need to enable and accept macros in the file).
Comment 8 Armondo Lopez 2024-04-14 20:05:26 UTC
(In reply to Mike Kaganski from comment #7)
> Created attachment 193670 [details]
> The file (version 2.909) from
> https://neverwintervault.org/project/nwn1/other/tool/
> characterbuildcalculator-cbc
> (https://neverwintervault.org/sites/all/modules/pubdlcnt/pubdlcnt.
> php?fid=262052)
> 
> (In reply to Armondo Lopez from comment #6)
> > Just chiming in to say that I was able to successfully open the file
> 
> Attaching the correct version of problematic file, still giving the same
> problem both
> 
> in Version: 24.2.2.2 (X86_64) / LibreOffice Community
> Build ID: d56cc158d8a96260b836f100ef4b4ef25d6f1a01
> CPU threads: 24; OS: Windows 10.0 Build 22631; UI render: Skia/Raster; VCL:
> win
> Locale: en-GB (ru_RU); UI: en-GB
> Calc: CL threaded
> 
> and in Version: 24.8.0.0.alpha0+ (X86_64) / LibreOffice Community
> Build ID: fce1fb034d282dc6e0434bf16c49d5f602d813bd
> CPU threads: 24; OS: Windows 10.0 Build 22631; UI render: Skia/Vulkan; VCL:
> win
> Locale: ru-RU (ru_RU); UI: en-US
> Calc: CL threaded
> 
> (of course, you need to enable and accept macros in the file).

My apologies. You're correct. In enabling macros, I do get the same behavior that you listed.
Comment 9 Jeffry 2024-07-01 20:58:11 UTC
Hey, again, are there any thoughts or decisions made on whether support for opening this Excel file could be added to Libre Calc?
Comment 10 Stéphane Guillou (stragu) 2024-07-02 02:43:33 UTC
(In reply to Mike Kaganski from comment #5)
> Right; so it seems that the question is, should we allow the non-standard
> extension that Excel allows; and if yes, then to what extent (question in
> comment 3).
Mike, who do you think could help reach a decision on this? Out of my depth here.
Comment 11 Mike Kaganski 2024-07-02 05:42:30 UTC
FTR:
The formal VBS specification is available at
https://learn.microsoft.com/en-us/openspecs/microsoft_general_purpose_programming_languages/ms-vbal/d5418146-0bd2-45eb-9c7a-fd9502722c74

In its current version (v20240521), the default parameter value is defined in section 5.3.1.5 Parameter Lists:

> default-value = "=" constant-expression

and the <constant-expression> ABNF rule (as per sect. 1.4) is defined in section 5.6.16.1 Constant Expressions:

> constant-expression = expression
> 
> Static semantics. A constant expression is valid only when <expression> is
> composed solely of the following constructs:
> ...
> * Simple name expression invoking the VBA intrinsic function Int, Fix, Abs, Sgn, Len, LenB,
>   CBool, CByte, CCur, CDate, CDbl, CInt, CLng, CLngLng, CLngPtr, CSng, CStr or CVar.

There are no other functions mentioned there as allowed in the expression.

Further, section 6.1.2.3.1.14 CVErr does not include any "Static Semantics" subsection, only "Runtime Semantics" subsection.

So:
1. VBA supports a subset of functions in constant expressions (explicitly defined set), which answers comment 3.
2. It formally doesn't allow CVErr there, so the problematic document uses a formally invalid construct.
3. After #1 is implemented, it will be possible to decide on a case-by-case basis, if we should extend the list of permitted functions to match the actual VBA implementation details.
4. It would be nice to file a ticket to MS about the problem in documentation (my expectation would be, that the function is added to the permitted intrinsic function list).

Setting to NEW, because at least the intrinsic functions must be supported in constant expressions.
Comment 12 Stéphane Guillou (stragu) 2024-07-02 14:01:28 UTC
Thanks Mike.

(Same numerous errors in 4.3.0.4. Macro not run in 4.2.0.4.)
Comment 13 Jeffry 2024-07-06 17:12:14 UTC
Thanks for taking another look at this.