VBA: Dar color a puntos de un gráfico según elementos seleccionados de un ListBox en Excel.

Última entrada de la serie de entradas del blog que empezó con el post Elementos seleccionados de un ListBox.
En este último post añadiremos las líneas de código que verifican punto por punto de la Serie de datos de nuestro gráfico, hasta encontrar las coincidencias con los elementos seleccionados, que como vimos en el anterior post, tenemos recogidos en nuestra matriz redimensionada.
A continuación del código de la entrada anterior ,insertaremos en la Hoja1 (en la hoja donde se encuentre nuestro ListBox) del Explorador de proyectos del Editor de VBA el siguiente código, formando nuestra macro de Excel, con un evento click, quedando:

Private Sub CommandButton1_Click()
Dim lItem As Long, seleccionados As Integer
Dim MiMatriz() 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

'redefinimos la matriz con el número de items seleccionados
ReDim MiMatriz(1 To seleccionados, 1) As Variant
x = 1
'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
    'que luego emplearemos para comparar en el gráfico
    If ListBox1.Selected(lItem) = True Then
    'añadimos el item a la matriz redimensionada
    MiMatriz(x, 1) = ListBox1.List(lItem)
    'dejamos el Listbox sin selección
    ListBox1.Selected(lItem) = False
    x = x + 1
    End If
Next lItem
''''''''''''''''''''''''''''''''''''''
'Trabajamos ahora sobre el gráfico
Dim grafico As ChartObject
Dim iPoint As Long, nPoint As Long
Dim s As Series
'Definimos y localizacmos el gráfico
Set grafico = Sheets(1).ChartObjects(1)
Set s = grafico.Chart.SeriesCollection(1)
nPoint = s.Points.Count
'unificamos el color de toda la serie
s.Interior.Color = vbBlue

'recorremos los elementos de la matriz
For itm = LBound(MiMatriz) To UBound(MiMatriz)
'recorremos el eje X,
'revisando si el nombre de los clientes
'coincide con los seleccionados en el Listbox
    For iPoint = 1 To nPoint
        If s.XValues(iPoint) = MiMatriz(itm, 1) Then s.Points(iPoint).Interior.Color = vbRed
    Next iPoint
Next itm

End Sub

Finalizada la programación, estamos dispuestos a probarla. Si seleccionamos varios elementos desde el ListBox, y ejecutamos la macro presionando el CommandButton que hemos llamado 'Ejecutar', veremos la acción resultante en el gráfico, cambiando de color Azul(vbBlue) a Rojo(vbRed) los puntos correspondientes.
Podemos ver una imagen:

Seguir leyendo...

VBA: Elementos seleccionados de un ListBox en Excel.

Continuando con el post anterior, en el que vimos cómo contar el número de elementos seleccionados en un ListBox, hoy veremos como identificar estos elementos; lo que haremos añadiéndolos a una Matriz-Array.
El trabajo consistirá en Redimensionar nuestra matriz de acuedo al número de elementos seleccionados, para luego recorrer todos los elementos del ListBox, comprobando cuáles están Seleccionados, en este caso, lo añadiremos como item de nuestra matriz-Array redimenensionada.
A continuación del código de la entrada anterior ,insertaremos en la Hoja1 (en la hoja donde se encuentre nuestro ListBox) del Explorador de proyectos del Editor de VBA el siguiente código, formando nuestra macro de Excel, con un evento click, quedando:

Private Sub CommandButton1_Click()
Dim lItem As Long, seleccionados As Integer
Dim MiMatriz() 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

'redefinimos la matriz con el número de items seleccionados
ReDim MiMatriz(1 To seleccionados, 1) As Variant
x = 1
'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
    'que luego emplearemos para comparar en el gráfico
    If ListBox1.Selected(lItem) = True Then
    'añadimos el item a la matriz redimensionada
    MiMatriz(x, 1) = ListBox1.List(lItem)
    'dejamos el Listbox sin selección
    ListBox1.Selected(lItem) = False
    x = x + 1
    End If
Next lItem
End Sub

