Bug 71248 - Excel VBA: ActiveDocument is not tracking currently selected document
Summary: Excel VBA: ActiveDocument is not tracking currently selected document
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: BASIC (show other bugs)
(earliest affected) release
Hardware: Other All
: high major
Assignee: Justin L
Whiteboard: target:4.4.0 target:4.3.3
Keywords: notBibisectable
Depends on:
Blocks: 86667
  Show dependency treegraph
Reported: 2013-11-05 09:44 UTC by Justin L
Modified: 2015-12-17 10:55 UTC (History)
3 users (show)

See Also:
Crash report or crash signature:

Describes how to reproduce and a macro tells you the "active" workbook, sheet, cell. (19.50 KB, application/vnd.ms-excel)
2013-11-05 09:44 UTC, Justin L

Note You need to log in before you can comment on or make changes to this bug.
Description Justin L 2013-11-05 09:44:36 UTC
Created attachment 88673 [details]
Describes how to reproduce and a macro tells you the "active" workbook, sheet, cell.

VBA macros in LibreOffice consider the "last opened" spreadsheet with VBA as the active Document/Sheet/Cell.  Changing to a previously opened XLS file does not switch the active document, and closing the "last opened" spreadsheet causes VBA references to the ActiveWhatever to Basic Runtime Error.

The test documents from Bug 54721 show this.  I'm not reopening that bug based on the comments in it - which say that a new bug should be filed instead. 

In my testing, it appears that only documents that contain VBA code affect this.  Opening simple .xls files without macros doesn't break the "active" document.

Tested on 4.0.4 (Linux) and 4.1.2 (Windows)
Comment 1 Justin L 2013-11-08 07:22:53 UTC
Tested on (Win) with the same problem.   Tested on 3.6.7 (Win) and this problem was not there.  So looks like a 4.0 regression.
Comment 2 Justin L 2013-11-11 14:28:42 UTC
Confirmed that this is still a bug in 4.1.3.

Changing importance to high, since this is a regression from 3.6.  Also, ActiveDocument is the default context for many macro commands, and I couldn't find a programming work-around for the bug.

When you close the last opened .xls file, the now undefined ActiveDocument references cause this VB error:
Basic runtime error
Type: com.sun.star.lang.DisposedException
Message: <blank>
Comment 3 Björn Michaelsen 2014-01-17 09:51:42 UTC Comment hidden (obsolete)
Comment 4 Justin L 2014-05-01 08:02:09 UTC
confirmed that the bug still exists in and
Comment 5 tommy27 2014-05-12 19:14:12 UTC
moving it to mab4.2 list since 4.1.x is EOL
Comment 6 Markus Mohrhard 2014-07-19 11:52:16 UTC
I'm sorry but that is surely not a MAB.

VBA support is only a feature that works to some degree and is only helpful for interoperability for a small group of people.
Comment 7 Björn Michaelsen 2014-08-21 12:21:05 UTC Comment hidden (obsolete)
Comment 8 Justin L 2014-09-01 14:59:42 UTC
I tried to bibisect this bug (downloaded bibisect-43all), however it seems that only up to the x.x.0 builds are included in bibisect.  The bug appears from the earliest 3.5 builds, but was fixed somewhere in 3.6.x. So, in bibisect I could never find a "place" where the bug was fixed, since it is broken again in 4.0.0. 

Whiteboarding this as NotBibisectable.
Comment 9 Justin L 2014-09-02 14:09:02 UTC
In the description, I wrote 'closing the "last opened" spreadsheet causes VBA references to the ActiveWhatever to Basic Runtime Error.'

Somewhere along the line, this VBA error part has been fixed (fix sometime earlier, but testing today with Version: Build ID: 1a91abb451806bd93a08953c6a1afdb88995705e).  I tried to find the fix commit location with bibisect, but it hasn't been fixed in any of the bibisect series (still an error with "git checkout latest" Version: Build ID: c15927f20d4727c3b8de68497b6949e72f9e6e9e).

Now the super-interesting part - EVERYTHING WORKS NOW when the "last" document has been closed.   I opened up 4 copies of the test document.  At first, the macro in all of them points to the LAST opened document (the reason for the bug report).   However, if I close the LAST OPENED document, the ActiveDocument status now PROPERLY switches between the remaining three!!!
Comment 10 Justin L 2014-09-09 13:58:51 UTC
The problematic code is in vbahelper/source/vbahelper/vbahelper.cxx function getCurrentDoc( const OUString& sKey ).  This function does NOT return the current document, but only the last opened document. 

