24 - Comentarios en Excel con VBA

En entrada de fecha Noviembre 2018 (video 17 del canal) hablamos de los estilos en Comentarios: diferentes formatos de fuente, colores, formas e incluso con imágenes.

En esta oportunidad veremos cómo programar el agregado de comentarios en diferentes eventos.

Al cambio en celdas.
1 - El primer ejemplo se trata de agregar un comentario a medida que vamos rellenando la tabla con datos. Si el importe registrado en col C es < a cierto valor se indicará con un comentario.
NOTA: para este ejemplo también es válida la opción de dejar un formato condicional. Pero el tema que nos ocupa hoy son los comentarios.

En el Editor, seleccionaremos el objeto HOJA donde se encuentre nuestra tabla de datos y allí colocaremos el siguiente código. El método utilizado es AddComment.

      Private Sub Worksheet_Change(ByVal Target As Range)
      'no se evalúan los cambios en fila 1
      If Target.Row = 1 Then Exit Sub
      'si el cambio se da en col 3 (C)
      If Target.Column = 3 Then
          'si la cifra es < 100 se deja nota
          If Target.Value < 100 Then Target.AddComment "Atención con este importe."
      End if
      End Sub

2 - En el segundo ejemplo se trata de agregar texto a comentarios que ya se encuentran en una celda.
Se trata de rellenar una tabla de días. Como son diferentes usuarios los que pueden registrar información se coloca un comentario por cada usuario que ha modificado las celdas de un rango de días. La información que se agrega es el nombre de usuario y el importe que ha registrado.

      Private Sub Worksheet_Change(ByVal Target As Range)
      'no se evalúan los cambios en fila 1
      If Target.Row = 1 Then Exit Sub
      'si el cambio se da en tabla de días ubicada a partir de col G
      If Target.Column > 6 Then
          'se agrega cada usuario e importe registrado en celdas de días
          On Error Resume Next
          comento = Target.Comment.Text
          'si es la 1ra vez se crea el comentario
          If comento = "" Then
              Target.AddComment Application.UserName & "_" & Target.Value
          Else
              Target.Comment.Text Text:=comento & Chr(10) & Application.UserName & "_" &          Target.Value
          End If
      End If
      End Sub

Primero se evalúa si la celda ya tiene comentarios. Si no lo tiene se agregará con el método AddComment visto en punto anterior.
Si lo tiene, se agrega un salto de línea y el nuevo comentario que será el nombre del usuario y el importe ingresado.

Agregar comentarios desde un proceso general.
También es posible contar con un proceso que se ejecutará desde cualquier otro evento, por ejemplo al clic de un botón, y allí evaluar si colocar o no un comentario en algunas celdas.
En el ejemplo se trata de armar un resumen de cierta tabla de datos y evaluar el resultado de cierta columna. Si es < a cierto valor se dejará un comentario en esa celda.

      Sub pasaTotal()
       'fin del rango de datos
      fini = Range("E1").End(xlDown).Row
      desti = Range("E" & Rows.Count).End(xlUp).Row + 1
      'se copia el rango E:F
      Range("E2:F" & fini).Copy
      Range("E" & desti).PasteSpecial xlPasteValues
      'se recorre el rango y coloca comentarios si el total es < 10
      For i = desti To Range("E" & Rows.Count).End(xlUp).Row
          If Range("F" & i) < 10 Then Range("F" & i).AddComment "Revisar valor"
      Next i
      End Sub

Esta macro se coloca en un módulo del Editor. Y se ejecutará desde un atajo de teclado, un botón o desde el mismo menú Desarrollador (o Programador). También es posible que sea llamada desde otro proceso con la instrucción:
      

                                        Call pasaTotal

El tema Comentarios da todavía para mucho más. En entradas siguientes seguiré desarrollando el tema.

  Descargar ejemplo desde aquí

  Acceso al  VIDEO N° 24.

23 - El método FINDNEXT para realizar búsquedas contínuas.

Siguiendo con el tema iniciado en video 16 de mi canal (entrada actualizada en mes de Febrero 2019) para una búsqueda con el método FIND, vamos a tratar ahora de realizar una búsqueda continua, con más de un criterio.
Para ello haremos uso del método FINDNEXT.

