Cached Objects

Often it’s necessary to pass a Java object between functions. You might have one function that creates a complex Java object that is used as an input to various other functions, and it’s not convenient or efficient to keep recreating that object from lots of primitive inputs every time you need it.

Jinx allows you to pass objects around as easily as primitive values via its managed object cache. When a function returns a Java type that can’t be converted to a simple Excel type, Jinx assigns it a unique handle and returns that to Excel. Any function that takes an object of the same type (or a super-type) can be passed that handle and Jinx will fetch the object from the cache and pass it to the Java method.

Jinx takes care of making sure that once objects are no longer referenced they are removed from the cache so they can be garbage collected.

Cached objects are not persisted when Excel closes, and they are not saved with the workbook. This means that every time you open a workbook that depends on cached objects it has to be recalculated so that the object cache is repopulated.

The following is a simple example to demonstrate the prinicple:

import com.exceljava.jinx.ExcelFunction;

class Car {
    private final String color;

    private Car(String color) {
        this.color = color;
    }

    @ExcelFunction
    public static Car newCar(String color) {
        return new Car(color);
    }

    @ExcelFunction
    public static String getCarColor(Car car) {
        return car.color;
    }
}

Transient Objects

When calling functions that return cached objects from the Excel worksheet the cached object is returned as an object handle to the Excel grid. That object handle can then be passed to other worksheet functions, and when the object is no longer needed it is removed from the object cache.

In some situations, such as calling a function from VBA, there is no calling cell that the object handle is returned to, and so Jinx can’t track whether or not the object reference is still in used.

In these cases, a transient object handle is returned.

A transient object handle has a limited lifetime, after which the reference to the object is released. Once a cached object has no transient references, and has no other cell references, it is removed from the cache.

The example functions above could be called from VBA, instead of from a worksheet function, as follows:

Sub Test
    ' Create a "Car" Java object.
    ' This returns a transient object reference.
    car = Run("newCar", "red")

    ' Call a function passing in the object reference.
    color = Run("getCarColor", car)
End Sub

In the above code the car object will be released automatically, but only after a pre-determined number of seconds (10 seconds by default).

The default time to live (TTL) of a transient object can be configured in the jinx.ini file by setting the object_cache_transient_ttl setting.

Object references can be released programatically instead of waiting for them to expire. This is done using the ExcelAddIn.releaseTransientObject(java.lang.String) method. To call that from VBA you need a wrapper function, such as the one included in the examples:

public class CachedObjectFunctions {
    private final ExcelAddIn xl;

    public CachedObjectFunctions(ExcelAddIn xl) {
        this.xl = xl;
    }

    @ExcelFunction(value = "jinx.releaseTransientObject", isHidden = true)
    public boolean releaseTransientObject(String id) {
        return xl.releaseTransientObject(id);
    }
}

With this function defined, the VBA subroutine can be modified as follows to release the reference once it is no longer needed:

Sub Test
    ' Create a "Car" Java object.
    ' This returns a transient object reference.
    car = Run("newCar", "red")

    ' Call a function passing in the object reference.
    color = Run("getCarColor", car)

    ' Release the object reference
    Run "jinx.releaseTransientObject", car
End Sub

If an object reference is required for longer than the default time to live the method ExcelAddIn.setTransientObjectTtl(java.lang.String, int) can be used to modify the ttl of an object reference. Again, this needs a wrapper function so it can be called from VBA:

public class CachedObjectFunctions {
    private final ExcelAddIn xl;

    public CachedObjectFunctions(ExcelAddIn xl) {
        this.xl = xl;
    }

    @ExcelFunction(value = "jinx.setTransientObjectTtl", isHidden = true)
    public boolean setTransientObjectTtl(String id, int ttl) {
        return xl.setTransientObjectTtl(id, ttl);
    }
}

This can then be used to change the TTL of an object reference. If an object reference should never be deleted, a negative number can be passed to ExcelAddIn.setTransientObjectTtl(java.lang.String, int).

Sub Test
    ' Create a "Car" Java object.
    ' This returns a transient object reference.
    car = Run("newCar", "red")

    ' Set the TTL of the object reference to 5 minutes (300 seconds)
    Run "jinx.setTransientObjectTtl", car, 300

    ' Call a function passing in the object reference.
    color = Run("getCarColor", car)

    ' Release the object reference
    Run "jinx.releaseTransientObject", car
End Sub