Con este código hemos creado una matriz con los elementos seleccionados en el ListBox; con estos item de dicha matriz, posteriormente (en el post siguiente) formatearemos la serie de datos de nuestro gráfico, dando un color distinto a los Puntos coincidentes con los seleccionados.

El próximo y último post, incluiremos la última parte del código de VBA para Excel que recorrera los puntos que compornen la serie, hasta encontrar los ocincidentes con los item seleccionados...

Seguir leyendo...

VBA: Contar elementos seleccionados de un ListBox en Excel.

Empezaré hoy una serie de algunos post donde explicar cómo conseguir formatear la serie de un gráfico a partir de ciertos elementos seleccionados de un ListBox (ActiveX). Se trata pués de insertar en primer lugar un Cuadro de Lista (ListBox) de los controles ActiveX, configurándolo para que podamos realizar una selección múltiple de varios elementos.
Inicialmente aprenderemos la forma de saber cuántos elementos hemos seleccionado de este ListBox que alimentaremos con la propiedad ListfillRange con unas celdas de nuestra hoja de cálculo de Excel. Como no existe ninguna propiedad para este Control ListBox, que permita averigüar el número de elementos marcados, tendremos que generar un contador que evalúe si cada elemento del LisBox ,ncuentra o no seleccionados, lo que conseguiremos con un Loop que recorra éstos. Estos cálculos se deberán ejecutar con un CommandButton.
Empecemos entonces viendo cuál es el origen de nuestros datos:

A continuación insertaremos desde Ficha Programador > Controles > Insertar > Controles ActiveX > Cuadro de Lista, al que cambiaremos las propiedades:
ListFillRange: A2:A11
Multiselect: 1-fmMultiSelectMulti

haz clic en la imagen

Tenemos ya un ListBox con elementos los valores del rango A2:A11, y con la propiedad de poder seleccionar al tiempo varios de ellos.

El siguiente paso es insertar un Botón de comando (ActiveX) - CommandButton, ya que es a este botón al que vamos a agregar la programación que nos permitirá contar, de momento, el número de elementos seleccionados.
Insertaremos en la Hoja1 (en la hoja donde se encuentre nuestro ListBox) del Explorador de proyectos del Editor de VBA el siguiente código, formando nuestra macro de Excel, con un evento click:

Private Sub CommandButton1_Click()
Dim seleccionados As Integer

'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

MsgBox "Número de elementos seleccionados: " & seleccionados

End Sub

Al seleccionar algunos elementos del ListBox, y presionar el CommandButton, aparece el cuadro de mensaje (MsgBox) indicando cuántos elemenos se han seleccionado.

Con lo que concluímos la primera parte; fundamental conocer cuál es este valor, ya que posteriormente lo emplearemos para redimensionar nuestra Matriz-Array, que estará compuesta exclusivamente por los elementos seleccionados... pero esto en el siguiente post.

Seguir leyendo...

Microsoft Office 2013 - Excel 2013.

Pues por fin han anunciado el esperado nuevo suite ofimático. Microsoft presenta su beta o versión previa de lo que será Office 15. Los mayores cambios de este suite parecen pensados para adaptarlos a la estética del próximo Windows 8 (si también toca este año), orientado de manera clara hacia los dispositivos móviles, como tablets.
De momento he de reconocer que no he leído mucho más de los enlaces que os adjunto, pero parece que con respecto a lo que toca a Excel seguiremos en la línea de Excel 2010, manteniendo la cinta Ribbon, y centrándose la novedad en el trato de estilos o formatos, así como alguna mínima mejora respecto de las tablas dinámicas.
Os adjunto los vínculos a algunas direcciones:
http://www.microsoft.com/OFFICE/PREVIEW/es
http://www.microsoft.com/office/preview/es/excel-2013-preview
http://msoffice2013.com/tag/microsoft-excel-2013/
Estaremos al tanto de esta nueva versión....

Seguir leyendo...

Distribuir cuotas por meses en Excel.

