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


domingo, 9 de septiembre de 2018

El control FRAME

Los Userforms nos permiten desarrollar aplicaciones evaluando contenidos (evitando o controlando posibles errores de tipeo o de ingreso de datos duplicados y tantos otros errores).
Además reduce el uso de fórmulas. O si las necesitamos, podemos reducir el tiempo de proceso programando que se pase el modo de cálculo a manual mientras ingresamos registros a una base y pasarlo a modo manual al finalizar con los registros.

En un Userform podemos utilizar varios tipos de controles: Label, TextBox, ComboBox y ListBox son los más frecuentes. Y son los mismos que podemos utilizar en una hoja Excel desde la barra de controles ActiveX.

Pero hay varios controles más: Frame, OptionButton, CheckBox, SpinButton y más.

En esta entrada vamos a ver un caso relacionado al control Frame.

Imaginemos que tenemos una cantidad de controles TextBox, CheckBox u otros que no necesariamente se llenarán todos sino que será opcional según algún otro criterio.
Entonces, podemos dibujarlos a todos dentro de un marco (Frame) e ir agrandando el frame a medida que necesitemos llenar más controles.

En las imágenes siguientes se observa el tamaño predeterminado y a continuación a medida que completamos los campos.




Esto requiere de una instrucción en el evento Enter de cada control para que a medida que lo utilizemos ya nos amplíe el marco para habilitar el siguiente control.

         Private Sub TextBox2_Enter()
           Frame3.Height = Frame3.Height + 20
           ' el tamaño que necesites para que se vea el otro box
          End Sub

          Private Sub TextBox3_Enter()
          Frame3.Height = Frame3.Height + 20
          End Sub

Pero como se puede observar en la segunda imagen, luego nos impide continuar con el resto del formulario. Por tal razón necesitamos otro código que nos vuelva nuevamente al tamaño original.
Ese código podría ser colocado al salir del último control:
         
           Private Sub TextBox5_AfterUpdate()
          'un modo: descontando la cantidad incrementada hasta llegar al último control
          Frame3.Height = Frame3.Height - 80
          'otro modo: dejando el marco del tamaño predeterminado
          If Frame3.Height > 54 Then Frame3.Height = 54
          End Sub


Ahora, programar este evento es válido si vamos a rellenar hasta el último control.

Sino debiéramos colocar un botón con la instrucción para reducir el Frame no importa cuántos controles tengamos desplegados.

            Private Sub CommandButton3_Click()
           If Frame3.Height > 54 Then Frame3.Height = 54
           End Sub




Ver VIDEO


sábado, 8 de septiembre de 2018

Cómo mejorar u optimizar nuestra programación.

Generalmente comento que un código, si resuelve la tarea que necesitamos, es correcto..... pero en realidad hay algunas situaciones en que no alcanza con que se resuelva la tarea, sino cómo se la resuelve.

Como gran cantidad de usuarios copian y pegan códigos encontrados en la web, veamos a continuación algunas situaciones donde es mejor desecharlo y buscar otras macros mejores.

1- Mirar la fecha de la publicación, del video o la respuesta en algún foro.
En ocasiones he recibido solicitud para ajustar alguna macro que dejé en alguna respuesta de años anteriores como, algunas del año 2004 !
Pero ya estamos en el 2018 y seguramente hoy habrá otro modo mejor de resolverlo.

La fecha nos da una idea de si se trata de códigos desarrollados para versiones xls.
Cuando Excel no contaba con alguna herramienta para resolver una situación recurríamos a una macro. Un caso concreto es el de 'eliminar duplicados' . Se necesitaban varias líneas de programación. Hoy encontramos esa herramienta en menú Datos.


Por lo que programar esta tarea ahora solo requiere de una instrucción, donde se indica el rango y la (o las) columnas a analizar:

          ActiveSheet.Range("$A$2:$F$5").RemoveDuplicates Columns:=1, Header:=xlYes

Como mucho le indicaré con una variable el fin de rango si se trata de una tabla de largo variable:

          Sub sinDuplica()
          x = Range("A" & Rows.Count).End(xlUp).Row
         ActiveSheet.Range("$A$2:$F$" & x).RemoveDuplicates Columns:=1, Header:=xlYes
         End Sub


2 - Observar cómo se busca el final de rango. 
Todavía se encuentran ejemplos donde para encontrar la primer fila libre o última celda ocupada se recurre a un bucle del tipo:

        fila = 2
          Do While Hoja5.Cells(fila, 1) <> ""
            fila = fila + 1
         Loop

