Macros

Product: MyOffice Professional, MyOffice Standard, MyOffice Education

Application: Spreadsheet Desktop version

A macro is a set of actions that automate repeated or time-consuming tasks in a document. MyOffice Spreadsheet macros are written in Lua programming language.

Macros are most commonly used for the following purposes:

•Add a formula in a cell.

•Add text in a cell.

•Find and replace items throughout the text.

•Delete text.

Saving macros is only available within the current spreadsheet. To use a macro in other spreadsheets, create them directly in these spreadsheets.

Create a macro

You can create a macro in one of the following ways:

1.Record a sequence of actions that a macro command should consist of.

2.Enter the macro script manually.

To record a macro, do the following:

1.In the Command menu, select Tools > Macros > Record Macro (beta).

2.Execute the sequence of actions that a macro command should consist of.

In the current version of the application, not all user actions are available for recording. When you perform an action that cannot be recorded, the message “Action cannot be recorded” pops up on the screen.

3.Finish the recording process in one of the following ways:

•In the Command menu, select Tools > Macros > Stop Recording.

•In the Status bar, click status_bar_macro_stop_recording Stop Recording.

4.In the opened dialog box, specify the macro name and click OK.

If the macros has been successfully saved, the following pop-up message will be displayed on the screen: “"Macro_name" macro saved”.

To enter the macro script manually, do the following:

1.In the Command menu, select Tools > Macros > Macro Editor.

2.In the Macro Editor window, click macros_add_script_icon .

3.Enter the macro name or keep the default name. To keep the name, press Enter or click anywhere on the macro editor area with the mouse.

4.Enter the macro script.

The script will be saved automatically.

When a macro has been added to a document, the Sidebar will display side_panel_macro Macros. Clicking this button opens the macros pane where you can perform the following actions:

•Find a macro

•Run a macro

•Open the Macro Editor

To close the macros pane, click the side_panel_macro_close button above the search line, or click Macros on the Sidebar once again.

The side_panel_macro Macros button is displayed on the Sidebar as long as the document contains at least one macros.

Find a macro

If the document contains a large number of macro commands, perform the following steps to quickly find the desired macro command:

1.On the Sidebar, click side_panel_macro Macros.

2.In the macro commands pane, specify a part of the name or the whole name of the required macro command in the search line.

The list of macro commands will display macro commands whose names satisfy the specified search results.

Run a macro

You can run a macro using the Macro Editor or the macros pane.

To run a macro using the Macro Editor, follow the steps below:

1.In the Tools menu, select Macros > Macro Editor.

2.In the opened Macro Editor window, select the macro from the list.

3.Click Run.

The results are displayed in the Output pane.

To run a macro using the macros pane, do the following:

1.On the Sidebar, click side_panel_macro Macros.

2.Execute the macro in one of the following ways:

•In the macros pane, hover the mouse cursor over the macro command name and click clip0001 Run.

•Select the macro line by double-clicking it.

Edit a macro

1.Open the Macro Editor in one of the following ways:

•In the Tools menu, select Macros > Macro Editor.

•On the Sidebar, click side_panel_macro Macros. On the macros pane, place the cursor over the title of the macro that you want to edit and click clip0003 Edit.

2.In the opened Edit Macros window, select the macro from the list.

3.Edit the macro script as needed.

All changes made to the script are saved automatically.

Debug a macro

Before you start the macro debugger, set the debugger breakpoints in the macro script. To do this:

1.Open the Macro Editor in one of the following ways:

•In the Command menu, click Tools > Macros > Macro Editor.

•On the Sidebar, click clip0002 Macros. In the macros pane, place the cursor over the title of the macro that you want to edit and click clip0003 Edit.

2.In the Macros Editor window, select the desired macro from the list.

3.Set the first debugger breakpoint. To do this, click to the right of the line number where you want to create a breakpoint. The breakpoint will be marked with the macros_breakpoint icon.

4.Set other breakpoints by repeating these actions.

To delete a breakpoint, click it with the mouse.

To debug a macro:

1.Click macros_debug Debug. The macro debugging process will begin. If the script of a macro contains breakpoints, the debugging process will stop on the line that contains the first breakpoint. If there are no breakpoints, the debugging process will stop at the first line of the macro.

2.To manage the debugging steps, use the following buttons:

macros_step_into Step Into: Perform one debugging step or step into the body of the function, if there is one in the current debugging position.

macros_step_over Step Over: Perform one debugging step without stepping into the body of the function.

macros_step_out Step Out: Continue executing the macro until leaving the function where the debugger is at the current position.

While debugging, the Macros Editor window displays the following areas:

•In progress: This window will display messages while debugging.

•Calls Stack: The calls stack window.

•Variables: This window displays the values of local and global variables available at the current macro step. If the displayed variable is a table or an array, you can view its detailed contents by clicking macros_variable to the left of the variable name.

The debugging process ends once the end of the macro has been reached.

To interrupt the debugging process, click macros_stop Break script.

Delete a macro

1.Open the Macro Editor widow in one of the following ways:

•In the Command menu, click Tools > Macros > Macro Editor.

•On the Sidebar, click clip0002 Macros. In the macros pane, place the cursor over the title of the macro that you want to edit and click clip0003 Edit.

2.In the opened Macros Editor window, select the macro from the list.

3.Click macros_delete_script_icon .

4.In the opened window, confirm that you want to delete the macro.

View VBA macro code

In MyOffice Spreadsheet, running VBA macros created in Microsoft Excel is not possible. However, you can view the code of the VBA macros contained in the document and rewrite them in Lua.

VBA macros saved in the “Current sheet” are available for viewing.

To view and rewrite the VBA macro code, follow these steps:

1.Open the .xlsm or .xlsb file.

2.Select the Lua macro with the name of the VBA module that contains the desired VBA macro. For example, if Macro1 VBA macro is in Module1 VBA module, select Module1 Lua macro. Do one of the following to select the Lua macro:

•In the Command menu, select Tools > Macros > Macro Editor. In the Macro Editor window, select the desired Lua macro.

•On the Sidebar, click side_panel_macro Macros. On the macro pane, hover the mouse over the name of the desired Lua macro and click side_panel_macro_edit Edit.

The Macro Editor window will display the code of VBA macros that are contained in the corresponding VBA module.

3.Rewrite the code of the required VBA macro in Lua.

4.Close the Macro Editor window.

5.Save the document in .xlsx format using the Save as command.

The changes made are not saved in the original .xlsm or .xlsb file.

Was this helpful?

Yes