martes, 4 de diciembre de 2018

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 se explica en detalle este tema.

domingo, 25 de noviembre de 2018

Formato condicional con fórmulas

El Formato Condicional es una herramienta muy útil a la hora de marcar registros que cumplen ciertas condiciones.
Generalmente marcamos de color registros de una lista que presentan ciertos valores (textos o números).
Así por ejemplo, podemos marcar en una lista de días los domingos de algún color como se observa en la siguiente imagen:
Lo que se hizo fue seleccionar el rango. Y desde menú Inicio, Formato Condicional, Nueva regla se seleccionó la 2da opción. En los campos de criterio se puede elegir que sean 'igual', 'menor o mayor que' y otros más. Luego se completa el tipo de formato y ya lo tendremos listo.
En la siguiente imagen se optó por valores superiores a un número y así se observa:
Otra opción que nos presenta esta herramienta de formato, es aplicarlo a valores duplicados o únicos. En ejemplo siguiente se optó por marcar los duplicados. Previamente se seleccionó el rango y se aplicó el formato de color. 

Ahora, en este ejemplo vemos que se seleccionan los 2 duplicados. ¿Cómo debiéramos aplicar el formato si solo deseamos marcar la 2da o siguiente aparición de cierto valor, pero no el primero?.
Utilizaremos la última opción que nos ofrece el Formato Condicional: con fórmulas.

Ejemplo 1: marcar repeticiones.
La fórmula utilizada es CONTAR.SI(rango hasta la celda utilizada;valor ingresado) > 1
Es importante aquí respetar los signos $ ya que estos indican el mismo inicio de rango para todos los registros y hasta la fila del registro que se está ingresando.

Ejemplo 2: marcar el valor máximo.
Aquí utilizamos la función MAX en la siguiente fórmula:
=I3=MAX($I$3:$I$40)
Lo que estamos indicando en cada fila es que si la celda es igual al valor máximo del rango se aplique el formato. Nuevamente es importante respetar los signos $ .

Ejemplo 3: marcar el valor máximo si valor <> ""
A la fórmula anterior le agregamos otra condición par que no se pinte el rango cuando está vacío. La fórmula en este caso será:
=Y(I3=MAX($I$3:$I$40);I3<>"")

Nota: En Manual Excel se podrán encontrar otros ejemplos del uso de fórmulas en el formato condicional.

*Descargar ejemplo desde aquí.

*VER VIDEO.





martes, 13 de noviembre de 2018

Comentarios en Excel

Seguramente todos en algún momento tuvimos necesidad de dejar algún comentario o recordatorio en alguna celda: cómo se ingresa un dato o qué tipo de conceptos se deben introducir, etc.

Para los que nunca utilizaron aún esta herramienta de Excel habrán visto alguna vez unos triángulos rojos pequeñitos en un extremo de una celda: esto indica que allí hay un comentario. Al pasar el ratón por esa celda se podrá leer su mensaje.


Si bien ese es su aspecto predeterminado, no es el único modo de dejar comentarios.

A continuación veremos otros modos de presentarlos.

1- Modelo clásico: Se selecciona una celda y se llama al menú Revisar, Nuevo comentario.  Escribir el mensaje. Al finalizar, el cuadro se ocultará marcando la celda con el triángulo rojo.
Para modificar el comentario seleccionaremos la celda e ingresaremos al mismo menú Revisar. Ahora el botón en lugar de Nuevo dirá Editar comentario.
Desde allí se podrá seleccionar todo o parte del texto y asignar fuente y tamaño como lo haríamos en cualquier celda. También negrita y/o cursiva.
Otra opción que nos presenta el menú Revisar es la de Eliminar comentario.


Nota:  Excel toma el nombre de usuario que aparece en las Opciones de Excel, General. Se puede borrar o colocar otro nombre de usuario para algún caso en concreto en el mismo cuadro del comentario. O modificar el campo 'usuario' desde las Opciones generales.

