VBA: Botones de Siguiente y Anterior en un UserForm

Días atrás recibí una solicitud de ayuda de un lector que demandaba colaboración para realizar un formulario de usuario con VBA donde implementar unos botones típicos de Siguiente y Anterior, así como dos más de Inicio y Fin.
Aunque el desarrollo es bastante sencillo, me pareció interesante compartirlo...

Para el ejemplo necesitamos una base de datos simple de cuatro campos (Nombre, Apellidos, Edad y Sexo):

Igualmente necesitamos componer nuestro formulario con los siguientes controles:
4 cuadros de texto (TextBox):
txtNombre
txtApellido
txtEdad
txtSexo
4 botones de comando (CommandBox):
cmdInicio
cmdAnterior
cmdSiguiente
cmdFin
y opcionalmente unas etiquetas para identificar los campos en el UserForm.
Este podría ser un diseño:

Y ahora lo importante.. en la ventana de código del UserForm incluimos los siguiente procedimientos:

'variables a emplear entre los diferentes procedimientos más abajo
Dim fila As Long
Dim NumItems As Long

Private Sub UserForm_Initialize()
Dim filaIni As Long
'damos valores de partida
fila = 2
'rellenamos el formulario
RecuperaDatos (fila)
'y determinamos el número total de registros
NumItems = WorksheetFunction.CountA(Range("A:A")) - 1
End Sub

'Gestionamos los botones  de Siguiente y Anterior
Private Sub cmdSiguiente_Click()
'controlamos el botón de Siguiente
'limitándolo por el último registro
If fila <= NumItems Then
    fila = fila + 1
    RecuperaDatos (fila)
Else
    MsgBox "Último registro"
End If
End Sub

Private Sub cmdAnterior_Click()
'controlamos el botón de Anterior
'limitándolo por el primer registro
If fila > 2 Then
    fila = fila - 1
    RecuperaDatos (fila)
Else
    MsgBox "Primera entrada!!"
End If
End Sub
  
Private Sub cmdFin_Click()
fila = NumItems + 1
RecuperaDatos (fila)
End Sub

Private Sub cmdInicio_Click()
fila = 2
RecuperaDatos (2)
End Sub

'Procedimiento para recuperar datos de la hoja hacia el UserForm
Private Sub RecuperaDatos(nRow As Long)
    Me.TxtNombre.Value = Cells(nRow, 1)
    Me.TxtApellido.Value = Cells(nRow, 2)
    Me.txtEdad.Value = Cells(nRow, 3)
    Me.txtSexo.Value = Cells(nRow, 4)
End Sub

Listo... puedes comprobar rápidamente el correcto funcionamiento de los diferentes botones.

Seguir leyendo...

VBA: El método Debug y su propiedad Assert

Aprenderemos hoy un método poco conocido que puede ayudarnos a controlar y depurar nuestros fallos de programación con VBA para Excel: método Debug.

Un problema que nos ocurre a todos es que cuando programamos nuestras macros no somos capaces de reconocer o detectar errores en la programación hasta que lanzamos-ejecutamos estas macros.. y será precisamente aquí donde cobre sentido la propiedad Assert del método Debug

Añadiendo este me´todo dentro de nuestro código, 'autmáticamente' detectaremos fallos de ejecución, forzando la detención del procedimiento en nuestra línea; ya que una vez incluida una línea con el método, nuestro procedimiento evaluará y comprobará la condición dada durante la ejecución de nuestra macro, pudiendo depurar errores o fallos que puedan provocar futuras catástrofes.

Es por tanto una alternativa a emplear puntos de interrupción en las líneas de nuestras macros


Muy importante!!, asegúrate de borrar o deshabilitar la instrucción una vez controlado los errores, una vez queden depurados estos fallos... de esta manera evitaremos saltos en la depuración de nuestra macro cuando ya trabajemos en real ;-)

La propiedad
Debug.Assert (condición)
es por tanto un punto de interrupción condicional que provocará una parada siempre y cuando la condición indicada sea FALSA!!!.
En este sencillo ejemplo:
Debug.Assert (Variable >= 0)
nuestra macro se detendrá y saltará el depurador siempre que (Variable >= 0) sea FALSO (es decir, cuando la variable sea negativa).

Veamos un par de ejemplo del método.

En el primer caso nos interesa controlar que el dato introducido es, ciertamente, positivo...
Por tanto necesitamos depurar en caso de introducir un valor negativo...

En el segundo caso, muy similar, queremos controlar si el tipo de dato introducido es de tipo texto-string o no.

En un módulo estándar de nuestro explorador de proyecto VBA insertamos los siguientes códigos:

Sub ControlErroreres_Assert()
Dim Valor As Long
Valor = InputBox("Introduce valor")

