Get python XLwings user-defined function on Excel to run at regular interval

Question

Suppose I have this UDF defined in python v3.6 xlwings v0.11 to run on Excel 2016.

import xlwings as xw

@xw.func
def random_val(x):
    import random
    return random.random()*x

The UDF is imported into an Excel sheet which supports xlwings. I have tested that the formula =random_val(2)at cell A1 works fine.

My question is how do I run this UDF at regular time interval of 1 minute so that the output is refreshed at cell A1 every minute.

Answer 1

This can be done easily through Macros.

import xlwings as xw

@xw.sub
def my_macro():
    import time
    import random
    wb = xw.Book.caller()
    while True:
        time.sleep(60)
        wb.sheets[0].range('A1').value = random.random()

After clicking on Import Python UDFs, you can then use this macro by executing it via Alt + F8 or by binding it e.g. to a button. To to the latter, make sure you have the Developer tab selected under File > Options > Customize Ribbon. Then, under the Developer tab, you can insert a button via Insert > Form Controls. After drawing the button, you will be prompted to assign a macro to it and you can select my_macro.

Answer 2

This is probably not what you are expecting, but it works the way you want it.

Thus, imagine that your function prints the the time in HH:MM:SS like this:

Public Function MyTimeMinutesSeconds() As String

    Application.Volatile
    MyTimeMinutesSeconds = Format(Hour(Now), "00") & ":" & _
                           Format(Minute(Now), "00") & ":" & _
                           Format(Second(Now), "00")

End Function

Thus, if you recalculate the worksheet every N seconds, then the function will be called every N seconds and it will be updated. This is how to call it like this:

Option Explicit

Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

Public Sub Starting()

    Dim i As Long: i = 1
    [a1].Formula = "=MyTimeMinutesSeconds()"

    While i < 6
        Sleep (500)
        Calculate
        i = i + 1
    Wend

End Sub

What you get is a nice ticking clock in excel:

---

The trick is in the Application.Volatile part of the function:

A volatile function must be recalculated whenever calculation occurs in any cells on the worksheet. A nonvolatile function is recalculated only when the input variables change. This method has no effect if it's not inside a user-defined function used to calculate a worksheet cell.