2- Modelo clásico con formato. Los cuadros de texto pueden recibir otros formatos no solo para la fuente.
Seleccionar la celda con comentario y desde menú Revisar, Editar comentario.
Clic derecho sobre los bordes del cuadro y optar por Formato de comentario lo que nos abrirá la ventana que se observa en la imagen.


Nota:  Observar que también desde esta ventana se puede modificar la fuente y color de la misma como lo haríamos desde formato de celdas.
Otros formatos a modificar: color y línea, alineación, márgenes, tamaño del cuadro.
Desde la pestaña Propiedades podemos optar por: 
- Mover y cambiar tamaño con la celda, Observar en la imagen siguiente que el texto se autoajustó al nuevo tamaño del cuadro.
- Mover pero no cambiar tamaño con la celda.
-No mover ni cambiar tamaño con la celda.


También desde la ventana Formato de comentarios puedo cambiar la forma del cuadro. Pero para ello previamente agregaremos la opción Cambiar forma en la barra de herramientas de acceso rápido.

Entonces una vez seleccionado el cuadro del comentario desplegamos el botón Cambiar forma y seleccionamos otra a gusto.

3 - Modelo con imagen: Si en lugar de texto deseamos mostrar una imagen editaremos nuevamente el comentario desde la opción Formato de Comentarios.
En pestaña Colores y líneas abrir el desplegable en Relleno, Color.
Desde Efectos de relleno se nos presentará otra ventana con una pestaña Imagen. Desde allí seleccionaremos la de nuestro agrado y aceptaremos.


4 - Modelo al clic de celda. Esta opción es ideal cuando la hoja presenta demasiados colores y formatos lo que quizás nos impida 'notar' la presencia de algún comentario en modo clásico (el triangulo en el extremo de la celda).

En lugar de insertar un comentario lo que haremos es seleccionar la celda o rango de celdas y desde el menú Datos le aplicaremos Validación de datos.
En la primer pestaña (Configuración) dejaremos lo predeterminado ('Cualquier valor')
Y desde la pestaña Mensaje de entrada tildamos la opción Mostrar mensaje al seleccionar la celda y escribimos el texto en el cuadro de mensaje.

5- Modelo con texto flotante. En una de las entradas de Septiembre 2018 comenté acerca de presentar controles ActiveX en modo flotante dentro de una hoja. 
Este mismo modelo nos servirá además para presentar un comentario.

Imaginemos que en la tabla de ejemplo podemos registrar importes en 2 monedas diferentes. 
La macro se ejecutará al seleccionar una celda de la columna de Importes. Se evaluará si el código inicia con letra A y en ese caso indicará que los importes deben corresponder a $, en caso contrario a dólares.


La macro se coloca en el objeto HOJA donde tengamos estos comentarios. Y el evento es a la selección de la celda (SelectionChange)

        Private Sub Worksheet_SelectionChange(ByVal Target As Range)
        'x Elsamatilde
        'solo se ejecuta en col G (importes)
        If Target.Column <> 7 Or Target.Row < 4 Then
            'si no corresponde a col G se oculta el control de texo
            TextBox1.Left = Columns("Z").Left
            Exit Sub
        End If
        'según el primer caracter del código (col C) será el texto a mostrar
        If Left(Range("C" & Target.Row), 1) = "A" Then
            TextBox1.Text = "Precios en $"
        Else
            TextBox1.Text = "Precios en USD"
        End If
        'ubicar el texto a la altura de la celda y sobre el margen de col I
            TextBox1.Top = Target.Top
            TextBox1.Left = Columns("I").Left
        End Sub


Ver VIDEO.


viernes, 26 de octubre de 2018

Búsquedas con VBA

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í.

ver VIDEO


domingo, 21 de octubre de 2018

Utilizar fórmulas mediante VBA

En ocasiones necesitamos 'armar hojas' a las que ya colocaremos algunas fórmulas.
Si esta tarea la realizamos mediante programación necesitamos conocer las instrucciones según el tipo de fórmula que utilicemos.

Veamos 3 modos de escribir fórmulas y luego haciendo uso de un 'ayudante'.

1- Utilizar una fórmulas pero registrar el resultado en la celda.
       [O12] = Application.WorksheetFunction.WeekNum([J12])

