How to access cached objects from macros

When returning Python objects from a worksheet function using @xl_func, complex objects can be returned to Excel as an object handle. This object handle can then be passed seamlessly to other Python worksheet functions. PyXLL retrieves the actual object and passes that to the next function automatically.

For information about how PyXLL passes Python objects between worksheet functions, see Using Python Objects Directly.

This works fine as long as you are using worksheet functions, but what about if you want to access these objects from a macro or menu function?

Retrieving Python Objects

When writing a macro function, you can use pyxll.xl_app to get the Excel.Application object and interact with it in the same way you would from VBA (see Python as a VBA Replacement). When you access a cell's value Excel doesn't know about PyXLL's object cache and so what you see is the same object handle you see in Excel. For example:

import pyxll

@xl_macro(shortcut="Ctrl+Shift+O")
def get_value():
    xl = pyxll.xl_app()
    value = xl.Selection.Value
    pyxll.xlcAlert(f"Value is {value}")

The value retrieved here will simply be the object handle itself, and not the Python object that handle represents. To get the Python object we need to convert it using either pyxll.XLCell, or pyxll.get_type_converter.

The pyxll.XLCell class has a static method from_range. We can use this to get an XLCell from an Excel Range object. Once we have the XLCell object, the value can be retrieved using the value property, and we can tell the XLCell to convert from object handles to Python objects by setting the option type="object" on the cell before accessing the value property.

import pyxll

@xl_macro(shortcut="Ctrl+Shift+O")
def get_value():
    xl = pyxll.xl_app()
    xl_range = xl.Selection
    cell = pyxll.XLCell.from_range(xl_range)
    value = cell.options(type="object").value
    pyxll.xlcAlert(f"Value is {value}")

Note: The from_range static method was added to XLCell in PyXLL 4.3. If you are using an earlier version of PyXLL then use the following method instead.

If we don't want to go via an XLCell object then there is another way to convert from a handle to an object. You may not be able to go via an XLCell in some situations, for example if your macro is passed an object handle as a value rather than as a cell reference.

The PyXLL function pyxll.get_type_converter is used to convert from one type recognised by PyXLL to another, and this includes the "object" type. To convert from an object handle to an object, we can get the conversion function by calling pyxll.get_type_converter("var", "object").

import pyxll

@xl_macro(shortcut="Ctrl+Shift+O")
def get_value():
    xl = pyxll.xl_app()
    value = xl.Selection.Value
    to_object = pyxll.get_type_converter("var", "object")
    py_object = to_object(value)
    pyxll.xlcAlert(f"Value is {py_object}")

Note: If the object handle does not refer to an object currently in PyXLL's object cache, PyXLL will not be able to retrieve the object. The object cache is cleared when Excel is restarted, and also when PyXLL is reloaded. If you do not want PyXLL to clear the object cache when reloading you can set the option "clear_object_cache_on_reload = 0" in the PYXLL section of your pyxll.cfg file. Be aware though that if you do, if the classes of the cached objects are reloaded then the cached objects will still be instances of the old classes and not the reloaded ones. To repopulate the object cache, recalculate the workbook using Ctrl+Alt+F9.

Setting Python Objects

If in a Python macro you want to set cell values to Python objects using PyXLL's object cache, you can. This can be useful if you need to set up some values in a worksheet that are later to be passed to worksheet functions.

To add Python objects to PyXLL's object cache you need to use pyxll.XLCell as in the example above. Cached objects always have to be associated with a cell in Excel, so objects can only be added to the cache by setting a cell value.

As before, the XLCell is obtained using the from_range static method and the options are set to type="object". Setting the value property on the cell inserts the Python object into the object cache and sets the Excel value to the newly created object handle.

import pyxll

@xl_macro(shortcut="Ctrl+Shift+O")
def get_value():
    xl = pyxll.xl_app()
    xl_range = xl.Selection
    cell = pyxll.XLCell.from_range(xl_range)
    py_object = MyClass(...)
    cell.options(type="object").value = py_object

Using Outside of Excel

The object cache is only available to code running inside Excel. This means that if you are running code from a Python prompt or in an IDE then you will not be able to access the object cache, since the object cache only exists inside the Excel process. If you are using xlwings, note that xlwings runs Python in a separate process outside of Excel and uses VBA and COM to communicate between the two processes - therefore you will not be able to access PyXLL's object cache from xlwings functions.

All PyXLL functions (@xl_func, @xl_macro, @xl_menu etc) run within the same Excel process, and so the object cache is available to any code running in Excel with PyXLL.