Using VBA to "apply names" to named range references
'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.