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.
A macro is a script with which you can automate routine operations and save a whole lot of time. The concept came up with the company Microsoft, so these scripts are designed for Microsoft Office and run on Windows.
')
“When will you make macros?” 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 plugin system has become much cooler (and more documented ). Now we add new functionality using plugins. For example, Symbol Table: special characters are needed in document editors, and adding them through plug-ins is much faster. In addition, we already had a ready SDK for generating and processing documents, spreadsheets and presentations - ONLYOFFICE Document Builder.
In general, when we once again talked about macros, we realized that everything worked out for us. And it turned out pretty nice. We take the 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. Documentation for it can be found here . Separate macros documentation is already writing.
If you saw our plugins, you should guess what it is, right away. That you will write not on Visual Basic, but on 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 a scripting language, we would be happy to support it. But for a million years to write a very old language compiler, and as a result, getting macros (the same as MS) is not a dream task. We will prefer the time to perfect the editors. Besides, everything is ready with js.
This is great because:
- Will work with all platforms;
- It is really very simple;
- Having written an intelligent script, you can use it both as a plugin and as a macro. Well, of course, as a script for the Document Builder, of course.
About the last point: the macro is, in fact, a special case of the plugin. Simply installed plug-ins work for all documents (that is, are attached to editors), and macros only for a specific document (that is, 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 we would like to run in an alternative office.
For now, we can offer the simplest option: rewrite the macros written in VBA a bit. We understand, perhaps it will sound like a headache, but in fact it is not difficult at all.
Here is an example of filling several cells with data (summation of a range). The first is an MS version with Visual Basic, the second is ours.
First example:
Second:
As you can see, it's pretty simple.
And now a cool example:
As a result, you should have this beauty:
You can download the ONLYOFFICE desktop application 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). Detailed information about the new version on GitHub .
It's all. We are waiting for your questions, suggestions, suggestions and thoughts. If you have interesting files with macros that you can share, send to files@onlyoffice.com . If you have files without macros, but with interesting problems and errors, we are waiting for them too.
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 we made macros?
A macro is a script with which you can automate routine operations and save a whole lot of time. The concept came up with the company Microsoft, so these scripts are designed for Microsoft Office and run on Windows.
')
“When will you make macros?” 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 plugin system has become much cooler (and more documented ). Now we add new functionality using plugins. For example, Symbol Table: special characters are needed in document editors, and adding them through plug-ins is much faster. In addition, we already had a ready SDK for generating and processing documents, spreadsheets and presentations - ONLYOFFICE Document Builder.
In general, when we once again talked about macros, we realized that everything worked out for us. And it turned out pretty nice. We take the 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. Documentation for it can be found here . Separate macros documentation is already writing.
Our macros are not like Microsoft.
If you saw our plugins, you should guess what it is, right away. That you will write not on Visual Basic, but on 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 a scripting language, we would be happy to support it. But for a million years to write a very old language compiler, and as a result, getting macros (the same as MS) is not a dream task. We will prefer the time to perfect the editors. Besides, everything is ready with js.
This is great because:
- Will work with all platforms;
- It is really very simple;
- Having written an intelligent script, you can use it both as a plugin and as a macro. Well, of course, as a script for the Document Builder, of course.
About the last point: the macro is, in fact, a special case of the plugin. Simply installed plug-ins work for all documents (that is, are attached to editors), and macros only for a specific document (that is, are attached to a file).
So what's up with compatibility?
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 we would like to run in an alternative office.
For now, we can offer the simplest option: rewrite the macros written in VBA a bit. We understand, perhaps it will sound like a headache, but in fact it is not difficult at all.
Here is an example of filling several cells with data (summation of a range). The first is an MS version 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, 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 beauty:
You can download the ONLYOFFICE desktop application 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). Detailed information about the new version on GitHub .
It's all. We are waiting for your questions, suggestions, suggestions and thoughts. If you have interesting files with macros that you can share, send to files@onlyoffice.com . If you have files without macros, but with interesting problems and errors, we are waiting for them too.
Source: https://habr.com/ru/post/346382/
All Articles