Hidden Excel 4.0 macros can't be removed

[Bancam]

I use Excel 365 on Windows 10.
I have a Membership database in an Excel workbook. Every so often, using a VBA macro, I copy a worksheet into a new book, remove all code from the copy and update various cell formats. I manually do some editing and remove some rows and columns and then run a macro to remove all external references hanging over from the original book, including named ranges, conditional formats, external links and comments.

The following remain: (properties shown)
Name _xlpm.code _xlfn.SINGLE _xlfn.COUNTIFS _xlfn.CONCAT
Value =#NAME? =#NAME? =#NAME? =#NAME?
Visible FALSE FALSE FALSE FALSE
Macrotype 2 1 1 1
WorkbookParameter FALSE FALSE FALSE FALSE
Creator 1480803660 1480803661 1480803662 1480803663

When I .delete these names I get the possibly spurious error:
"Error 1004 The syntax of this name isn't correct.
Verify that the name:
-Starts with a letter or underscore (_)
-Doesn't include a space or character that isn't allowed
-Doesn't conflict with an existing name in the workbook."

I tested _xlfn.CONCAT and it actually invokes the CONCAT function, so the blighter is real but I don't know how to remove it. These are causing a warning when I save the new workbook "Excel 4.0 function stored in defined names" and asks me if I want to save as an xlsx anyway. This I do. If I reopen it, and save again, there is no warning. So Excel is able to remove the offending macros.

I have never intentionally written an Excel 4.0 macro, so I'm asking for help on locating and removing the offending macros from my master membership database.

 

[Fluff]

Those are not Excel4 macros, they are for backwards compatibility for xl functions.
You can delete them, but they will reappear again if any of those functions are still used in the workbook.

 

[Bancam]

Thanks Fluff for the quick reply. Are these names related to the warning about Excel 4.0 macros ?

If I understand you, if I stop using functions in the workbook, then I should be able to delete the names. Then maybe Excel will stop pestering me with the warning about Excel 4.0 macros. Although finding a replacement for COUNTIFS could be difficult.

 

[Fluff]

All functions that came into being with xl2007 or later will generate those names, however I suspect the main culprit for the warnings will be the _xlpm.xxx names which are generated by the LET function.

 

[Bancam]

Thanks Fluff for the quick reply. Are these names related to the warning about Excel 4.0 macros ?

If I understand you, if I stop using functions in the workbook, then I should be able to delete the names. Then maybe Excel will stop pestering me with the warning about Excel 4.0 macros. Although finding a replacement for COUNTIFS could be difficult.

How else could the workbook contain Excel 4.0 macros?

 

[Bancam]

All functions that came into being with xl2007 or later will generate those names, however I suspect the main culprit for the warnings will be the _xlpm.xxx names which are generated by the LET function.

OK, LET() does not currently exist in the workbook, but I may have tried it out then removed it. So that should not explain the _xlpm.xxx, unless some other function calls it into existence. Is there a list that I can refer to?

 

[Fluff]

Is there a list that I can refer to?

Not that I'm aware of. If you no longer use LET, then once you delete those names, you should be ok.

 

[Bancam]

Not that I'm aware of. If you no longer use LET, then once you delete those names, you should be ok.

Is there a way to delete stubborn names? These ones defy the name.delete method.

 

[Fluff]

You can delete them via the name manager.

 

[Bancam]

You can delete them via the name manager.

I would delete via the name manager if these four names were displayed. Looping through Application.names tossed up these hidden ones.
I changed _xlfn.SINGLE, one of the hidden names to visible but there was still an error Error 1004 "The syntax of this name isn't correct." when I tried to delete in VBA.
However, that caused both _xlfn.SINGLE and _xlpm.xxx to be visible in the Name Manager and I was able to delete them. Not only that, they did not reappear.

Problem solved. Thanks!