Working with named Excel ranges with Python/VBA code

Named ranges are useful things, especially for writing formulas that need to refer to the same block of cells constantly. You can easily set them by hand just by selecting the range you’re interested in, and then clicking the box to the left to the formula entry bar, and type in the name of your range.

On the programming side, named ranges are a bit more complicated to work with. You can easily refer to a range, just set the formula attribute to “=name” and that’s it. myRange.Formula=”=myName” for VBA, range.formula = ‘=myName’ for python via win32 COM once you have ‘range’ storing a range object.

However, making new names takes more effort. The names are stored in a Names collection object that lives in the workbook object. So, you’d need to use the Names.Add method to add new entries into the list.

In VBA:

Names.Add Name:="myName", RefersTo:="=sheet1!$a$1:$a$10"

In Python:

#as usual, xl = win32com.client.Dispatch('excel.application')
xl.ActiveWorkbook.Names.Add(Name="cheese", RefersTo="Sheet1$A$1:$B$2")

To fetch your names, you can iterate over the objects in the collection, for example in python you can use a list comprehension quickly:

namelist = [x.Names for x in xl.ActiveWorkbook.Names]

after the operations above would give me something like: [u'cheese',u'myName']. (as always, you get unicode strings back from Excel)

One thing to remember for python is that the “count from 1″ quirk is in effect again. You can refer to the first object in the Names collection using .Names(index) but if index=0, it’ll throw an exception at you. index=1 is the first entry, and it counts up from there. Yeah.

This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>