Using VBA to "apply names" to named range references

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

'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.

StackExchange Code Review Q#112885, answer score: 11