Aquí se está recorriendo la col A desde la fila 2 controlando si la celda está vacía o no. Si consideramos que hoy una hoja Excel puede contener datos hasta fila más allá del millón, evidentemente no es el mejor modo de encontrar el fin de rango.... mejor desechar este código.
Algunas instrucciones posibles:

         Range("A" & Rows.Count).End(xlup).Row            'devolverá la última celda con texto en col A
           Range("B" & Rows.Count).End(xlup).Row + 1       'devolverá la primer celda vacía en col B
           Range("A2").CurrentRegion.Rows.Count               'devolverá la última fila del rango.


3 - Observar cómo se nombran las hojas.
Si toman parte de alguna aplicación (propia o ajena) pero la misma menciona a las hojas como Hoja1, Hoja3, etc presentará bastante dificultad la adaptación de esas macros a nuestro proyecto.

Por ejemplo, teníamos una aplicación con varias hojas y de allí tomamos algunas macros de este tipo:


Observamos que el código hace mención al indice de hojas (no a su nombre) y nuestro libro no tiene esa cantidad de hojas lo que lleva a realizar un seguimiento y muchas correcciones.

Para que un código pueda ser utilizado en varios proyectos lo conveniente es declarar las hojas en alguna variable. Así con solo modifcar esa instrucción ya nos servirá no importa como se llamen nuestras hojas en otros libros.

        Private Sub UserForm_Initialize()
          Set hop = Sheets("Productos")
          Set hoe = Sheets("Existencias")
          Set hom = Sheets("Movimientos")
          End Sub

Nota: Estas variables se declaran al inicio del Userform.

4 - No seleccionar-copiar..... seleccionar -pegar.
Cuando creamos una macro con la grabadora, ésta registra cada una de nuestras acciones: seleccionar una celda, copiarla, avanzar con el scroll o pasar a otra hoja, seleccionar la celda y pegar.

              Sheets("Compras").Select
               Range("R2").Select
               Range(Selection, Selection.End(xlDown)).Select
              Application.CutCopyMode = False
              Selection.Copy
              Sheets("Productos").Select
              Range("B2").Select
              ActiveSheet.Paste

Si esta tarea la tenemos que realizar con gran cantidad de información que iremos moviendo nos llevará muchas líneas innecesarias de código, pudiendo simplificarlo del siguiente modo:

          Range("R2:R" & Range("R" & Rows.Count).End(xlUp).Row).Copy
          Sheets("Productos").[B2].PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                   :=False, Transpose:=False

5 - Que las macros vengan con explicaciones. 
Un código explicado siempre será más fácil de ajustar y adaptar a nuestro requerimiento. 


Ver VIDEO

miércoles, 11 de julio de 2018

Bloquear, ocultar, agrupar esquemas e inmovilizar paneles.

Son muchas las situaciones donde necesitamos ocultar o bloquear rangos de celdas.

Y para ello tenemos varias opciones:

1- Bloquear acceso a rangos más allá de los datos. Generalmente son rangos donde realizamos cálculos o guardamos las listas que utilizaremos en la aplicación.
En una tabla como en la de la imagen solo se accede al rango A:G con el siguiente código:

   Sub ocultando()
   'atajo de teclado CTRL O
   Sheets("Ventas").ScrollArea = "A:G"
   End Sub



Para desbloquear los rangos tendremos otra macro dejando el ScrollArea vacío.

   Sub mostrando()
   'atajo de teclado CTRL M
   Sheets("Ventas").ScrollArea = ""
   End Sub

Del mismo modo que procedemos con las columnas podemos hacerlo con filas. Por ejemplo, permitiendo solo el acceso hasta la fila 20.

   Sheets("Ventas").ScrollArea = "A1:G20"

2- Ocultar rangos más allá de los datos: este método también nos permite tener un área para cálculos auxiliares y no visibles para terceros.
Con la misma macro se ocultan las columnas desde la H hasta el final y se vuelven a mostrar (en caso de estar ocultas). Lo mismo se puede aplicar a filas.

Sub ocultaDesdeH()
'atajo de teclado CTRL H
If Columns("H:XFD").Hidden = True Then
    Columns("H:XFD").Hidden = False
Else
    Columns("H:XFD").Hidden = True
End If
End Sub



3- Utilizar esquemas: si contamos con una tabla donde algunas columnas contienen datos necesarios pero no relevantes, podemos agrupar esas columnas en un esquema:


Para este ejemplo hemos seleccionado los títulos de las columnas C:G y desde menú Datos, Agrupar optamos por Columnas.
De este modo fácilmente mostramos y ocultamos aquellas columnas cuya información no es necesaria tenerla siempre a la vista.
Para quitar el esquema iremos nuevamente al menú Datos, Desagrupar, Borrar Esquema.

