xlwings range.api.validation.add throws com_error -2146827284

Question

I'm trying to add data validation to an Excel worksheet range using xlwings:

def set_validation(rng):
    """Add data validation to the given range"""
    rng.value = "linear_benefit"
    rng.api.validation.delete()
    # xlValidateList = 3
    rng.api.validation.add(3, None, None, 
        "linear_benefit,linear_cost,sigmoid_benefit,sigmoid_cost", None)

when I run this, it stops at the last line with error:

com_error: (-2147352567, 'Exception occurred.', (0, None, None, None, 0, -2146827284), None)

the error number -2146827284, from what I've read, seems to be a general "name not found" error. VBA works fine with this:

rng.Validation.Add Type:=3, _
    Formula1:="linear_benefit,linear_cost,sigmoid_benefit,sigmoid_cost")

What am I doing wrong?

Answer 1

I think your problem comes from passing None to the second and third arguments of the add method. The revised line below worked for me.

rng.api.Validation.add(3,1,3,"linear_benefit,linear_cost,sigmoid_benefit,sigmoid_cost")