Testing macros, templates and compatibility

Module 6: Files and documents · Reading time: 12 minutes

Learning objective: After this lesson, you'll be able to use a test set of your own documents to work out what functions well in a new online office, what needs adjusting and what you're better off keeping in another program, with special attention to VBA macros, templates, fonts and mail merge.

Key concepts: test set, compatibility, VBA macro (xlsm, docm), JavaScript macro, custom function (#NAME?), add-in, template (dotx, xltx, potx), house style, content control, form field, mail merge, track changes, comments, function names and separator

Why test, if the formats are open anyway?

In lesson 6.3, you saw that docx and odt are open standards. That doesn't mean every program handles everything in such a file equally well. A simple letter or spreadsheet works everywhere. Things go wrong with the special elements: macros, buttons, add-ins, form fields, complex templates and fonts that aren't available. These are almost always found in precisely the documents an organisation runs on: the quote generator, the timesheet, the letterhead.

That's why, before switching, you create a test set and work through it systematically. Not to prove that the new package is good, but to find out what you need to arrange.

Step 1: choose your test set

Choose 15 to 25 documents. Not random ones, but the documents that are the most important or the most complex:

• the documents that are used most often (ask colleagues, or look at "recent" in your old service);
• all templates and the letterhead;
• every file with macros (extensions xlsm, docm, pptm, and old xls or doc files that may contain macros);
• documents with forms, mail merge (letters from an address list) or links to other files;
• documents you exchange with external parties (accountant, client);
• a few large or old files.

At Bakkerij De Korrel, this came to 18 documents, including the quote generator (Excel with a macro), the letterhead (a Word template with a house style font), the price list, the leave form, the annual Christmas letter to customers and the trade fair presentation.

Step 2: test each document against fixed points

Open each document in the new office suite and do what you'd normally do with it. Note, for each document:

1. Does it open? And does it look the same (formatting, line spacing, pagination)?
2. Editing and saving: change something, save, reopen. Is it still correct?
3. Back to the partner: open the edited file in the recipient's program (for example, Word at the accountant's). Does it still look right there?
4. Special elements: do the buttons, formulas, fields, table of contents, headers and footers work?
5. Co-editing: can two people work in it at the same time without problems?
6. Printing and pdf: does the printout look correct?

Macros: the honest story

A macro is a small piece of program inside a document: a button that formats a quote, copies data or sorts a list. In Microsoft Office, macros are written in VBA (Visual Basic for Applications). Files with macros have their own extension: xlsm, docm and pptm. Inside the zip file, the code sits in a component called vbaProject.bin.

The online office suites used by European clouds do not run VBA. Euro-Office and ONLYOFFICE support macros in JavaScript; Collabora and LibreOffice have their own Basic and Python, and can run some VBA code to a limited extent in the LibreOffice desktop program. In the browser, you can't rely on this in practice. The file will still open: you'll see all the data and formulas, but the buttons won't do anything.

What can you do?

Two pitfalls. Always keep the original: if you save an xlsm file in another package, the VBA code may become unusable in Excel. And custom functions: if a formula uses a self-made VBA function, such as =VAT(A1), the new package will show an error value such as #NAME?.

Security also plays a role. Macros in files from others can be harmful. A good package asks when opening a file whether you want to run automatic macros. Only choose yes if you trust the file.

Templates and house style

A template (dotx, xltx, potx, or ott for ODF) is a starting document: letterhead, quote, presentation in house style. Test three things:

• Font: is the house style font available on the online office server? If not, the text will shift (lesson 6.3). Ask the provider whether they can install it, or choose a free font that comes close.
• Logo and headers/footers: do they appear correctly, including on the second page?
• Fields: date, page numbers, table of contents. Have them update once.

In the new cloud, create a fixed folder called Templates, with read-only access for most people, so no one accidentally changes the original.

Forms, mail merge and tracking

• Form fields and content controls (drop-down lists, checkboxes, date pickers in Word) don't work the same way in every package. Test filling them in and sending them back.
• Mail merge (letters from an address list) works in many online packages, but sometimes with limits, such as a maximum number of recipients at once or no label sheets. Test it with your real address list.
• Track changes and comments usually go back and forth fine. Do check that authors' names are correct, and remove them before sending a document externally.
• Add-ins from Microsoft Office, such as those from accounting or CRM software, don't work in a different office suite. Check whether the supplier of that tool has a different integration available.

Spreadsheets: function names and separators

Someone used to Excel in Dutch types =SOM(B3:B11) and =ALS(B5>0;"profit";"loss"), with a semicolon between the parts. An online office suite may be set to English names and commas (=SUM(...), =IF(...,...)). This doesn't change the file itself: functions are stored in the file in a fixed way. What matters is what you type. Check the setting for the language of formulas and the region, and let colleagues know which form they should use.

Step 3: decide per document

Give each document in your test set an outcome: green (works), orange (works after an adjustment, note which one) or red (doesn't work, choose a solution from the macros table). A few red documents aren't a reason not to switch. They are a reason to resolve them before the switchover date, or to deliberately keep one Excel licence for the quote generator until it's replaced.

In practice: what it looks like with one provider

In Euro-Office in a WebOké Nextcloud, an xlsm file opens with all the data, but without working VBA buttons. Via the View and Macros tab, you create a macro in JavaScript or record one; under Custom functions, you create your own function. Under File, Advanced settings, you'll find the language for formulas and the region. Mail merge is on the Collaboration tab. In other packages, the menus have different names, but the limitations are similar: VBA doesn't fully work in the browser with any of the European online packages.

Summary

• Open standards say nothing about macros, add-ins and forms: test your own documents.
• Build a test set of 15 to 25 important and complex documents and test them against fixed points.
• VBA macros don't work in the online packages; first check whether you still need the macro, and always keep the original.
• Check templates for font, logo and fields, and put them in a fixed folder with read-only access.
• Mark each document green, orange or red, and fix the red ones before the switchover date.

Quiz

Exercise

Exercise A6.5: Compatibility scan of your own document. Upload a document you consider important (for example, a spreadsheet with macros or your template). The program checks for macros, external links, embedded objects, form fields, tracked changes and comments, unknown functions, and fonts without a substitute, and gives a green, orange or red rating per point with advice.

Exercise E6.5: Your test set. Fill in the test set for at least ten of your own documents, with the outcome per test point and a decision per document.

Further reading

The steps in these articles apply to WebOké's services; with other providers, it works in a similar way.

• Euro-Office guide 15.30: An Excel file with VBA macros: what now?
• Euro-Office guide 15.2: Copying formulas from Excel: English function names and the comma
• Euro-Office guide 15.22: Creating letters from a recipient list
• Euro-Office guide 12.2: A document opens as read only
• Euro-Office guide 12.3: Fonts look different
• Euro-Office guide 15.24: Removing comments and changes before sending a document