Suddenly: a new version of ONLYOFFICE with macros

    We have important news: a new version of desktops with macros has been released. You can download it on the official website and try everything yourself.

    In this article we will tell you what macros we have, how they differ from Microsoft macros and how to work with them.



    How are macros made with us?


    A macro is a script with which you can automate routine operations and save a whole bunch of time. Microsoft itself came up with the concept, so these scripts are intended for Microsoft Office and run on Windows.

    “When will you make the macros?” - This is a very popular question. At first we answered: “Never. But we can do the same with plugins ”(and did).

    So, over the year our plug-in system has become much cooler (and more fully documented) Now we ourselves add new functionality using plugins. For example, Symbol Table: special symbols are needed in document editors, and adding them through plugins is many times faster. In addition, we already had a ready SDK for generating and processing documents, spreadsheets and presentations - ONLYOFFICE Document Builder.

    In general, when it came to macros once again, we realized that everything worked out for us. And it turned out pretty nicely. We take Document Builder, wrap it in a plugin and forward it to the interface. Now we have the ability to create and run macros!



    Now macros can do everything that Builder can do. The documentation for it can be found here . We are already writing separate documentation on macros.

    Our macros are not like those of Microsoft


    If you saw our plugins, then you must guess what it is right away. That you will write not in Visual Basic, but in JavaScript.

    “Why not Visual Basic, but what about compatibility ?!”

    We anticipate this question and answer it in advance. Firstly, VBA is only for Windows, and we have all the platforms here. If Microsoft had some kind of scripting language, we would gladly support it. But it's been a million years to write a compiler of a very old language and as a result to get macros (the same as MS) is not a dream task. We prefer to improve editors at this time. In addition, everything is ready with js.

    This is cool, because:

    - It will work with all platforms;
    - Really very simple;
    - By writing a smart script, you can use it both as a plugin and as a macro. Well, of course, as a script for Document Builder, of course.

    About the last point: a macro is, in fact, a special case of the plugin. Simply installed plugins work for all documents (that is, are tied to editors), and macros - only for a specific document (that is, are tied to a file).

    So what about compatibility?


    Yes, macros from Microsoft so simply will not open with us. At the same time, we understand that many already have entire macro libraries that they would like to run in an alternative office.

    For now, we can offer the simplest option: rewrite macros written in VBA a bit. We understand, perhaps this will sound like a headache, but in fact it is completely uncomplicated.

    Here is an example of filling several cells with data (range summation). The first is a variant of MS with Visual Basic, the second is ours.

    First example:

    Sub Example()
        Dim myRange
        Dim result
        Dim Run As Long
        For Run = 1 To 3
            Select Case Run
            Case 1
                result = "=SUM(A1:A100)"
            Case 2
                result = "=SUM(A1:A300)"
            Case 3
                result = "=SUM(A1:A25)"
            End Select
            ActiveSheet.range("B" & Run) = result
        Next Run
    End Sub


    Second:

    (function()
    {
        for (let run = 1; run <= 3; run++)
        {
            var result = "";
            switch (run)
            {
                case 1:
                    result = "=SUM(A1:A100)";
                    break;
                case 2:
                    result = "=SUM(A1:A300)";
                    break;
                case 3:
                    result = "=SUM(A1:A25)";
                    break;
                default:
                    break;
            }
            Api.GetActiveSheet().GetRange("B" + run).Value = result;
        }
    })();


    As you can see, this is pretty simple.

    And now a cool example:

    Here we have a long script
    (function()
    {
    var oSheet = Api.GetActiveSheet();
    oSheet.SetName('Medal Number');
    oSheet.SetColumnWidth(0, 7.57);
    oSheet.SetColumnWidth(1, 12.43);
    oSheet.SetColumnWidth(2, 32.50);
    oSheet.SetColumnWidth(3, 13.86);
    oSheet.SetColumnWidth(4, 13.86);
    oSheet.SetColumnWidth(5, 13.86);
    oSheet.SetColumnWidth(6, 13.86);
    var range = oSheet.GetRange('C1');
    range.SetFontSize(56);
    range = oSheet.GetRange('B2:G29');
    range.SetFontName('Calibri');
    range.SetFontSize(13);
    range.SetFontColor(Api.CreateColorFromRGB(0, 0, 0));
    range.SetAlignHorizontal('center');
    oSheet.GetRange('B2').SetValue('Rank');
    oSheet.GetRange('C2').SetValue('Country');
    oSheet.GetRange('D2').SetValue('Gold');
    oSheet.GetRange('E2').SetValue('Silver');
    oSheet.GetRange('F2').SetValue('Bronze');
    oSheet.GetRange('G2').SetValue('Total');
    for (var nCell = 0; nCell < 25; ++nCell)
    {
    oValue = nCell + 1;
    oCellNumber = nCell + 3;
    oSheet.GetRange('B' + oCellNumber.toString()).SetValue(oValue.toString());
    }
    oSheet.GetRange('C3:C27').SetAlignHorizontal('left');
    oSheet.GetRange('C3').SetValue('USA');
    oSheet.GetRange('C4').SetValue('China');
    oSheet.GetRange('C5').SetValue('Great Britain');
    oSheet.GetRange('C6').SetValue('Russia');
    oSheet.GetRange('C7').SetValue('Germany');
    oSheet.GetRange('C8').SetValue('Japan');
    oSheet.GetRange('C9').SetValue('France');
    oSheet.GetRange('C10').SetValue('South Korea');
    oSheet.GetRange('C11').SetValue('Italy');
    oSheet.GetRange('C12').SetValue('Australia');
    oSheet.GetRange('C13').SetValue('Netherlands');
    oSheet.GetRange('C14').SetValue('Hungary');
    oSheet.GetRange('C15').SetValue('Brazil');
    oSheet.GetRange('C16').SetValue('Spain');
    oSheet.GetRange('C17').SetValue('Kenya');
    oSheet.GetRange('C18').SetValue('Jamaica');
    oSheet.GetRange('C19').SetValue('Croatia');
    oSheet.GetRange('C20').SetValue('Cuba');
    oSheet.GetRange('C21').SetValue('New Zealand');
    oSheet.GetRange('C22').SetValue('Canada');
    oSheet.GetRange('C23').SetValue('Uzbekistan');
    oSheet.GetRange('C24').SetValue('Kazakhstan');
    oSheet.GetRange('C25').SetValue('Colombia');
    oSheet.GetRange('C26').SetValue('Switzerland');
    oSheet.GetRange('C27').SetValue('Iran');
    oSheet.GetRange('D3').SetValue('46');
    oSheet.GetRange('D4').SetValue('27');
    oSheet.GetRange('D5').SetValue('26');
    oSheet.GetRange('D6').SetValue('19');
    oSheet.GetRange('D7').SetValue('17');
    oSheet.GetRange('D8').SetValue('12');
    oSheet.GetRange('D9').SetValue('10');
    oSheet.GetRange('D10').SetValue('9');
    oSheet.GetRange('D11').SetValue('8');
    oSheet.GetRange('D12').SetValue('8');
    oSheet.GetRange('D13').SetValue('8');
    oSheet.GetRange('D14').SetValue('8');
    oSheet.GetRange('D15').SetValue('7');
    oSheet.GetRange('D16').SetValue('7');
    oSheet.GetRange('D17').SetValue('6');
    oSheet.GetRange('D18').SetValue('6');
    oSheet.GetRange('D19').SetValue('5');
    oSheet.GetRange('D20').SetValue('5');
    oSheet.GetRange('D21').SetValue('4');
    oSheet.GetRange('D22').SetValue('4');
    oSheet.GetRange('D23').SetValue('4');
    oSheet.GetRange('D24').SetValue('3');
    oSheet.GetRange('D25').SetValue('3');
    oSheet.GetRange('D26').SetValue('3');
    oSheet.GetRange('D27').SetValue('3');
    oSheet.GetRange('E3').SetValue('37');
    oSheet.GetRange('E4').SetValue('23');
    oSheet.GetRange('E5').SetValue('18');
    oSheet.GetRange('E6').SetValue('18');
    oSheet.GetRange('E7').SetValue('10');
    oSheet.GetRange('E8').SetValue('8');
    oSheet.GetRange('E9').SetValue('18');
    oSheet.GetRange('E10').SetValue('3');
    oSheet.GetRange('E11').SetValue('12');
    oSheet.GetRange('E12').SetValue('11');
    oSheet.GetRange('E13').SetValue('7');
    oSheet.GetRange('E14').SetValue('3');
    oSheet.GetRange('E15').SetValue('6');
    oSheet.GetRange('E16').SetValue('4');
    oSheet.GetRange('E17').SetValue('6');
    oSheet.GetRange('E18').SetValue('3');
    oSheet.GetRange('E19').SetValue('3');
    oSheet.GetRange('E20').SetValue('2');
    oSheet.GetRange('E21').SetValue('9');
    oSheet.GetRange('E22').SetValue('3');
    oSheet.GetRange('E23').SetValue('2');
    oSheet.GetRange('E24').SetValue('5');
    oSheet.GetRange('E25').SetValue('2');
    oSheet.GetRange('E26').SetValue('2');
    oSheet.GetRange('E27').SetValue('1');
    oSheet.GetRange('F3').SetValue('38');
    oSheet.GetRange('F4').SetValue('17');
    oSheet.GetRange('F5').SetValue('26');
    oSheet.GetRange('F6').SetValue('19');
    oSheet.GetRange('F7').SetValue('15');
    oSheet.GetRange('F8').SetValue('21');
    oSheet.GetRange('F9').SetValue('14');
    oSheet.GetRange('F10').SetValue('9');
    oSheet.GetRange('F11').SetValue('8');
    oSheet.GetRange('F12').SetValue('10');
    oSheet.GetRange('F13').SetValue('4');
    oSheet.GetRange('F14').SetValue('4');
    oSheet.GetRange('F15').SetValue('6');
    oSheet.GetRange('F16').SetValue('6');
    oSheet.GetRange('F17').SetValue('1');
    oSheet.GetRange('F18').SetValue('2');
    oSheet.GetRange('F19').SetValue('2');
    oSheet.GetRange('F20').SetValue('4');
    oSheet.GetRange('F21').SetValue('5');
    oSheet.GetRange('F22').SetValue('15');
    oSheet.GetRange('F23').SetValue('7');
    oSheet.GetRange('F24').SetValue('9');
    oSheet.GetRange('F25').SetValue('3');
    oSheet.GetRange('F26').SetValue('2');
    oSheet.GetRange('F27').SetValue('4');
    for (var nCell = 0; nCell < 25; ++nCell)
    {
    oCellNumber = nCell + 3;
    oSheet.GetRange('G' + oCellNumber.toString()).SetValue('=SUM(D' + oCellNumber.toString() + ':F' + oCellNumber.toString() + ')');
    }
    oSheet.GetRange('C29').SetValue('Total:');
    oSheet.GetRange('C29').SetAlignHorizontal('right');
    oSheet.GetRange('D29').SetValue('=SUM(D3:D27)');
    oSheet.GetRange('E29').SetValue('=SUM(E3:E27)');
    oSheet.GetRange('F29').SetValue('=SUM(F3:F27)');
    oSheet.GetRange('G29').SetValue('=SUM(G3:G27)');
    oSheet.GetRange('D29:F29').SetFontColor(Api.CreateColorFromRGB(67, 67, 67));
    oSheet.GetRange('G29').SetFontColor(Api.CreateColorFromRGB(49, 133, 154));
    oSheet.GetRange('C29:G29').SetFontSize(14);
    oSheet.FormatAsTable('B2:G29');
    var oChart = oSheet.AddChart("'Medal Number'!$C$2:$F$27", false, 'barStacked3D', 18, 8, 1, 16, 14);
    oChart.SetVerAxisTitle("Medals", 10);
    oChart.SetHorAxisTitle("Countries", 10);
    oChart.SetLegendPos("right");
    oChart.SetShowDataLabels(false, false, false);
    oChart.SetTitle("Total Medal Count", 18);
    var oChart2 = oSheet.AddChart("'Medal Number'!$C$2:$E$12", false, 'lineStacked', 2, 8, 15, 16, 27);
    oChart2.SetVerAxisTitle("Medals", 10);
    oChart2.SetHorAxisTitle("Top 10 Countries", 10);
    oChart2.SetLegendPos("right");
    oChart2.SetShowDataLabels(false, false, false);
    oChart2.SetTitle("Gold&Silver Medals Count", 18);
    })();


    As a result, you should get such beauty here:

    image

    you can download the ONLYOFFICE desktop application and try everything. By the way, macros are not the only innovation of the released version. We fixed a lot of things, added support for SSO, new interface languages ​​(Czech and Slovak). Details of the new version on GitHub .

    It's all. We are waiting for your questions, suggestions, wishes and thoughts. If you have interesting files with macros that you can share, send them to files@onlyoffice.com . If you have files without macros, but with interesting problems and errors, we are waiting for them too.

    Also popular now: