Señalar rangos de nombres definidos - versión mejorada

En la nota anterior mostré como hacer visibles los rangos de nombres definidos en una hoja de Excel.  Uno de mis lectores me pregunta si se puede mejorar la macro de manera que cada rango se señale con un color de fondo distinto.
Para hacerlo tendremos que modificar un poco el código de la macro de la nota anterior, agregando el mecanismo para crear colores de fondo en forma aleatoria. Además tendremos que asegurarnos que el tono de los colores no oculte el contenido de las celdas.

El código modificado es el siguiente:

 Sub mostrar_nombres_dif_color()  
   Dim n As Name  
   Dim strNameStart As String  
   On Error Resume Next  
   Application.ScreenUpdating = False  
   For Each n In ActiveWorkbook.Names  
     With Range(n.RefersTo).Interior  
       .ColorIndex = Int((56 * Rnd) + 1)  
       .TintAndShade = 0.9  
     End With  
     strNameStart = Left(n.RefersTo, WorksheetFunction.Find(":", n.RefersTo) - 1)  
     With Range(strNameStart)  
       .AddComment  
       .Comment.Text n.Name  
       .Comment.Visible = False  
     End With  
   Next n  
   Application.ScreenUpdating = True  
   On Error GoTo 0  
 End Sub  

Como puede apreciarse estoy usando la expresión Int((56 * Rnd) + 1) para generar números enteros entre 1 y 56. Como alguno de estos colores son oscuros e impedirían ver el contenido de las celdas, usamos la propiedad .TintAndShade con un valor de 0.7 para obtener tonos pálidos. Si queremos tonos má claros usamos números más cercanos al 1, por ejemplo 0.85.

El resultado puede verse en esta animación

He agregado la sentencia On Error Resume Next para evitar la interrupción de la rutina si en la primer celda del rango ya existe un comentario. En ese caso el comentario original quedará y no se registrará el nombre del rango.

Como cuestión de buena práctica recomiendo no usar  On Error Resume Next tal como se ve en el código. Pero me permito esta licencia ya que se trata de una pequeña herramienta que puede resultar útil al construir modelos complejos.

Hacer visibles los nombres definidos en una hoja Excel

Cuando usamos varios nombres en nuestras hojas suele surgir la necesidad de visualizar los rangos a que se refieren los nombres.
El método más inmediato es usar Pegar Nombres-Pegar Lista, que accedemos con la tecla F3

De esta manera obtenemos una lista de los rangos de los nombres, pero si buscamos un método más visual tendremos que echar mano a otras técnicas.

Un viejo y conocido truco es reducir el nivel de zoom en la hoja a menos de 40%

Al hacerlo los nombres aparecen sobre los rangos. Pero este método tiene sus inconvenientes. El tamaño de los nombres se adecua al tamaño del rango, haciendo que alguno de ellos sea ilegible. Además el nivel de zoom dificulta la lectura y el trabajo en la hoja.

La solución que propongo es una macro que ponga un fondo de color al rango del nombre definido e inserte un comentario en la primer celda del rango.

El código de la macro es el siguiente:

 Sub mostrar_nombres()  
   Dim n As Name  
   Dim strNameStart As String  
   For Each n In ActiveWorkbook.Names  
     Range(n.RefersTo).Interior.ColorIndex = 36  
     strNameStart = Left(n.RefersTo, WorksheetFunction.Find(":", n.RefersTo) - 1)  
     With Range(strNameStart)  
       .AddComment   
       .Comment.Text n.Name  
     End With  
   Next n  
 End Sub  

Y este el resultado despues de activar la macro

Si queremos que los comentarios no sean visibles definimos la propiedad Visible como False

 Sub mostrar_nombres()  
   Dim n As Name  
   Dim strNameStart As String  
   For Each n In ActiveWorkbook.Names  
     Range(n.RefersTo).Interior.ColorIndex = 36  
     strNameStart = Left(n.RefersTo, WorksheetFunction.Find(":", n.RefersTo) - 1)  
     With Range(strNameStart)  
       .AddComment  
       .Comment.Text n.Name  
       .Comment.Visible = False  
     End With  
   Next n  
 End Sub  

Generar con Excel una lista de todas la permutaciones posibles

