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