If the last opened document is closed, this function raises an exception [referencing xModel->getURL()].  When the exception is raised, the function "getCurrentExcelDoc" falls back to getThisExcelDoc() which works properly.

So, a quick bandaid would be to always call "getThisExcelDoc" instead of "getCurrentDoc" since the "current" doc isn't correct anyway.  THISdoc will USUALLY be the activeDoc (but not necessarily).

I'll look a bit more into what getCurrentDoc() does and see if I can figure out how to properly fix it, but at the moment I don't understand what it is trying to do.
Comment 11 Justin L 2014-09-29 17:56:18 UTC
(In reply to comment #10)
This is the code line that causes the exception:

            SAL_INFO("vbahelper", "Have model points to url " << xModel->getURL());
Comment 12 Justin L 2014-09-29 22:54:42 UTC
s_aRegisteredVBAConstants in sfx2/source/doc/objxtor.cxx is never SET.  A name for an XInterface is searched for, but no XInterfaces are ever entered into this map.  If each Excel document is registered in here as "ThisExcelDoc", then SetCurrentComponent()/lclGetVBAGlobalConstName() will set the GlobalUNOConstant for ThisExcelDoc.

Alternatively, if the document had a 'ThisVBADocObj' property assigned as 'ThisExcelDoc', lclGetVBAGlobalConstName() might work.  I don't see anywhere where 'ThisVBADocObj'/UNO_NAME_VBA_DOCOBJ is assigned any value in the code.

So basically, nothing about the lclGetVBAGlobalConstName seems to function.  It is only used in objxtor.cxx, so all of this code can be removed I think.
Comment 13 Justin L 2014-09-30 19:06:37 UTC
bug (re)introduced around Nov 28, 2012 by a patch marked as date 25-Mar-2011  by Daniel Rentz @ oracle.com, comment "calcvba: #164410# improve VBA compatibility implementation in various areas:".   At this time SC_UNO_VBADOCOBJ/"ThisVBADocObj" was changed to  SC_UNO_VBAGLOBNAME/ "VBAGlobalConstantName".    This appears to be rebasing the code on OOo.

Simply replacing 'ThisVBADocObj' in objxtor.cxx to produce this line:
xProps->getPropertyValue("VBAGlobalConstantName") >>= aConstName;
will fix the problem.  That matches the OOo code.

"ThisVBADocObj" is also referenced in LO Writer (but not in OOo Writer) (sw/inc/unoprnms.hxx defines UNO_NAME_VBA_DOCOBJ as "ThisVBADocObj".)  It doesn't appear that anything uses this code, so it should be safe to "tinker" with it. UNO_NAME_VBA_DOCOBJ introduced into Word around October 2010 by Noel Power, comment "initial commit for vba blob ( not including container_control stuff )" 

The WORD portion probably needs the same fix as happened in Bug 54721, changing sw/source/core/unocore/unomap.cxx:
{ OUString(UNO_NAME_VBA_DOCOBJ), WID_DOC_VBA_DOCOBJ,  cppu::UnoType< cppu::UnoSequenceType<css::beans::PropertyValue> >::get(), PropertyAttribute::READONLY, 0},
{OUString(UNO_NAME_VBA_DOCOBJ), WID_UNO_NAME_VBA_DOCOBJ, cppu::UnoType<OUString>::get(),                  beans::PropertyAttribute::READONLY, 0},

and fixing UNO_NAME_VBA_DOCOBJ in sw/source/core/unocore/unomap.cxx and sw/source/uibase/uno/unotxdoc.cxx (or sw/source/core/uibase/uno/).
Comment 14 Justin L 2014-09-30 19:19:35 UTC
submitted patch for review:  https://gerrit.libreoffice.org/#/c/11725/
Comment 15 Justin L 2014-10-07 14:52:56 UTC
Justin's patch was accepted and pushed to "master":

patch also cherrypicked for LO 4.3, pending review.
patch failed to cherry-pick for LO 4.2.7 rc1.

Patch submitted for ThisWordDoc, pending review:
Comment 16 Justin L 2014-10-07 19:32:20 UTC
correction:  ThisWordDoc patch is at https://gerrit.libreoffice.org/11842/
Comment 17 Justin L 2014-11-03 08:57:30 UTC
ThisWordDoc patch has been accepted into the 4.4 development tree.

The main patch was accepted into the 4.3 stable tree.  It will be included in 4.3.4 due Dec 21, 2015.

Thanks to Noel Power and Samuel Mehrbrodt for helping and encouraging me in tracking down the fix and helping to submit and move the patch through the development process.
Comment 18 Robinson Tryon (qubit) 2015-12-17 10:55:22 UTC Comment hidden (obsolete)