domingo, 29 de abril de 2012

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.

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

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

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


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.

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

22 comentarios:

  1. Saludos,
    ...y cómo utilizo en una macro un nombre, que no es un rango sino una fórmula?. Me Explico: definí un nombre "SiIgual" que es una fórmula que retorna VERDADERO o FALSO y quiero utilizarla como evaluador en una parte de mi macro, algo asi como ...: If SiIgual then ...

    Gracias miles en nombre de miles que te aprovechamos.

    ResponderEliminar
    Respuestas
    1. ... bueno, yo mismo investigué y hallé respuesta a mi pregunta, la cual aporto con gusto por parecerme de utilidad y que otros puedan aprovechar también:

      Para usar un nombre definido por usuario en un libro que hace referencia a una fórmula y no a un rango (por lo cual retorna un valor) se utiliza la función o el método Evaluate.

      Ejemplo: si defino un nombre "SiIgual" en el libro de cálculo, puedo referirme a él en una macro asi: If Evaluate("SiIgual") Then ...

      ó si defino el nombre Ventas=Suma(H5:H30), en una macro podría decir: Ganancias = Evaluate("Ventas") * 15%

      ... espero les sea de utilidad.

      Saludos.

      Eliminar
    2. Hola Yav,
      disculpa por no atenderte a la primera cuestión, pero me ha sido imposible por diversos motivos...
      Agradezco tu aporte y la verdad me parece muy interesante la solución al problema.
      Un cordial saludo

      Eliminar
  2. Excelente aporte. Muchas gracias por esa macro, me salvó. Tenía 78 nombres definidos por cada una de las 12 hojas que estaba trabajando. Realmente fue una maravilla encontrar este blog.

    Muchísimas gracias.

    ResponderEliminar
    Respuestas
    1. Me alegro te sirviera Gustavo!!
      Un saludo

      Eliminar
  3. Excelente articulo, justo lo que necesitaba

    ResponderEliminar
  4. Hola Ismael espero que te encuentres bien, lo que te quería preguntar es si puedo saltar de una celda, por ejemplo, B2 a otra que este alejada como puede ser C14, presionando la tecla Enter. He averiguado en algunos foros y dicen que tengo que recurrir a Macros, ¿se puede hacer esto sin macros?

    Agradeciendo de antemano se despide Max Aedo

    ResponderEliminar
    Respuestas
    1. Hola Max!!
      no es necesario emplear macros, pero si realizar alguna operación previa.
      Imaginemos que tiene que recorrer en orden las celdas B2, C14 y D10.
      Lo primero sería seleccionar en ese orden las celdas.
      A continuación, por comodidad, asignas a ese rango discontinuo un nombre definido.
      Y listo, cada vez que quieras moverte entre esas celdas en ese orden, seleccionas ese nombre desde el Cuadro de nombres, y luego ir presionando Enter cada vez que quieras saltar de celda.

      Espero te sirva.
      Slds cordiales

      Eliminar
  5. Muchas gracias era justo lo que necesitaba

    Max Aedo

    ResponderEliminar
    Respuestas
    1. Sencillo y sin macros
      que más se puede pedir
      ;-)

      Eliminar
  6. Muchas Gracias, me ha servido de mucho tu aporte....

    ResponderEliminar
  7. hola, he aplicado tu codigo y es una gran cosa, me ayudo con mi problema,,, mil gracias por conpartir tus conocimientos

    ResponderEliminar
  8. Hola. Interesante. Pero, y si fuera al revés? Es decir, Cambiar el ámbito de Libro a solo una hoja?

    ResponderEliminar
    Respuestas
    1. Hola Bun,
      la diferencia, respecto al uso de VBA, para crear un Nombre definido con ámbito Libro o una hoja concreta estaría en la última línea del código del ejemplo:
      para ámbito Libro:
      ThisWorkbook.Names.Add nameNombreDefinido, refersTo

      para ámbito en Hoja2
      ThisWorkbook.Worksheets("Hoja2").Names.Add nameNombreDefinido, refersTo

      Como ves, en el segundo caso se menciona en qué hoja se añade el nombre definido.

      Saludos cordiales

      Eliminar
  9. ¡Con solo copiar y pegar tu Macro me has ahorrado bastante trabajo!

    Muchas gracias.

    ResponderEliminar
  10. Gracias!!! es excelente! me has salvado HORAS de trabajo!!

    ResponderEliminar
  11. El único problema es que cambia las áreas de impresión.
    Aquí un pequeño cambio para que no queden afectadas:

    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
    If InStr(1, LCase(nameNombreDefinido), "print") = 0 Then
    '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
    End If
    Next
    Next

    End Sub

    ResponderEliminar