NOTA: a la hora de programar el uso de una tabla con esquemas, debemos tener presente estos detalles:
a - Si por ejemplo estando en la celda B3 utilizamos la instrucción ActiveCell.Offset(0,1) nos mostrará la Dirección del cliente. 
Pero si tenemos las columnas ocultas, con la misma instrucción seguiremos obteniendo información de la columna C y no las Ventas que se encuentran en col H.


En estos casos, en lugar de ir contando cuántas columnas colocar en el argumento del Offset, para asegurarnos de tomar la columna correcta será mejor utilizar la expresión:

    Range("H" & ActiveCell.Row)

b - Si protegemos la hoja del modo habitual desde menú Revisar, Proteger hoja e intentamos utilizar el esquema para ocultar/mostrar columnas, un mensaje nos indicará que no es posible utilizar esta herramienta en una hoja protegida.
Para resolver esta situación tendremos que proteger la hoja mediante código, agregando otros argumentos a los habituales.

    Sub protegiendo()
    'atajo: CTRL p
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, _
      Scenarios:=True, AllowFormattingColumns:=True, AllowFiltering:=True, _
      UserInterfaceOnly:=True
    ActiveSheet.EnableOutlining = True
    End Sub


4- Inmovilizar Paneles: este es otro método que nos permite ocultar columnas o filas a medida que avanzamos por la hoja, dejando siempre visibles aquellas filas superiores o columnas a izquierda como títulos o encabezados.

Si por ejemplo tenemos una hoja con información diaria, en algún momento tendremos que ocultar los primeros días para observar los últimos días del mes.


Podemos inmovilizar solamente la columna de conceptos (seleccionando la col D) o inmovilizar solamente los títulos superiores (seleccionando la fila 10).
Pero si deseamos mantener inmovilizados tanto los encabezados superiores como las primeras columnas de conceptos, tendremos que seleccionar una celda (D10),  es decir la intersección entre filas superiores y columna de conceptos a la izquierda. Lo que hará que al movernos por la hoja podremos ver los encabezados de este modo. (observar los rangos de letras de columnas y números de fila que van quedando ocultos a medida que nos movemos por la hoja).



Ver VIDEO


lunes, 25 de junio de 2018

Compartir macros y Userforms

El tema de hoy tiene que ver con la posibilidad de llamar a diferentes Userforms o subrutinas desde distintos procesos, según algún criterio común a todos.

Primer Caso: Imaginemos la siguiente situación: tenemos  varios userforms y todos utilizan una misma lista de clientes. Para no repetirla podremos mostrarla en otro formulario al que tengan acceso los anteriores.

Entonces vamos a crear un Userform con una lista (ListBox) y en evento doble clic de la misma volcaremos el registro seleccionado al formulario que lo llamó.

Veamos cómo programarlo:
Aquí tenemos 3 formularios (Clientes, Ventas y Cobros) que utilizarán la misma lista que se encuentra en el formulario UF_Lista.
En un módulo tendremos las subrutinas que llaman a cada formulario y al inicio una declaración de variable pública (llamaUf)

Entonces cada formulario tendrá un botón de llamada con las siguientes instrucciones, donde se guardará un número para indicar de qué formulario se trata (1 para Clientes, 2 para Ventas y 3 para Cobros)
     Private Sub CommandButton2_Click()
     llamaUf = 3
     UF_Lista.Show
     End Sub


Solo falta ver cómo se devuelve la información al control Cliente de cada formulario luego de la selección de la lista:

El evento que utilizamos en el ListBox es el DobleClick y el bucle es del tipo Select Case... End Select.
   Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
   If ListBox1.ListIndex = -1 Then
       MsgBox "Debes seleccionar un elemento con doble clic"
       Exit Sub
   End If
   Select Case llamaUf
   Case Is = 1
       UF_Clie.TextBox1 = ListBox1.List(ListBox1.ListIndex)
   Case Is = 2
       UF_Ventas.TextBox3 = ListBox1.List(ListBox1.ListIndex)
   Case Is = 3
       UF_Cobros.TextBox4 = ListBox1.List(ListBox1.ListIndex)
   End Select
   'se cierra el uf
   Unload Me
   End Sub

NOTA: si son pocas las opciones también se puede utilizar un bucle del tipo If...Elseif....End If
   If llamaUf = 1 Then
       UF_Clie.TextBox1 = ListBox1.List(ListBox1.ListIndex)
   Elseif llamaUf = 2 Then
      UF_Ventas.TextBox3 = ListBox1.List(ListBox1.ListIndex)
   End If