'se detendrá/depurará si la condición es Falsa (i.e., si es negativo el dato introducido)
Debug.Assert (Valor >= 0)
MsgBox "Dato Introducido " & Valor
End Sub

Sub ControlErroreres_Assert_2()
Dim strTxt As String
strTxt = InputBox("Introduce texto")

'se detendrá/depurará si la condición sea Falsa (i.e., si el dato no es texto)
'Debug.Assert (Not IsNumeric(strTxt))         'alternativa uno
Debug.Assert (Application.WorksheetFunction.IsText(Val(strTxt)))     'alternativa dos
MsgBox "Dato Introducido " & strTxt
End Sub

Se comprueba fácilmente al ejecutar uno u otro procedimiento, como el depurador saltará cuando no se cumple la condición descrita en el método Debug.Assert... precisamente lo que requeríamos.

Por finalizar con el método Debug comentado hablaremos de su otra propiedad disponible:
Debug.Print (varible)
con una utilidad de control similar al anterior, pero que no provoca la detención del procedimiento, sino que mandará a la ventana de inmediato dentro de nuestro proyecto el valor que indiquemos...
OJO, por que en esta propiedad no hay aspecto condicional, simplemente se trata de llevar el dato a la venta de inmediato.

Después de mostrar la variable escrita en la ventana de inmediato, el código continuará ejecutándose de manera normal.

Como curiosidad indicar que es posible mostrar diferentes variables simultáneamente separándolas por comas, por ejemplo:
Debug.Print X, Y, Z

En un módulo estándar de nuestro explorador de proyecto VBA insertamos los siguientes códigos:

Sub debug_print()
For i = 1 To 10
    Debug.Print ("nuestro cálculo es " & i * Rnd)
Next
End Sub

Seguir leyendo...

Power Query: Texto en Filas

Sabemos la constante mejora a la que somete Microsoft a esta herramienta de Obtener y transformar (Power Query), y loq ue veremos hoy es una más de las funcionalidades disponibles de esta buena herramienta de BI:
Dividir columnas por filas.

El ejemplo que veremos a continuación se basa en una primera tabla donde alguien ha registrado por días diferentes tareas, controlando en una sola celda los empleados que han desarrollado dichas tareas (TblServicios).
Por otra parte tenemos un listado simple con los códigos de empleados y sus nombres (TblEmpleados).

Nuestro objetivo es combinar ambas tablas para identificar por cada día y servicio los nombres de los empleados...

Nuestro primer paso sería cargar (solo como conexión!!) la Tabla con el listado de empleados. Lo haremos seleccionando la tabla y desde la ficha Datos > grupo Obtener y Transformar > botón Desde una Tabla.

La segunda Tabla la cargaremos a nuestras Consultas repitiendo el proceso anterior... pero en este caso realizaremos la acción requerida.
Dentro del Editor de consultas, con el campo llamado 'cod empleados' iremos a la ficha Transformar > grupo Columna de texto > botón Dividir columnas > desplegable por Separador

Se abrirá la ventana diálogo de Dividir columna por delimitador, donde seleccionaremos el delimitador como Personalizado indicando en el campo editable del separador la coma.
Obviamente también podemos seleccionar directamente del desplegable la 'coma'.
Lo importante aparece al mostrar las Opciones avanzadas, donde marcaremos la opción Dividir por Filas

Al Aceptar se transformará nuestra tabla cargada, mostrando separados por filas cada uno de los items del campo 'cod empleados'

Terminamos esta parte del proceso Cerrando y cargando como conexión! esta segunda consulta.

El último paso será Combinar nuestras dos consultas.
Yo optaré por crear una tercera consulta resultante de la combinación de las dos anteriores. Para ello desde la hoja de cálculo en la ficha Datos > grupo Obtener y Transformar > Nueva consulta > Combinar consultas > botón Combinar.

Esto nos mostrará el Editor de consultas y la ventana de Combinar, desde donde indicaremos la relación que necesitamos entre nuestras dos consultas previas ya cargadas, y en concreto:
campo 'cod empleados' de la TblServicios con el campo 'codEmpl' de la TblEmpleados
marcando el tipo de relación: Externa izquierda

El penúltimo paso consiste en desplegar la Nueva columna incorporada tras la combinación, donde solo mostraremos el campo 'nombre' de la TblEmpleados:

El último paso consiste en Cerrar y cargar la consulta, y esta vez sí, devolverla a una hoja de cálculo de nuestro libro, con el siguiente resultado:

Seguir leyendo...

Completar celdas hasta llegar a un acumulado

En el post de hoy conseguiremos, mediante fórmulas y también con programación, conseguir rellenar unas celdas con un valor parcial hasta conseguir que su suma acumulada llegue a una cantidad total, pero sin rebasarla.