Cómo generar una lista con todas las permutaciones posibles usando Excel es una de las consultas recurrentes que recibo.
En este post publico una rutina de Vb (macro) que permite hacerlo, pero me apresuro a aclarar que el código no es de mi autoría. El código fue publicado por John Walkenbach en esta página y él a su vez aclara que no conoce el autor del algoritmo.

Entrando más en tema, todas la permutaciones posibles de un grupo de un conjunto de elementos (números o letras) está dado por el factorial de ese número. Por ejemplo, a partir del número 123 se pueden crear seis combinaciones(=FACT(3)=6):

• 123
• 132
• 213
• 231
• 312
• 321

El número de permutaciones crece violentamente con el número de elementos, como puede verse en esta tabla (calculada con la función FACT):

Por este motivo el código permite calcular las permutaciones de hasta 8 elementos.

Los resultados aparecen siempre a partir de la celda A1 de la hoja activa.

Para obtener las permutaciones activamos la rutina GetString, que comprueba si el texto ingresado tiene más de 1 caracter o menos de 9, y luego llama a la rutina GetPermutation.

Option Explicit

Dim CurrentRow

Sub GetString()
    Dim InString As String

    InString = InputBox("Ingrese el numero o texto")
    If Len(InString) < 2 Then Exit Sub
    If Len(InString) >= 8 Then
        MsgBox "Demasiadas permutaciones"
        Exit Sub
    Else
        ActiveSheet.Columns(1).Clear
        CurrentRow = 1
        Call GetPermutation("", InString)
    End If
End Sub

Sub GetPermutation(x As String, y As String)
'   Algoritmo de autor desconocido

    Dim i As Integer, j As Integer
    j = Len(y)
    If j < 2 Then
        Cells(CurrentRow, 1) = x & y
        CurrentRow = CurrentRow + 1
    Else
        For i = 1 To j
            Call GetPermutation(x + Mid(y, i, 1), _
            Left(y, i - 1) + Right(y, j - i))
        Next
    End If
End Sub

Tamaño de los comentarios en Excel - macro mejorada

Las advertencias sobre la falta de control de errores en la macro de la nota anterior sobre el manejo del tamaño de los comentarios no aliviaron mi conciencia. Estoy seguro que alguno de mis lectores habrá adoptado la macro e intentado usarla para impresionar a su jefe (o, mejor aún, a su secretaria) descubriendo en el intento que si se ingresan datos incorrectos, la macro falla mostrando con poca gracia el consabido mensaje

Aquí publico una versión mejorada, con control de errores y con un formulario para ingresar los valores de la altura y el ancho del cuadro en una sola operación.

En un módulo común del editor de Vb ponemos estos códigos

Sub abrir_uf()
    ufDimensiones.Show
End Sub

Sub cambiar_dimension_comentario_3(x As Double, y As Double)

    Dim shComment As Comment

    Unload ufDimensiones

    For Each shComment In ActiveSheet.Comments
        With shComment
            .Shape.Width = x
            .Shape.Height = y
        End With

    Next shComment

End Sub


El primer código abre el formulario (Userform) donde ponemos las dimensiones deseadas para los comentarios. El segundo recibe los datos del formulario y los aplica a los comentarios.

Una vez diseñado el Userform

agregamos los códigos en el módulo del userform

Private Sub cbCancelar_Click()
    Unload ufDimensiones
End Sub

Private Sub cbAceptar_Click()
    Dim pntAltura As Double, pntAncho As Double

    On Error GoTo NoEsNumero
    With ufDimensiones
      
        pntAltura = .tbxAltura / 2.54 * 72
        pntAncho = .tbxAncho / 2.54 * 72
    
    End With
    

    Call cambiar_dimension_comentario_3(pntAltura, pntAncho)

    Exit Sub

NoEsNumero:
MsgBox "El valor ingresado no es un numero", vbCritical
MsgBox "Vuelva a intentarlo o apriete Cancelar", vbInformation

End Sub


Este video muestra el funcionamiento de la macro

Controlando el tamaño de los comentarios en la hoja de Excel

Cuando introducimos un comentario en una celda Excel le dá un tamaño predeterminado. El comentario es una forma y como tal tiene propiedades que podemos cambiar. Si hacemos visible el comentario podemos abrir el menú contextual de sus propiedades, como ya he mostrado en este post nueve años atrás