En esta instrucción estamos diciendo que O12 sea igual al resultado que nos devuelva la función: Num.de.Semana (número de semana) de la fecha ubicada en J12.

2- Colocando una fórmula:
       [O12].Formula = "=WEEKNUM(J12)"

Nota: la lista completa de funciones (inglés-español) la pueden descargar de la sección Fórmulas de mi sitio web.

3- Colocando una fórmula con notación R1C1, es decir en referencia a la ubicación de la celda.
      [O12].FormulaR1C1 = "=WEEKNUM(RC[-5])"

Aquí le estamos diciendo que la celda con la fecha se encuentra en la misma fila (R) y 5 columnas a izquierda de la columna O 

Estas 3 notaciones permiten también el uso de variables como veremos a continuación:

Ejemplo 1:
Tenemos una macro que filtra una hoja base y a continuación del resumen obtenido colocaremos una fórmula para obtener el total de registros filtrados.

La fórmula utiliza la función CONTARA y el rango va desde la fila 12 hasta la última celda ocupada.
En primer lugar necesitamos establecer cuál es la última celda, que la busco desde la col D.

          x = Range("D" & Rows.Count).End(xlUp).Row

Para este ejemplo la fórmula sería: =CONTARA(C12:C17)

Caso 1: dejar el resultado de la fórmula:
             [C26] = Application.WorksheetFunction.CountA(Range("C12:C" & x))

Caso 2: utilizar la sintaxis FORMULA solamente:
             [C26].Formula = "=COUNTA(C12:C" & x & ")"

Caso 3: utilizar la sintaxis FORMULAR1C1:
              'calcula a cuántas filas por encima de la 26 se ncuentra la última del rango
              y = 26 - x   
             [C26].FormulaR1C1 = "=COUNTA(R[-14]C:R[-" & y & "]C)"

Ejemplo 2:
Si necesitamos colocar una fórmula más compleja utilizaremos la grabadora de macros para obtener las instrucciones precisas.
 - Para ello ya tendremos escrita (o copiada) la fórmula en la primer celda donde la necesitemos.
 - Encendemos la grabadora de macros (con botón ubicado en extremo inferior izquierdo de la hoja o desde el menú Desarrollador, Macros). Con opción de crearla en 'Este libro'.
 - Colocamos el cursor en la barra de fórmulas y damos Enter.


 - Detenemos la grabadora. En el Editor de macros la encontraremos en algún módulo.

      Sub Macro1()
      '
      ' Macro1 Macro
      '
     ActiveCell.FormulaR1C1 = _
        "=IF(RC[3]=""Pendiente"",""Vencida            ""&TEXT(------------------) & ""
     Range("L2").Select
     End Sub

Ahora solo nos resta ajustarla para incluir las variables si fuesen necesarias. Recordar que la sintaxis debe presentar este formato: entre comillas la parte fija y entre & la parte variable.
Por ejemplo:
                      [C28].FormulaR1C1 = "=COUNTA(R[-16]C:R[-" & y & "]C)"

Ver VIDEO

lunes, 24 de septiembre de 2018

Simular un CheckBox

Los controles CheckBox o casillas de verificación, cuando los dibujamos en la hoja, presentan un tamaño fijo y generalmente demasiado pequeños. 

Para no tener que acudir a alguna api podemos resolver el problema del tamaño con algunos trucos que simulen una casilla.

Ejemplo 1:
A una celda donde querramos presentar una casilla de verificación le vamos a asignar estos formatos: tamaño de fuente mayor, negrita y centrada en alto y ancho.
En el VIDEO pueden observar que a continuación inserté una forma (rectángulo) y la coloqué por encima de esa celda. Dejé el objeto con borde y sin relleno.  

En el Editor de macros insertamos un módulo y escribimos el siguiente código que corresponde a la celda B3:


          Sub marcando()
          If [B3] = "" Then
              [B3] = "X"
          Else
              [B3] = ""
          End If
          End Sub

