new version ONLYOFFICE with macros / Blog company ONLYOFFICE / Habrahabr

January 11, 2018
113 Views
We have important news: a new version of desktops with macros. You can download it on the official website and try everything yourself.

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


A macro is a script with which you can automate routine operations and save a whole bunch of time. The very concept was invented by Microsoft, so these scripts are intended for Microsoft Office and run on Windows.

"When will you make macros?" Is a very popular question. At first we said: "Never. But we can do the same with plugins "(and did).

So, for a year our system of plug-ins has become much steeper (and more fully documented). Now we add new functionality by using plugins. For example, Symbol Table: in document editors you need special characters, and add them through plug-ins 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 speech once again came about macros, we realized that we had everything. And it turned out pretty beautiful. We take Document Builder, wrap it in a plugin and throw it into the interface. Now we have the ability to create and run macros!

Now macros know how to do everything Builder can do. The documentation for it can be found here. We already write separate documentation on macros.

If you saw our plugins, you should guess what it is, right away. The fact that you will not write in Visual Basic, but in JavaScript.

"Why not Visual Basic, but what about compatibility?"

We foresee this question and answer it in advance. First, 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 for a million years to write a very old language compiler and as a result to get macros (same as MS) is not a dream job. At this time, we prefer to improve the editors. In addition, everything is ready with js.

It's cool, because:

– Will work with all platforms
 – The reality is very simple;
 – By writing a clever script, you can use it both as a plugin and as a macro. Well, it goes without saying as a script for Document Builder, of course.

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

Yes, macros from Microsoft are not so easy to open with us. At the same time, we understand that many already have entire libraries of macros that I would like to run in an alternative office.

While we can offer the easiest option: a little rewrite macros written in VBA. We understand, it may sound like a headache, but in fact it is not difficult.

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

The 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, it's 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 have this kind of beauty:

 image

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

That'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.

Leave a Comment

Your email address will not be published.