You can write Excel macros or menu functions in Python using PyXLL. The following pages from the user guide explain how to write macros and menus:
In your macro function you can call into Excel and read cell values using the "Excel Object Model". This is exactly the same API that you would use if you programming in VBA, but accessed via Python.
This is explained in more detail here Python as a VBA Replacement.
To access a cell value you need to use the Excel "Range" property on the Excel "Application" object. You can get the Excel Application object using the function pyxll.xl_app.
The "Range" property is documented in the Microsoft Excel documentation here https://docs.microsoft.com/en-us/office/vba/api/excel.application.range, and you will see it takes a cell address as an argument.
To get the value of the Range object you can use the "Value" property, as documented here https://docs.microsoft.com/en-us/office/vba/api/excel.range.value.
In Python we can get the Range and its value as follows:
from pyxll import xl_app, xl_macro
@xl_macro
def range_test():
xl = xl_app() # The Excel Application Object
cell = xl.Range("A1") # The Range object for cell A1
value = cell.Value # Note the uppercase "V" in "Value"
print(value) # This will print the value to the log file
Arrays and other types
The above works fine for simple types and ranges, but sometimes we want to convert an Excel range into a different Python type - for example, a pandas DataFrame.
If we were to do the same as the above but for the range A1:D10 as follows:
from pyxll import xl_app, xl_macro
@xl_macro
def range_test():
xl = xl_app()
cells = xl.Range("A1:D10")
value = cell.Value
The "value" here would be a list of lists corresponding the the values in the range A1:D10.
To get the value as a different type instead of accessing the raw "Value" property we can use the PyXLL class XLCell. This lets us use PyXLL's type converters and convert the raw values into a different type.
The XLCell class can be constructed from an Excel Range object using the static method XLCell.from_range, or that same method can also takes an address as a string.
To get the range A1:D10 as a DataFrame using XLCell we could do the following:
from pyxll import xl_macro, XLCell
@xl_macro
def range_test():
cells = XLCell.from_range("A1:D10")
value = cells.options(type="dataframe").value
Here we have specified that we want to get the value as a DataFrame via the options method on the XLCell instance, and then accessed the value using the "value" property.
Another way to do type conversions
If you have the raw values from an Excel Range and need to convert them to a different type using PyXLL's type converters you can do so using the PyXLL function get_type_converter.
The get_type_converter function takes the type you want to convert from and the type you want to convert to and returns another function that you can call with the values you need to convert.