Macro Examples

image_pdfimage_print
Hide/unhide row

In the example below, the two simple functions hide() and unhide() are shown. The hide() function sets the value in A1 to TRUE, while the unhide() function sets the value in A1 to FALSE.

These macros have been assigned to the buttons “Hide row” and “Unhide row”, as shown in the following screenshot:The HIDEROW(A1) function in A3 reacts to the value in A1, either hiding or unhiding row 3.

setCell () Function 

The function setCell () defines C8 as the target cell. If the target cell contains the value “locked”, then the content is deleted. If nothing is in C8, the value “locked” is written into the cell.

function setCell() {
$range = activesheet()->range('C8');
if ($range->value == 'locked') {
$newVal = '';
} else {
$newVal = 'locked';
}
$range->value=$newVal;
}

 
getcell () Function 

Getcell() gets the value from Sheet2!B2 and writes it into cell C10 of the active sheet.

function getcell(){
$value = activeworkbook()->sheets('Sheet2')->Range('B2')->value;
$target = activesheet()->range('C10');
$target->value=$value;
}

 
Export workbook to hard drive

The following code exports the workbook to the server’s hard drive.

function exportfile() {
// save the current workbook as xlsx
 activeworkbook()->export('C:\\dump.xlsx', 'xlsx', 0);
// save the current workbook as full xlsx snapshot
 activeworkbook()->export('C:\\dump_snapshot.xlsx', 'xlsx', 1);
// save the current workbook as wss
 activeworkbook()->export('C:\\dump.wss', 'wss', 0);
// save the current workbook as full wss snapshot
 activeworkbook()->export('C:\\dump_snapshot.wss', 'wss', 1);
}

Parameters
savepath – The filepath where to save the exported workbook
type – The filetype of the export. xlsx = excel 2010+ format, wss = Jedox format
mode – The export mode. 0 = no snapshot, 1 = full snapshot, 2 = snapshot of OLAP formulas

Note: this code will export the file into a local folder on the server, so it may not be accessible to remote users. For this, you have to export into a folder in the Apache docroot, then dynamically create and display a link to the file. Not all folders on the file system are accessible for the Macro Engine, nor should they be; the open_basedir directive of the Macro Engine may prevent you from exporting to a specific folder.

Checking for “error” type

It is possible to check whether some resource (cell reference, named formula, or variable) currently holds a value of “error” type, such as #Value! or #N/A. For example, the following code is executed when a workbook is opened, checks if some variable currently holds an error value, and then initializes the variable to a fixed value:

function __open() {
$varvalue = retrieve_variable("myyear");
if ( $varvalue instanceof variant_error) {
define_variable("myyear", "2011");
}
}

 

Macros started by a form element

The following macro examples will work only if the macro is started by a form element (combo box, check box, or button). They do not work when they are called as a spreadsheet function.

Display message popup

Show_popup() gets the text of C4 and displays it in an info message box titled “Test”. Warning (“warn”) and error (“err”) messages are also available.

function show_popup() {
$value = ActiveSheet()->Range('C4')->value;
// return command to show popup; arguments: message body, message header, message type
return __msgbox($value, 'Test', 'Info');
}

 
Executing a Hyperlink

The code below will execute a hyperlink to a specific location:

function Button_Hyperlink()
{
return __hyperlink('/Demo Spreadsheets/Demos/Bikers Best/Navigation/Bikers Best Navigation', 'test link');
}

Or, if the Hyperlink is present in some cell, you can simply refer to that cell:

function Button_Hyperlink()
{
return __hyperlink('A1');
}

 
Spreadsheet Recalculation

function Button_Recalc()
{
return __recalc();
}

 
Logout

function logout_btn() {
return __logout();
}

image_pdfimage_print
Was this post helpful?
NoYes (+4 rating, 12 votes)
Loading...