20. peatükk · 20.4
VBA-makro ise JavaScripti teisendamine: tõlketabel
Artiklist Excel-fail VBA-makrodega: mida teha? saad teada, et Euro-Office ei käivita VBA-makrosid. Fail küll avaneb, kuid nupud ja automaatsed toimingud ei tee midagi. Kui makro on oluline, teisenda see JavaScripti, sest selles keeles kirjutatakse Euro-Office'i makrod. See kõlab keerulisemalt, kui tegelikult on: enamik Exceli makrosid teeb lahtritega paar lihtsat asja ja peaaegu igal VBA-real on kindel JavaScripti vaste. Siit leiad tõlketabeli ja läbitöötatud näite.
Kõigepealt: automaatselt teisendada ei saa
Aknas Macros (makrod) näed nupu Macros kõrval võlukepiga nuppu. Selle all on Create from description (loo kirjelduse põhjal) ja Convert from VBA (teisenda VBA-st). Need on hallid: need töötavad ainult pistikprogrammiga, aga WebOké Euro-Office töötab teadlikult ilma pistikprogrammideta (vt Pluginad Euro-Office'is: miks WebOké neid ei paku ja mida teha selle asemel). Seega teisendad makro ise, allpool oleva tabeli abil.

Töövoog kolmes sammus
- Kirjuta tavakeeles üles, mida makro teeb. Ava xlsm-fail Excelis, vajuta Alt+F11 ja loe koodi. Näiteks: "käi läbi read 2 kuni 10; kui summa veerus D on üle 500, tee rida kollaseks ja summa paksuks".
- Tõlgi rida-realt allpool oleva tabeli abil.
- Testi koopia peal Euro-Office'is: vahekaart View (vaade), Macros, plussmärk, kleebi kood, Run (käivita). Kui see ei tööta, vaata artiklit Makro ei tööta: vigade otsimine.
Tõlketabel
Iga allpool olevat rida oleme Euro-Office'is testinud. ws tähistab töölehte: kirjuta makro algusse const ws = Api.GetActiveSheet();.
| VBA | Euro-Office (JavaScript) |
|---|---|
| Sub Nimi() ... End Sub | (function () { ... })(); |
| ActiveSheet | Api.GetActiveSheet() |
| Worksheets("Arhiiv") | Api.GetSheet("Arhiiv") |
| Range("A1").Value = 5 | ws.GetRange("A1").SetValue(5); |
| x = Range("A1").Value | let x = ws.GetRange("A1").GetValue(); |
| Cells(2, 2) | ws.GetRangeByNumber(1, 1) (loendamine algab 0-st) |
| Selection | Api.GetSelection() |
| For Each c In Range("D2:D10") ... Next | ws.GetRange("D2:D10").ForEach(function (c) { ... }); |
| For r = 2 To 10 ... Next r | for (let r = 2; r <= 10; r++) { ... } |
| If ... Then ... Else ... End If | if (...) { ... } else { ... } |
| Select Case x ... Case "Valmis" | switch (x) { case "Valmis": ...; break; } |
| a = b And c, a = b Or c, Not a | a = b && c, a = b || c, !a |
| "Rida " & r | "Rida " + r |
| x <> "" | x !== "" |
| Interior.Color = RGB(255, 235, 156) | SetFillColor(Api.CreateColorFromRGB(255, 235, 156)) |
| Font.Bold = True | SetBold(true) |
| Rows(5).Delete | ws.GetRows(5).Delete("up") |
| Range("A2:E2").Copy Range("A12") | ws.GetRange("A2:E2").Copy(ws.GetRange("A12")) |
| Workbook_Open | makro valikuga Autostart (vt Kuupäev ja kellaaeg automaatselt lahtrisse, kui keegi lahtrit muudab) |
| Worksheet_Change | Api.attachEvent("onWorksheetChange", ...) (vt Kuupäev ja kellaaeg automaatselt lahtrisse, kui keegi lahtrit muudab) |
| Function KM(x) valemite jaoks | custom function (kohandatud funktsioon, vt Exceli valemid LET või LAMBDA abil: mida Euro-Office'is selle asemel teha) |
| MsgBox "Valmis" | puudub; kirjuta teade lahtrisse (vt Makro ei tööta: vigade otsimine) |
Näide: suurte tellimuste esiletõstmine
Lillepoel Bloemisterij Het Zonnetje oli Excelis selline makro, et suured tellimused silma paistaksid:
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
Euro-Office'i jaoks teisendatuna näeb see välja nii:
(function () {
// Teisendatud VBA-st: üle 500 euro tellimused kollaseks ja summa paksuks.
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);
}
}
})();