Hoy explicaré como con unas fórmulas sencillas y un buen planteamiento, esto es, con una buena disposición de un informe en una hoja de cálculo de Excel, podemos distribuir un importe según las cuotas mensuales, en los meses correspondientes.
Se trata de, una vez conocido el momento inicial de compra, distribuir a lo largo de los meses siguientes, los pagos o cuotas conocidos correspondientes.
Supongamos para distintas compras conocemos en cuántas cuotas/pagos debemos hacer frente a la compra, a partir de la fecha del mes de compra.
Este ejercicio me lo propuso un lector, inicialmente con una plantilla distinta a la que voy a desarrollar. Y de eso se trata realmente, cómo podemos dirigir nuestros esfuerzos a facilitar la elaboración de nuestros cálculos. Recordad que Excel es la herramienta, nosotros los artesanos.

Veamos el planteamiento inicial (no demasiado óptimo):

Podemos ver que la información necsaria está toda demasiado integrada en una sóla celda, por lo que trabajar sobre esto se puede complicar. Así que la primera parte del trabajo para optimizar este informe será detallar la información necesaria, disgregando en celdas diferentes la la fecha o momento de compra, el número de cuotas pactada, la cuota total a distribuir, el cálculo de cuota mensual para cada item comprado, quedando la estructura de informe como sigue:

Para calcular la cuota mensual, simplemente hemos aplicado la fórmula: =SI(C3="";0;D3/C3), que basicamente divide el Total entre el número de cuotas.
En el rango F2:R2, aunque visualmente aparecen los textos 'Mes 1', 'Mes 2', etc, son realmente valores de 1 a 13, pero con un Formato de celda personalizado tipo "Mes "0. Este aspecto es importante ya que nuestra futura formulación se basará en estos valores de 1 a 13.
Por último el rango F8:R8 (en amarillo) sencillamente suma las filas 3 a 7.

Ya estamos en disposición de generar una fórmula que reconozca en qué mes corresponde la cuota mensual para cada registro, es decir, en qué mes debe iniciarse el pago de cada cuota y en qué mes debe finalizar. Para esta tarea, como indicaba al inico del post, usaremos funciones muy sencillas y conocidas, principalmente el condicional SI. Con el debido cuidado de aplicar correctamente las referencias absolutas o relativas a la formula; en la celda F3 (la primera de las celdas, desde donde copiaremos al resto) tendremos:
=SI(F$2>=$B3;SI(F$2-$B3+1<=$C3;$E3;0);0)

Seguir leyendo...

VBA: Formato condicional en Excel según valores repetidos.

Veremos hoy una aplicación sencilla de una macro que identifica qué elementos de un rango se han repetido más de x veces, para luego asignarles a éstos, un color diferente según agrupaciones.
El ejemplo siguiente trata de dar una solución a la pregunta dada por un lector del blog:

...tengo varias columans con mas de 10000 registros o lineas y quisiera poner un color diferente para cada dato que se repita 30 veces...

Supondremos tenemos un listado de unos cuantos registros numéricos, algunos de los cuales están repetidos. Por simplificar, tendremos un listado de 17 elementos (positivos y negativos):

Se trata, por tanto, de asignar un color diferente a cada agrupación de valores repetidos, siempre que se repitan un número de veces (para nosotros tres o más).
Para ello, haremos un uso extensivo de las funciones VBA y alguna función de hoja de cálculo, todo ello como parte de nuestra macro.
Una de ellas, ya que se trata de dar color a las celdas, será la función VBA RGB(rojo, verde, azul): cada uno de los argumentos debe ser de tipo Variant ó Integer, y con un valor dentro del intervalo de 0-255, ambos inclusive, que representa el componente rojo del color (valores mayores los admite como 255).
Otras funciones que emplearemos son la de MAX y MIN que aplicaremos sobre nuestro rango de trabajo, para conseguir los valores mínimo y máximo del rango, datos sobre los que luego realizaremos unos cálculos. También usaremos las funciones INT y ABS, para determinar el valor absoluto y quedarnos con la parte entera del cálculo que realizaremos. Y finalmente otra función de hoja de cálculo: CONTAR.SI, que llamaremos desde nuestra macro de la siguiente manera:
Application.WorksheetFunction.CountIf(rango, criterio)

