sc: fix VBA Copy-Paste using same and separate document

This fixes 2 issues with VBA copy-paste:
- VBA command Range(..).Copy issue where the range wasn't selected
when copying and the copied cells were from the previous selection.
The Copy command now does the same selection as the Cut command.

- VBA PasteSpecial issue where the wrong view was used to get the
clip document.

- VBA Workbooks.Add issue where the new workbook wasn't activated
after it was created, which causes an issue when running tests, but
not when running in LO application. The Add command does now the same
as the Workbooks.Open command.

All the issues are supported by new test cases.

Change-Id: I36ec45c01f18f7f76e4f95a25a28402a6ee0e2e2
Reviewed-on: https://gerrit.libreoffice.org/c/core/+/128720
Tested-by: Jenkins
Reviewed-by: Tomaž Vajngerl <quikee@gmail.com>

7 files changed, 176 insertions, 16 deletions

diff --git a/sc/CppunitTest_sc_macros_test.mk b/sc/CppunitTest_sc_macros_test.mk
index a6dd427cde67..f5dabb960015 100644
--- a/sc/CppunitTest_sc_macros_test.mk
+++ b/sc/CppunitTest_sc_macros_test.mk
@@ -19,6 +19,7 @@ $(eval $(call gb_CppunitTest_use_common_precompiled_header,sc_macros_test))
 
 $(eval $(call gb_CppunitTest_add_exception_objects,sc_macros_test, \
     sc/qa/extras/macros-test \
+    sc/qa/extras/vba-macro-test \
 ))
 
 $(eval $(call gb_CppunitTest_use_libraries,sc_macros_test, \
diff --git a/sc/qa/extras/testdocuments/MultiDocumentCopyPaste.xlsm b/sc/qa/extras/testdocuments/MultiDocumentCopyPaste.xlsm
new file mode 100644
index 000000000000..4f24cf4e663f
--- /dev/null
+++ b/sc/qa/extras/testdocuments/MultiDocumentCopyPaste.xlsm
diff --git a/sc/qa/extras/testdocuments/SimpleCopyPaste.xlsm b/sc/qa/extras/testdocuments/SimpleCopyPaste.xlsm
new file mode 100644
index 000000000000..6c71c75b345e
--- /dev/null
+++ b/sc/qa/extras/testdocuments/SimpleCopyPaste.xlsm
diff --git a/sc/qa/extras/vba-macro-test.cxx b/sc/qa/extras/vba-macro-test.cxx
new file mode 100644
index 000000000000..36aa84badd2d
--- /dev/null
+++ b/sc/qa/extras/vba-macro-test.cxx
@@ -0,0 +1,148 @@
+/* -*- Mode: C++; tab-width: 4; indent-tabs-mode: nil; c-basic-offset: 4 -*- */
+/*
+ * This file is part of the LibreOffice project.
+ *
+ * This Source Code Form is subject to the terms of the Mozilla Public
+ * License, v. 2.0. If a copy of the MPL was not distributed with this
+ * file, You can obtain one at http://mozilla.org/MPL/2.0/.
+ */
+
+#include <sal/config.h>
+#include <test/unoapi_test.hxx>
+#include <osl/file.hxx>
+#include <sal/log.hxx>
+#include <vcl/svapp.hxx>
+
+#include <docsh.hxx>
+#include <document.hxx>
+#include <attrib.hxx>
+#include <scitems.hxx>
+
+#include <com/sun/star/sheet/XSpreadsheet.hpp>
+
+using namespace css;
+
+class VBAMacroTest : public UnoApiTest
+{
+public:
+    uno::Reference<lang::XComponent> mxComponent;
+
+    VBAMacroTest()
+        : UnoApiTest("/sc/qa/extras/testdocuments")
+    {
+    }
+
+    virtual void tearDown() override
+    {
+        if (mxComponent.is())
+        {
+            mxComponent->dispose();
+            mxComponent.set(nullptr);
+        }
+
+        test::BootstrapFixture::tearDown();
+    }
+
+    void testSimpleCopyAndPaste();
+    void testMultiDocumentCopyAndPaste();
+
+    CPPUNIT_TEST_SUITE(VBAMacroTest);
+    CPPUNIT_TEST(testSimpleCopyAndPaste);
+    CPPUNIT_TEST(testMultiDocumentCopyAndPaste);
+    CPPUNIT_TEST_SUITE_END();
+};
+
+void VBAMacroTest::testSimpleCopyAndPaste()
+{
+    // Copy-paste values in the same sheet
+
+    // Range(Cells(4, 3), Cells(6, 3)).Copy
+    // Cells(4, 2).Activate
+    // ActiveCell.PasteSpecial xlValues
+
+    OUString aFileName;
+    createFileURL(u"SimpleCopyPaste.xlsm", aFileName);
+    mxComponent = loadFromDesktop(aFileName, "com.sun.star.sheet.SpreadsheetDocument");
+
+    uno::Any aRet;
+    uno::Sequence<sal_Int16> aOutParamIndex;
+    uno::Sequence<uno::Any> aOutParam;
+    uno::Sequence<uno::Any> aParams;
+
+    SfxObjectShell* pFoundShell = SfxObjectShell::GetShellFromComponent(mxComponent);
+
+    CPPUNIT_ASSERT_MESSAGE("Failed to access document shell", pFoundShell);
+    ScDocShell* pDocSh = static_cast<ScDocShell*>(pFoundShell);
+    ScDocument& rDoc = pDocSh->GetDocument();
+
+    // Check state
+    CPPUNIT_ASSERT_EQUAL(10.0, rDoc.GetValue(ScAddress(2, 3, 0)));
+    CPPUNIT_ASSERT_EQUAL(20.0, rDoc.GetValue(ScAddress(2, 4, 0)));
+    CPPUNIT_ASSERT_EQUAL(30.0, rDoc.GetValue(ScAddress(2, 5, 0)));
+
+    CPPUNIT_ASSERT_EQUAL(0.0, rDoc.GetValue(ScAddress(1, 3, 0)));
+    CPPUNIT_ASSERT_EQUAL(0.0, rDoc.GetValue(ScAddress(1, 4, 0)));
+    CPPUNIT_ASSERT_EQUAL(0.0, rDoc.GetValue(ScAddress(1, 5, 0)));
+
+    SfxObjectShell::CallXScript(
+        mxComponent, "vnd.sun.Star.script:VBAProject.Module1.test?language=Basic&location=document",
+        aParams, aRet, aOutParamIndex, aOutParam);
+
+    // Copy from C4-C6
+    CPPUNIT_ASSERT_EQUAL(10.0, rDoc.GetValue(ScAddress(2, 3, 0)));
+    CPPUNIT_ASSERT_EQUAL(20.0, rDoc.GetValue(ScAddress(2, 4, 0)));
+    CPPUNIT_ASSERT_EQUAL(30.0, rDoc.GetValue(ScAddress(2, 5, 0)));
+
+    // Paste to B4-B6
+    CPPUNIT_ASSERT_EQUAL(10.0, rDoc.GetValue(ScAddress(1, 3, 0)));
+    CPPUNIT_ASSERT_EQUAL(20.0, rDoc.GetValue(ScAddress(1, 4, 0)));
+    CPPUNIT_ASSERT_EQUAL(30.0, rDoc.GetValue(ScAddress(1, 5, 0)));
+}
+
+void VBAMacroTest::testMultiDocumentCopyAndPaste()
+{
+    // Creates a new workbook (document) and copy-pastes values
+    // between the documents.
+
+    // Set CurrentWB = ActiveWorkbook
+    // Workbooks.Add
+    // Set NewWB = ActiveWorkbook
+    // Cells(3, 2).Value = 200
+    // Cells(4, 2).Value = 100
+    // Range(Cells(3, 2), Cells(4, 2)).Copy
+    // CurrentWB.Activate
+    // Cells(2, 2).Activate
+    // ActiveCell.PasteSpecial xlValues
+    // ...
+
+    OUString aFileName;
+    createFileURL(u"MultiDocumentCopyPaste.xlsm", aFileName);
+    mxComponent = loadFromDesktop(aFileName, "com.sun.star.sheet.SpreadsheetDocument");
+
+    uno::Any aRet;
+    uno::Sequence<sal_Int16> aOutParamIndex;
+    uno::Sequence<uno::Any> aOutParam;
+    uno::Sequence<uno::Any> aParams;
+
+    SfxObjectShell* pFoundShell = SfxObjectShell::GetShellFromComponent(mxComponent);
+
+    CPPUNIT_ASSERT_MESSAGE("Failed to access document shell", pFoundShell);
+    ScDocShell* pDocSh = static_cast<ScDocShell*>(pFoundShell);
+    ScDocument& rDoc = pDocSh->GetDocument();
+
+    CPPUNIT_ASSERT_EQUAL(0.0, rDoc.GetValue(ScAddress(1, 1, 0)));
+    CPPUNIT_ASSERT_EQUAL(0.0, rDoc.GetValue(ScAddress(1, 2, 0)));
+    CPPUNIT_ASSERT_EQUAL(0.0, rDoc.GetValue(ScAddress(1, 3, 0)));
+
+    SfxObjectShell::CallXScript(
+        mxComponent, "vnd.sun.Star.script:VBAProject.Module1.test?language=Basic&location=document",
+        aParams, aRet, aOutParamIndex, aOutParam);
+
+    CPPUNIT_ASSERT_EQUAL(200.0, rDoc.GetValue(ScAddress(1, 1, 0)));
+    CPPUNIT_ASSERT_EQUAL(100.0, rDoc.GetValue(ScAddress(1, 2, 0)));
+    CPPUNIT_ASSERT_EQUAL(0.0, rDoc.GetValue(ScAddress(1, 3, 0)));
+}
+
+CPPUNIT_TEST_SUITE_REGISTRATION(VBAMacroTest);
+
+/* vim:set shiftwidth=4 softtabstop=4 expandtab: */
diff --git a/sc/source/ui/vba/excelvbahelper.cxx b/sc/source/ui/vba/excelvbahelper.cxx
index d337a0f6836e..37d3a5fcf1de 100644
--- a/sc/source/ui/vba/excelvbahelper.cxx
+++ b/sc/source/ui/vba/excelvbahelper.cxx
@@ -210,25 +210,28 @@ void implnPasteSpecial( const uno::Reference< frame::XModel>& xModel, InsertDele
 {
     PasteCellsWarningReseter resetWarningBox;
 
-    ScTabViewShell* pTabViewShell = getBestViewShell( xModel );
-    ScDocShell* pDocShell = getDocShell( xModel );
-    if ( !(pTabViewShell && pDocShell) )
+    ScTabViewShell* pTabViewShell = getBestViewShell(xModel);
+    if (!pTabViewShell)
+        return;
+
+    ScDocShell* pDocShell = getDocShell(xModel);
+    if (!pDocShell)
         return;
 
     ScViewData& rView = pTabViewShell->GetViewData();
     vcl::Window* pWin = rView.GetActiveWin();
-    if (pWin)
+    if (!pWin)
+        return;
+
+    const ScTransferObj* pOwnClip = ScTransferObj::GetOwnClipboard(ScTabViewShell::GetClipData(pWin));
+    if (pOwnClip)
     {
-        const ScTransferObj* pOwnClip = ScTransferObj::GetOwnClipboard(pDocShell->GetClipData());
-        ScDocument* pDoc = nullptr;
-        if ( pOwnClip )
-            pDoc = pOwnClip->GetDocument();
-        pTabViewShell->PasteFromClip( nFlags, pDoc,
+        pTabViewShell->PasteFromClip(nFlags, pOwnClip->GetDocument(),
             nFunction, bSkipEmpty, bTranspose, false,
-            INS_NONE, InsertDeleteFlags::NONE, true );
+            INS_NONE, InsertDeleteFlags::NONE, true);
+
         pTabViewShell->CellContentChanged();
     }
-
 }
 
 ScDocShell*
diff --git a/sc/source/ui/vba/vbarange.cxx b/sc/source/ui/vba/vbarange.cxx
index d264682a1f7a..d15420bb698c 100644
--- a/sc/source/ui/vba/vbarange.cxx
+++ b/sc/source/ui/vba/vbarange.cxx
@@ -2535,7 +2535,9 @@ ScVbaRange::Copy(const ::uno::Any& Destination)
     }
     else
     {
-        excel::implnCopy( getUnoModel() );
+        uno::Reference<frame::XModel> xModel = getModelFromRange(mxRange);
+        Select();
+        excel::implnCopy(getUnoModel());
     }
 }
 
@@ -2893,7 +2895,8 @@ ScVbaRange::PasteSpecial( const uno::Any& Paste, const uno::Any& Operation, cons
 
     InsertDeleteFlags nFlags = getPasteFlags(nPaste);
     ScPasteFunc nFormulaBits = getPasteFormulaBits(nOperation);
-    excel::implnPasteSpecial(pShell->GetModel(), nFlags,nFormulaBits,bSkipBlanks,bTranspose);
+
+    excel::implnPasteSpecial(xModel, nFlags, nFormulaBits, bSkipBlanks, bTranspose);
 }
 
 uno::Reference< excel::XRange >
diff --git a/sc/source/ui/vba/vbaworkbooks.cxx b/sc/source/ui/vba/vbaworkbooks.cxx
index bd52292cfa5a..e3b608b33942 100644
--- a/sc/source/ui/vba/vbaworkbooks.cxx
+++ b/sc/source/ui/vba/vbaworkbooks.cxx
@@ -144,9 +144,14 @@ ScVbaWorkbooks::Add( const uno::Any& Template )
 
     // need to set up the document modules ( and vba mode ) here
     excel::setUpDocumentModules( xSpreadDoc );
-    if( xSpreadDoc.is() )
-        return getWorkbook( mxContext, xSpreadDoc, mxParent );
-    return uno::Any();
+    if (!xSpreadDoc.is())
+        return uno::Any();
+
+    uno::Any aRet = getWorkbook( mxContext, xSpreadDoc, mxParent );
+    uno::Reference< excel::XWorkbook > xWBook( aRet, uno::UNO_QUERY );
+    if (xWBook.is())
+        xWBook->Activate();
+    return aRet;
 }
 
 void SAL_CALL