Skip to content

Cookie settings

Choose which kinds of cookies you allow. You can change your choice at any time via Cookie settings at the bottom of every page. Privacy statement

Necessary

Always on

Needed to log in, keep your cart and remember your choices. These are always on.

Which cookies?
  • weboke_sessie, weboke_klant, weboke_remember: your session and staying logged in
  • weboke_basket: your cart
  • wkBtwIncl: showing prices with or without VAT
  • wok_cookies: your cookie choice, 12 months

Measures which ad leads to a visit or order, so we do not pay for ads that do nothing.

Which cookies?
  • Google Ads via Google Tag Manager (_gcl_au, _gcl_aw), up to 90 days
  • OpenAI Ads, ads in ChatGPT
  • wok_aff: which partner referred you to us (affiliate programme), 60 days

4.6

Country and language

Each country has its own site, with prices and VAT for that country.

View

Converting a VBA macro to JavaScript yourself: the translation table

In An Excel file with VBA macros: what now? you read that Euro-Office does not run VBA macros. The file opens, but buttons and automatic actions do nothing. If the macro matters, you convert it to JavaScript, the language of macros in Euro-Office. That sounds harder than it is: most Excel macros do a few simple things with cells, and almost every VBA line has a fixed JavaScript counterpart. This article gives you the translation table and a worked example.

First: automatic conversion is not possible

