Download it now!
Bug 102078 - Excel XLSM import with VBA: calling function fails with bogus "Object variable not set"
Summary: Excel XLSM import with VBA: calling function fails with bogus "Object variabl...
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
5.2.1.2 release
Hardware: All All
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords: filter:xlsx
Depends on: 114932
Blocks: XLSX Macro-VBA
  Show dependency treegraph
 
Reported: 2016-09-12 14:03 UTC by Tobias Burnus
Modified: 2018-12-18 19:15 UTC (History)
2 users (show)

See Also:
Crash report or crash signature:


Attachments
test.xlsm file (created with MSO Excel 2007) (14.80 KB, application/vnd.ms-excel.sheet.macroEnabled.12)
2018-01-10 14:15 UTC, Tobias Burnus
Details

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

If one changes the "Sub Auswerten()" to use the lines after "'LO:" instead of the lines after "'MSO" - and then tries to run it, it fails with the odd error message:

  BASIC runtime error.
  '91'
  Object variable not set.


It stops at the line:
  Function getColNum(col, offset) As String

If one sets the break point there and hovers over "col" and "offset", LO shows: "col=<missing parameter>" and "offset=<missing parameter>"

The caller is:

  colNo2 = getColNum(colNo2, deltaCol)

and hovering over the LHS shows: "colNo2=H" and on the RHS: "colNo2=H" and "deltaCol=3". If one now clicks "Step into", hovering over "offset" shows "offset=3" but hovering over "col" doesn't show any tool tip. Clicking "Step into" again, shows the error message from above.


Expected:
  The function call works, passing successfully the string argument (and the numeric argument).
Comment 1 Buovjaga 2016-10-08 10:28:46 UTC
I don't get the error message.

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

Changes:

  Do While True
    'LO: (untested)
     If "" = Sheets("Zeiten").Range(colNo2 & "6").Value Then Exit Do
    'MSO:
    'If Evaluate("EXACT(Zeiten!" & colNo2 & "6,"""")") Then Exit Do
    'n_WF = n_WF + 1
    'colNo2 = getColNum(colNo2, deltaCol)
  Loop
  
  Do While True
    cnt = cnt + 1
    'LO: (untested)
     If "" = Sheets("Zeiten").Range(colNo2 & "6").Value Then Exit Do
    'MSO:
    'If Evaluate("EXACT(Zeiten!" & colNo & "6,"""")") Then Exit Do
    
    Rem Geseglte Zeit je Wettfahrt
    sh_name = "GesZeit_WF" & cnt
    'LO: (untested)
     If Sheets.hasByName(hasByName) Then Sheets(sh_name).Delete
    'MSO:
    'If Evaluate("ISREF('" & sh_name & "'!A1)") Then Sheets(sh_name).Delete
    'Sheets.Add After:=Sheets(Sheets.Count)
    'Sheets(Sheets.Count).Name = sh_name
    'Set sht = Sheets(sh_name)
Comment 2 Carlos 2017-04-04 17:53:01 UTC
Unfortunately without clear steps to reproduce it, we cannot track down the origin of the problem. 
Please provide a clearer set of step-by-step instructions on how to reproduce the problem.

I have set the bug's status to 'NEEDINFO'. Please change it back to 'UNCONFIRMED' once the requested information is provided.
Comment 3 QA Administrators 2018-01-02 10:14:59 UTC Comment hidden (obsolete)
Comment 4 Tobias Burnus 2018-01-09 14:54:23 UTC Comment hidden (obsolete)
Comment 5 Tobias Burnus 2018-01-09 14:55:11 UTC
(In reply to Tobias Burnus from comment #4)
> Crossref: Bug 102078 prevents to provide more details.
Should have been bug 114932.
Comment 6 Tobias Burnus 2018-01-10 14:15:41 UTC
Created attachment 139019 [details]
test.xlsm file (created with MSO Excel 2007)

New test case with simplified macros

* In MSO Excel, opening the file, enabling macros and clicking "Run" will show a "K" in cell A4.

In LO (both 6.0rc1 and 6.1.0.0.alpha0+, Build ID: 0074951704022d173a5fdb9df933f47be1dcbb91 TinderBox: Win-x86@42):

[First, Enable macros]

(A) Clicking "Run" seemingly doesn't do anything.

(B) Running the macro directly:
    Tools | Macros > Run Macros ...
    "test.xlsm" -> VBAProject -> Module1 -> "Run" [Run]
=> This shows a dialog with:
   "Error" "BASIC runtime error. '91' Object variable not set.' [OK]"
   and stops in line 3 in the source code ("Function getColNum"...)

In the debugger, one sees (line 22) that the arguments' variables have the proper values "H" and 3, respectively).
Comment 7 Buovjaga 2018-01-14 14:44:30 UTC
(In reply to Tobias Burnus from comment #6)
> => This shows a dialog with:
>    "Error" "BASIC runtime error. '91' Object variable not set.' [OK]"
>    and stops in line 3 in the source code ("Function getColNum"...)

I get the error, pointing to line 4:
BASIC runtime error.
'449'
Argument is not optional.

What could be the cause? Setting to NEW in any case, but hopefully Tobias can shed more light on this.

Arch Linux 64-bit
Version: 6.1.0.0.alpha0+
Build ID: ef22c4a0a99be5d2903fb9e9d09fc852cd791173
CPU threads: 8; OS: Linux 4.14; UI render: default; VCL: kde4; 
Locale: fi-FI (fi_FI.UTF-8); Calc: group threaded
Built on January 12th 2018
Comment 8 himajin100000 2018-12-10 22:47:41 UTC
Eike, the document Tobias Burnus gave in comment 6 has two functions. Which of the two functions did you run from the dialog?
Comment 9 himajin100000 2018-12-11 03:50:03 UTC
I commented-out the lines

colPre = col.Chars(1)
colNo = col.Chars(0)

,thinking String type in StarBasic does not have Chars property.

and I started Run() function, which later calls the other function getColNum(col,offset), and I was able to end the macro without errors, if I don't mind the result.
Comment 10 himajin100000 2018-12-18 18:42:39 UTC
sorry, my comment 8 was not for Eike. I got confused with other bug report.
It's for Buovjaga(comment 7)
Comment 11 Buovjaga 2018-12-18 19:15:55 UTC
(In reply to himajin100000 from comment #8)
> Eike, the document Tobias Burnus gave in comment 6 has two functions. Which
> of the two functions did you run from the dialog?

I see, I think I originally ran the wrong one and not the one named "Run". I do get the same error as Tobias, if running "Run" now.

Arch Linux 64-bit
Version: 6.3.0.0.alpha0+
Build ID: 9059457a1a8385cb80b5dd2c797cee77af4222a9
CPU threads: 8; OS: Linux 4.19; UI render: default; VCL: gtk3_kde5; 
Locale: fi-FI (fi_FI.UTF-8); UI-Language: en-US
Calc: threaded
Built on 30 November 2018