La cuestión planteada por el usuario:

[...]Tengo en una columna el valor de la cifra acumulada de una sumatoria de valores iguales, conozco el valor de este número también y quiero que el resto de celdas se rellene automáticamente hasta llegar a sumar el valor acumulado y si la última cifra no puede ser igual al valor de la serie, entonces debe ajustarse hasta conseguir que la sumatoria sea igual al acumulado. Algo así: 9 (acumulado)  2 2 2 2 1 (último valor ajustado para que la suma sea igual a 9) (Los valores de 9 el acumulado y el primer 2 de la serie ya se conocen) Otro ejemplo 16.3    1.5  1.5  1.5  1.5  1.5  1.5  1.5  1.5  1.5  1.5  1.3 (16.3 y 1.5 son valores que ya se conocen)[...]

Comenzaremos exponiendo la solución con fórmulas, partiendo de dos datos:
1-celda D1: valor total a alcanzar (en el ejemplo 16,3)
2-celda E1: valor parcial para completar x celdas (en el ejemplo 1.5)

La fórmula buscada, añadida en B1 y luego arrastrada hacia abajo hasta B14 (en realidad tantas celdas como necesitemos hasta conseguir alguna celda vacía...):
=SI($E$1*FILA()<$D$1;$E$1;SI($E$1-($E$1*FILA()-$D$1)<0;"";$E$1-($E$1*FILA()-$D$1)))

Con este condicional comparamos el valor parcial acumulado con el total a alcanzar, fila a fila.
Mientras no superemos el valor Total retornaremos el dato de E1 (parcial), y solo cuando superemos dicha cantidad, y por diferencias:
$E$1-($E$1*FILA()-$D$1)
completaremos el valor. Tal como pedía el lector.

Un fórmula relativamente sencilla de aplicar.. con el único inconveniente que tendremos que ser nosotros los que controlemos hasta donde debemos arrastrarla...

Para evitar el inconveniente anterior propondré una solución alternativa con macros.
Así pues abrimos la ventana de código de nuestro módulo estándar y añadimos el siguiente código:

Sub Repeticion()
Dim Total As Range, Parcial As Range
'seleccionas las celdas con los valores Total y Parcial...
Set Total = Application.InputBox("Valor a alcanzar", "Excelforo", Type:=8)
Set Parcial = Application.InputBox("Selecciona dónde se encuentra el valor parcial", "Excelforo", Type:=8)

Dim valor As Double, Tot As Double
valor = Parcial.Value
Tot = Total.Value
Dim Acum As Double
Acum = Parcial.Value

'comenzamos el inicio del bucle para completar la secuencia de valores
Parcial.Select
Do
    Acum = Acum + valor 'acumulamos valores parciales
    ActiveCell.Offset(1, 0).Select  'bajamos una celda abajo...
    'controlamos el último valor de la secuencia
    If Acum >= Tot Then
        ActiveCell.Value = valor - (Acum - Tot)
    Else
        ActiveCell.Value = valor
    End If
Loop Until Acum >= Tot  'salimos del bucle al superar el Total

End Sub

Al ejecutar nuestra macro nos abrirá dos InputBox pidiendonos que seleccionemos las celdas con los valores 'Parcial' y 'Total', completando hacia abajo, desde la celda 'Parcial' con la serie requerida.

Seguir leyendo...

Comprimir Imágenes en Excel

Veremos en la entrada de hoy un truco muy simple para reducir el tamaño de nuestros ficheros:
Comprimir Imágenes en Excel.

Esta acción reducirá el peso de nuestros libros de trabajo cuando contengan un número elevado de imágenes...

Veamos un ejemplo de un libro de trabajo con tres imágenes 'pesadas', que nos lleva a comprobar (antes de nuestra acción) que tiene un peso de:
2076 Kb

Esto puede resultar problemático cuando el número de imágenes es alto, ya que conlleva un peso elevado del libro de trabajo, dificultando la apertura y recálculo del resto de funcionalidades de Excel.
Una posible solución es 'Comprimir imágenes'.

Para ello solo tendremos que:
1 Guardar como...
2 Acceder al botón de Herramientas (buscar posición según versión de Windows.
3 Opción Comprimir imágenes...
4 En la ventana diálogo que se abrirá seleccionar= Correo electrónico (96 ppi):minimiza el tamaño de los documentos para compartir

Tras guardar el fichero comprobamos el efecto en el peso de nuestro libro, comprobando que ha bajado hasta los 83 Kb !!

Obviamente el truco es rebajar los puntos por pulgada (ppi) al mínimo, rebajando la calidad de nuestras imágenes...

Seguir leyendo...