por Hace unos días recibí una breve consulta acerca de cómo crear y actualizar un nombre definido en Excel mediante VBA.
Dado que la petición me pareció interesante, he desarrollado una pequeña macro que añade o actualiza nombres definidos en la macro.
Pero antes de entrar en el código, vemos los datos que nos van a servir de ejemplo, realizaremos el ejercicio con la base de datos habitual que suelo utilizar de ejemplo, empleados de unos grandes almacenes:
Los nombres definidos se pueden crear de dos formas o métodos:
1 – Crear nombre definido desde la selección: donde Excel crea automáticamente el nombre a partir de los datos que tengamos seleccionados en la hoja Excel. Este es el comando:
Además permite crear el nombre a partir de los valores de la primera fila, la última fila, la columna izquierda o la columna derecha. Además en caso de que el nombre definido (nombre) tenga espacios o empiece por un carácter numérico, automáticamente añade un carácter de subrayado «_».
Para este método 1 vamos a utilizar la siguiente macro:
Sub NombresDefinidos_metodo1()
Dim i As Double
Sheets("DATOS").Select
With Sheets("DATOS")
'Contamos las columnas con datos sobre las que crear el nombre definido
Fin = Application.CountA(Worksheets("DATOS").Range("1:1"))
'Desactivamos las notificaciones al actualizar los nombres
Application.DisplayAlerts = False
'Mediante un "for" creamos nombre definido por cada columna
For i = 1 To Fin
Range(Cells(1, i), Cells(1, i).End(xlDown)).Select
Selection.CreateNames Top:=True, Left:=False, Bottom:=False, Right:= _
False
Next
'Activamos las notificaciones
Application.DisplayAlerts = True
End With
End Sub
2 – Crear nombre definido asignándolo manualmente: los nombres definidos también se pueden crear de forma manual especificando nombre, ámbito (hoja o libro) y rango. Esto se realiza a través de este comando:
En este caso, el sistema no formatea el nombre con el carácter «_» sino que debes introducirlo tú directamente en caso de que la palabra que define al nombre contenga espacios o empiece por un número.
Para este método vamos a utilizar la siguiente macro:
Sub NombresDefinidos_metodo2()
'Declaramos las variables
Dim Nombre As String
Dim Seleccion As Range
'Con la hoja activa
With ActiveSheet
'Contamos las columnas
Fin = Application.CountA(.Range("1:1"))
'Iniciamos un "for" seleccionamos rangos de las columnas a partir de la segunda
'fila
For i = 1 To Fin
Set Seleccion = Range(Cells(2, i), Cells(2, i).End(xlDown))
'Si el nombre tiene espacios los sustituimos por "_"
Nombre = Application.WorksheetFunction.Substitute(.Cells(1, i).Value, " ", "_")
'Si el nombre comienza por número anteponemos un "_" (caracter subrayado)
If Mid(Nombre, 1, 1) = IsNumeric(Mid(Nombre, 1, 1)) Then
Nombre = "_" & Nombre
End If
'Agregamos nombre definido, o bien al libro o a la página
ActiveSheet.Names.Add Name:=Nombre, RefersTo:=Seleccion
Next
End With
End Sub
Como podéis observar he introducido unas líneas en el código para formatear el nombre en caso de espacios o de empezar por un número:
'Si el nombre tiene espacios los sustituimos por "_"
Nombre = Application.WorksheetFunction.Substitute(.Cells(1, i).Value, " ", "_")
'Si el nombre comienza por número anteponemos un "_" (caracter subrayado)
If Mid(Nombre, 1, 1) = IsNumeric(Mid(Nombre, 1, 1)) Then
Nombre = "_" & Nombre
End If
Para finalizar, me gustaría añadir una tercera macro que puede resultar de utilidad, un código que borra todos los nombres definidos de nuestro proyecto. La dejo en el archivo en otro módulo (aunque se puede introducir en las macros anteriores con un «Call» para que se ejecute en el mismo proceso):
Sub Elimina_nombres()
Dim Nombre As Name
'Por cada nombre definido en el libro, lo eliminamos.
For Each Nombre In ActiveWorkbook.Names
Nombre.Delete
Next Nombre
End Sub
En resumen, en ambos casos podemos añadir automáticamente nombres definidos al libro que estamos utilizando, pero solo en la segunda macro vamos a poder especificar si queremos que el ámbito del nombre sea el libro o la hoja (activa).
Este es el resultado de aplicar cualquiera de las dos macros:
Y con estas dos macros ya podemos añadir y actualizar nombres definidos a nuestra hoja o libro Excel 🙂
Descarga el archivo de ejemplo pulsando en: CREAR NOMBRE DEFINIDO EN EXCEL CON VBA
Excelente!! Si quisiera hacer lo mismo pero para tomar los nombres que aparecen en las celdas de la columna B y colocarlos como NOMBRES DE CELDA en las celdas de la columna H por ejemplo, como cambiaria la macro?
Hola:
No comprendo que es lo que necesitas, en este post lo que se hace es crear nombre definido y guardarlo en el sistema. No entiendo bien, eso de colocarlos como nombre de celda en las celdas de la columna H.
Saludos
Hola , todos los ejemplos me parecen magníficos y muy útiles. Tengo una pregunta. ¿Si adicionalmente quiero dar un nombre al conjunto de esos datos (por ejemplo mirango), como debería hacer?. gracias
Hola Manuel:
En la segunda macro los nombres de asignan así: ActiveSheet.Names.Add Name:=Nombre
En la primera macro es de forma automática y se crean con los datos introducidos en la hoja,.
Saludos.
Hola Segu.
Muchas gracias por tu pronta respuesta. Ante todo, te pido disculpas pues como podrás observar conozco muy poco todavía de macros.
Intentaré explicarme algo más. La macro 2, después de probar creo que no realiza lo que necesito. intentaré explicarme.
Ejemplo:Si tengo el siguiente rango («A1 :E4»). la macro 2, hace perfecto, el dar nombre a cada columna. Mi cuestión es, ¿ como puedo además crear el nombre del rango («A1:E4») en la misma macro.
gracias
Hola Manuel:
Para agregar además otro nombre, debes añadir estas dos líneas después del next:
Set Seleccion1 = Range(«A1:E4″)
ActiveSheet.Names.Add Name:=»Mirango», RefersTo:=Seleccion1
Saludos.
Hola Segu
Muchas gracias. Está entendido. Tan solo una cosa mas, El Range(«A1:E4»), era un ejemplo, la realidad es que debería ser para un rango inicialmente no conocido, por lo que debería ser una variable, al igual que lo has hecho con los nombres de cada columna.
¿Eso como se puede hacer?.
Muchas gracias por todo.
Hola Manuel:
En ese caso debes hacer un esfuerzo e intentar aprender sobre variables, tipos, declaraciones, cómo programarlas, etc. Es un tema extenso como para responderlo aquí.
No obstante, un ejemplo sencillo, si quieres seleccionar todo el rango desde A1 hasta el final de la tabla, sería así:
Set Seleccion = Range(Cells(1, 1), Cells(1, 1).SpecialCells(xlLastCell))ActiveSheet.Names.Add Name:="mirango", RefersTo:=Seleccion
Saludos
Hola Segu
Muchas gracias. Efectivamente con ese código unido a la macro inicial, el procedimiento funciona perfectamente.
En cuanto al esfuerzo personal que debo realizar para aprender, en ello estoy, no me resulta excesivamente fácil los conceptos, pero pondré todo mi empeño. Te reitero mi agradecimiento.
Manuel
¿Cómo se pueden usar los nombres definidos con el Administrador de Nombres en EXCEL en una macro VBA?. Los nombres pueden referirse a un rango de celdas, a una función de otro nombre o a una constante. Ejemplo matriz=Range(A1:D200), cantidad=filas(matriz), constante=10
Hola Pedro.
No entiendo tu pregunta.
Saludos
hola, bueno, para que los nombres definidos de rangos en vba sean visible en todo el libro?, porque si deseo hacer referencia desde otra hoja, ya no se muestran los nombres de rango definidos en la macro. gracias de antemano
Si bajas el ejemplo, verás que con el método 1 ya hace referencia al libro, y si quieres que lo haga el método 2 también, debes sustituir en la macro:
ActiveSheet.Names.Add
Por esto:
ThisWorkbook.Names.Add
Hola qué tal!! Hay alguna forma de nombrar rangos mediante el contenido de una celda? Por ejemplo, en mi celda «A1» tengo la palabra «Inquilinos005» y quiero que mi rango (A2:A60) se nombre así, pero si lo cambio a «Inquilinos006» la macro cambie automáticamente el nombre de mi rango a ese.
En esta rutina el rango tendrá el nombre que tenga la celda A1.
Y lista el contenido desde A2 al final
Sub Macro1()Set Seleccion = Range(Cells(2, 1), Cells(2, 1).End(xlDown))
ActiveWorkbook.Names.Add Name:=Range("A1"), RefersTo:=Seleccion
End Sub
Muy Bien. Gracias por el aporte