Solo nos resta asignar esa macro al objeto dibujado en la hoja. Haremos clic derecho sobre el mismo, opción 'Asignar macro' y seleccionamos la llamada 'marcando'

Y así lo repetiremos para cada cuadro dibujado que simule una casilla. Cada uno tendrá su macro donde debe hacerse mención a la celda que se encuentra debajo de cada cuadro
NOTA: en el código se le asigna la letra X a la celda, pero cada usuario puede elegir qué texto mostrar.

Ejemplo 2:
El ejemplo anterior presenta el inconveniente de que se necesita un objeto o forma por cada casilla que necesitemos.
Entonces, otro truco es simular o formatear directamente la celda como una casilla o checkbox.

Seleccionamos todas las casillas necesarias y le damos el formato: negrita, centrada en alto y ancho, con borde.
La fuente elegida para simular el tilde es Webdings dándole un tamaño de 16 o mayor. La letra 'a' corresponde al tilde típico de un checkbox.
Y en este caso, la macro se coloca en el objeto HOJA donde estamos trabajando, en el evento Selection_Change:

          Private Sub Worksheet_SelectionChange(ByVal Target As Range)
         'se controla el rango H3:H11
          If Intersect(Target, Range("H3:H11")) Is Nothing Then Exit Sub
         'al seleccionar más de 1 celda no se ejecuta
          If Target.Count > 1 Then Exit Sub
          If Target.Value = "" Then
               Target.Value = "a"       'fuente webdings
          Else
              Target.Value = ""
          End If
          Target.Offset(0, 1).Select
          End Sub

Este modo nos sirve para gran cantidad de checkbox simulados, donde solo le indicaremos el rango donde se encuentran las celdas asignadas para esta tarea.


Descargar ejemplo desde aquí.

martes, 18 de septiembre de 2018

Textbox (u otro control) flotante

Ya en una entrada anterior comenté cómo inmovilizar paneles para dejar rangos siempre visibles a medida que nos vamos moviendo por la hoja Excel.

El caso de hoy no tiene que ver con rangos o celdas sino con algunos controles, como puede ser un TextBox (o algún otro control) que necesitamos tenerlos visibles a medida que avanzamos por la hoja.

Ejemplo 1:
Veamos la siguiente imagen: a medida que vamos completando la columna se nos oculta el control donde ingresamos la información.

Una solución sería colocar el control por encima de una línea que inmovilice las primeras filas. En la siguiente imagen notamos que la fila 1 está inmovilizada y debiéramos colocar entonces allí el control.

Y la otra solución, que es el motivo de este tema, es que el control 'acompañe' el avance por las filas de la hoja. O sea que el control sea flotante.


Lo que haremos entonces es indicar que la propiedad Top del control es la misma que la de la celda que recibe el dato.
                 ActiveSheet.TextBox1.Top = Range("A" & x).Top

Nota: estos ejemplos presentan controles dibujados con la barra ActiveX.

Ejemplo 2:
En este segundo ejemplo contamos con 2 controles: un TextBox que nos permite ingresar la información y un Label que nos va mostrando la sumatoria del rango ocupado.


El código completo presenta estas instrucciones:
      Private Sub TextBox1_Change()
      'al limpiar el control no se ejecuta
      If TextBox1 = "" Then Exit Sub
     'al llegar a 11 caracteres vuelca el contenido a la hoja
      largo = Len(TextBox1.Text)
     If largo = 10 Then
         'busca la primer fila libre
          x = Range("A" & Rows.Count).End(xlUp).Row + 1
         Range("A" & x) = TextBox1.Text
        ' se acumula en el label el total
         totx = totx + Range("B" & x).Value
         Label1.Caption = Format(totx, "0.00")
        'limpia el control y permite una nueva entrada
        TextBox1 = "": TextBox1.Activate
        'ubicar el control en la línea de la celda
        ActiveSheet.TextBox1.Top = Range("A" & x).Top
        ActiveSheet.Label1.Top = Range("A" & x).Top
       'mover también el scroll
       ActiveWindow.SmallScroll Down:=1
    End If
    End Sub


Descargar el ejemplo desde aquí.

Ver VIDEO