Opción 1:
Partiremos de una tabla de varias columnas donde una de ellas será la del primer criterio (mes) y otra contendrá datos del segundo criterio (art.). La tarea será calcular el acumulado de registros que coincidan en MES y ART.

Si bien esto puede ser resuelto con un filtro y la función Subtotales, vamos a ver cómo sería la programación en VBA utilizando los métodos FIND para encontrar el primer elemento y FINDNEXT para continuar la búsqueda hasta cubrir toda la tabla.

En el Editor de macros insertaremos un botón (que lo asociaremos al botón 'Actualizar' de la hoja) y allí copiaremos el siguiente código.

     Sub busquedaRepetidos()
     'busca el valor de F1 en todas las apariciones en hoja activa
     dato = Range("F1")
     'se inicia la búsqueda en col A de la hoja activa
     Set busco = Range("A:A").Find(dato, LookIn:=xlValues, lookat:=xlWhole)
     'si no se encuentra ninguna coincidencia se notifica y finaliza el proceso
     If busco Is Nothing Then MsgBox "Dato no encontrado": Exit Sub
     'guarda la 1er fila encontrada
     filx = busco.Row
     'inicia un bucle
     Do
         'compara la col B con criterio en G1
         If busco.Offset(0, 1) = [G1] Then
             totx = totx + busco.Offset(0, 2)
         End If
         'repite la búsqueda
         Set busco = Range("A:A").FindNext(busco)
     Loop While Not busco Is Nothing And busco.Row <> filx
     'terminó la búsqueda. Se coloca el acumulado en H1
     [H1] = totx
     End Sub

Opción 2:
También podríamos utilizar método FINDNEXT para obtener el acumulado de todo enero, sin uso del segundo criterio. Lo único que se quitaría en este caso es la comparación con la celda G1.
La macro en la parte del bucle DO....LOOP WHILE nos quedará del siguiente modo:

     'inicia un bucle
     Do
         totx = totx + busco.Offset(0, 2)
         'repite la búsqueda
         Set busco = Range("A:A").FindNext(busco)
     Loop While Not busco Is Nothing And busco.Row <> filx

Descargar libro de ejemplo desde aquí.

Acceso al VIDEO N° 23

Ver más macros con bucles en nuevo manual Bucles en Excel.

16 - Búsquedas con VBA - Método FIND

Entre las funciones Excel quizás la estrella de todas es BUSCARV, la que nos permite buscar un dato y devolver el contenido de otra columna de la fila encontrada.

En VBA podría decir que la estrella es FIND , que nos permite buscar un dato y obtener su ubicación para luego movernos o devolver otros valores en relación a la referencia encontrada.

La instrucción con todos sus argumentos es:
Find (What, After, LookIn, LookAt, SearchOrder, SearchDirection, MatchCase, MatchByte, SearchFormat)

What: dato a buscar
After: a partir de dónde se realiza la búsqueda
Lookin: búsqueda entre valores (xlValues) o fórmulas (xlFomulas)
LookAt: coincidir con el contenido total (xlWhole) o parcial (xlPart) de la celda.
SearchOrder: realizar la búsqueda por filas (xlByRows) o por columnas (xlByColumns)
SearchDirection: búsqueda siguiente (xlNext) o anterior (xlPrevious).

Pero como hay varias sintaxis posibles vamos a ver aquellas que pueden llegar a fallar (ejemplos 1 a 4) y aquellas más recomendables (ejemplos 5 a 7)

Ejemplo 1: sin control de error en caso de dato no encontrado.

      Cells.Find (qué se busca, a partir de dónde se busca, cómo se busca).Activate

Con Cells  le estamos indicando la hoja completa, no acotada a un rango en especial.
Con Activate o Select le estamos diciendo que cuando lo encuentre ya lo deje seleccionado.

Ejemplo 2:

       If j = 1 Then Set busca = ha.Find(numero)
      If j > 1 Then Set busca = ha.FindNext(busca)
      celda = busca.Address

Pero en cualquiera de los 2 casos, el código fallará si no encuentra el dato y por lo tanto no lo puede seleccionar o no puede obtener la dirección de la celda ya que no se encontró el dato a buscar.

