Overcoming problems when using Excel Range Names in VBA

When you are accessing fixed ranges in Excel, naming these ranges and then referring to them in VBA using the Excel Range Name is very convenient.

However, if you have a lot of Range Names, figuring out the appropriate Range Name to use can become very confusing.

A simple solution is to create a User Function with just a single line of code that will display the name of a Range Name in an Excel Worksheet.

Situation: Range Names very helpful in VBA coding

We recently developed a pricing calculator for our company FindWAtt, which uses semantically-enabled knowledgebases to “read,” interpret and automatically enhance retailers’ product data for use in shopping data feeds, Product Information Management (PIM) systems and on retailers’s own websites to power site search.

There are many factors that affect the (a) complexity and difficulty of enhancing data and (b) consequent amount of computer and algorithmic resources required. Below is a very simplified version of some of the inputs used in our pricing model, along with hypothetical values for illustrative purposes (only).

To simplify coding, each “input set” has been named using Excel’s Named Range Facility. For example, the range specifying Processing fee per Product is RangeProcessingFeePerProduct.

Complication: Too many Range Names: which one is the right one?

VBA coding using Excel range names is easy when you only have a few range names to deal with. But if you have a lot it can be inconvenient and frustrating to trawl through Name Manager trying to find the range name you’ve used for a particular input set.

When we first wrote our VBA Pricing Calculator, we had 23 range names. And finding the right one even if you have 2 monitors/screens (1 for Excel workbook, 1 for VBA IDE) is a pain – ghastly if you only have 1 screen and have to flip back and forth between VBA and the workbook.

Solution: Use VBA Function to display Range Names in Worksheet

Just one line of code is all you need to create a function that will display a Range Name in a Worksheet

Function GetRangeNameOfRange(rng As Range)

On Error Resume Next

GetRangeNameOfRange = rng.Name.Name


End Function

Get more stuff like this

Subscribe to our mailing list and get interesting stuff and updates to your email inbox.