42- Macros para manejar el Administrador de Nombres (colección NAMES)

En entrada anterior vimos el tema del Administrador de Nombres: dónde se ubica, cómo crear nombres de rango y asociarlos a celdas. Tema desarrollado desde la herramienta misma.

Hoy vamos a ver unas cuántas macros que nos permitan trabajar con esta herramienta a nivel programación (VBA).

Nota: La colección de 'Nombres de rango' que presenta el Administrador se denomina Names. A partir de la segunda macro veremos el uso y recorrido de esta colección.

1- Macro para asignar nombres de rango a celdas validadas.   (00:02:00

Sub AsignarNames()   'macro asigna indicadores a desplegable

    'nropro = sheets(....).[L4]    'guardar en variable el número de proyecto

'L4 de la hoja activa contiene el número de proyecto

indicaO = "indica" & Format([L4], "0000") & "_O"       'x ej: indica0025_O

indicaF = "indica" & Format([L4], "0000") & "_F"

indicaP = "indica" & Format([L4], "0000") & "_P"

 

On Error Resume Next

'en cada rango de celdas validadas se elimina cualquier validación anterior

'y se asigna una nueva lista con su fórmula correspondiente

With Range("E23:E27").Validation

    .Delete

    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _

    xlBetween, Formula1:="=" & indicaO

End With

With Range("E30:E33").Validation

    .Delete

    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _

    xlBetween, Formula1:="=" & indicaF

End With

With Range("E36:E40").Validation

    .Delete

    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _

    xlBetween, Formula1:="=" & indicaP

End With

End Sub

2- Macro para listar todos los elementos que se encuentran en el Administrador de Nombres: sus nombres y referencias.  (00:05:11)

Sub ListaNombres()     'listar todos los nombres de rango

    'se indica la hoja y primer fila para el listado

Sheets("LISTAS").[A1].ListNames

    'ListNames devuelve en 2 col el nombre y la referencia.

End Sub

3- Macro para listar Nombres de rango según un criterio establecido.  (00:06:34)

Sub listaNames()

    'listar en una col de cierta hoja todos los nombres de rango del libro.

Set hojax = Sheets("LISTAS")

x = 2

For Each Nb In ActiveWorkbook.Names

    If Left(Nb.Name, 7) = "indica0" Then

        hojax.Range("A" & x) = Nb.Name

        hojax.Range("B" & x) = Nb

        x = x + 1

    End If

Next Nb

MsgBox "Fin"

End Sub

4- Macros para eliminar todos los elementos del Administrador de Nombres.    

    a- Método 1: recorriendo cada elemento. (00:09:50) 

    b- Método 2: contando la cantidad de elementos.   (00:10:40) 

Sub quitaNbres()      'eliminar todos los nombres de rango (sin criterio)

'Método 1:

For Each Nb In ActiveWorkbook.Names

    Nb.Delete

Next Nb

MsgBox "Fin"

 

'Método 2:

canti = ActiveWorkbook.Names.Count   'contar la cantidad de nbres de rango

For i = 1 To canti

    ActiveWorkbook.Names(i).Delete

Next i

End Sub

    c- Contar cantidad de elementos en el Administrador de Nombres. Uso de NAMES.COUNT 

canti = ActiveWorkbook.Names.Count   'contar la cantidad de nbres de rango

5- Macro para eliminar elementos del Administrador de Nombres según un criterio.  (00:11:50)

Sub quitaNbres_Criterio()      'eliminar todos los nombres de rango (según criterio)

For Each elemento In ActiveWorkbook.Names

    'si el nombre comienza con el texto 'indica0' se elimina

    If Left(elemento.Name, 7) = "indica0" Then elemento.Delete

Next elemento

'opcional

MsgBox "Fin"

End Sub

Ver Video 42.