Entonces habrá que colocar previamente una instrucción como la siguiente:
      On Error goto NoEncontrado
       Cells.Find (qué se busca, a partir de dónde se busca, cómo se busca).Activate

Y una entrada donde se instruya qué hacer en caso de no encontrar el dato.

      NoEncontrado:
           MsgBox "El dato buscado no se encuentra en la hoja activa."
           Exit sub

NOTA: también es posible utilizar On Error Resume Next  pero esto hará que siga con el resto de las instrucciones lo que en algún momento dará un resultado equivocado.

Ejemplo 3: acotando el rango de búsqueda pero a partir de celda seleccionada.
   
      Range("A1:H1").Find(What:="marzo", After:=ActiveCell, SearchOrder:=xlByRows).Select

La misma instrucción puede escribirse del siguiente modo. En este caso habrá que colocar una coma por cada argumento que se omite.

     Range("A1:H1").Find("marzo", ActiveCell, , xlByRows).Select

Aquí le indicamos el rango y con Select le instruímos que seleccione la celda encontrada.

Pero con After le estamos indicando que lo busque a partir de la celda activa.
Y esto nos dará error si la celda activa se encuentra a continuación del dato buscado o en otras filas.

Ejemplo 4: no indicando orden o dirección de búsqueda cuando el dato puede encontrarse en distintas celdas.
En la siguiente imagen se observa que el texto 'marzo' se encuentra en 2 lugares.

Para que no nos devuelva un dato erróneo debemos instruirle a Excel en qué dirección buscarlo con el argumento SearchOrder. En este ejemplo le indicamos que lo busque por columnas por lo que devolverá el de la celda A3:

        Cells.Find("marzo", , , SearchOrder:=xlByColumns).Select

Ejemplo 5: 
El método de búsqueda guarda los argumentos LookIn, LookAt y SearchOrder de la última búsqueda efectuada. Por lo tanto si no recordamos cuáles fueron esos argumentos utilizados debemos declararlos en nuestra instrucción para no correr riesgos de obtener resultados erróneos.
Una instrucción segura debiera incluir entonces:

     Range("A1:D8").Find("ma", Lookin:=xlvalues, lookAt:=xlPart, SearchOrder:=xlbyRows)

LookAt:=xlPart indica que el texto buscado debiera estar incluido en el contenido de la celda.
LookAt:=xlWhole indica que el texto debe coincidir con el contenido de la celda

Ejemplo 6: 
Al realizar búsquedas dentro de fórmulas, los argumentos que debemos incluir son:
LookIn:=xlFormulas
LookAt:=xlPart

Ejemplo 7: utilizando una variable declarada con SET
Este método puede ser el más preciso considerando la declaración de argumentos según cada caso.
Aquí buscamos en un rango de otra hoja, por columnas, un texto parcial y luego ejecutamos varias acciones con la celda encontrada. Si no se encuentra coincidencias se envía un mensaje.

     Sub buscando()
     Set ho2 = Sheets("Hoja2")
     Set busco = ho2.Range("A1:D8").Find("ma", LookIn:=xlValues, lookAt:=xlPart,    SearchOrder:=xlByColumns)
     If Not busco Is Nothing Then
         'podemos obtener todas las referencias del dato encontrado sin necesidad de seleccionarlo
         filx = busco.Row
         colx = busco.Column
        'traer un rango a partir de la celda encontrada
         rgo = Range(Cells(filx, colx), Cells(filx, colx + 2)).Address
         ho2.Range(rgo).Copy Destination:=ActiveCell
         'colorear celda encontrada
         busco.Interior.ColorIndex = 3
     Else
         MsgBox "No se encontró el dato buscado en la Hoja 2"
     End If
     End Sub

NOTA: Este método no requiere que se seleccione la hoja para obtener las referencias de la celda encontrada ni para realizar alguna tarea allí, como copiarla, darle color, obtener un rango a partir de la celda encontrada y tantas otras tareas.


Descargar ejemplo desde aquí.

Acceso al VIDEO N° 16.

22 - Minigráficos - Formatos con gráficos