Un lector me consulta sobre cómo cambiar de una el tamaño de todos los comentarios en una celda. En su caso se debe a que cada comentario tiene un tamaño distinto, pero también puede darse el caso de querer cambiar todos los comentarios del tamaño estándar a otro.

La selección multiple de cuadros de texto la hacemos apuntando con el mouse usando la tecla Shift (Mayúsculas)

Pero no podemos hacer lo mismo con los comentarios. Por lo tanto tendremos que echar mano a las macros.

Empezamos con una macro sencilla que selecciona todos los comentarios en la hoja activa y les da un tamaño (altura y ancho) determinado

Sub cambiar_dimension_comentario_1()

    Dim shComment As Comment

    For Each shComment In ActiveSheet.Comments
        With shComment
            .Shape.Width = 108
            .Shape.Height = 82
        End With

    Next shComment

End Sub

Mis atentos lectores habrán notado que en el cuadro de propiedades (la primer imagen del post) la altura y el ancho del comentario aparecen en centímetros, mientras que en el código de la macro usamos otra unidad. El tamaño del cuadro del comentario, y de todas las formas, la expresamos en Vba en "puntos".
En una pulgada, 2.54 centímetros, hay 72 puntos. Para convertir las dimensiones de centímetros a puntos, a los efectos de usarlas en la macro, tenemos que dividir la dimension en centímetros por 2.54 y multiplicar el resultado por 72.

Así que vamos a mostrar una macro más elaborada donde el usuario ingresa las medidas deseadas en centímetros y el código las traduce a "puntos":

Sub cambiar_dimension_comentario_2()

    Dim shComment As Comment
    Dim dblShW As Double
    Dim dblShH As Double

    dblShW = InputBox("Ancho en centimetros?", "Dimensiones del Comentario")
    dblShH = InputBox("Altura en centimetros?", "Dimensiones del Comentario")

    For Each shComment In ActiveSheet.Comments
        With shComment
            .Shape.Width = dblShW * (72 / 2.54)
            .Shape.Height = dblShH * (72 / 2.54)
        End With

    Next shComment

End Sub

En esta macro usamos InputBox sin validación de los valores introducidos, así que deberemos ser cuidadosos de ingresar números reales (Actualización: una versión mejorada está disponible en este post).

Este video muestro el funcionamiento de la macro.

Calcular registros únicos con PowerPivot

En la nota anterior vimos como calcular los registros únicos de un campo usando la técnica "pivotear una pivot", es decir, crear una tabla dinámica cuya base de datos es a su vez una tabla dinámica. Esta técnica nos permite sobrellevar los problemas de tiempo de proceso que surgen al usar las técnicas más tradicionales: campo auxiliar con CONTAR.SI y Filtro Avanzado. Recordemos que si estamos usando Excel 2013 podemos agregar la base de datos al modelo de datos y usar luego la función Recuento Distinto para resumir el campo (ver en la parte final de la nota mencionada).

Existe una forma aún más sencilla de hacer el cálculo de registros únicos (o Recuento Distinto) si usamos Excel 2010. Primero deberemos asegurarnos de instalar el complemento PowerPivot (¿cómo??!!!! ¿Todavía no lo han hecho?!!!).

Volviendo al ejemplo de la nota anterior, empezamos por cargar la base de datos en la ventana del PowerPivot para lo cual seleccionamos alguna de las celdas de la tabla de datos y usamos "Crear tabla vinculada"

Una vez cargada creamos una tabla dinámica con la opción "Tabla Dinámica" en la ventana del PowerPivot

con el campo País en el área de las filas e Importe en el área de los valores

Para poder calcular la cantidad de clientes por país, vamos a crear una "Medida". En PowerPivor una "medida" (measure, en inglés) es similar a los campos calculados de las tablas dinámicas tradicionales pero mucho más flexibles y potentes . No entraré aquí en el tema, pero a los interesados en el potencial de PowerPivot les sugiero visitar el sitio de Powered Solutions (en español)

Volviendo al tema, creamos una medida apuntando al nombre de la tabla en la lista de campos y eligiendo la opción "Agregar nueva medida"

En la ventana que se abre para definir la medida vamos a usar la función DISTINCTCOUNT (esta es una función DAX que pertenece a PowerPivot, tema que no tocaremos en esta nota).

Este video muestra el proceso



El resultado final es este