Hidden Excel 4.0 macros can't be removed

Bancam

New Member
Joined
May 19, 2022
Messages
6
Office Version
  1. 365
Platform
  1. Windows
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.
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
74,374
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
May 19, 2022
Messages
6
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
74,374
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
May 19, 2022
Messages
6
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

New Member
Joined
May 19, 2022
Messages
6
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
74,374
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

New Member
Joined
May 19, 2022
Messages
6
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
74,374
Office Version
  1. 365
Platform
  1. Windows
You can delete them via the name manager.
 
Solution

Bancam

New Member
Joined
May 19, 2022
Messages
6
Office Version
  1. 365
Platform
  1. Windows
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!
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,167,063
Messages
5,851,869
Members
431,470
Latest member
mikaelaleksandrowicz

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top