Bug 102076 - Excel XLSM import: VBA compatibility mode insufficient: "EVALUATE" not supported
Summary: Excel XLSM import: VBA compatibility mode insufficient: "EVALUATE" not supported
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
Inherited From OOo
Hardware: All All
: medium enhancement
Assignee: Not Assigned
URL:
Whiteboard:
Keywords: difficultyMedium, easyHack, filter:xlsx, skillCpp, skillDebug
Depends on:
Blocks: XLSX Macro-VBA
  Show dependency treegraph
 
Reported: 2016-09-12 13:52 UTC by Tobias Burnus
Modified: 2024-04-03 09:17 UTC (History)
5 users (show)

See Also:
Crash report or crash signature:


Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description Tobias Burnus 2016-09-12 13:52:10 UTC
Item related to the same file as bug 102075, namely attachment 127275 [details] [Excel XLSM file (XLS with macros)]

LibreOffice's VBA support stumbles over MSO's VBA function

   If Evaluate("EXACT(Zeiten!" & colNo2 & "6,"""")") Then Exit Do

EVALUATE is supposed to yield the same result as entering the this for a cell (with prefix "="), e.g. "=EXACT(Zeiten!A6)" as cell value.

Current result:
  Error box with:
    BASIC runtime error.
    '1'
    Type: com.sun.star.uno.RuntimeException
    Message:
    [OK]

Expected:
(1) A better error message
(2) Support of the EVALUATE.
Comment 1 Buovjaga 2016-10-07 12:07:17 UTC
If I click Auswerten, I don't get an error.
Am I missing something?

Arch Linux 64-bit, KDE Plasma 5
Version: 5.3.0.0.alpha0+
Build ID: dc41255dc725dfa912326cc5d9e420bd66471c64
CPU Threads: 8; OS Version: Linux 4.7; UI Render: default; 
Locale: fi-FI (fi_FI.UTF-8); Calc: group
Built on October 1st 2016

Arch Linux 64-bit, KDE Plasma 5
Version: 5.2.2.2.0+
Build ID: 5.2.2-1
CPU Threads: 8; OS Version: Linux 4.7; UI Render: default; 
Locale: fi-FI (fi_FI.UTF-8); Calc: group
Comment 2 Carlos 2017-04-04 17:47:42 UTC
Thank you for reporting the bug. I can confirm that the bug is present in
Version: 5.3.1.2 (x64)
Build ID: e80a0e0fd1875e1696614d24c32df0f95f03deb2
CPU Threads: 4; OS Version: Windows 6.19; UI Render: default; Layout Engine: new; 
Locale: en-US (en_US); Calc: group


After clicking on "Auswerten" i get

BASIC runtime error.
'1'

Type: com.sun.star.uno.RuntimeException
Comment 3 Carlos 2017-04-04 17:50:11 UTC
Could you provide a more minimal file?
This makes it easier for us to verify the bug. 

I have set the bug's status to 'NEEDINFO'. 
Please change it back to 'UNCONFIRMED' once the requested document is provided.
Comment 4 himajin100000 2017-07-03 02:49:07 UTC
' I'm not the bug reporter but let me provide simple code

Option VBASupport 1
Option Explicit

Sub Main()
    Evaluate ("A1:A2") ' Works both on Excel and on LibreOffice
    Evaluate ("TODAY()") ' Works on Excel, but Runtime Error on LibreOffice
End Sub

' FYI:
' http://opengrok.libreoffice.org/xref/core/sc/source/ui/vba/vbaglobals.cxx?r=198c41c4fe8be4ce8a6ddab43ae0c5f17a4889ac#178
' http://opengrok.libreoffice.org/xref/core/sc/source/ui/vba/vbaapplication.cxx?r=5c5a49eda1555db7b7519e9b0398cfd469c9bcb5#341
Comment 5 Markus Mohrhard 2017-07-09 11:26:11 UTC
Let me make this an easy hack.

Code pointers:

https://cgit.freedesktop.org/libreoffice/core/tree/sc/source/ui/vba/vbaapplication.cxx#n344 contains the code that calls the evaluation.

Most likely the easiest way to implement support for any calc formula is through ScSimpleFormulaCalculator which already allows to provide a formula and get a result back.

In addition to fixing this issue the task will also involve adding a number of automatic test cases to e.g. ScMacrosTest::testVba.
Comment 6 Jesse McKenna 2018-02-25 07:33:18 UTC Comment hidden (obsolete)
Comment 7 Jesse McKenna 2018-03-17 14:27:21 UTC Comment hidden (obsolete)
Comment 8 Hossein 2024-01-07 19:49:10 UTC
Re-evaluating the EasyHack in 2024

This enhancement is still relevant, as the "EVALUATE" support is not implemented yet.

The same "Error" message happens when you enable macros, open attachment 127275 [details] and click on the "Auswerten" button:

BASIC runtime error.
'1'
An exception occurred 
Type: com.sun.star.uno.RuntimeException
Message: at ...sc/source/ui/vba/vbarange.cxx:1248.

Tested with:

Version: 24.8.0.0.alpha0+ (X86_64) / LibreOffice Community
Build ID: 1ec3ef4db8c4c03d89a3d86c0e633effde04efda
CPU threads: 20; OS: Windows 10.0 Build 22631; UI render: Skia/Raster; VCL: win
Locale: en-US (en_US); UI: en-US
Calc: CL threaded

A simpler way to test is provided in comment 4, and the code pointers are available in comment 5. Please note that neither of those 2 calls to "Evaluate()" work for me.