Using VBA to "apply names" to named range references

Question

So there's a known bug in excel where applying named ranges via ribbon doesn't work properly (for 2010 and 2013 and possibly 2016). There was a question about it on superuser - Apply names in excel bugged?. I took a shot at it.

---

This code will find all of the named ranges in the book and then search a specified range for any cells that have formulas containing a reference tied to the named range's address and replace that with the named range.

This does that, but only works for absolute references when searching as I couldn't figure out a simple way to get the named range's relative reference.

I guess I'm wondering if there's a better way to do this (maybe with a dictionary so I don't need two loops?) and also capture the relative references. Or if there are any properties I didn't use that will do what I want.

Option Explicit
Sub FixNames()

Dim ClctNames As Variant
Set ClctNames = ActiveWorkbook.Names

Dim rngName As String
Dim rngNameLoc As String
Dim strFrmla As String

Dim c As Range
Dim n As Integer

'Define as needed
Dim srchRng As Range
Set srchRng = ActiveSheet.UsedRange

'For each name (n) in the collection
For n = 1 To ClctNames.Count

    'I'm storing the Named Range's name and address as strings to use below
    rngName = ClctNames(n).Name
    rngNameLoc = ClctNames(n).RefersToRange.Address

    '--Should I break this out into a function? If so, at what point?
    For Each c In srchRng
        'We only want to test cells with formulas
        If c.HasFormula = True Then
           'We have to check if the cell contains the current named range's address
           If InStr(1, c.Formula, rngNameLoc, vbTextCompare) <> 0 Then
              'Since these are perfect matches, no need to look for length or location, just replace
              strFrmla = Replace(c.Formula, rngNameLoc, rngName)
              c.Formula = strFrmla
           End If
        End If
    Next
Next

'No error handling should be needed

End Sub

Answer 1

'No error handling should be needed

Except, you never really know what assumptions you're making without realizing. Better safe than sorry.

For example, here's where your procedure might blow up:

Set srchRng = ActiveSheet.UsedRange

You're assuming that the ActiveSheet will be a Worksheet object, but ActiveSheet can also be a Chart object, which doesn't have a UsedRange method, so that line raises error 438 "Object doesn't support this property or method".

You could verify that ActiveSheet is indeed a Worksheet object before accessing its UsedRange, but that wouldn't be all.

For n = 1 To ClctNames.Count

You're also assuming ClctNames.Count will be equal to or greater than 1. However in a new workbook, ActiveWorkbook.Names.Count returns 0, so that For loop goes from 1 to 0... lucky for you, VBA won't enter that loop - but it's not obvious behavior and would be better off explicitly handled at the start of the procedure:

If ClctNames.Count = 0 Then Exit Sub

---

When evaluating a Boolean value in an If condition:

If c.HasFormula = True Then

You never need to make a Boolean expression out of the Boolean value. This is equivalent, and reads better:

If c.HasFormula Then

---

As for the forced-absolute references, you can pass parameters to Range.Address:

rngNameLoc = ClctNames(n).RefersToRange.Address(RowAbsolute:=False, ColumnAbsolute:=False)

This removes the dollar signs from the RefersToRange address, but then this:

strFrmla = Replace(c.Formula, rngNameLoc, rngName)

Needs to actually look for the 4 variants of the range address:

• $A$1
• $A1
• A$1
• A1

Things get more "fun" when the address refers to a range of cells:

• $A$1:$A$10
• $A1:$A$10
• A$1:$A$10
• A1:$A$10

Wait, did you notice?

Replacing $A$1 with Foobar in =SUM($A$10:$A$25) will break something - it won't raise a VBA runtime error, but I'm pretty sure =SUM(Foobar0:$A$25) won't evaluate properly.

This definitely looks like a job for a regex replace, more than a simple string comparison and replacement.

Answer 2

NAMING!

Proper Variable naming is one of the most important skills you can develop.

Names should be Clear, Concise and, above all, Unambiguous. Names should follow consistent conventions (and, preferably, those which are most widely-adopted). Essentially, I should be able to take any single line from your code, give it to some random person, and have them understand roughly what all the variables contain and what's being done to them.

srchRng is a good variable name. It is clear that is a range, it is concise, and it is unambiguously a "Range to be searched". c is a terrible variable name. It is an almost universal convention that single-letter variables denote general counters. Dim i As Long, j As Long, k As Long, is perfectly fine. Dim c as Range most definitely is not.

If you need a general range-holder, why not rng or rngHolder or tempRng or similar?

When it comes to VBA conventions, the most common ones are:

Procedure-Level variables use Dim and are in camelCase Module-Level variables use Private and are in PascalCase Global-Level variables use Public and are in PascalCase Constants use Public Const and are in SHOUTY_SNAKE_CASE

Personally, I use ws_ObjectName for worksheets, modules m_, UserForms uf_ etc. You can use your own convention, just pick one and stick to it.