In the Macros window you see a button with a magic wand next to Macros. Below it are Create from description and Convert from VBA. These are greyed out: they only work with a plug-in, and Euro-Office at WebOké deliberately works without plug-ins (see Plugins in Euro-Office: why WebOké doesn't offer them and what to do instead). So you convert a macro yourself, using the table below.

Convert from VBA is greyed out
Under the magic wand (1), Create from description and Convert from VBA are greyed out: they do not work without a plug-in

How to do it in three steps

  1. Write down in plain language what the macro does. Open the xlsm file in Excel, press Alt+F11 and read the code. For example: "go through rows 2 to 10; if the amount in column D is more than 500, make the row yellow and the amount bold".
  2. Translate line by line using the table below.
  3. Test on a copy in Euro-Office: View tab, Macros, plus sign, paste the code, Run. If it does not work, see A macro that doesn't work: finding errors.

The translation table

We tested every line below in Euro-Office. ws stands for the worksheet: put const ws = Api.GetActiveSheet(); at the top of your macro.

VBAEuro-Office (JavaScript)
Sub Name() ... End Sub(function () { ... })();
ActiveSheetApi.GetActiveSheet()
Worksheets("Archief")Api.GetSheet("Archief")
Range("A1").Value = 5ws.GetRange("A1").SetValue(5);
x = Range("A1").Valuelet x = ws.GetRange("A1").GetValue();
Cells(2, 2)ws.GetRangeByNumber(1, 1) (counts from 0)
SelectionApi.GetSelection()
For Each c In Range("D2:D10") ... Nextws.GetRange("D2:D10").ForEach(function (c) { ... });
For r = 2 To 10 ... Next rfor (let r = 2; r <= 10; r++) { ... }
If ... Then ... Else ... End Ifif (...) { ... } else { ... }
Select Case x ... Case "Done"switch (x) { case "Done": ...; break; }
a = b And c, a = b Or c, Not aa = b && c, a = b || c, !a
"Row " & r"Row " + r
x <> ""x !== ""
Interior.Color = RGB(255, 235, 156)SetFillColor(Api.CreateColorFromRGB(255, 235, 156))
Font.Bold = TrueSetBold(true)
Rows(5).Deletews.GetRows(5).Delete("up")
Range("A2:E2").Copy Range("A12")ws.GetRange("A2:E2").Copy(ws.GetRange("A12"))
Workbook_Opena macro with Autostart (see Automatically fill in the date and time when someone changes a cell)
Worksheet_ChangeApi.attachEvent("onWorksheetChange", ...) (see Automatically fill in the date and time when someone changes a cell)
Function VAT(x) for use in formulasa custom function (see Excel formulas with LET or LAMBDA: what to do in Euro-Office instead)
MsgBox "Done"does not exist; put a message in a cell (see A macro that doesn't work: finding errors)

An example: highlighting large orders

Bloemisterij Het Zonnetje had this macro in Excel to make large orders stand out:

Sub GroteOrdersMarkeren()
    Dim r As Long
    For r = 2 To 10
        If Cells(r, 4).Value > 500 Then
            Range("A" & r & ":E" & r).Interior.Color = RGB(255, 235, 156)
            Cells(r, 4).Font.Bold = True
        End If
    Next r
End Sub

Converted to Euro-Office, it becomes:

(function () {
    // Converted from VBA: orders over 500 euros yellow and the amount bold.
    const blad = Api.GetActiveSheet();
    for (let r = 2; r <= 10; r++) {
        if (blad.GetRange("D" + r).GetValue() > 500) {
            blad.GetRange("A" + r + ":E" + r).SetFillColor(Api.CreateColorFromRGB(255, 235, 156));
            blad.GetRange("D" + r).SetBold(true);
        }
    }
})();
The converted macro in the Macros window
The converted macro: the same steps as in VBA, in JavaScript

What changed: Sub became a function in brackets, Cells(r, 4) became GetRange("D" + r) (column 4 is D), the & for joining text became a +, and Interior.Color and Font.Bold became SetFillColor and SetBold. Every line ends with a semicolon, and blocks go between curly brackets instead of Then and End If.

The order list after running the converted macro
The orders from Bakkerij De Korenaar (€620) and Hotel De Haven (€1,240) are yellow, and the amount is bold

Cheat sheet: Record macro

Not sure what an action is called in JavaScript? Record it. On the View tab, click Record macro. The button then changes to Stop recording. Perform your actions, click Stop recording and open Macros: a new macro appears in the list with the code for what you did.

Record macro is running
While recording, the button is called Stop recording (1); use Pause recording to pause it for a moment

In our test we clicked D5 and made it bold, typed "Gecontroleerd" in F1, copied A2 to A12 and then made C3 and C4 italic. This is what Euro-Office recorded:

(function()
{
    let worksheet = Api.GetActiveSheet();
    let workbook = Api.GetActiveWorkbook();
    Api.GetRange("D5").Select();
    Api.GetSelection().SetBold(true);
    Api.GetRange("F1").Select();
    worksheet.GetActiveCell().SetValue("Gecontroleerd");
    Api.GetRange("F2").Select();
    Api.GetRange("A2").Select();
    Api.GetRange("A12").Select();
    Api.GetSelection().SetItalic(true);
})();
The recorded macro
The recorded macro: making text bold and typing a value are included, copying and pasting are not

You immediately see the names you need: Select, SetBold, SetValue, SetItalic. But you also see the limits of recording:

  • Copying and pasting is not recorded: from A2 to A12 you only see Select twice.
  • Selecting via the name box (top left, where you type C3:C4) is not recorded. As a result, the recording applies the italics meant for C3:C4 to A12, the last cell we clicked with the mouse. So click cells with the mouse while recording.
  • No loops or conditions are recorded. You cannot record "All rows with Done".

So use recording to look up names, and write the macro yourself. For repetitive work with a fixed selection (formatting a header row, entering a fixed text), a recorded macro can be perfectly adequate.

Pitfalls

  • Capitals matter. GetValue works, getValue does not. VBA does not care, JavaScript does.
  • Counting from 0 or 1. Cells(2, 2) in VBA is B2. In Euro-Office that is GetRangeByNumber(1, 1): rows and columns count from 0. GetRange("B2") is often clearer.
  • GetValue returns text, even for an amount: €85 gives you "85". Comparing with a number (> 500) works, but adding does not: "85" + 10 becomes "8510". If you want to calculate, first turn it into a number with Number(...).
  • Entering numbers with decimals. In our test with the region set to Dutch (Netherlands), SetValue(1.25) did not give a number but the text "1.25". If your region uses a decimal comma, pass such a number as text with a comma, just as you type it: SetValue("1,25"), or with String(x).replace(".", ","). Whole numbers work fine.
  • Keep the original. If you save the xlsm file in Euro-Office, the VBA code may become unusable for Excel (see An Excel file with VBA macros: what now?). It is better to put the converted macro in a copy saved as xlsx.
  • Code from the internet or a chatbot often uses method names that do not exist. Always test, and see A macro that doesn't work: finding errors if nothing happens.

What cannot be converted

A macro in Euro-Office only works within the open file. You cannot convert VBA macros that do the following:

  • open, save or print other files (Workbooks.Open, SaveAs, PrintOut);
  • send email via Outlook;
  • read files or folders on your computer;
  • show custom input windows (UserForm) or messages (MsgBox, InputBox).

Keep a macro like that in Excel. Or split it up: the calculations in Euro-Office, the rest by hand or with the features of your WebOké Nextcloud, such as sharing and version history.

One moment…

Set up your domains

For each domain, arrange the nameservers, the link to your hosting and who becomes the owner.

Loading your domains…