CREAR NOMBRE DEFINIDO EN EXCEL CON VBA

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:

CONECTAR BASE DE DATOS DE EXCEL_1

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:

crear-nombre-definido-en-excel-con-vba1

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:

crear-nombre-definido-en-excel-con-vba2

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:

crear-nombre-definido-en-excel-con-vba3

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

 

Anuncios

¿Te ha gustado?. Deja un comentario

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s