Some useful samples of macros

The following example will show you how to use ONLYOFFICE macros and compare the JavaScript code with Microsoft Visual Basic for Applications code so that you could see the difference and understand what can be done to convert the VBA code to ONLYOFFICE macros.

Write data to worksheet cell

In this example we are writing the data (the "Hello world" phrase) to fourth column of the third row of the worksheet

(function()
{
    Api.GetActiveSheet().GetRange("C4").SetValue("Hello world");
})();

Methods used: GetActiveSheet, GetRange, SetValue

Reference Microsoft VBA macro code

Sub example()
    Cells(3, 4)="Hello world"
End Sub

Change cell font and background color and make font bold

In this example we are setting the font to bold, change its color and cell background color

(function()
{
    Api.GetActiveSheet().GetRange("A2").SetBold(true);
    Api.GetActiveSheet().GetRange("B4").SetFontColor(Api.CreateColorFromRGB(255, 0, 0));
    Api.GetActiveSheet().GetRange("B3").SetFillColor(Api.CreateColorFromRGB(0, 0, 250));
})();

Methods used: GetActiveSheet, GetRange, SetBold, SetFontColor, SetFillColor, CreateColorFromRGB

Reference Microsoft VBA macro code

Sub example()
    Range("B4").Font.Color = RGB(255, 0, 0)
    Range("B4").Font.Bold = True
    Range("B3").Interior.Color = RGB(0, 0, 250)
End Sub

Merge and unmerge selected cell range

In this example we are merging one cell range and unmerge the other one

(function()
{
    Api.GetActiveSheet().GetRange("A1:B3").Merge(true);
    Api.GetActiveSheet().GetRange("A1:B3").UnMerge();
})();

Methods used: GetActiveSheet, GetRange, Merge, UnMerge

Reference Microsoft VBA macro code

Sub example()
    Range("A1:B3").Merge
    Range("C5:D10").UnMerge
End Sub

Set column width

In this example we are setting the width for the second ("B") column

(function()
{
    Api.GetActiveSheet().SetColumnWidth(1, 25);
})();

Methods used: GetActiveSheet, SetColumnWidth

Reference Microsoft VBA macro code

Sub example()
    Columns("B").ColumnWidth = 25
End Sub

Format range as table

In this example we are formatting the range of cells as a table

(function()
{
    Api.GetActiveSheet().FormatAsTable("A1:D10");
})();

Methods used: GetActiveSheet, FormatAsTable

Reference Microsoft VBA macro code

Sub example()
    Sheet1.ListObjects.Add(xlSrcRange, Range("A1:D10"), , xlYes).Name = "myTable1"
End Sub

Add new chart for the selected cell range

In this example we are creating the chart from the data in the "C5:D7" cell range

(function()
{
    Api.GetActiveSheet().AddChart("'Sheet1'!$C$5:$D$7", true, "bar", 2, 105 * 36000, 105 * 36000, 5, 2 * 36000, 1, 3 * 36000);
})();

Methods used: GetActiveSheet, AddChart

Reference Microsoft VBA macro code

Sub example()
    With ActiveSheet.ChartObjects.Add(Left:=300, Width:=300, Top:=10, Height:=300)
        .Chart.SetSourceData Source:=Sheets("Sheet1").Range("C5:D7")
    End With
End Sub