Our users asked us how to recreate some VBA macros they were using in Excel. In this post, we’ll show how to do it on two simple examples.
ONLYOFFICE macros use JavaScript language syntax and ONLYOFFICE Document Builder API scripting notation. This means that you won’t be able to use VBA macros until you recreate them using JavaScript.
We chose to use JavaScript for our macros because:
- It is cross-platform,
- it is really easy to use,
- you can use the code you wrote as a macro or as a plugin.
Also, our macros are safe because they do not have any access to the system. They are just JS code that runs in the same window with the editors.
- In spreadsheet editor, open the Plugins tab and click Macros.
- Click “New” in the window that appeared.
- Consult API documentation to write the script.
Let’s write a macro to highlight duplicates in the selected area with different colors to recognize duplicated values quickly. We’ll use this simple VBA macro as a base.
Here is the resulting script with comments for you to understand how it’s done. The colors are determined in macro code.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 |
(function () { // Background color of cells with non-repeating values var whiteFill = Api.CreateColorFromRGB(255, 255, 255); // The current index of the color range var uniqueColorIndex = 0; // Color range to highlight duplicate values var uniqueColors = [Api.CreateColorFromRGB(255, 255, 0), Api.CreateColorFromRGB(204, 204, 255), Api.CreateColorFromRGB(0, 255, 0), Api.CreateColorFromRGB(0, 128, 128), Api.CreateColorFromRGB(192, 192, 192), Api.CreateColorFromRGB(255, 204, 0)]; // Function to get color for duplicates function getColor() { // If you have chosen all the unique colors, then let's go from the beginning if (uniqueColorIndex === uniqueColors.length) { uniqueColorIndex = 0; } return uniqueColors[uniqueColorIndex++]; } // Getting an active sheet var activeSheet = Api.ActiveSheet; // Getting selection on the active sheet var selection = activeSheet.Selection; // Map of values in cells with the duplicates number var mapValues = {}; // All cells range var arrRanges = []; // Going through the selection selection.ForEach(function (range) { // Getting value from cell var value = range.GetValue(); if (!mapValues.hasOwnProperty(value)) { mapValues[value] = 0; } mapValues[value] += 1; arrRanges.push(range); }); var value; var mapColors = {}; // We go through all the cells of the selection and setting the highlighting if this value is repeated more than 1 time for (var i = 0; i < arrRanges.length; ++i) { value = arrRanges[i].GetValue(); if (mapValues[value] > 1) { if (!mapColors.hasOwnProperty(value)) { mapColors[value] = getColor(); } arrRanges[i].SetFillColor(mapColors[value]); } else { arrRanges[i].SetFillColor(whiteFill); } } })(); |
Here’s what we get with this macro:
This one finds the next available blank row in a worksheet. We took this VBA macro as an example and created this script:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
(function() { // Getting the active sheet var activeSheet = Api.ActiveSheet; // Minimum row index var indexRowMin = 0; // Maximum row index var indexRowMax = 1048576; // Column 'A' var indexCol = 0; // Row index for empty cell search var indexRow = indexRowMax; for (; indexRow >= indexRowMin; --indexRow) { // Getting the cell var range = activeSheet.GetRangeByNumber(indexRow, indexCol); // Checking the value if (range.GetValue() && indexRow !== indexRowMax) { range = activeSheet.GetRangeByNumber(indexRow + 1, indexCol); range.Select(); break; } } })(); |
This code will help you select the next empty row (we’ll bring scrolling in one of the next versions).
Note that the macro will skip any blanks that are between your data but not at the very end of it.
If you need help with writing macros or the method you need is absent from our API, post an issue on GitHub.
ONLYOFFICE on GitHub
Article on starting writing plugins. Read now>>
Plugin example: fill the cells with color and turn your worksheet into an image. Check it out>>
