So I've been trying to write some python code in Microsoft Excel. The code produces a cell reference, which should then be fed into the xl() function, so that the value of that cell will be returned.

But the obstacle that I keep running into is that xl() doesn't seem to be able to accept variables.

Eg, putting this python code in excel:

ref = "A1"
var = xl(ref)
var

…should return the value in cell A1. But it doesn't. It just returns a KeyError.

Typing A1 directly into the xl() function (xl("A1")) works fine, but as soon as you put the cell reference into a variable excel can't handle this.

How can I fix this or are there any workarounds to this?

Preferably workarounds that don't involve the use of office add-ins

解决方案

I suspect that the ref parameter of the xl() function is only represented to us humans inside of excel's formula bar, built-in python code editor, and in-line documentation as a string type, but is actually something more complex. .

For instance, I suspect that the "A1" that we see in the xl() function inside the formula bar:

or in the python code editor:

Or as detailed in in-line documentation:

(ref: str, headers: bool = False) -> Any

or

(ref: str, type: type[T@xl] = Any, headers: bool = False) -> T@xl

Isn't actually a string-literal "A1". I suspect that the actual object is determined at the time of writing/selection and not at the time of execution and is merely represented as a string-literal to us users.

The reason I suspect this is if you attempt to use named variables:

xl(ref="A1")

The error below pops:

TypeError: xl_ref() missing 1 required positional argument: 'address'

Suggesting that the xl's ref parameter is more complex than a string.

Furthermore, once one enters in an actual cell reference like xl("A1") the in-line documentation changes to read:

(ref: Literal['A1'], headers: bool = False) -> float

Returns a value given an Excel range string. For example:

xl('A1') returns a scalar value. xl('A1:C6') returns a data frame. headers -- If headers=True is passed additionally, the data frame has column headers that are taken from the first row in the range. type -- If type is passed in, the return value is cast to that type (unchecked). This is done automatically according to the type of the Excel range string, and should not be manually added.

Suggestion there is more going on under the hood.

Unfortunately, I've not found any other documentation to back this up.