jueves, 23 de marzo de 2017

VBA: Suma Selectiva con un ListBox

Un usuario, a partir de este post (ver), preguntaba la manera de acumular/sumar importes correspondientes a una selección de 'clientes':
[...]me gustaría saber como hacer lo mismo pero que me sume la cantidades seleccionadas; por ejemplo, si seleccionara el campo importe[...]


El objetivo es obtener la suma de los importes de aquellos clientes que hubiéramos seleccionado previamente:



Nuestra procedimiento la creamos en la ventana de código de la hoja que contenga los controles ActiveX insertados.

Private Sub CommandButton1_Click()
Dim seleccionados As Integer, lItem As Long
Dim Suma As Double, stClientes As String

Dim MisImportes() As Variant
'recorremos cada elemento del ListBox
For i = 0 To ListBox1.ListCount - 1
    'verificamos si está o no seleccionado
    'en caso afirmativo, acumulamos el contador
    If ListBox1.Selected(i) = True Then
        seleccionados = seleccionados + 1
    End If
Next i
'comprobamos que hay algún elemento seleccionado
If seleccionados = 0 Then
    MsgBox "Debes marcar al menos un cliente"
    Exit Sub
End If
'redimensionamos nuestra Matriz (Array) al tamaño necesario...
'tantas filas como clientes marcados y dos columnas
ReDim MisImportes(1 To seleccionados, 1 To 2) As Variant

x = 0
'recorremos todos los elementos del Listbox
For lItem = 0 To ListBox1.ListCount - 1
    'en caso de que el elemento esté seleccionado
    'lo añadimos a una matriz
    If ListBox1.Selected(lItem) = True Then
        x = x + 1
        'para idetificar listad de clientes
        MisImportes(x, 1) = ListBox1.List(lItem, 0)
        stClientes = stClientes & " " & MisImportes(x, 1)
        
        'para acumlar los importes
        MisImportes(x, 2) = ListBox1.List(lItem, 1)
        Suma = Suma + CDbl(MisImportes(x, 2))
        'dejamos el Listbox sin selección
        ListBox1.Selected(lItem) = False
    End If
Next lItem
'mostramos el resultado final...
MsgBox "Número de clientes seleccionados: " & seleccionados & vbCrLf & _
    "que son: " & stClientes & vbCrLf & _
    "Suma de importes:= " & Suma

End Sub



Para probarlo bastará seleccionar en el ListBox de la hoja los clientes necesarios y presionar el botón 'Ejecutar'.
La respuesta será la esperada, un MsgBox que devuelve el listado de clientes marcados y el importe acumulado de dichos clientes.

martes, 21 de marzo de 2017

Orden Personalizado en Excel

En ocasiones tenemos que realizar ordenaciones en nuestros rangos de datos que no responden a una lógica 'habitual', esto es, no son ordenaciones ascendentes o descendentes... es cuando hablamos de un Orden Personalizado.

Hoy comentaré la opción que nos ofrece Excel para ordenar nuestros datos según un criterio alternativo, según una lista personalizada.


Veámoslo con un ejemplo.
En el rango de la imagen siguiente:

Orden Personalizado en Excel


La meta a lograr es conseguir ordenar nuestros datos según el campo 'Departamento' siguiendo el criterio del rango H1:H4...
Como vemos es un orden distinto, que no responde a ningún patrón... simplemente es un criterio personal.


Nuestro trabajo comenzará entonces accediendo a la herramienta de Listas personalizadas.
La forma más ortodoxa es desde la ficha Archivo > Opciones de Excel > Avanzadas > sección General > botón Modificar Listas Personalizadas.

Orden Personalizado en Excel



Completamos los pasos, seleccionado el rango H1:H4 (donde se encuentre los valores con el orden adecuado), y tras asegurarnos están agregados, presionamos Aceptar.

Orden Personalizado en Excel



Con la Lista personalizada creada, estamos en disposición de realizar nuestra ordenación.

