Macros

Creating an Excel macro using Jinx is very similar to writing a worksheet function.

Macros are useful as they can be called when GUI elements (buttons, checkboxes etc.) fire events. They can also be called from VBA.

Methods are exposed as Excel macros by addition the @ExcelMacro annotation. They must be public methods, and can either be static or non-static (instance) methods. Classes with non-static methods must have a public default constructor.

For example, the following code adds a new macro showMessage to Excel:

import com.exceljava.jinx.ExcelMacro;
import javax.swing.JOptionPane;

public class MacroFunctions {
    @ExcelMacro
    public static void showMessage() {
        JOptionPane.showMessageDialog(null,
                "showMessage macro called!",
                "showMessage Macro",
                JOptionPane.INFORMATION_MESSAGE);
    }
}

Once that code is built the class MacroFunctions is added to the Jinx config file:

> javac -cp <path to jinx jar> MacroFunctions.java
> jar -cf MacroFunctions.jar MacroFunctions.class

[JINX]
classes = MacroFunctions

[JAVA]
classpath = ./MacroFunctions.jar

Now when Excel is started the macro showMessage exists and we can associate it with a control or call it from VBA.

ExcelMacro Options

The @ExcelMacro annotation takes a number of optional parameters. These are used to control how the Java method is exposed to Excel.

Keyboard Shortcuts

A shortcut for a macro can be set using the shortcut parameter to the @ExcelMacro annotation.

Shortcuts should be one or more modifier key names (Ctrl, Shift or Alt) and a key, separated by the ‘+’ symbol. For example, ‘Ctrl+Shift+R’.

If a key combination is already in use by Excel it may not be possible to assign a macro to that combination.

In addition to letter, number and function keys, the following special keys may also be used (these are not case sensitive and cannot be used without a modifier key):

• Backspace
• Break
• CapsLock
• Clear
• Delete
• Down
• End
• Enter
• Escape
• Home
• Insert
• Left
• NumLock
• PgDn
• PgUp
• Right
• ScrollLock
• Tab

Calling back into Excel

Macros are most useful when you can call back into Excel, as you would do when writing a VBA macro.

Excel exposes its object model via COM, and that is the same object model that you would access through VBA so anything you can do in VBA you can also do using the COM Excel object model.

To access the Excel object model from Java and Jinx, we recommend that you use the jinx-com4j project, which acts as a bridge between your Jinx add-in and Excel.

For details of jinx-com4j see its github page https://exceljava.github.io/jinx-com4j.

Calling Macros from VBA

Macros can take parameters and return values, just like user defined worksheet functions. They can be called from VBA using the Run function.

Sub SomeVBASubroutine
    x = Run("myJavaMacro", "arg1", "arg2")
End Sub