Si necesitamos graficar rápidamente algún informe o ciertas columnas de una hoja de datos podemos recurrir a 2 herramientas que nos dejarán resultados en la misma columna (o en otra auxiliar). Ocupando menor espacio que un gráfico tradicional.

1 - Formatos con gráficos:
Entre los nuevos estilos de Formato Condicional podemos observar el que simula un gráfico de barras:

Se selecciona el rango que se desea graficar y se elige algún estilo de los mostrados en la opción 'Barras de datos'.
El resultado puede verse como en este ejemplo:

También podemos agregar otro formato más, como el de 'Escalas de color', quedándonos los 2 formatos superpuestos de este modo:

2- Minigráficos:
Esta herramienta requiere de una columna auxiliar donde colocaremos el minigráfico por cada fila de nuestro informe.
Seleccionamos la celda donde dejaremos el minigráfico y desde menú Insertar, Minigráfico se elegirá el modelo.

Se nos abrirá la siguiente ventana que nos permite seleccionar el rango de datos.

Si hemos seleccionado una sola celda para el gráfico, debemos seleccionar solo una fila de datos. Luego arrastraremos el resultado al resto de las celdas que necesitemos mostrar un gráfico.

Otro modo sería seleccionar todas las celdas de resultado (aquellas donde vamos a dejar el gráfico) antes de llamar a la herramienta. Y desde la ventana seleccionar todas las filas de datos.

En imagen siguiente vemos el resultado de 2 modelos de minigráficos: columnas y líneas.

Para borrar el gráfico de alguna celda seleccionarla con clic derecho, optar por Minigráfico, Borrar.

Para modificar el aspecto de los gráficos seleccionar una celda y desde el menú Herramientas de Minigráficos, Diseño se nos presentarán varias opciones: ajuste de color, puntos más altos o más bajos, etc.

Acceso al VIDEO N° 22.

21 - Cómo crear Funciones personales

Si bien Excel presenta gran cantidad de funciones y en cada versión se van agregando otras nuevas, en ocasiones no encontramos la que nos hace falta para ciertos cálculos especiales.
En esos casos, generalmente recurrimos a una macro. Pero si necesitamos realizar ese cálculo de modo frecuente.... ¿porqué no desarrollar nuestra propia función?

En este video se puede seguir el paso a paso de cómo se crearon 2 funciones personales:

1- Encontrar un número en una matriz:     =BUSCARM(nro; rango)

2- Sumar de modo alterno en un rango de una columna:  = SUMA_ALTERNA(rango)

Las funciones se desarrollan en un módulo del Editor.

IMPORTANTE: las funciones personales deben ser desarrolladas para ser utilizadas en cualquier escenario. Verificar si el resultado obtenido coincide con los resultados esperados en los distintos escenarios.

A continuación los códigos para cada función que encontrarán en libro que se deja para descargar.

      Function BUSCARM(dato, rango)
      'buscar en la matriz
      For Each cd In Range(rango.Address)
          If cd.Value = dato Then BUSCARM = cd.Address(False, False)
      Next cd
      End Function

      Function SUMA_ALTERNA(rango)
      '1er fila y col del rango
      fil = Range(rango.Address).Row
      col = Range(rango.Address).Column
      'total de filas
      filas = Range(rango.Address).Rows.Count
      'se recorre el rango en saltos de 2
      For i = fil To filas + fil Step 2
          tot = tot + Cells(i, col)
       Next i    
     SUMA_ALTERNA = tot
      End Function

3- Sumar de modo alterno en un rango de una columna, con criterio.
Si además necesitamos sumar según alguna condición al estilo SUMAR.SI, agregaremos como segundo argumento el criterio deseado:
     = SUMA_ALTERO(rango;criterio)

Lo que cambiará es la instrucción de suma, incuyendo la condición. En este ejemplo se sumarán los conceptos que incluyan el texto 'Adaptador')

      Function SUMA_ALTERO(rango, criterio)
      'instrucciones
      For i = fil To filas + fil Step 2
          If InStr(1, Cells(i, col - 1), criterio) > 0 Then tot = tot + Cells(i, col)
      Next i

      'resto de instrucciones

      End Sub

Descargar libro de ejemplo

Acceso al VIDEO N° 21.

20 - Gifs animados en un formulario

