?  search:
summary | log | graphiclog1 | graphiclog2 | commit | commitdiff | tree | refs | edit | fork
blame | history | raw | HEAD
Fix default value of Emscripten --with-main-module
[LibreOffice.git] / sc / qa / extras / vba-macro-test.cxx
blob4dc1f0fe93c469fbb022c3148a475b963fda5320
1 /* -*- Mode: C++; tab-width: 4; indent-tabs-mode: nil; c-basic-offset: 4 -*- */
2 /*
3 * This file is part of the LibreOffice project.
4 *
5 * This Source Code Form is subject to the terms of the Mozilla Public
6 * License, v. 2.0. If a copy of the MPL was not distributed with this
7 * file, You can obtain one at http://mozilla.org/MPL/2.0/.
8 */
9
10 #include <sal/config.h>
11 #include <test/unoapi_test.hxx>
12 #include <osl/file.hxx>
13 #include <sal/log.hxx>
14 #include <vcl/filter/pdfdocument.hxx>
15 #include <vcl/scheduler.hxx>
16 #include <viewdata.hxx>
17 #include <tabvwsh.hxx>
18
19 #include <docsh.hxx>
20 #include <document.hxx>
21 #include <attrib.hxx>
22 #include <scitems.hxx>
23 #include <sortparam.hxx>
24
25 #include <com/sun/star/frame/DispatchResultEvent.hpp>
26 #include <com/sun/star/frame/DispatchResultState.hpp>
27 #include <com/sun/star/sheet/XSpreadsheet.hpp>
28 #include <com/sun/star/sheet/XPrintAreas.hpp>
29 #include <com/sun/star/table/CellRangeAddress.hpp>
30 #include <com/sun/star/ui/XUIConfigurationManagerSupplier.hpp>
31 #include <com/sun/star/ui/XUIConfigurationManager.hpp>
32 #include <com/sun/star/awt/KeyModifier.hpp>
33
34 #include <ooo/vba/excel/XlSpecialCellsValue.hpp>
35
36 #include <comphelper/propertysequence.hxx>
37
38 using namespace css;
39 using namespace ooo::vba;
40
41 class VBAMacroTest : public UnoApiTest
42 {
43 public:
44 VBAMacroTest()
45 : UnoApiTest(u"/sc/qa/extras/testdocuments"_ustr)
46 {
47 }
48 };
49
50 CPPUNIT_TEST_FIXTURE(VBAMacroTest, testSimpleCopyAndPaste)
51 {
52 // Copy-paste values in the same sheet
53
54 // Range(Cells(4, 3), Cells(6, 3)).Copy
55 // Cells(4, 2).Activate
56 // ActiveCell.PasteSpecial xlValues
57 loadFromFile(u"SimpleCopyPaste.xlsm");
58
59 SfxObjectShell* pFoundShell = SfxObjectShell::GetShellFromComponent(mxComponent);
60
61 CPPUNIT_ASSERT_MESSAGE("Failed to access document shell", pFoundShell);
62 ScDocShell* pDocSh = static_cast<ScDocShell*>(pFoundShell);
63 ScDocument& rDoc = pDocSh->GetDocument();
64
65 // Check state
66 CPPUNIT_ASSERT_EQUAL(10.0, rDoc.GetValue(ScAddress(2, 3, 0)));
67 CPPUNIT_ASSERT_EQUAL(20.0, rDoc.GetValue(ScAddress(2, 4, 0)));
68 CPPUNIT_ASSERT_EQUAL(30.0, rDoc.GetValue(ScAddress(2, 5, 0)));
69
70 CPPUNIT_ASSERT_EQUAL(0.0, rDoc.GetValue(ScAddress(1, 3, 0)));
71 CPPUNIT_ASSERT_EQUAL(0.0, rDoc.GetValue(ScAddress(1, 4, 0)));
72 CPPUNIT_ASSERT_EQUAL(0.0, rDoc.GetValue(ScAddress(1, 5, 0)));
73
74 executeMacro(
75 u"vnd.sun.Star.script:VBAProject.Module1.test?language=Basic&location=document"_ustr);
76
77 // Copy from C4-C6
78 CPPUNIT_ASSERT_EQUAL(10.0, rDoc.GetValue(ScAddress(2, 3, 0)));
79 CPPUNIT_ASSERT_EQUAL(20.0, rDoc.GetValue(ScAddress(2, 4, 0)));
80 CPPUNIT_ASSERT_EQUAL(30.0, rDoc.GetValue(ScAddress(2, 5, 0)));
81
82 // Paste to B4-B6
83 CPPUNIT_ASSERT_EQUAL(10.0, rDoc.GetValue(ScAddress(1, 3, 0)));
84 CPPUNIT_ASSERT_EQUAL(20.0, rDoc.GetValue(ScAddress(1, 4, 0)));
85 CPPUNIT_ASSERT_EQUAL(30.0, rDoc.GetValue(ScAddress(1, 5, 0)));
86 }
87
88 CPPUNIT_TEST_FIXTURE(VBAMacroTest, testMultiDocumentCopyAndPaste)
89 {
90 // Creates a new workbook (document) and copy-pastes values
91 // between the documents.
92
93 // Set CurrentWB = ActiveWorkbook
94 // Workbooks.Add
95 // Set NewWB = ActiveWorkbook
96 // Cells(3, 2).Value = 200
97 // Cells(4, 2).Value = 100
98 // Range(Cells(3, 2), Cells(4, 2)).Copy
99 // CurrentWB.Activate
100 // Cells(2, 2).Activate
101 // ActiveCell.PasteSpecial xlValues
102 // ...
103 loadFromFile(u"MultiDocumentCopyPaste.xlsm");
104
105 SfxObjectShell* pFoundShell = SfxObjectShell::GetShellFromComponent(mxComponent);
106
107 CPPUNIT_ASSERT_MESSAGE("Failed to access document shell", pFoundShell);
108 ScDocShell* pDocSh = static_cast<ScDocShell*>(pFoundShell);
109 ScDocument& rDoc = pDocSh->GetDocument();
110
111 CPPUNIT_ASSERT_EQUAL(0.0, rDoc.GetValue(ScAddress(1, 1, 0)));
112 CPPUNIT_ASSERT_EQUAL(0.0, rDoc.GetValue(ScAddress(1, 2, 0)));
113 CPPUNIT_ASSERT_EQUAL(0.0, rDoc.GetValue(ScAddress(1, 3, 0)));
114
115 executeMacro(
116 u"vnd.sun.Star.script:VBAProject.Module1.test?language=Basic&location=document"_ustr);
117
118 CPPUNIT_ASSERT_EQUAL(200.0, rDoc.GetValue(ScAddress(1, 1, 0)));
119 CPPUNIT_ASSERT_EQUAL(100.0, rDoc.GetValue(ScAddress(1, 2, 0)));
120 CPPUNIT_ASSERT_EQUAL(0.0, rDoc.GetValue(ScAddress(1, 3, 0)));
121 }
122
123 CPPUNIT_TEST_FIXTURE(VBAMacroTest, testSheetAndColumnSelectAndHide)
124 {
125 loadFromFile(u"SheetAndColumnSelectAndHide.xlsm");
126
127 SfxObjectShell* pFoundShell = SfxObjectShell::GetShellFromComponent(mxComponent);
128
129 CPPUNIT_ASSERT_MESSAGE("Failed to access document shell", pFoundShell);
130 ScDocShell* pDocSh = static_cast<ScDocShell*>(pFoundShell);
131 ScDocument& rDoc = pDocSh->GetDocument();
132
133 ScTabViewShell* pView = pDocSh->GetBestViewShell(false);
134 CPPUNIT_ASSERT(pView != nullptr);
135 auto const& rViewData = pView->GetViewData();
136
137 CPPUNIT_ASSERT(!rDoc.ColHidden(0, 1));
138 CPPUNIT_ASSERT(!rDoc.ColHidden(1, 1));
139 CPPUNIT_ASSERT(!rDoc.ColHidden(2, 1));
140 CPPUNIT_ASSERT(!rDoc.ColHidden(3, 1));
141 CPPUNIT_ASSERT(!rDoc.ColHidden(4, 1));
142
143 CPPUNIT_ASSERT(!rDoc.ColHidden(0, 2));
144 CPPUNIT_ASSERT(!rDoc.ColHidden(1, 2));
145 CPPUNIT_ASSERT(!rDoc.ColHidden(2, 2));
146 CPPUNIT_ASSERT(!rDoc.ColHidden(3, 2));
147 CPPUNIT_ASSERT(!rDoc.ColHidden(4, 2));
148
149 executeMacro(
150 u"vnd.sun.Star.script:VBAProject.ThisWorkbook.testHide?language=Basic&location=document"_ustr);
151
152 CPPUNIT_ASSERT(!rDoc.ColHidden(0, 1));
153 CPPUNIT_ASSERT(rDoc.ColHidden(1, 1));
154 CPPUNIT_ASSERT(rDoc.ColHidden(2, 1));
155 CPPUNIT_ASSERT(!rDoc.ColHidden(3, 1));
156 CPPUNIT_ASSERT(!rDoc.ColHidden(4, 1));
157
158 CPPUNIT_ASSERT(!rDoc.ColHidden(0, 2));
159 CPPUNIT_ASSERT(!rDoc.ColHidden(1, 2));
160 CPPUNIT_ASSERT(rDoc.ColHidden(2, 2));
161 CPPUNIT_ASSERT(rDoc.ColHidden(3, 2));
162 CPPUNIT_ASSERT(rDoc.ColHidden(4, 2));
163
164 CPPUNIT_ASSERT_EQUAL(SCTAB(0), rViewData.GetTabNo());
165
166 executeMacro(
167 u"vnd.sun.Star.script:VBAProject.ThisWorkbook.testUnhide?language=Basic&location=document"_ustr);
168
169 CPPUNIT_ASSERT(!rDoc.ColHidden(0, 1));
170 CPPUNIT_ASSERT(!rDoc.ColHidden(1, 1));
171 CPPUNIT_ASSERT(!rDoc.ColHidden(2, 1));
172 CPPUNIT_ASSERT(!rDoc.ColHidden(3, 1));
173 CPPUNIT_ASSERT(!rDoc.ColHidden(4, 1));
174
175 CPPUNIT_ASSERT(!rDoc.ColHidden(0, 2));
176 CPPUNIT_ASSERT(!rDoc.ColHidden(1, 2));
177 CPPUNIT_ASSERT(!rDoc.ColHidden(2, 2));
178 CPPUNIT_ASSERT(!rDoc.ColHidden(3, 2));
179 CPPUNIT_ASSERT(!rDoc.ColHidden(4, 2));
180
181 CPPUNIT_ASSERT_EQUAL(SCTAB(0), rViewData.GetTabNo());
182 }
183
184 CPPUNIT_TEST_FIXTURE(VBAMacroTest, testPrintArea)
185 {
186 // Sets the print area to A1:B5
187 // ActiveSheet.PageSetup.PrintArea = "$A$1:$B$5"
188 loadFromFile(u"VariousTestMacros.xlsm");
189
190 uno::Reference<sheet::XSpreadsheetDocument> xDoc(mxComponent, uno::UNO_QUERY_THROW);
191 uno::Reference<container::XIndexAccess> xIndex(xDoc->getSheets(), uno::UNO_QUERY_THROW);
192 uno::Reference<sheet::XSpreadsheet> xSheet(xIndex->getByIndex(0), uno::UNO_QUERY_THROW);
193 uno::Reference<sheet::XPrintAreas> xPrintAreas(xSheet, uno::UNO_QUERY_THROW);
194
195 {
196 const uno::Sequence<table::CellRangeAddress> aSequence = xPrintAreas->getPrintAreas();
197 CPPUNIT_ASSERT_EQUAL(false, aSequence.hasElements());
198 }
199
200 executeMacro(u"vnd.sun.Star.script:VBAProject.ThisWorkbook.testPrintArea?language=Basic&"
201 "location=document"_ustr);
202
203 {
204 const uno::Sequence<table::CellRangeAddress> aSequence = xPrintAreas->getPrintAreas();
205 CPPUNIT_ASSERT_EQUAL(true, aSequence.hasElements());
206 }
207 }
208
209 CPPUNIT_TEST_FIXTURE(VBAMacroTest, testSelectAllChaged)
210 {
211 // Columns("A:A").Select
212 // Range(Selection, Selection.End(xlToRight)).Select
213 loadFromFile(u"VariousTestMacros.xlsm");
214
215 SfxObjectShell* pFoundShell = SfxObjectShell::GetShellFromComponent(mxComponent);
216 CPPUNIT_ASSERT_MESSAGE("Failed to access document shell", pFoundShell);
217
218 ScDocShell* pDocSh = static_cast<ScDocShell*>(pFoundShell);
219 ScTabViewShell* pView = pDocSh->GetBestViewShell(false);
220 CPPUNIT_ASSERT(pView != nullptr);
221 auto const& pViewData = pView->GetViewData();
222 CPPUNIT_ASSERT_EQUAL(ScRange(), pViewData.GetMarkData().GetMarkArea());
223
224 executeMacro(u"vnd.sun.Star.script:VBAProject.ThisWorkbook.testSelectAll?language=Basic&"
225 "location=document"_ustr);
226
227 // A1:E1048576
228 CPPUNIT_ASSERT_EQUAL(ScRange(0, 0, 0, 4, MAXROW, 0), pViewData.GetMarkData().GetMarkArea());
229 }
230
231 CPPUNIT_TEST_FIXTURE(VBAMacroTest, testRangeSelect)
232 {
233 // Range("B2").Select
234 // Range(Selection, Selection.End(xlToRight)).Select
235 loadFromFile(u"VariousTestMacros.xlsm");
236
237 SfxObjectShell* pFoundShell = SfxObjectShell::GetShellFromComponent(mxComponent);
238 CPPUNIT_ASSERT_MESSAGE("Failed to access document shell", pFoundShell);
239
240 ScDocShell* pDocSh = static_cast<ScDocShell*>(pFoundShell);
241 ScTabViewShell* pView = pDocSh->GetBestViewShell(false);
242 CPPUNIT_ASSERT(pView != nullptr);
243 auto const& pViewData = pView->GetViewData();
244 CPPUNIT_ASSERT_EQUAL(ScRange(), pViewData.GetMarkData().GetMarkArea());
245
246 executeMacro(u"vnd.sun.Star.script:VBAProject.ThisWorkbook.testRangeSelect?language=Basic&"
247 "location=document"_ustr);
248
249 // B2:E5
250 CPPUNIT_ASSERT_EQUAL(ScRange(1, 1, 0, 4, 1, 0), pViewData.GetMarkData().GetMarkArea());
251 }
252
253 CPPUNIT_TEST_FIXTURE(VBAMacroTest, testWindowState)
254 {
255 // Application.WindowState = xlMinimized
256 // Application.WindowState = xlMaximized
257 // Application.WindowState = xlNormal
258 loadFromFile(u"VariousTestMacros.xlsm");
259
260 executeMacro(u"vnd.sun.Star.script:VBAProject.ThisWorkbook.testWindowState?language=Basic&"
261 "location=document"_ustr);
262 }
263
264 CPPUNIT_TEST_FIXTURE(VBAMacroTest, testScroll)
265 {
266 // ActiveWindow.ScrollColumn = 30
267 // ActiveWindow.ScrollRow = 100
268
269 loadFromFile(u"VariousTestMacros.xlsm");
270
271 SfxObjectShell* pFoundShell = SfxObjectShell::GetShellFromComponent(mxComponent);
272 CPPUNIT_ASSERT_MESSAGE("Failed to access document shell", pFoundShell);
273
274 ScDocShell* pDocSh = static_cast<ScDocShell*>(pFoundShell);
275 ScTabViewShell* pView = pDocSh->GetBestViewShell(false);
276 CPPUNIT_ASSERT(pView != nullptr);
277 auto const& rViewData = pView->GetViewData();
278
279 CPPUNIT_ASSERT_EQUAL(ScSplitPos::SC_SPLIT_BOTTOMLEFT, rViewData.GetActivePart());
280 CPPUNIT_ASSERT_EQUAL(SCCOL(0), rViewData.GetPosX(ScHSplitPos::SC_SPLIT_LEFT));
281 CPPUNIT_ASSERT_EQUAL(SCROW(0), rViewData.GetPosY(ScVSplitPos::SC_SPLIT_BOTTOM));
282
283 executeMacro(
284 u"vnd.sun.Star.script:VBAProject.ThisWorkbook.testScroll?language=Basic&location=document"_ustr);
285
286 CPPUNIT_ASSERT_EQUAL(ScSplitPos::SC_SPLIT_BOTTOMLEFT, rViewData.GetActivePart());
287 CPPUNIT_ASSERT_EQUAL(SCCOL(29), rViewData.GetPosX(ScHSplitPos::SC_SPLIT_LEFT));
288 CPPUNIT_ASSERT_EQUAL(SCROW(99), rViewData.GetPosY(ScVSplitPos::SC_SPLIT_BOTTOM));
289 }
290
291 CPPUNIT_TEST_FIXTURE(VBAMacroTest, testMacroKeyBinding)
292 {
293 // key_U() -> CTRL+U
294 // key_T() -> CTRL+T
295 loadFromFile(u"KeyShortcut.xlsm");
296
297 uno::Reference<frame::XModel> xModel(mxComponent, uno::UNO_QUERY);
298 CPPUNIT_ASSERT(xModel.is());
299
300 uno::Reference<ui::XUIConfigurationManagerSupplier> xConfigSupplier(xModel, uno::UNO_QUERY);
301 CPPUNIT_ASSERT(xConfigSupplier.is());
302 uno::Reference<ui::XUIConfigurationManager> xConfigManager
303 = xConfigSupplier->getUIConfigurationManager();
304 uno::Reference<ui::XAcceleratorConfiguration> xAccelerator
305 = xConfigManager->getShortCutManager();
306
307 awt::KeyEvent aCtrlU;
308 aCtrlU.KeyCode = css::awt::Key::U;
309 aCtrlU.Modifiers = css::awt::KeyModifier::MOD1;
310
311 CPPUNIT_ASSERT_EQUAL(
312 u"vnd.sun.star.script:VBAProject.ThisWorkbook.key_U?language=Basic&location=document"_ustr,
313 xAccelerator->getCommandByKeyEvent(aCtrlU));
314
315 awt::KeyEvent aCtrlT;
316 aCtrlT.KeyCode = css::awt::Key::T;
317 aCtrlT.Modifiers = css::awt::KeyModifier::MOD1;
318
319 CPPUNIT_ASSERT_EQUAL(
320 u"vnd.sun.star.script:VBAProject.ThisWorkbook.key_T?language=Basic&location=document"_ustr,
321 xAccelerator->getCommandByKeyEvent(aCtrlT));
322 }
323
324 CPPUNIT_TEST_FIXTURE(VBAMacroTest, testVba)
325 {
326 // FIXME: the DPI check should be removed when either (1) the test is fixed to work with
327 // non-default DPI; or (2) unit tests on Windows are made to use svp VCL plugin.
328 if (!IsDefaultDPI())
329 return;
330 TestMacroInfo testInfo[] = {
331 { u"TestAddress.xls"_ustr,
332 u"vnd.sun.Star.script:VBAProject.testMacros.test?language=Basic&location=document"_ustr },
333 {
334 u"vba.xls"_ustr,
335 u"vnd.sun.Star.script:VBAProject.Modul1.Modul1?language=Basic&location=document"_ustr,
336 },
337 { u"MiscRangeTests.xls"_ustr,
338 u"vnd.sun.Star.script:VBAProject.testMacros.test?language=Basic&location=document"_ustr },
339 { u"bytearraystring.xls"_ustr,
340 u"vnd.sun.Star.script:VBAProject.testMacro.test?language=Basic&location=document"_ustr },
341 { u"AutoFilter.xls"_ustr,
342 u"vnd.sun.Star.script:VBAProject.testMacros.test?language=Basic&location=document"_ustr },
343 { u"CalcFont.xls"_ustr,
344 u"vnd.sun.Star.script:VBAProject.testMacros.test?language=Basic&location=document"_ustr },
345 { u"TestIntersection.xls"_ustr,
346 u"vnd.sun.Star.script:VBAProject.testMacros.test?language=Basic&location=document"_ustr },
347 { u"TestUnion.xls"_ustr,
348 u"vnd.sun.Star.script:VBAProject.testMacros.test?language=Basic&location=document"_ustr },
349 { u"range-4.xls"_ustr,
350 u"vnd.sun.Star.script:VBAProject.testMacros.test?language=Basic&location=document"_ustr },
351 // FIXME: sometimes it fails on Windows with
352 // Failed: : Test change event for Range.Clear set:
353 // Failed: : Test change event for Range.ClearContents set:
354 // Failed: : Test change event for Range.Replace:
355 // Failed: : Test change event for Range.FillRight:
356 // Tests passed: 4
357 // Tests failed: 4
358 #if !defined(_WIN32)
359 { u"Ranges-3.xls"_ustr,
360 u"vnd.sun.Star.script:VBAProject.testMacros.test?language=Basic&location=document"_ustr },
361 #endif
362 { u"TestCalc_Rangetest.xls"_ustr,
363 u"vnd.sun.Star.script:VBAProject.testMacros.test?language=Basic&location=document"_ustr },
364 { u"TestCalc_Rangetest2.xls"_ustr,
365 u"vnd.sun.Star.script:VBAProject.testMacros.test?language=Basic&location=document"_ustr },
366 { u"Ranges-2.xls"_ustr,
367 u"vnd.sun.Star.script:VBAProject.testMacros.test?language=Basic&location=document"_ustr },
368 { u"pagesetup.xls"_ustr,
369 u"vnd.sun.Star.script:VBAProject.testMacros.test?language=Basic&location=document"_ustr },
370 { u"Window.xls"_ustr,
371 u"vnd.sun.Star.script:VBAProject.testMacros.test?language=Basic&location=document"_ustr },
372 { u"window2.xls"_ustr,
373 u"vnd.sun.Star.script:VBAProject.testMacros.test?language=Basic&location=document"_ustr },
374 { u"PageBreaks.xls"_ustr,
375 u"vnd.sun.Star.script:VBAProject.testMacros.test?language=Basic&location=document"_ustr },
376 { u"Shapes.xls"_ustr,
377 u"vnd.sun.Star.script:VBAProject.testMacros.test?language=Basic&location=document"_ustr },
378 { u"Ranges.xls"_ustr,
379 u"vnd.sun.Star.script:VBAProject.testMacros.test?language=Basic&location=document"_ustr },
380 { u"CheckOptionToggleValue.xls"_ustr,
381 u"vnd.sun.Star.script:VBAProject.testMacros.test?language=Basic&location=document"_ustr },
382 { u"GeneratedEventTest.xls"_ustr,
383 u"vnd.sun.Star.script:VBAProject.testMacros.test?language=Basic&location=document"_ustr },
384 { u"MiscControlTests.xls"_ustr,
385 u"vnd.sun.Star.script:VBAProject.testMacros.test?language=Basic&location=document"_ustr },
386 { u"Workbooks.xls"_ustr,
387 u"vnd.sun.Star.script:VBAProject.testMacros.test?language=Basic&location=document"_ustr },
388 { u"Names.xls"_ustr,
389 u"vnd.sun.Star.script:VBAProject.testMacros.test?language=Basic&location=document"_ustr },
390 { u"NamesSheetLocal.xls"_ustr,
391 u"vnd.sun.Star.script:VBAProject.testMacros.test?language=Basic&location=document"_ustr },
392 { u"vba_endFunction.xls"_ustr,
393 u"vnd.sun.Star.script:VBAProject.testMacros.test?language=Basic&location=document"_ustr },
394 { u"vba_findFunction.xls"_ustr,
395 u"vnd.sun.Star.script:VBAProject.testMacros.test?language=Basic&location=document"_ustr },
396 { u"BGR-RGBTest.xls"_ustr,
397 u"vnd.sun.Star.script:VBAProject.Module1.test?language=Basic&location=document"_ustr }
398 };
399 OUString sTempDir;
400 OUString sTempDirURL;
401 osl::FileBase::getTempDirURL(sTempDirURL);
402 osl::FileBase::getSystemPathFromFileURL(sTempDirURL, sTempDir);
403 sTempDir += OUStringChar(SAL_PATHDELIMITER);
404 OUString sTestFileName(u"My Test WorkBook.xls"_ustr);
405 uno::Sequence<uno::Any> aParams;
406 for (const auto& rTestInfo : testInfo)
407 {
408 OUString aFileName = loadFromFile(rTestInfo.sFileBaseName);
409
410 // process all events such as OnLoad events etc. otherwise they tend
411 // to arrive later at a random time - while processing other StarBasic
412 // methods.
413 Scheduler::ProcessEventsToIdle();
414
415 bool bWorkbooksHandling = rTestInfo.sFileBaseName == "Workbooks.xls" && !sTempDir.isEmpty();
416
417 if (bWorkbooksHandling)
418 {
419 aParams = { uno::Any(sTempDir), uno::Any(sTestFileName) };
420 }
421
422 SAL_INFO("sc.qa", "about to invoke vba test in " << aFileName << " with url "
423 << rTestInfo.sMacroUrl);
424
425 uno::Any aRet = executeMacro(rTestInfo.sMacroUrl, aParams);
426 OUString aStringRes;
427 aRet >>= aStringRes;
428
429 CPPUNIT_ASSERT_EQUAL_MESSAGE(
430 OUString("script reported failure in file " + rTestInfo.sFileBaseName)
431 .toUtf8()
432 .getStr(),
433 u"OK"_ustr, aStringRes);
434
435 if (bWorkbooksHandling)
436 {
437 OUString sFileUrl;
438 OUString sFilePath = sTempDir + sTestFileName;
439 osl::FileBase::getFileURLFromSystemPath(sFilePath, sFileUrl);
440 if (!sFileUrl.isEmpty())
441 osl::File::remove(sFileUrl);
442 }
443 }
444 }
445
446 CPPUNIT_TEST_FIXTURE(VBAMacroTest, testTdf149579)
447 {
448 loadFromURL(u"private:factory/scalc"_ustr);
449
450 css::uno::Reference<css::document::XEmbeddedScripts> xDocScr(mxComponent, uno::UNO_QUERY_THROW);
451 auto xLibs = xDocScr->getBasicLibraries();
452 auto xLibrary = xLibs->createLibrary(u"TestLibrary"_ustr);
453 xLibrary->insertByName(u"TestModule"_ustr, uno::Any(u"Option VBASupport 1\n"
454 "Sub TestTdf149579\n"
455 "Range(\"A1\").Sort Key1:=Range(\"A1\")\n"
456 "End Sub\n"_ustr));
457
458 SfxObjectShell* pFoundShell = SfxObjectShell::GetShellFromComponent(mxComponent);
459 ScDocShell* pDocSh = static_cast<ScDocShell*>(pFoundShell);
460 CPPUNIT_ASSERT(pDocSh);
461 ScDocument& rDoc = pDocSh->GetDocument();
462
463 rDoc.SetValue(ScAddress(0, 0, 0), 5.0);
464 rDoc.SetValue(ScAddress(0, 1, 0), 10.0);
465 rDoc.SetValue(ScAddress(0, 2, 0), 1.0);
466
467 // Without the fix in place, this call would have crashed in debug builds with failed assertion
468 executeMacro(
469 u"vnd.sun.Star.script:TestLibrary.TestModule.TestTdf149579?language=Basic&location="
470 "document"_ustr);
471 // Without the fix in place, this test would have failed with
472 // - Expected: 1
473 // - Actual : 5
474 CPPUNIT_ASSERT_EQUAL(1.0, rDoc.GetValue(ScAddress(0, 0, 0)));
475 CPPUNIT_ASSERT_EQUAL(5.0, rDoc.GetValue(ScAddress(0, 1, 0)));
476 CPPUNIT_ASSERT_EQUAL(10.0, rDoc.GetValue(ScAddress(0, 2, 0)));
477 }
478
479 CPPUNIT_TEST_FIXTURE(VBAMacroTest, testVbaRangeSort)
480 {
481 loadFromURL(u"private:factory/scalc"_ustr);
482
483 css::uno::Reference<css::document::XEmbeddedScripts> xDocScr(mxComponent, uno::UNO_QUERY_THROW);
484 auto xLibs = xDocScr->getBasicLibraries();
485 auto xLibrary = xLibs->createLibrary(u"TestLibrary"_ustr);
486 xLibrary->insertByName(u"TestModule"_ustr,
487 uno::Any(u"Option VBASupport 1\n"
488 "Sub TestRangeSort\n"
489 " Range(Cells(1, 1), Cells(3, 1)).Select\n"
490 " Selection.Sort Key1:=Range(\"A1\"), Header:=False\n"
491 "End Sub\n"_ustr));
492
493 SfxObjectShell* pFoundShell = SfxObjectShell::GetShellFromComponent(mxComponent);
494 ScDocShell* pDocSh = static_cast<ScDocShell*>(pFoundShell);
495 CPPUNIT_ASSERT(pDocSh);
496 ScDocument& rDoc = pDocSh->GetDocument();
497
498 rDoc.SetValue(ScAddress(0, 0, 0), 1.0);
499 rDoc.SetValue(ScAddress(0, 1, 0), 0.5);
500 rDoc.SetValue(ScAddress(0, 2, 0), 2.0);
501
502 // Without the fix in place, this call would have crashed in debug builds with failed assertion
503 executeMacro(
504 u"vnd.sun.Star.script:TestLibrary.TestModule.TestRangeSort?language=Basic&location="
505 "document"_ustr);
506
507 CPPUNIT_ASSERT_EQUAL(0.5, rDoc.GetValue(ScAddress(0, 0, 0)));
508 CPPUNIT_ASSERT_EQUAL(1.0, rDoc.GetValue(ScAddress(0, 1, 0)));
509 CPPUNIT_ASSERT_EQUAL(2.0, rDoc.GetValue(ScAddress(0, 2, 0)));
510
511 // Change sheet's first param sorting order
512 ScSortParam aParam;
513 rDoc.GetSortParam(aParam, 0);
514 aParam.maKeyState[0].bAscending = false;
515 rDoc.SetSortParam(aParam, 0);
516
517 executeMacro(
518 u"vnd.sun.Star.script:TestLibrary.TestModule.TestRangeSort?language=Basic&location="
519 "document"_ustr);
520
521 // Without the fix in place, this test would have failed in non-debug builds with
522 // - Expected: 2
523 // - Actual : 0.5
524 CPPUNIT_ASSERT_EQUAL(2.0, rDoc.GetValue(ScAddress(0, 0, 0)));
525 CPPUNIT_ASSERT_EQUAL(1.0, rDoc.GetValue(ScAddress(0, 1, 0)));
526 CPPUNIT_ASSERT_EQUAL(0.5, rDoc.GetValue(ScAddress(0, 2, 0)));
527 }
528
529 CPPUNIT_TEST_FIXTURE(VBAMacroTest, testTdf107885)
530 {
531 loadFromFile(u"tdf107885.xlsm");
532
533 SfxObjectShell* pFoundShell = SfxObjectShell::GetShellFromComponent(mxComponent);
534
535 CPPUNIT_ASSERT_MESSAGE("Failed to access document shell", pFoundShell);
536 ScDocShell* pDocSh = static_cast<ScDocShell*>(pFoundShell);
537 ScDocument& rDoc = pDocSh->GetDocument();
538
539 CPPUNIT_ASSERT(!rDoc.RowHidden(1, 0));
540 CPPUNIT_ASSERT(!rDoc.RowHidden(2, 0));
541 CPPUNIT_ASSERT(!rDoc.RowHidden(3, 0));
542 CPPUNIT_ASSERT(!rDoc.RowHidden(4, 0));
543
544 // Call auto filter macro using a string condition
545 executeMacro(
546 u"vnd.sun.Star.script:VBAProject.Module1.AFString?language=Basic&location=document"_ustr);
547
548 //Without the fix in place, all rows in autofilter would have been hidden
549 CPPUNIT_ASSERT(rDoc.RowHidden(1, 0));
550 CPPUNIT_ASSERT(!rDoc.RowHidden(2, 0));
551 CPPUNIT_ASSERT(!rDoc.RowHidden(3, 0));
552 CPPUNIT_ASSERT(!rDoc.RowHidden(4, 0));
553
554 // Call auto filter macro using a numeric condition without any locale
555 executeMacro(
556 u"vnd.sun.Star.script:VBAProject.Module1.AFNumeric?language=Basic&location=document"_ustr);
557
558 CPPUNIT_ASSERT(rDoc.RowHidden(1, 0));
559 CPPUNIT_ASSERT(rDoc.RowHidden(2, 0));
560 CPPUNIT_ASSERT(!rDoc.RowHidden(3, 0));
561 CPPUNIT_ASSERT(!rDoc.RowHidden(4, 0));
562 }
563
564 CPPUNIT_TEST_FIXTURE(VBAMacroTest, testTdf52602)
565 {
566 loadFromFile(u"tdf52602.xls");
567
568 SfxObjectShell* pFoundShell = SfxObjectShell::GetShellFromComponent(mxComponent);
569
570 CPPUNIT_ASSERT_MESSAGE("Failed to access document shell", pFoundShell);
571 ScDocShell* pDocSh = static_cast<ScDocShell*>(pFoundShell);
572 ScDocument& rDoc = pDocSh->GetDocument();
573
574 //Without the fix in place, it would have failed with 'Unexpected dialog: Error: BASIC runtime error.'
575 executeMacro(u"vnd.sun.Star.script:VBAProject.Modul1.Test_NumberFormat_DateTime?language=Basic&"
576 "location=document"_ustr);
577
578 CPPUNIT_ASSERT_EQUAL(u"15:20"_ustr, rDoc.GetString(ScAddress(0, 0, 0)));
579 CPPUNIT_ASSERT_EQUAL(u"15:20"_ustr, rDoc.GetString(ScAddress(0, 1, 0)));
580 CPPUNIT_ASSERT_EQUAL(u"03/01/2012 15:20"_ustr, rDoc.GetString(ScAddress(1, 0, 0)));
581 CPPUNIT_ASSERT_EQUAL(u"03/01/2012 15:20"_ustr, rDoc.GetString(ScAddress(1, 1, 0)));
582 CPPUNIT_ASSERT_EQUAL(u"03/01/2012 15:20:00"_ustr, rDoc.GetString(ScAddress(2, 0, 0)));
583 CPPUNIT_ASSERT_EQUAL(u"03/01/2012 15:20:00"_ustr, rDoc.GetString(ScAddress(2, 1, 0)));
584 CPPUNIT_ASSERT_EQUAL(u"1/3/12 15:20"_ustr, rDoc.GetString(ScAddress(3, 0, 0)));
585 CPPUNIT_ASSERT_EQUAL(u"1/3/12 15:20"_ustr, rDoc.GetString(ScAddress(3, 1, 0)));
586 CPPUNIT_ASSERT_EQUAL(u"1/ March 2012"_ustr, rDoc.GetString(ScAddress(4, 0, 0)));
587 CPPUNIT_ASSERT_EQUAL(u"1/ March 2012"_ustr, rDoc.GetString(ScAddress(4, 1, 0)));
588 CPPUNIT_ASSERT_EQUAL(u"1/ Mar 2012"_ustr, rDoc.GetString(ScAddress(5, 0, 0)));
589 CPPUNIT_ASSERT_EQUAL(u"1/ Mar 2012"_ustr, rDoc.GetString(ScAddress(5, 1, 0)));
590 }
591
592 CPPUNIT_TEST_FIXTURE(VBAMacroTest, testTdf107902)
593 {
594 loadFromFile(u"tdf107902.xlsm");
595
596 SfxObjectShell* pFoundShell = SfxObjectShell::GetShellFromComponent(mxComponent);
597
598 CPPUNIT_ASSERT_MESSAGE("Failed to access document shell", pFoundShell);
599 ScDocShell* pDocSh = static_cast<ScDocShell*>(pFoundShell);
600 ScDocument& rDoc = pDocSh->GetDocument();
601
602 //Without the fix in place, it would have failed with 'Unexpected dialog: Error: BASIC runtime error.'
603 executeMacro(
604 u"vnd.sun.Star.script:VBAProject.Module1.AF?language=Basic&location=document"_ustr);
605
606 //Check the autofilter was created
607 const ScPatternAttr* pPattern = rDoc.GetPattern(0, 0, 0);
608 CPPUNIT_ASSERT(pPattern);
609
610 const ScMergeFlagAttr& rAttr = pPattern->GetItem(ATTR_MERGE_FLAG);
611 CPPUNIT_ASSERT_MESSAGE("Autofilter was not created", rAttr.HasAutoFilter());
612
613 //Check the last row is hidden
614 CPPUNIT_ASSERT(!rDoc.RowHidden(0, 0));
615 CPPUNIT_ASSERT(!rDoc.RowHidden(1, 0));
616 CPPUNIT_ASSERT(!rDoc.RowHidden(2, 0));
617 CPPUNIT_ASSERT(!rDoc.RowHidden(3, 0));
618 CPPUNIT_ASSERT(rDoc.RowHidden(4, 0));
619 }
620
621 CPPUNIT_TEST_FIXTURE(VBAMacroTest, testTdf90278)
622 {
623 loadFromFile(u"tdf90278.xls");
624
625 // Without the fix in place, changing the border weight
626 // would cause a Basic exception/error in the following script.
627 uno::Any aRet = executeMacro(
628 u"vnd.sun.Star.script:VBAProject.Module1.BorderWeight?language=Basic&location=document"_ustr);
629
630 // Check the border weight of the corresponding cell in the test document
631 sal_Int32 aReturnValue;
632 aRet >>= aReturnValue;
633 CPPUNIT_ASSERT_EQUAL(sal_Int32(2), aReturnValue);
634 }
635
636 CPPUNIT_TEST_FIXTURE(VBAMacroTest, testTdf149531)
637 {
638 loadFromFile(u"tdf149531.xls");
639
640 SfxObjectShell* pFoundShell = SfxObjectShell::GetShellFromComponent(mxComponent);
641
642 CPPUNIT_ASSERT_MESSAGE("Failed to access document shell", pFoundShell);
643 ScDocShell* pDocSh = static_cast<ScDocShell*>(pFoundShell);
644 ScDocument& rDoc = pDocSh->GetDocument();
645
646 for (sal_Int32 i = 0; i < 5; ++i)
647 {
648 // Without the fix in place, this test would have crashed
649 // also check the test doesn't crash when the macro is executed a few times in a row
650 executeMacro(u"vnd.sun.Star.script:VBAProject.Module1.SetColumnWidth?language=Basic&"
651 "location=document"_ustr);
652 }
653
654 sal_uInt16 nWidth
655 = o3tl::convert(rDoc.GetColWidth(0, 0), o3tl::Length::twip, o3tl::Length::mm100);
656 CPPUNIT_ASSERT_EQUAL(static_cast<sal_uInt16>(25749), nWidth);
657 }
658
659 CPPUNIT_TEST_FIXTURE(VBAMacroTest, testTdf118247)
660 {
661 loadFromFile(u"tdf118247.xlsm");
662
663 uno::Any aRet = executeMacro(
664 u"vnd.sun.Star.script:VBAProject.Module1.testXlSpecialCellsValuesConstantsEmpty?"
665 "language=Basic&location=document"_ustr);
666
667 OUString aReturnValue;
668 aRet >>= aReturnValue;
669 CPPUNIT_ASSERT_EQUAL(u"$A$1:$A$3"_ustr, aReturnValue);
670
671 const std::vector<std::pair<sal_Int32, OUString>> aTestParams(
672 { { excel::XlSpecialCellsValue::xlNumbers, "$A$1:$A$2" },
673 { excel::XlSpecialCellsValue::xlTextValues, "$A$3" },
674 { excel::XlSpecialCellsValue::xlLogical, "$A$1:$A$2" },
675 { excel::XlSpecialCellsValue::xlErrors, "$A$1:$A$4" } });
676
677 for (auto & [ nXlSpecialCellsValue, sRange ] : aTestParams)
678 {
679 uno::Sequence<uno::Any> aParams = { uno::Any(nXlSpecialCellsValue) };
680 aRet = executeMacro(
681 u"vnd.sun.Star.script:VBAProject.Module1.testXlSpecialCellsValuesConstants?"
682 "language=Basic&location=document"_ustr,
683 aParams);
684 aRet >>= aReturnValue;
685 CPPUNIT_ASSERT_EQUAL(sRange, aReturnValue);
686 }
687 }
688
689 CPPUNIT_TEST_FIXTURE(VBAMacroTest, testTdf126457)
690 {
691 loadFromURL(u"private:factory/scalc"_ustr);
692
693 // Save a copy of the file to get its URL
694 uno::Reference<frame::XStorable> xDocStorable(mxComponent, uno::UNO_QUERY);
695 utl::TempFileNamed aTempFile(u"testWindowsActivate", true, u".ods");
696 aTempFile.EnableKillingFile();
697 uno::Sequence<beans::PropertyValue> descSaveAs(
698 comphelper::InitPropertySequence({ { "FilterName", uno::Any(u"calc8"_ustr) } }));
699 xDocStorable->storeAsURL(aTempFile.GetURL(), descSaveAs);
700
701 // Insert initial library
702 css::uno::Reference<css::document::XEmbeddedScripts> xDocScr(mxComponent, uno::UNO_QUERY_THROW);
703 auto xLibs = xDocScr->getBasicLibraries();
704 auto xLibrary = xLibs->createLibrary(u"TestLibrary"_ustr);
705 xLibrary->insertByName(
706 u"TestModule"_ustr,
707 uno::Any(u"Option VBASupport 1\n"
708 "Function TestWindowsActivate\n"
709 " dirName = Workbooks(1).Path\n"
710 " workbookName = Workbooks(1).Name\n"
711 " fileName = dirName + Application.PathSeparator + workbookName\n"
712 " Workbooks.Open Filename := fileName\n"
713 " On Error Goto handler\n"
714 // activate window using its URL
715 " Windows(fileName).Activate\n"
716 // activate window using its caption name
717 " Windows(workbookName).Activate\n"
718 // activate window using a newly generated window caption
719 " newCaption = \"New Window Caption\"\n"
720 " Windows(fileName).Caption = newCaption\n"
721 " Windows(newCaption).Activate\n"
722 " TestWindowsActivate = 0\n"
723 " Exit Function\n"
724 "handler:\n"
725 " TestWindowsActivate = 1\n"
726 "End Function\n"_ustr));
727
728 uno::Any aRet;
729 uno::Sequence<sal_Int16> aOutParamIndex;
730 uno::Sequence<uno::Any> aOutParam;
731
732 SfxObjectShell* pFoundShell = SfxObjectShell::GetShellFromComponent(mxComponent);
733 ScDocShell* pDocSh = static_cast<ScDocShell*>(pFoundShell);
734 CPPUNIT_ASSERT(pDocSh);
735
736 ErrCode result
737 = SfxObjectShell::CallXScript(mxComponent,
738 u"vnd.sun.Star.script:TestLibrary.TestModule."
739 "TestWindowsActivate?language=Basic&location=document"_ustr,
740 {}, aRet, aOutParamIndex, aOutParam);
741
742 // Without the fix in place, the windows could not be activated in the macro
743 CPPUNIT_ASSERT_EQUAL(ERRCODE_NONE, result);
744 sal_Int16 nReturnValue;
745 aRet >>= nReturnValue;
746 CPPUNIT_ASSERT_EQUAL(sal_Int16(0), nReturnValue);
747 }
748
749 CPPUNIT_TEST_FIXTURE(VBAMacroTest, testVbaPDFExport)
750 {
751 loadFromURL(u"private:factory/scalc"_ustr);
752
753 // Save a copy of the file to get its URL
754 uno::Reference<frame::XStorable> xDocStorable(mxComponent, uno::UNO_QUERY);
755 utl::TempFileNamed aTempFile(u"testVBA_PDF_Export", true, u".ods");
756 aTempFile.EnableKillingFile();
757 uno::Sequence<beans::PropertyValue> descSaveAs(
758 comphelper::InitPropertySequence({ { "FilterName", uno::Any(u"calc8"_ustr) } }));
759 xDocStorable->storeAsURL(aTempFile.GetURL(), descSaveAs);
760
761 utl::TempFileNamed aTempPdfFile(u"exportedfile", true, u".pdf");
762 aTempPdfFile.EnableKillingFile();
763
764 css::uno::Reference<css::document::XEmbeddedScripts> xDocScr(mxComponent, uno::UNO_QUERY_THROW);
765 auto xLibs = xDocScr->getBasicLibraries();
766 auto xLibrary = xLibs->createLibrary(u"TestLibrary"_ustr);
767 OUString sMacro = "Option VBASupport 1\n"
768 "Sub ExportAsPDF\n"
769 " fileName = \""
770 + aTempPdfFile.GetFileName()
771 + "\"\n ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, "
772 "FileName:=fileName, Quality:=xlQualityStandard, "
773 "IncludeDocProperties:=True, OpenAfterPublish:=False\n"
774 "End Sub\n";
775 xLibrary->insertByName(u"TestModule"_ustr, uno::Any(sMacro));
776
777 executeMacro(u"vnd.sun.Star.script:TestLibrary.TestModule.ExportAsPDF?language=Basic&location="
778 "document"_ustr);
779
780 // Parse the export result.
781 vcl::filter::PDFDocument aDocument;
782 SvFileStream aStream(aTempPdfFile.GetURL(), StreamMode::READ);
783 CPPUNIT_ASSERT_MESSAGE("Failed to get the pdf document", aDocument.Read(aStream));
784 }
785
786 CPPUNIT_TEST_FIXTURE(VBAMacroTest, testForEachInSelection)
787 {
788 loadFromFile(u"ForEachInSelection.ods");
789 SfxObjectShell* pFoundShell = SfxObjectShell::GetShellFromComponent(mxComponent);
790
791 CPPUNIT_ASSERT_MESSAGE("Failed to access document shell", pFoundShell);
792 ScDocShell* pDocSh = static_cast<ScDocShell*>(pFoundShell);
793 ScDocument& rDoc = pDocSh->GetDocument();
794
795 CPPUNIT_ASSERT_EQUAL(u"foo"_ustr, rDoc.GetString(ScAddress(0, 0, 0)));
796 CPPUNIT_ASSERT_EQUAL(u"bar"_ustr, rDoc.GetString(ScAddress(0, 1, 0)));
797 CPPUNIT_ASSERT_EQUAL(u"baz"_ustr, rDoc.GetString(ScAddress(0, 2, 0)));
798
799 // tdf#153724: without the fix, this would fail with
800 // assertion failed
801 // - Expression: false
802 // - Unexpected dialog: Error: BASIC runtime error.
803 // '13'
804 // Data type mismatch.
805 executeMacro(u"vnd.sun.Star.script:Standard.Module1.TestForEachInSelection?"
806 "language=Basic&location=document"_ustr);
807
808 CPPUNIT_ASSERT_EQUAL(u"oof"_ustr, rDoc.GetString(ScAddress(0, 0, 0)));
809 CPPUNIT_ASSERT_EQUAL(u"rab"_ustr, rDoc.GetString(ScAddress(0, 1, 0)));
810 CPPUNIT_ASSERT_EQUAL(u"zab"_ustr, rDoc.GetString(ScAddress(0, 2, 0)));
811 }
812
813 CPPUNIT_TEST_FIXTURE(VBAMacroTest, testNonAsciiMacroIRI)
814 {
815 loadFromFile(u"ForEachInSelection.ods");
816 SfxObjectShell* pFoundShell = SfxObjectShell::GetShellFromComponent(mxComponent);
817
818 CPPUNIT_ASSERT_MESSAGE("Failed to access document shell", pFoundShell);
819 ScDocShell* pDocSh = static_cast<ScDocShell*>(pFoundShell);
820 ScDocument& rDoc = pDocSh->GetDocument();
821
822 CPPUNIT_ASSERT_EQUAL(u"foo"_ustr, rDoc.GetString(ScAddress(0, 0, 0)));
823 CPPUNIT_ASSERT_EQUAL(u"bar"_ustr, rDoc.GetString(ScAddress(0, 1, 0)));
824 CPPUNIT_ASSERT_EQUAL(u"baz"_ustr, rDoc.GetString(ScAddress(0, 2, 0)));
825
826 auto ret
827 = dispatchCommand(mxComponent, u"macro://./Standard.Module1.NonAsciiName_αβγ"_ustr, {});
828 css::frame::DispatchResultEvent retEvent;
829 CPPUNIT_ASSERT(ret >>= retEvent);
830 // tdf#153752: without the fix, this would fail with
831 // equality assertion failed
832 // - Expected: 1
833 // - Actual : 0
834 CPPUNIT_ASSERT_EQUAL(css::frame::DispatchResultState::SUCCESS, retEvent.State);
835
836 CPPUNIT_ASSERT_EQUAL(u"oof"_ustr, rDoc.GetString(ScAddress(0, 0, 0)));
837 CPPUNIT_ASSERT_EQUAL(u"rab"_ustr, rDoc.GetString(ScAddress(0, 1, 0)));
838 CPPUNIT_ASSERT_EQUAL(u"zab"_ustr, rDoc.GetString(ScAddress(0, 2, 0)));
839 }
840
841 CPPUNIT_PLUGIN_IMPLEMENT();
842
843 /* vim:set shiftwidth=4 softtabstop=4 expandtab: */
FREE OFFICE SUITE