Nombres definidos ocultos

Hoy hablaremos de un problema con el que me he enfrentado recientemente, donde cuando trataba de guardar hojas de un libro Excel me advertía de la existencia de Funciones-macro 4.0 (que no eran visibles y desde luego yo no había creado!!!)

Un colega, Charles Willians, me recomendó usar el complemento de Jan Karel Pieterse, en el cual colaboró.
Su complemento/Add-in 'Name manager' lo puedes encontrar en: https://jkp-ads.com/excel-name-manager.asp

Pero sobre todo me dió la pista por donde empezar... 'busca nombres definidos ocultos'.
Me acordé entonces de una publicación de hace muchos años atrás (Ocultar y Mostrar los nombres definidos) que resolvería el problema.

Así pues añadí la macro siguiente en un módulo del libro 'problemático':

Sub Localiza_Nombres_Ocultos()
Dim ndNombreDef As Name

For Each ndNombreDef In ThisWorkbook.Names
    If ndNombreDef.Visible = False Then
        If InStr(1, ndNombreDef.Name, "_xl") > 0 Then
            Debug.Print ndNombreDef.Name & "_" & ndNombreDef.Value
            ndNombreDef.Visible = True   ' para hacerlo visible...
         End If
    End If
Next ndNombreDef

End Sub

La sorpresa es el listado de 'nombres ocultos' que aparecieron listados!!
_xlfn._xlws.FILTER_=#NAME?
_xlfn.ANCHORARRAY_=#NAME?
_xlfn.CONCAT_=#NAME?
_xlfn.COUNTIFS_=#NAME?
_xlfn.IFERROR_=#NAME?
_xlfn.SUMIFS_=#NAME?
_xlfn.TEXTJOIN_=#NAME?
_xlfn.UNIQUE_=#NAME?
_xlpm.miFuncion1_=#NAME?
_xlpm.miFuncion2_=#NAME?
_xlpm.miFuncion3_=#NAME?

¿Qué son esos prefijos (_xlfn. _xlws o _xlpm)?.

El primero: _xlfn es el clásico prefijo que aparece cuando una función está trabajando en una versión previa, donde no existía.
Por ejemplo, si habrimos la función SUMAR.SI.CONJUNTO (SUMIFS) en una versión de Excel 2007, Excel mostrará en las celdas donde aparezcan dichas funciones, ese prefijo... además del valor resultado.

El segundo prefijo que vemos es _xlws que identifica algunas de las nuevas/últimas funciones lanzadas por Microsoft (FILTRAR, ORDENAR, ....), y con idéntico significado.

El tercer prefijo que aparece es _xlpm que nos refiere a las funciones LAMBDA, que hayamos podido generar...

Por todo lo leído en diferente documentación, parece ser este fallo se puede producir al abrir el archivo de trabajo con versiones anteriores.

Por suerte, una vez identificado ese listado, y lanzada la macro anterior, ya tendríamos a nuestra disposición en el Adminstrador de nombres definidos dichos nombres que deberemos eliminar.
Una vez borrados el problema desaparece!!!

También podríamos haber añadido en la programación de la macro la acción .Delete sobre el objeto ndNombreDef.

Cómo exactamente ha ocurrido esto y por que los identifica como funciones macro 4.0 es dificil de entender... pero al menos tenemos una solución (al menos en mi caso sirvió).

Un punto importante para comenzar a revisar un fichero, cuando algo empieza a fallar en él es revisar el Inspector de documentos.
Accederemos a esta herramienta desde la ficha Archivo > Información > Inspeccionar libro

Observamos un listado de utilidades, herramientas, funciones, etc. usadas en el libro presente!!!
Por ejemplo, fíjate en la imagen anterior (corresponde en parte al fichero problemático del ejemplo), como aparece listado 'Nombres ocultos'.

Sin duda una primera aproximación al problema... pero, si además presionamos el botón desplegable de 'Comprobar si hay problemas' > 'Inspeccionar documento', nos surgirá una ventana donde optar por analizar diferentes puntos de un libro:
Tablas dinámicas y segmentaciones
Gráficos dinámicos
Fórmulas de cubo
Escalas de tiempo (en caché)
Datos XML personalizados
Comentarios y anotaciones
Propiedades del documento e información personal
Encabezados y pies de página
Filas y columnas ocultas
Nombres y hojas de cálculo ocultos
Contenido invisible
Funciones de datos y vínculos externos
Encuestas de Excel
Propiedades de la hoja de cálculo personalizadas
(entre otras...)

Al Aceptar (botón Inspeccionar) los términos de ese listado del Inspector obtenemos unos resultados o validaciones en dicha ventana del Inspector del libro, donde decidiremos qué hacer:

Una herramienta muy útil y sobre todo sencilla, que nos permite empezar a buscar problemas...