Insertaremos en un módulo del Explorador de proyectos del Editor de VBA el siguiente código, formando nuestra macro de Excel:

Sub ColorearRepetidos()
Dim rng As Range
Set rng = Range("A2:A18")

Static rojo As Integer, verde As Integer, azul As Integer
Static contador As Integer

For Each celda In rng
contador = Application.WorksheetFunction.CountIf(rng, celda.Value)
rojo = Abs(Int(255 * (celda.Value / Application.Max(rng))))
verde = Abs(Int(255 * (Application.Min(rng) / celda.Value)))
azul = Abs(Int(rojo + (verde / celda.Value)))
    If contador >= 3 Then
    Range(celda, celda.Offset(0, 3)).Interior.Color = RGB(rojo, verde, azul)
    Else
    Range(celda, celda.Offset(0, 3)).Interior.Color = xlNone
    End If
Next celda
End Sub

las variables estáticas rojo, verde y azul responden a diferentes fórmulas, que devuelven el mismo valor para cada elemento, de esta forma nos aseguramos que cualquier elemento obtiene tres valores iguales, independientemente de dónde se encuentre en el listado origen.
Con la sentencia IF...THEN... evaluamos las veces que se encuentra repetido el elemento en total, ya que sólo queremos dar color a los repetidos tres o más veces, en cuyo caso, daremos color al fondo de la celda con la función RGB, y argumentos el resultado del cálculo rojo, verde y azul.
Como el lector pedía dar color a toda la fila de la base de datos, he creado un rango por fila al que asignar color con la propiedad OFFSET.
Podemos ver el resultado tras ejecutar nuestra macro:

Seguir leyendo...

Sumar alfanuméricos en Excel.

En una entrada anterior expliqué una forma de sumar letras en Excel asociados a ciertos valores (ver). En esta ocasión explicaremos la forma de segregar información de celdas, en la que tenemos datos alfanuméricos juntos.
Veámoslo mejor en una imagen:

haz clic en la imagen

Como vemos tenemos una plantilla para ciertos trabajadores, en los que informamos de las horas trabajadas según una tipología:
Hora tipo A = Faltas
Hora tipo B = Bono
Hora tipo C = Horas al 50%
Hora tipo D = Horas al 100%
En esta plantilla nos encontramos, entonces, información alfanumérica tipo 1A, 3B, 10C, etc reflejando el número de horas empleadas en el mes junto al tipo de horas.
Se trata entonces de resumir la información de todo el año, según dicha tipología, es decir, cuantas horas en todo el año ha empleado tal trabajador de tipo A, B, C ó D.

Para ello aplicaremos una función matricial (validada presionando Ctrl+Mayusc+Enter):
=SUMA(SI.ERROR(VALOR(SUSTITUIR($B3:$M3;O$2;""));0))
Pero ¿cómo funciona esta fórmula?...en primer lugar, empleamos la función SUSTITUIR, mediante la cual, eliminamos, letra a letra, para cada mes del rango B:M y cada empleado, dejando, por tanto sólo los valores numéricos; para asegurarnos que luego podremos trabajar con este valor numérico, es decir, que no habrá problemas en que Excel lo trate como texto (a pesar de ser un número, que por otro lado es lo más normal), anidamos esta función dentro de otra VALOR.
Ya tenemos en el rango $B3:$M3 una serie de valores numéricos junto a otros que siguen siendo alfanuméricos, que al haberles aplicado VALOR, se habrán convertido en un error. Si a este rango de errores y valores numéricos, le aplicamos el condicional SI.ERROR, se queda un rango final de valores con horas, para aquellos alfanuméricos que correspondían a la letra sustituida, y un montón de ceros para los demás; asi que sólo nos queda sumar ese rango con la función SUMA.
Podemos ver las tres etapas de la fórmula en la imagen siguiente:

Seguir leyendo...