Excel crashes or hangs when setting Range.Value

If you have some code that is trying to set the value on a Range object from inside a worksheet function (UDF), depending on the version of Excel you are using, Excel may crash.

Excel manages a graph of dependencies between cells which it uses to figure out what needs to be called when it re-calculates. Your worksheet functions are called during Excel's calculation phase, and trying to set values during this phase can cause problems for Excel.

If you need to set values from a worksheet function, it is recommended that you use pyxll.async_call to schedule a callback after Excel has finished calculating, and then set the value. For example:

from pyxll import xl_func, xl_app, xlfCaller, async_call

@xl_func(macro=True)
def set_values(rows, cols, value):
    """copies `value` to a range of rows x cols below the calling cell"""

    # get the address of the calling cell
    caller = xlfCaller()
    address = caller.address

    # the update is done asynchronously so as not to block Excel
    # by updating the worksheet from a worksheet function
    def update_func():
        xl = xl_app()
        xl_range = xl.Range(address)

        # get the cell below and expand it to rows x cols
        xl_range = xl.Range(range.Resize(2, 1),
                            range.Resize(rows+1, cols))

        # and set the range's value
        xl_range.Value = value

    # kick off the asynchronous call the update function
    pyxll.async_call(update_func)

    return address

A common problem when setting worksheet values from a worksheet function is that you can end up in an infinite loop, causing Excel to hang.

When a value is set, it will cause Excel to recalculate. If your function takes the cell that you're setting as a value then your function will be called again, and if it then sets the value again you will end up in an infinite loop.

If your function is volatile it will be called every time the sheet is updated, so if you set a value from a volatile function then your function will be called again regardless of whether it depends on the value you're setting or not.

You must take care in your code to ensure you do not cause an infinite loop, as that will cause Excel to hang.