Can't swap reference style between R1C1 and A1 due to _xlpm.

[mookyon]

A 365 worksheet may load fine either @ R1C1 reference style or @ A1 reference style. However, when trying to set or clear the Options\Formulas\R1C1 checkbox, Excel shows a Name Conflict message box with a subtitle “Name cannot resemble a reference”.

Afterwards, many “Old name:”s appear with a prefix of “_xlpm.” and a “New name:” is required.

Some such OLD NAMEs resemble the LET function parameters but not all of them.

When looking at the name manager and the actual formulas, neither “_xlpm.” Nor “_xlfn.” Found.

Any idea pertaining to how to resolve this phenomenon? Please advise.

 

[Fluff]

Hi & welcome to MrExcel.
You could run this code, which will make all names visible, after which you can delete them from the name manager.

Sub CleanNames()
   Dim Nm As Name
   For Each Nm In ActiveWorkbook.Names
      Nm.Visible = True
   Next Nm
End Sub

 

[mookyon]

Dear Fluff,
1000THX
?

 

[Fluff]

You're welcome & thanks for the feedback.

 

[mookyon]

I am not sure it is related to the former issue.

Following making all names visible, I see tens of inaccessible macros using the same names. Names can neither be deleted (greyed out), nor Edited / Run (a message "Reference isn't valid." is generated when Run or Edit is selected).

Can those macro names be removed?

 

[mookyon]

BTW, at the time the former screen shot was taken: no add-ins, or macro worksheets were involved.
Just a plain 365 xlsm (I have a couple of my own macros including the one Fluff offered earlier in this thread)

 

[Fluff]

Once you delete those names from the name manager & then save & close, they should disappear from the macro list.

 

[mookyon]

OK. Appreciated.
Thanks for the tip.
Had to remove my added VB code. Save as xlsx.
Then reopen (after all macros gone) added my VB code and save as xlsm again.

 

[Fluff]

Glad to help & thanks for the feedback.