Seleccionamos nuestro rango de datos A1:D21 y desde la ficha Datos > grupo Ordenar y Filtrar > botón Ordenar, que nos abrirá la ventana diálogo con las opciones de ordenación.
Donde seleccionaremos, para este ejemplo:
1- Ordenar por el campo 'Departamento'
2- Según Valores
3- Criterio de ordenación: lista personalizada

nos abrirá la ventana de listas personalizadas, donde seleccionaremos la lista anterior creada:

Orden Personalizado en Excel



Se nos confirma el criterio y estamos dispuestos para Aceptar la condición.

Orden Personalizado en Excel



El resultado es por tanto el esperado... Como vemos en nuestra imagen:

Orden Personalizado en Excel



Meta conseguida.

Esta forma de trabajar es igualmente válida cuando queremos ordenar los meses del año según el orden natural de éstos, y no alfabéticamente!! (algo muy frecuente en tablas dinámicas u otros casos).
Bastaría seleccionar como criterio de ordenación una de las listas de meses predefinidas:

Orden Personalizado en Excel



Consiguiendo nuestros datos queden ordenados por el criterio temporal de los meses, y no el alfabético , que nos distorsionaría el horizonte temporal...

viernes, 17 de marzo de 2017

VBA: Validación Datos con Registros Únicos

Una cuestión frecuentemente planteada es cómo conseguir listar empleando la herramienta de Validación de datos, registros únicos y sin vacíos.

Partiremos del siguiente rango de trabajo, donde se repiten en el tiempo para diferentes usuarios el empleo de diferentes aplicaciones del paquete Office de Microsoft, tal como se muestra en la imagen siguiente:

VBA: Validación Datos con Registros Únicos


Se ve claramente como el rango B3:E10 está compuesto por diferentes elementos repetidos y otros vacíos. Con un problema añadido, y es que los datos están dispuestos en una matriz (cuando sabemos que la validación de datos solo admite trabajr sobre vectores-una fila o una columna-).

El objetivo es incorporar una validación de datos en la celda G2 que permita introducir, y que muestre, solo los elementos de manera única y de elementos no vacíos.

Así pues, trabajaremos una macro en nuestro Excel.
Nuestra macro la creamos en un módulo estándar de nuestro libro de trabajo.
El código:

Sub ListaValidacionCelda()
Dim rngAplicaciones As Range
Set rngAplicaciones = Range("B3:E10")

Set apps = New Collection
'recorremos el rango
For Each celda In rngAplicaciones
    'nos centramos solo en celdas con valor...
    If celda.Value <> "" Then
        'identificamos valores únicos!!
        On Error Resume Next
        apps.Add celda.Value, CStr(celda.Value)
        On Error GoTo 0
    End If
Next celda

'trasladamos las aplicaciones únicas a una Array
Dim arr As Variant
ReDim arr(1 To apps.Count) As Variant
For i = 1 To apps.Count
    arr(i) = apps(i)
Next i
'para poder generar nuestra validación de celdas
With Range("G2").Validation
    .Delete
    .Add Type:=xlValidateList, _
           AlertStyle:=xlValidAlertStop, _
           Operator:=xlEqual, _
           Formula1:=Join(arr, ",")
End With
'liberamos memoria
Set rngAplicaciones = Nothing
End Sub



La magia del procedimiento se basa en tres puntos:
1- al pasar los datos por una Collection, nos aseguramos un listado de registros únicos... además, han pasado un filtro previo con el condicional de no vacíos.
2- generamos una Array con los elementos de la Collection. Es un paso necesario para poder realizar el tercer paso.
3- con la función Join unimos los elementos de la Array, con un separador de 'coma'... Cadena que añadimos a la herramienta de Validación.


Tras ejecutar nuestro procedimiento, el resultado lo observamos en la celda G2 comentada:

VBA: Validación Datos con Registros Únicos



Consiguiendo nuestra meta.

Como curiosidad, si accedemos a la configuración de la validación de datos de nuestra celda G2, veremos la lista generada:

VBA: Validación Datos con Registros Únicos