En épocas festivas como es el mes de diciembre, podemos 'decorar' nuestros formularios con un detalle como es la incorporación de algún gif animado.

En libro que se deja de ejemplo (al pie queda el enlace), se partió del Userform de la entrada publicada en el mes de marzo 2018: Interactuando con Userforms.
Lo que se ha hecho aquí es correr hacia abajo el frame o marco del formulario para insertar una especie de banner con un gif animado.

Previamente habrá que verificar si se tiene instalado el control llamado Microsoft Web Browser. Para ello desde la ficha Desarrollador o Programador se buscarán entre los controles tal como se muestra en la imagen.

Al Aceptar dibujar en la hoja el control. Si se encuentra correctamente registrado aparecerá un cuadro negro (ver imagen), de lo contrario se nos mostrará un mensaje de que falta instalar ese control.

En caso de tener que instalarlo, se pueden seguir las instrucciones dejadas en el siguiente video,

Una vez ya verificado que se dispone del control correctamente instalado, procederemos a entrar al Editor de macros y trabajar con nuestro Userform.
Dependiendo del tamaño de nuestro gif extenderemos el formulario hacia abajo o a derecha y moveremos el frame para poder ubicar el gif.

Incluso podríamos cambiar de lugar algunos controles para dejar el gif dentro del frame o marco:

Ahora nos resta incluir el código para mostrar la animación. 

Las instrucciones las colocaremos en el evento Initialize ajustando nombre del archivo gif.

Private Sub UserForm_Initialize()

  ruta = ThisWorkbook.Path         'la imagen se encuentra en el mismo directorio del libro

  archivo = "feliz-navidad"

  ext = ".gif"    

  WebBrowser1.Navigate _

  "about:<html><body scroll='no'><img src='" & _

  ruta & "\" & archivo & ext & " '></img></body></html>"

End Sub

Descargar ejemplo desde aquí

Acceso al  VIDEO N° 20.

19 - Combinar estructuras o bucles.

En programación contamos con algunas estructuras de código. Es decir un bloque de instrucciones que puede utilizarse para varias tareas.
Cuando estas instrucciones son repetitivas las llamamos Bucles.
Así encontramos diferentes bucles que pueden ser:

• While......Wend
• Do (While)...... Loop  y su variante: Do.....Loop While
• Do (Until)........Loop  y su variante: Do..... Loop Until
• For   ...Next
• For Each .... Next

Luego encontramos otras estructuras, que si bien no son repetitivas presentan un bloque de instrucciones comunes a varios escenarios:

• If .....Else......End If
• Select Case.... End Select
• With .....End With

Estas estructuras pueden estar contenidas unas dentro de otras.

Para el ejemplo, imaginemos que tenemos un libro con gran cantidad de hojas cada una con su formato: rangos de datos que inician y finalizan en diferentes filas y columnas, hojas en las que se borra un rango completo y otras donde deben ser mantenidas las fórmulas, diferentes condiciones a la hora de protegerlas... y así cada hoja tendrá sus propias características.

Si trabajamos en un libro durante el diseño y programación agregando registros de prueba, en algún momento necesitaremos dejarlo limpio de datos para iniciar su uso real.
O quizás al finalizar por ejemplo un año contable, se necesitan limpiar algunas hojas de registros para iniciar un nuevo período.
Entonces utilizaremos varias de estas estructuras para luego incorporar las instrucciones de borrado de datos.
A continuación se puede observar el armado de la macro con el uso de las siguientes estructuras:

1. For Each......Next
2. Select Case ..... End Select
3. With ......End With

      Sub LIMPIAR()

      'se recorren todas las hojas que deben ser vaciadas de datos:

      For Each sh In Sheets

          'se evalúa cuál es la hoja
          Select Case sh.Name

            'según el nombre de la hoja serán las instrucciones de borrado
            Case Is = "PRODUCTOS"

                 With Sheets("PRODUCTOS")
                    'instrucciones para hoja Productos
                 End With
           Case Is = "MOVTOS"
                With Sheets("MOVTOS")
                   'instrucciones para hoja Movimientos
                End With
           'el resto de las hojas
         End Select
     Next sh
     End Sub

En el siguiente VIDEO N° 19 se explica en detalle este tema.