1

I am trying to create a User defined function (UDF) using python xlwings library to recall price of the product. I have a list of 299 products, remembering name of all the products is not possible.

I am trying to create an UDF like below to get the price of the product:

=ProductPrice(Name_of_Product) *#this will be used in excel cell*

Here, I want a drop down of all my 299 products to choose one from the list. Something like below:

enter image description here

I can not see any help on this available on internet.

Is it possible to achieve this? if yes, can you please guide?

sagardbhangale
  • 57
  • 5
  • It may be possible to return something similar through an integration with the IntelliSense add-in as mentioned in [xlwings issue 1718](https://github.com/xlwings/xlwings/issues/1718). However, as it is not possible to otherwise complete this in VBA [(see the end of the answer here)](https://stackoverflow.com/questions/5340002/vba-function-argument-list-select), it is unlikely to work without additional add-ins for xlwings. – Rawson Sep 08 '22 at 16:21
  • You could try a rough attempt at producing a drop-down menu when entering this formula, like the VBA [here](https://stackoverflow.com/questions/32148460/vba-custom-excel-function-with-dropdown-list). – Rawson Sep 08 '22 at 16:21

1 Answers1

0

I'm not sure if it can be done with Excel-DNA/IntelliSense or something like that. But it can be done with Excel itself.

As far as you're talking about a list of products, I assume you have this list in a python code. So I suggest to create a new sheet with products in one row. Convet them into table (ListObject), with products as a column names. Put corresponding prices (or a function like =CalculatePrice(@ProductPrice[#Headers]) under each product in a single row of this table. Name this table ProductPrice. And voila! Whenever you print in a cell =ProductPrice[ a dropdown list of your products appears. You select a product, close the square bracket and get the price of the product.

Here's a visual how it can look:

image

Let's see how it can be done programmaticaly:

import xlwings as xl
from pathlib import Path

products = '''
Scented Candles
Portable Projector
Bluetooth Speaker
Smart Watch
Vegetable Chopper
Neck Massager
Ice Skates
Back Cushion
Portable Blender
Nail Polish
'''

products = products.split("\n")[1:-1]

def CalculatePrice(product: str) -> float:
    'Some function to calculate the price of the product'
    return len(product)

f = Path('test_products.xlsx')
if f.exists():
    f.unlink()    # remove the old file if exists

wb = xl.Book()
sh = wb.sheets.add("PriceList")
sh.range((1,1), (1,len(products))).value = products
sh.range((2,1), (2,len(products))).value = [*map(CalculatePrice, products)]
# create a table (a.k.a. ListObject) by name ProductPrice
sh.tables.add(sh['A1'].current_region, 'ProductPrice').show_autofilter = False
sh.visible = 2   # https://learn.microsoft.com/en-us/office/vba/api/excel.xlsheetvisibility

wb.save()
wb.close()
Vitalizzare
  • 4,496
  • 7
  • 13
  • 32