Mis muutus: Sub sai sulgudes funktsiooniks, Cells(r, 4) sai GetRange("D" + r) (veerg 4 on D), teksti kokkuliitmise & sai + ning Interior.Color ja Font.Bold said SetFillColor ja SetBold. Iga rida lõpeb semikooloniga ja plokid on looksulgudes, mitte Then ja End If vahel.

Spikker: Record macro
Ei tea, kuidas mõnda toimingut JavaScriptis nimetatakse? Salvesta see. Klõpsa vahekaardil View nuppu Record macro (salvesta makro). Seejärel on nupu nimi Stop recording. Tee oma toimingud, klõpsa Stop recording ja ava Macros: loendis on uus makro sinu tehtu koodiga.

Oma testis klõpsasime lahtrit D5 ja tegime selle paksuks, kirjutasime lahtrisse F1 "Gecontroleerd", kopeerisime A2 lahtrisse A12 ning tegime seejärel C3 ja C4 kaldkirja. Euro-Office salvestas järgmise:
(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);
})();

Näed kohe vajalikke nimesid: Select, SetBold, SetValue, SetItalic. Kuid näed ka salvestamise piire:
- Kopeerimist ja kleepimist ei salvestata: A2-st A12-sse liikumisest näed vaid kaht Select-käsku.
- Valimist nimevälja kaudu (üleval vasakul, kuhu kirjutad C3:C4) ei salvestata. Seetõttu on C3:C4 kaldkirja tegemine salvestuses seotud lahtriga A12, viimase lahtriga, mida hiirega klõpsasime. Salvestamise ajal klõpsa lahtreid seega hiirega.
- Tsükleid ega tingimusi salvestusse ei tule. "Kõik read, kus on Valmis" salvestada ei saa.
Kasuta salvestamist seega nimede otsimiseks ja kirjuta makro ise. Kindla valikuga korduvtöö jaoks (päiserea vormindamine, kindla teksti sisestamine) võib salvestatud makrost siiski täiesti piisata.
Lõksud
- Suurtähed on olulised. GetValue töötab, getValue mitte. VBA-le pole see oluline, JavaScriptile on.
- Loendamine 0-st või 1-st. Cells(2, 2) on VBA-s B2. Euro-Office'is on see GetRangeByNumber(1, 1): rida ja veerg loetakse alates 0-st. GetRange("B2") on sageli selgem.
- GetValue tagastab teksti, ka summa puhul: 85 eurost saad "85". Võrdlemine arvuga (> 500) toimib, aga liitmine mitte: "85" + 10 annab "8510". Kui tahad arvutada, tee sellest kõigepealt arv funktsiooniga Number(...).
- Kümnendkohtadega arvude sisestamine. SetValue(1.25) andis meie testis regiooniga, kus kümnendmärk on koma (nagu Eestis), arvu asemel teksti "1.25". Anna selline arv edasi tekstina komaga, täpselt nagu seda trükid: SetValue("1,25") või String(x).replace(".", ","). Täisarvudega probleeme pole.
- Hoia originaal alles. Kui salvestad xlsm-faili Euro-Office'is, võib VBA-kood Exceli jaoks kasutuskõlbmatuks muutuda (vt Excel-fail VBA-makrodega: mida teha?). Pane teisendatud makro pigem koopiasse xlsx-vormingus.
- Internetist või vestlusrobotilt pärit kood kasutab sageli meetodinimesid, mida pole olemas. Testi alati ja vaata artiklit Makro ei tööta: vigade otsimine, kui midagi ei juhtu.
Mida teisendada ei saa
Euro-Office'i makro töötab ainult avatud faili piires. VBA-makrosid, mis teevad järgmist, teisendada ei saa:
- teiste failide avamine, salvestamine või printimine (Workbooks.Open, SaveAs, PrintOut);
- e-kirjade saatmine Outlooki kaudu;
- arvutis olevate failide või kaustade lugemine;
- oma sisestusakende (UserForm) või teadete (MsgBox, InputBox) kuvamine.
Sellise makro jätad Excelisse. Või jaotad selle osadeks: arvutused Euro-Office'is, ülejäänu käsitsi või oma WebOké Nextcloud funktsioonidega, nagu jagamine ja versiooniajalugu.
