Cambiar el ámbito de un Nombre definido de Local a Global (de Hoja a Libro).

Seguro que muchas veces nos ha ocurrido lo mismo, tener un Nombre definido con un ámbito de aplicación local (de una sóla Hoja de nuestro Libro) y querer editarlo para cambiarlo a ámbito global (a todo el Libro) y no poder hacerlo desde el Adminstrador de nombres; viéndonos obligados, en muchos casos, a borrar el viejo y generar uno exactamente igual, pero sin olvidarnos de aplicar un ámbito a todo el Libro.

Vemos cómo el campo, al editar el nombre es No editable..

En esta entrada aprenderemos un truco, mediante macros, que replican de alguna manera esa forma manual que contabamos al principio. Con la macro que veremos un poco más abajo, buscaremos en cada Hoja del Libro todas los Nombres definidos locales (aplicados a Hojas) y tras 'procesarlos', los borraremos y generaremos otro Nombre, con las mismas características de Nombre y Hacer referencia a, pero con un Ámbito a Libro.
Pero antes, para comprender mejor el siguiente código debemos recordar algo que diferencia un Nombre local de un Nombre global:
Todos los nombres tienen un ámbito, ya sea una hoja de cálculo concreta (ámbito local) o el libro completo (ámbito global). El ámbito de un nombre es, por tanto, la ubicación dentro de la cual se reconoce el Nombre definido.

Por ejemplo, si hemos definido un nombre, como Nombrelocal, y su ámbito es Hoja1, este nombre sólo se reconocerá en Hoja1, pero no en el resto de hojas. Para poder emplear este Nombre local en otra hoja de cálculo, tendremos que precedorlo del nombre de la hoja de cálculo, como sigue:
Hoja1!Nombrelocal.
Por otro lado, si hemos definido un nombre, como NombreLibro, y su ámbito es todo el Libro de trabajo, éste se reconocerá en todas las hojas de cálculo del libro, pero no en otros libros.

Es importante recordar (Excel lo hace) que un nombre siempre debe ser único en su ámbito; sin embargo, es posible utilizar el mismo nombre en diferentes ámbitos. Por ejemplo, podríamos definir un nombre, como MismoNombre, para las Hoja1, Hoja2 y Hoja3 del mismo libro de trabajo. Aunque todos los nombres son iguales, cada uno es único en su ámbito. Incluso es posible definir el mismo nombre, MismoNombre, para el nivel de libro global, aunque una vez más el ámbito es único; aunque en este caso puede producirse un conflicto de nombre.

Lo importante para nosotros de todo lo explicado es que los Nombres con ámbito de Hoja, internamente Excel los nombra como:
Hoja1!Nombrelocal
es decir, nombre de la hoja seguido de la exclamación y el Nombre definido.. como en tantas otras herramientas o utilidades.
Este aspecto es del que nos aprovecharemos para localizar estos Nombres definidos 'locales' y cambiarles el ámbito a Libro.
Abriremos nuestro editor de VBA (Alt+F11) e insertaremos un módulo, en el cual añadiremos el siguiente código:

Sub CambiarAmbitoNombresDefinidos()
Dim HojaTrabajo As Worksheet
Dim NombreDefinido As Name
Dim nameNombreDefinido As String
Dim refersTo As String

'recorremos todas las hojas del Libro.
For Each HojaTrabajo In ThisWorkbook.Worksheets
    'pasamos por todos los Nombres definidos existentes en cada hoja
    For Each NombreDefinido In HojaTrabajo.Names
    'creamos variable con cada Nombre
    nameNombreDefinido = NombreDefinido.Name
        'evaluamos si tiene el símbolo !
        'lo que significa que es un Nombre con ámbilo de Hoja
        If InStr(nameNombreDefinido, "!") > 0 Then
        'si es cierto que tiene !, entonces generamos una variable Nombre
        'pero sin la primera parte del nombre anterior
        nameNombreDefinido = Mid(nameNombreDefinido, InStr(nameNombreDefinido, "!") + 1)
        End If
    'creamos variable con cada 'Se refiere a' de cada Nombre
    refersTo = NombreDefinido.refersTo
    'se borra el antigüo Nombre
    NombreDefinido.Delete
    'Creamos una nuevo con el nuevo Nombre referido al mismo rango que antes
    'pero en este cambio el ámbito de aplicación es todo el Libro
    ThisWorkbook.Names.Add nameNombreDefinido, refersTo
    Next
Next
End Sub

Como decíamos esta macro realiza, de alguna manera, lo que habitualmente hacíamos nosotros 'a mano', duplicamos el 'Nombre' y el 'Hacer referencia a', cambiando a Libro el ámbito.
Podemos comprobar si ejecutamos la macro, cómo efecivamente el resultado es el esperado, esto es, convierte el Nombre definido llamado Nombrelocal, cuyo ámbito es la Hoja1, a un Nombre definido llamado Nombrelocal con ámbito todo el Libro.