New in PyXLL 5.11
While it is possible to completely replace VBA macros with Python macros, sometimes it is necessary to mix the two and call Python macros from VBA. For example, when migrating a large VBA project to Python it may not be feasible to change everything in one go.
When mixing VBA and Python occasionally you may want to pass a complex Python object from Python to VBA, in order to pass it to another Python function later.
Not all Python types convert easily to standard Excel types. In order to pass Python objects from Python to VBA and from VBA to Python, PyXLL mainains an ‘object cache’. Rather than return an actual Python object, PyXLL can store a returned object in this cache and return a handle to that object from the macro. This handle can then be passed to other Python macros, and PyXLL will retreive the object from the cache.
For example, here are two macros - one that returns an object, and one that expects an object. To tell PyXLL that we want to pass this object via the object cache the functions use the ‘object’ type:
from pyxll import xl_macro
class CustomObject:
def __init__(self, name):
self.name = name
@xl_macro("string name: object")
def create_object(x):
return CustomObject(x)
@xl_macro("object x: string")
def get_object_name(x):
assert isinstance(x, CustomObject)
return x.name
There’s no other code needed, just marking the object type as ‘object’ is enough to tell PyXLL that the return value should be put in the object cache and a handle to that object returned to Excel.
We can call these functions from VBA:
Sub Test
' a is an object handle we can pass to other Python Macros
a = Run("create_object", "TEST")
' get_object_name is called with the object handle
b = Run("get_object_name", a)
End Sub
Cached objects in macros work in the same way as cached objects for worksheet functions, with just one important difference:
Unlike objects that are returned to the worksheet, objects returned via macros have an expiry time after which they are removed from the cache. This is explained in more detail in the following section.
New in PyXLL 5.11
When using cached objects from worksheet functions, the object handle is returned to the Excel sheet and the lifetime of the cached object is managed by PyXLL. When the cell containing the object handle is cleared, the cached object is removed from the cache.
For macro functions, the object handle is not written to the Excel sheet and so PyXLL has no way of knowing when the cached object is no longer required.
To prevent the object cache from growing indefinitely, objects returned from macros are removed from the cache after a pre-determined amount of time. Usually, as objects are passed to VBA in order to be passed to another Python function soon after, this amount of time only needs to be a few seconds.
Python objects returned from macros are called transient objects as they only remain in the cache for a finite period of time.
The amount of time a transient object is given before it will be removed from the cache is called the time to live or TTL.
The default TTL can be set in the pyxll.cfg config file as follows:
[PYXLL]
; Default time to live for transient cached objects, in seconds
object_cache_transient_ttl = 10
The TTL for an object can also be set in your Python code. The TTL can be set before the object is returned, or at any time after. Changing the object’s TTL restarts the clock to when it will be removed from the cache.
The function set_transient_object_ttl can be used to set the TTL for an object. If an object should never
be removed from the cache, a negative TTL can be used.
For example, if a returned object should be kept in cache for 5 minutes you would do the following:
from pyxll import xl_macro, set_transient_object_ttl
class CustomObject:
def __init__(self, name):
self.name = name
@xl_macro("string name: object")
def create_long_lived_object(x):
obj = CustomObject(x)
# Keep the object alive for at least 5 minutes
set_transient_object_ttl(obj, 300)
return obj
