Apply Names in Excel is bugged?

Question

I see people had this problem 10 year ago and it's still not fixed. Or maybe I just don't know how to use this feature.

In my sheet I have these named ranges:
A1 - "name1"
B1 - "name2"
C1 - "name3"

In some other 3 cells I have these formulas:

=A1
=A1+B1
=COUNT(A1:C1)

After running Apply Names... I expect to have this:

=name1
=name1+name2
=COUNT(name1:name3)

I never get that no matter what options I choose. Moreover, usually my formulas become completely ruined...

=name1
=name1
=name1

How could I get the result I need?
I am knowledgeable in VBA, so I tried to design a solution to this problem using VBA, but the code was not working either.

I'm using Excel 2013, 32-bit (MS Office Professional Plus). OS - Windows 7 Enterprise, 64-bit.

Answer 1

I can't replicate this (with my version of excel on Windows 7) -

If I define your names and then create formulas

Then apply names

I get the desired result -

How are you defining the names? I select the cell then click on its title to the left of the formula bar and type in the name.

---

With yours -

apply names -

As shown here

---

No change without "showing formulas" -

apply names

---

Okay, here we go. Office 2013, windows 7

apply names

Looks like a replicated failure.

---

Okay, let's try Excel 2016 on OSX Yosemite

Let's define our names and formulas -

Good, good, let's apply our names

What? Alert Formula is too long

Now it selected my count and.. what? Alert Microsoft Excel cannot find any references to replace

And it's a.. partial failure?

Okay then, let's do this manually -

Strange, it doesn't highlight the range, just the two cells?

But it works?

Just for comparison, a regular count highlights the range -

Answer 2

As was pointed out in code review, this will cause problems if, for instance, it's looking for "A1" and finds "A10" etc.

Okay, here's my attempt at a work-around. With this, your formulas must use absolute references all the time. It works on named ranges larger than 1 cell.

Please note that I'm searching usedrange - but you can narrow that down as you like by resetting srchRng.

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

You need to use absolute references because when I pull the named range's RefersToRange.Address it's returning a range object - not a range, so I'm setting it as a string. I guess you could write a function that removes the $ absolute references if you'd like.

_{that was a fun one}

Answer 3

Say we start with:

and we already have assigned Names to A2 and B2. In the Formulas Tab, pull-down:

Define Name > Apply Names...

Make sure we hi-light both names and touch OK

and we get:

and so the Names get applied!