Macro Engine

image_pdfimage_print

Related links: Macro Engine Configuration, Macro Examples

Jedox Web is a powerful framework for building feature-rich web applications, based on the concept of web spreadsheets. It is a client-server architecture, where the client runs in a web browser. With over two hundred spreadsheet functions and many built-in components like charts, micro charts, form elements, DynaRanges, framesets, and widgets, there are almost limitless ways to create extensive applications with Jedox Web.

However, sometimes even all this is not enough for a demanding Jedox Web user, which is why Jedox Web comes with integrated scripting support called Macro Engine (ME). ME is based on PHP: Hypertext Preprocessor (PHP5), and it enables a workbook designer to write PHP code that is executed within Jedox Web’s Core runtime.

The Macro Engine offers the core PHP functionality as well as a number of specific language elements (classes, functions, and global variables) to provide capabilities related to Jedox Web applications.

For an overview of the functions and objects exposed to Macro Engine, please refer to our Macro Engine API Documentation. You can also download this documentation as a zip archive.

User-defined functions

The definition of functions in ME is very similar to writing Visual Basic Applications (VBA) in Microsoft Excel. For example, Excel Object Workbook is translated to Jedox Web Object Workbook, and instead of using VBA syntax, you use PHP syntax.

A function name can be used only once. You cannot create two macro functions with the same name. 

Note: if a spreadsheet cell holds a PALO.DATA formula, and a value is set in this cell via a macro, the formula will not be overwritten. Instead, the set value is sent to OLAP for writeback.

Functions available as spreadsheet formulas

You can use ME to write user-defined functions for the workbook, and these functions can be used like any other integrated functions.

Below is an example how you can implement a user-defined spreadsheet function.

Click
“Tools – Macro – Macro Editor …”
On the right you see the definition of the function MySUM. After saving, MySUM is registered under Modul1.

Now we use the defined function in the worksheet:
When we hit the Enter key, we get the correct result for our function: 5

Functions defined this way are available in the current workbook; i.e., they can be used on each sheet in the workbook, but they are not available in other workbooks. Macro functions beginning with an underscore cannot be called as spreadsheet functions. This constraint only applies to functions defined in a workbook macro, not to globally registered, user-defined functions.

Function call when a workbook is opened or closed

Starting with Jedox Web 4.0, there are the special functions you can use to run a macro when a workbook is opened or closed. These functions are  __open() and __close(). To use them, simply define a new macro like this:

You do not have to assign this function to a form element, or call it in a cell. It will automatically be executed every time you open the workbook. The __close() function can be used similarly.

Server-wide functions

Imagine a scenario where you define a complex custom spreadsheet function that calculates average monthly income based on input values, and everybody from your team finds it useful and would like to use it in their workbooks. Copying this functionality into every workbook would be one solution, but a more elegant one would be to make those functions “server wide”. This is also possible with ME.

See Macro Engine Configuration for an example of a server-wide function in action. In  macro_engine_config.xml are the entries to use the function INCOMECALC() server wide.

Starting Macros with form elements

Macros can also be started with form elements. The macro will be started by clicking on a button, changing the argument in a ComboBox, or by setting it on/off with a check box.

With the command “Tools – Form elements – switches” you can create a button in any spreadsheet cell:
You can assign a macro to this button with a right click. Similarly, you can assign macros to ComboBoxes and Checkboxes.If a macro is assigned to a button, with a right click you can edit or unassign the macro.

Using modules

In a macro with multiple modules that reference each other, you must use the module() function in combination with require|include[_once]. For example, to use a function from Module2 in Module1, the following code must be put in Module1:

image_pdfimage_print
Was this post helpful?
NoYes (0 rating, 10 votes)
Loading...