Segundo Caso: una misma macro es llamada desde varios procesos diferentes.
Aquí tendremos algo similar a lo anterior pero los procesos no llamarán a un formulario sino a una macro común a todos, que se encontrará ubicada en un módulo.

Imaginemos esta situación: tenemos 3 hojas con información y en algún proceso una hoja debe ser ordenada por una columna en especial. En otro proceso otra hoja también debe ser ordenada pero no necesariamente por la misma columna sino por otra y así con todas las hojas.

Entonces, primero vamos a declarar las variables públicas al inicio de un módulo (*) para guardar la siguiente información:
- nombre de la hoja que debe ser ordenada.
- rango ocupado por la tabla de datos.
- columna por la que hay que ordenar esa tabla.

(*) Se declaran como Public si van a ser utilizadas fuera del módulo donde están declaradas y con Dim si van a ser utilizadas en subrutinas dentro del mismo módulo.

     Public hojax As String   'para indicar quién llamó a la macro de orden
     Dim colOrden As String, rgoOrd As String

Ya con eso, cada botón o proceso que llame al ordenamiento, tendrá estas instrucciones donde irán cambiando el nombre de las hojas:

     Sub ordenaClie()
     hojax = "Clientes"      'podría ser: hojax = ActiveSheet.Name si es un proceso que se ejecutará desde la hoja activa.
     Call macroOrdena
     End Sub

La macroOrdena primero selecciona la hoja, guarda las referencias de rango y columna a ordenar y luego llama a la subrutina de orden propiamente dicha.

Sub macroOrdena()
'se selecciona la hoja solicitada
Sheets(hojax).Select
'se establece la última fila con datos de la hoja activa
filx = Range("A" & Rows.Count).End(xlUp).Row

'según la hoja será el rango y criterio de orden
If ActiveSheet.Name = "Clientes" Then
    colOrden = "D2:D" & filx
    rgoOrd = "A1:F" & filx
ElseIf ActiveSheet.Name = "Ventas" Then
    colOrden = "E3:E" & filx
    rgoOrd = "A2:E" & filx
ElseIf ActiveSheet.Name = "Cobros" Then
    colOrden = "D2:D" & filx
    rgoOrd = "A1:D" & filx
Else
    'en otras hojas no se ejecuta
    Exit Sub
End If

'se ejecuta el ordenamiento
Call ordenando
Range("A1").Select
End Sub

Sub ordenando()
Application.ScreenUpdating = False
    Range(rgoOrd).Select
    ActiveWorkbook.Worksheets(hojax).Sort.SortFields.Clear
    ActiveWorkbook.Worksheets(hojax).Sort.SortFields.Add Key:=Range(colOrden) _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets(hojax).Sort
        .SetRange Range(rgoOrd)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

Como se puede observar, la última subrutina es la que nos genera la grabadora de macros cuando necesitamos ordenar un rango. Y en lugar de tenerla repetida por cada hoja aquí la tenemos 1 sola vez.

En el libro de ejemplo, se agregó otra que vuelve todas las hojas a su orden normal, en este caso por col A. Y esa macro también,  luego de asignar a cada hoja su rango,  utiliza la subrutina anterior llamada 'ordenando'. 

Ver VIDEO

Descargar el ejemplo desde aquí.





domingo, 10 de junio de 2018

Listas (con rangos en otras hojas)

Hasta ahora hemos visto el tema Listas con los rangos siempre en la misma hoja.
Excel no permite asignar a una celda con Validación de datos un origen marcando un rango de otra hoja.
Pero para esto también hay una solución.

En la entrada anterior (y en libro de descarga) observamos que nuestra hoja de trabajo, con las celdas validadas, la lista de meses y la tabla de datos, es la llamada 'ListaDinamica'.

Ahora, si quitamos esa lista de meses de allí y la colocamos en otra hoja, por ejemplo 'Validación', lo que haremos es asignarle, a esta nueva lista, un nombre de rango. 
Desde menú Fórmulas, Administrador de nombres, Nuevo y la llamaré LISTA_MES.


El ámbito ahora debe quedar marcado como 'Libro'.

El paso siguiente es asignar a la celda C4 como origen este nuevo nombre de rango:


De este modo nuestra hoja de trabajo no necesita la lista de meses.


Ver VIDEO
Descargar libro con ejemplo de las 2 últimas entradas desde aquí.

Listas dependientes (sin macros)

Siguiendo con el tema desarrollado en entrada anterior, aquí veremos cómo utilizar en una hoja Excel listas dependientes pero sin macros sino con fórmulas.

Vimos que los distintos tipos de listas que podemos utilizar en Excel son:
- Celdas con validación de datos (menú Datos)
- Controles dibujados (menú Desarrollador o Programador)
- Controles en un Userforms.

Entonces, si no queremos utilizar macros en nuestro libro optaremos por el modelo de celdas con Validación de datos.

NOTA: desde el VIDEO pueden observar el paso a paso.

1- Primero vamos a colocar una lista de meses para asignarla a la primer celda, que en el ejemplo es C4. Desde menú Datos, Validación de datos optar por Lista y en Origen seleccionar o ingresar el que corresponda (en el ejemplo es M1:M12)

2- Luego vamos a copiar esa lista de meses y con Pegado especial, Transponer, la ubicaremos a partir de fila 1 para contar con un rango de meses y sus días en forma vertical.

3- Seleccionaremos el rango de títulos (en el ejemplo O1:Z1) y desde el menú Fórmulas, Administrador de Nombres, Nuevo le asignaremos el nombre MESES.



4- Completamos las columnas con días (vencimientos, feriados, cumpleaños, etc)

5- Ahora seleccionaremos cada rango para asignarles Nombres de rango. Utilizaremos los mismos títulos para que el sistema los reconozca al seleccionar un mes en el desplegable de C4.
Por ejemplo, seleccionamos el mes de Abril con sus días y desde menú Fórmulas, Administrador de nombres dejaremos el nombre seleccionado y ajustaremos el rango para que inicie en fila 2. Esto se hará con cada mes de la tabla.


6- Solo nos resta validar la segunda celda, la que será dependiente de la primera. Es decir, que al seleccionar algún mes desde C4, en otra celda nos muestre los días que le corresponden a ese mes.
Aquí necesitaremos de la función INDIRECTO, es decir que busque el contenido de C4 y según eso despliegue el rango de igual nombre.


Observemos entonces el resultado obtenido. Al seleccionar Abril nos mostrará en el siguiente desplegable el rango con ese mismo nombre.


Lo visto hasta aquí es perfecto para listas de rangos fijos, por ejemplo País-Ciudades.
Pero en otros casos quizás necesitemos ir agregando datos a los rangos, en nuestro ejemplo más días en algunos meses.

Por lo tanto necesitaremos rangos dinámicos. Y las modificaciones que realizaremos entonces son, a partir del punto 5:

5b - Dejaremos los rangos asociados con cantidad suficiente de celdas, podría ser hasta 31, 100 o directamente toda la columna:


6b- Utilizaremos para el Origen en la Validación de datos de la celda dependiente, la función DESREF que devuelve la referencia de un rango, que es un número de filas y columnas de una referencia dada.
La función tiene los siguientes argumentos:

=DESREF(ref, filas, col, [alto], [ancho])


filas: es el número de filas, hacia arriba o hacia abajo de la ref. 
columnas: es el número de columnas hacia derecha o izquierda de la ref. 
[alto]: es un argumento opcional que nos permite indicar la cantidad de filas con datos que tiene la columna.
[ancho]: indica la cantidad de columnas que mostraremos, que como en este caso es 1 sola columna la omitimos.

Nuestra fórmula para la validación de la celda dependiente quedará entonces así, según el modelo de la imagen anterior:

=DESREF(O1;1;COINCIDIR(C4;MESES;0)-1;CONTARA(INDIRECTO($C$4))-1)

ref: O1 que es la 1er celda de la tabla de MESES.

filas: 1 fila hacia abajo porque los datos empiezan en fila 2.

columnas: la buscaremos en el rango MESES según el valor de C4 con la función COINCIDIR
.
                 COINCIDIR(C4;MESES;0) -1

Como la función COINCIDIR nos devolverá el número de columna dentro del rango MESES, para ENERO nos devolverá 1. 
Pero el argumento 'columnas' indica el número de columnas que nos desplazamos a derecha, siendo para ENERO 0 columnas, por eso en la fórmula DESREF restamos 1.

[alto]: buscaremos dentro de la columna la cantidad de celdas ocupadas, utilizando la función CONTARA, donde el rango será el que tenga el nombre del contenido de C4. Entonces si en C4 seleccionamos ABRIL buscará la cantidad de celdas ocupadas en rango de nombre ABRIL y le restamos 1 para omitir el título.

                  CONTARA(INDIRECTO($C$4))-1


Ver VIDEO

Descargar libro con ejemplo desde aquí.