VBA: Recuperar el color con DisplayFormat

Un lector consultaba por un post publicado hace algún tiempo (ver).

El comentario del usuario era:

[...]Muchas gracias publicar esta macro, es exactamente lo que necesito , pero lamentablemente no me esta funcionando. Me arrojo siempre el mismo numero independientemente de color que que tenga la celda ,14. Estoy usando tres tipos de colores para el formato condicional Verde Rojo y amarillo[...]

Lo que veremos hoy es una alternativa a lo publicado que me permite recuperar el color de un objeto Range a través de su propiedad .DisplayFormat.

Ojo!! que tiene un par de inconvenientes:
1- es una propiedad de solo lectura, por lo que no podremos modificar nuestras celdas empleando esta propiedad.
2-Únicamente se puede emplear en procedimientos Sub (y NO en Function!!), por lo que no podremos generar UDF para recuperar el color de la celda (tal como se empleaba en el post anterior comentado).
Si lo intentas comprobarás que tu UDF te devolverá siempre un error de #¡VALOR!

Con estas condiciones presentes, veamos cómo podemos recuperar el color de relleno y de la fuente de nuestras celdas.

Abrimos la ventana de código de nuestro módulo estándar y añadimos el siguiente código:

Sub Color_Celda()
'recorremos el rango
For Each celda In Range("A2:A7")
    'y recuperamos el color de relleno y fuente
    'ya provenga de formato o formato condicional
    celda.Offset(0, 2).Value = celda.DisplayFormat.Interior.ColorIndex
    celda.Offset(0, 3).Value = celda.DisplayFormat.Font.ColorIndex
Next celda

End Sub

Tras ejecutarlo comprobaremos cómo nos devuelve los mismos valores que empleando la función del post publicado tiempo atrás tiempo (ver), tanto si el color proviene de un formato 'normal' o uno condicional.

Seguir leyendo...

DESREF y los rangos variables

Un usuario me consultaba por la posibilidad de generar con DESREF una rango variable de acuerdo a un criterio particular... que siempre devolviera los datos de las últimos cinco periodos.

El comentario del usuario:

[...]He visto en la red que los ejemplos para rangos dinámicos son para series en columnas, es muy claro y se entiende, pero lo que no veo en la red y me canse de buscar y peor aún he intentado hacerlo es tener un rango dinámico pero para series que están en fila Cómo lo haría claro esta usando la función DESREF, u otra misma ya sea para calcular promedio y sumas de las 5 últimos días ya que esto va ir creciendo y gráficos igual[...]

Para resolver este tema nos apoyaremos, entre otros post, en este donde se explicaba como localizar la última posición con valor de un rango... (ver post).

El modelo sobre el que trabajar será:

En nuestra celda O3 tenemos la fórmula buscada:
=SUMA(DESREF(B3;0;SI(CONTARA(B3:M3)<5;0;COINCIDIR(9,9E+307;B3:M3;1)-5);1;5))

comprobando en la celda contigua, mediante un suma directa que el resultado es correcto.

Pero, ¿cómo hemos montado esta fórmula?.
Lo primero ha sido localizar, dentro de nuestro rango de estudio B3:M3, cuál es la última celda ocupada, i.e., a qué mes corresponde.
Por ejemplo, sobre las ventas previstas de nuestro ejercicio, la función
COINCIDIR(9,9E+307;B3:M3;1)
devuelve un 9; que efectivamente corresponde con el último mes con dato.
A continuación restamos 5, ya que es precisamente el punto de partida requerido (cinco últimos periodos).

Para que la fórmula sea válida en cualquier caso, incluso cuando no hay cinco periodos completos,
montamos el condicional:
SI(CONTARA(B3:M3)<5;0;COINCIDIR(9,9E+307;B3:M3;1)-5)
que básicamente nos condiciona a que si hay menos de cinco datos, retorna un valor cero (a continuación veremos su significado) o de lo contrario el resultado de la diferencia anterior comentada (COINCIDIR(9,9E+307;B3:M3;1)-5)

El uso de este cero o de la diferencia, según el caso, aplicará como argumento 'columnas' de la función DESREF.
Recordemos la sintáxis:
DESREF(ancla, filas, columnas, [alto], [ancho])

Es decir, este valor condicionado, nos pautará el inicio del rango que estamos construyendo con DESREF.

El resto de la función DESREF está claro:
DESREF(B3;0;SI(CONTARA(B3:M3)<5;0;COINCIDIR(9,9E+307;B3:M3;1)-5);1;5)

El primer argumento, la celda B3, indica desde donde empieza a contar...el ancla.
Desde B3 me desplazo 0 filas hacia arriba o hacia abajo (esto es, me quedo en la fila 3).
Y desde B3 me desplazo el resultado del condicional (el cero o el resultado de la resta) hacia la derecha (esto es, columnas B, C, D, E, ..., M).
Los dos últimos argumentos empleados nos determinan el alto y ancho del rango variable que estamos generando.
El cuarto argumento, valor 1, nos dice que el rango tendrá una fila de alto; mientras que el quinto argumento nos marca el ancho del rango, en el ejercicio, cinco columnas (las requeridas en el planteamiento).

El último paso es aplicar el cálculo que necesitemos sobre dicho rango variable generado con DESREF; en el ejemplo la función SUMA.

Seguir leyendo...

Condicionales Personalizados con Power Query

Al hilo de la entrada anterior, donde hablamos de la funcionalidad de Añadir una columna condicional dentro de una consulta de Power Query, aprenderemos a generar un condicional más elaborado, con condiciones múltiples.
Partimos de nuestro mismo origen de datos, donde hemos creado nuestra Consulta:

haz clic en la imagen

Accedemos al menú de Agregar Columna > grupo General > botón Columna personalizada,
lo que nos abre la ventana de la imagen anterior, donde escribiremos:
= if [Producto]="Pdto01" then "1,23"
else if [Producto]="Pdto02" then "2,34"
else if [Producto]="Pdto03" then "3,45"
else "0"
código que replicaría la funcionalidad de Columna condicional que vimos en el post anterior.
Esto sin embargo, tiene ventajas añadidas, ya que podemos construir condicionales más complejos o compuestos de múltiples variables (empleando los operadores lógicos OR o bien AND).

Un ejemplo, en caso de que el comercial sea el 'AAA' y su zona de venta sea 'SUR' aplicamos un porcentaje de comisión del 5%, si el comercial es el 'AAA' y la zona es 'NORTE' el 10%. Al resto de comerciales, independientemente de su zona de venta, aplicamos una comisión del 1%.
Esta sería la fórmula a crear:
=if [Comercial]="AAA" then
     if [Zona]="Sur" then "0,05"
     else if [Zona]="Norte" then "0,10"
     else "0"
else "0,01"

haz clic en la imagen

O también, empleando los operadores lógicos y un orden concreto de ejecución, la fórmula:
=if [Comercial]="AAA" and [Zona]="Sur" then "0,05"
else if [Comercial]="AAA" and [Zona]="Norte" then "0,10"
else if [Comercial]="AAA" then "0"
else "0,01"

Con igual resultado que la anterior:

haz clic en la imagen

Como podemos comprobar, personalizar nuestras columnas, nos aporta una flexibilidad infinitamente mayor que la simple 'Columna condicional'.

No olvides cambiar el tipo de datos de nuestras columnas a Número decimal para trabajarlos fácilmente como números...

Seguir leyendo...

El Condicional con Power Query en Excel

Aprenderemos hoy el uso de una última actualización del complemento de moda de Excel: La columna condicional con Power Query.
Esta utilidad, como veremos nos permitirá utilizar, dentro de nuestras consultas (de una manera bien simple), la estructura condicional:
SI condición ENTONCES resultado si es verdadero DE LO CONTRARIO resultado si es falso

Veamos paso a paso su sencilla configuración.
A partir de un origen, por ejemplo de tabla, creamos una consulta:

haz clic en la imagen

Una vez creada la consulta procedemos a añadirle una nueva columna condicional desde el menú Agregar Columna > grupo General > botón Columna condicional.
Ver imagen anterior.

Sea abrirá una ventana que nos permite fácilmente añadir las condiciones y los resultados según sean ciertos o no.

En nuestro ejemplo he configurado varias posibilidades según sea un producto u otro, sin olvidar el caso de que no sea ninguno de los tres productos indicados (caso DE LO CONTRARIO).

Una recomendación para este o cualquier otro caso de columna personalizada, cuando trabajemos con números, es seleccionar la columna en cuestión y desde el menú Inicio > grupo Transformar > botón Tipo de datos: marcar como Número decimal

Ya podemos devolver los datos a nuestra hoja de cálculo, si es lo que necesitamos, mediante el botón de Cerrar y Cargar.

Seguir leyendo...

VBA: El control ListView en Excel

Un usuario preguntaba por la posibilidad de asignar un color a un elemento de un ListBox de acuerdo a un criterio.
Y aunque esto no parece posible, rebuscando entre la documentación de MSDN encontré el control ListView que sí lo permitirá.

El comentario del usuario:

[...]Tengo un LISTBOX en un FORM con una data de 4 columnas que obtengo de una hoja de calculo, hasta ahí todo bien.  Lo que deseo es resaltar ciertos registros según el valor de la cuarta columna, algo así como formato condicional, si en la cuarta columna dice "SI" que se resalte toda la linea del LISTBOX.  Esto es posible o de que manera podría lograrlo??  [...]

Lo primero es activar el control adicional entre los controles disponibles del UserForm:
Microsoft ListView Control, version 6.0

Así pues ya podemos incorporar a un UserForm nuestro control ListView:

Abrimos la ventana de código de nuestro formulario 'UserForm1' y añadimos el siguiente código asociado al evento UserForm_Initialize:

Private Sub UserForm_Initialize()
Dim FilaIni As Long
Dim UFila As Long
Dim pos As Long
Dim lv_item As Long 'número del item en el ListView
Dim fila As Long

Dim Item As ListItem
'damos valores al inicio y fin de los datos
FilaIni = 2: UFila = 11

pos = 2
lv_item = 1
With ListView1
     'esta propiedad indica que tendremos encabezados
    .View = lvwReport
     'definimos cuáles serán los encabezados
    With .ColumnHeaders
        .Clear  'primero limpiamos..
        'y leugo añadimos Nombre del campo y su ancho
        '.Add(index, key, text, width, alignment, icon)
        .Add Text:="Fecha", Width:=60
        .Add Text:="Región", Width:=60
        .Add Text:="Unidades", Width:=60
        .Add Text:="Control", Width:=40
    End With
    'dejamos a la vista los encabezados
    .HideColumnHeaders = False
    'damos aspecto 3d
    .Appearance = cc3D
    'habilitamos que al marcar un elemento se selecciona la fila entera
    .FullRowSelect = True
    
    'recorremos la hoja de cálculo
    'donde están los datos.. y los cargamos al ListView
    For fila = FilaIni To UFila
        If Cells(pos, "D").Value = "Sí" Then
            Set Item = .ListItems.Add(Text:=Cells(pos, "A").Value)
            Item.ListSubItems.Add Text:=Cells(pos, "B").Value
            Item.ListSubItems.Add Text:=Cells(pos, "C").Value
            Item.ListSubItems.Add Text:=Cells(pos, "D").Value
            'damos color a la fila
            Item.ForeColor = vbBlue
            For x = 1 To Item.ListSubItems.Count
                Item.ListSubItems.Item(x).ForeColor = vbBlue
            Next x
        Else
            Set Item = .ListItems.Add(Text:=Cells(pos, "A").Value)
            Item.ListSubItems.Add Text:=Cells(pos, "B").Value
            Item.ListSubItems.Add Text:=Cells(pos, "C").Value
            Item.ListSubItems.Add Text:=Cells(pos, "D").Value
        End If
        lv_item = lv_item + 1
        pos = pos + 1
    Next fila
End With
End Sub

A partir de los datos volcados en el rango A1:D11 de nuestra hoja de trabajo, si lanzamos nuestro formulario podemos comprobar el éxito de nuestro procedimiento

Seguir leyendo...

Curiosidades sobre la Validación de datos

Al hilo de la entrada anterior me he animado a exponer algunas curiosidades de esta herramienta tan extendida entre los usuarios de Excel: la Validación de datos.

Respecto a la Validación de datos tipo lista...
1- NO se puede modificar ni la fuente ni el número máximo de elementos mostrados (ocho elementos!!).

2- OJO, por que al reducir/aumentar el zoom de la hoja, el listado y los elementos en eél desplegados disminuyen/aumentan su tamaño...pudiendo hacerse ilegibles.

3- Para mostrar el listado todos utilizamos el ratón, pulsando el 'botoncito' del desplegable.
Otra posibilidad es presionar, sobre la celda con la validación, la combinación de teclas:
Alt + Flecha abajo
Una vez desplegado, por cualquiera de las formas, es posible moverse por el listado con las flechas del cursor.
Obviamente es posible emplear las teclas de Inicio y Fin, o incluso (para listas suficientemente largas) las teclas de avance y retroceso página (RePag y AvPag)

4- Ocultar/Mostrar el botón desplegable.
La opción más sencilla es desde la configuración de la validación marcar/desmarcar el check: Celda con lista desplegable

Otro aspecto relevante de este botón (en realidad Excel lo trata como un objeto) es que en ocasiones (sobre todo en la versión Excel 2013), que muchas veces 'perdíamos' de la vista ese desplegable.
Normalmente era o es suficiente con cambiar de hoja, o de aplicación... pero otras veces había que tomar medidas más 'severas', como por ejemplo, presionar:
Ctrl+6
Esta muestra u oculta TODOS los objetos del libro de trabajo, al ser este desplegable de nuestra validación un objeto, se veía afectado y solía aparecer...

También es posible gestionar esta última posibilidad (mostrar/ocultar objetos) desde las Opciones de Excel > Avanzadas > sección Mostrar opciones para este libro > Para objetos, mostrar: [Todo o Nada]

5- Maýusculas y Minúsculas.
Cuando creamos nuestra lista directamente en el campo editable 'Origen' de la configuración, la regla de validación se vuelve sensible a las mayúsculas/minúsculas.
Por ejemplo, de nuestra lista personalizada: 'Sí; No; Ns/Nc'

Si tratamos de escribir en nuestra celda validada uno de esos valores sin respetar la condición de mayúscula o minúscula, saltará el error:

Seguir leyendo...

Validación de datos en Excel..

Un cuestión que se me ha planteado en algunas ocasiones, relacionada con la Validación de datos, es el uso de la opción de Omitir blancos.
De hecho hace ya un tiempo publiqué algo al respecto en este post.

Por ejemplo, una usuaria planteaba lo siguiente:

[...]estoy tratando de validar que se tenga un contenido específico en una celda (si está en blanco no debería dejar). Tengo esto: =Y(ESTEXTO(H22);H22="Supervisad") en validación de datos personalizado, pero por alguna razón si H22 está vacía, me deja copiar igual. Sabes que puede ser?, he intentado con ESBLANCO(), ESNOTEXTO() y H22<>"" pero ninguna me funciona[...]

Lo que trataremos en este artículo es refrescar el uso de la opción de Omitir blancos combinándolo con la personalización de nuestra validación.

Recordemos que:
- Si tenemos marcada la opción 'Omitir blancos' entonces permitimos escribir cualquier valor en la celda validada.
- Si la opción 'Omitir blancos' está desactivada entonces únicamente se permite un valor NO vacío en la celda.

Es una opción algo confusa, ya que se puede interpretar justo al contrario de como actúa.

Resolvamos la cuestión de la lectora.

Generamos sobre nuestra celda una Validación de datos personalizada e incluimos la siguiente fórmula:
=Y($B$3="Supervisad";NO(ESBLANCO($B$3)))

Si como primera posibilidad, dejamos marcada la opción de 'Omitir blancos', y probamos a escribir en nuestra celda validada C3 (dejando vacía B3), y a pesar de la fórmula personalizada en la su validación, comprobaremos sí es posible escribir!!.
Esto es, se incumple la fórmula, y aún así, se nos permite escribir un valor en la celda C3.
Podría parecer que la exigencia formulada de que B3 no sea 'blanco' NO(ESBLANCO($B$3)) bastaría.

Probemos lo siguiente, misma validación, misma celda y misma condición de partida, pero desmarcamos la opción 'Omitir blancos'.
Tras Aceptar probamos a introducir un dato en C3 dejando B3 vacío y...

Ahora sí conseguimos el objetivo de nuestra lectora. Únicamente cuando en B3 tenemos escrito 'Supervisad' podremos escribir algo en C3...

La lectura, por tanto de 'Omitir blancos' es que al estar desmarcada evita el trabajo sobre celdas vacías.

Un importante recordatorio final. Esta herramienta de Validación de datos en Excel, en cualquiera de sus formas, solo controla la acción de escribir en esa celda... NUNCA acciones posteriores de borrado, pegado, etc...

Seguir leyendo...

VBA: Encontrar Celdas No Vacías

Hoy veremos una forma de localizar las celdas NO vacías que se encuentren en nuestro rango, y en cualquier posición que nos interese.

Construiremos una sencilla función personalizada con VBA (una UDF) que recorrerá nuestro rango, guardándose las posiciones de las celdas vacías, para luego retornarlas a petición.

Nuestro procedimiento Function lo insertamos en un módulo estándar de nuestro proyecto de VBA.

Function PosVacia(rng As Range, pos As Long) As String
Application.Volatile
Dim ArrVacias() As Variant
vacias = Application.WorksheetFunction.CountBlank(rng)
'redefinimos la matriz
ReDim ArrVacias(1 To vacias) As Variant

'recorremos las celdas marcadas (las vacías)
x = 1
For Each celda In rng
    'cuando la celdas esté vacía/sin dato
    'entonces la cargamos a nuestra matriz
    If celda.value = "" Then
        ArrVacias(x) = celda.Address
        x = x + 1
    End If
Next celda

'devolvemos el valor a la función
PosVacia = ArrVacias(pos)
End Function

Una alternativa con un procedimiento Sub, sería empleando el método .SpecialCells, en concreto con el parámetro:
xlCellTypeBlanks

Vemos cómo quedaría.

Sub PosicionVacia()
pos = 2     'posición deseada
Set rng = Range("B2:B13")   'rango donde localizar las celdas vacías.
Dim ArrVacias() As Variant
Dim vacias As Range
Set vacias = rng.SpecialCells(xlCellTypeBlanks)

'redefinimos la matriz
ReDim ArrVacias(1 To vacias.Count) As Variant
'recorremos las celdas marcadas (las vacías)
x = 0
For Each celda In vacias
        'entonces la cargamos a nuestra matriz
    x = x + 1
    ArrVacias(x) = celda.Address
Next celda
'devolvemos la dirección de la celda con un MsgBox
MsgBox ArrVacias(pos)
End Sub

Esto me lleva, automáticamente a construir una función, variante de la primera, que sirva para localizar cualquier posición de cualquier valor dentro de un rango.

Así un nuevo procedimiento Function insertado en un módulo estándar de nuestro proyecto de VBA.

Function PosCelda(buscado As Variant, rng As Range, pos As Long) As String
Application.Volatile
Dim ArrCeldas() As Variant
contador = Application.WorksheetFunction.CountIf(rng, buscado)
'redefinimos la matriz
ReDim ArrCeldas(1 To contador) As Variant

'recorremos las celdas marcadas
x = 1
For Each celda In rng
    'cuando la celdas coincidan con el valor buscado
    'entonces la cargamos a nuestra matriz
    If celda.Value = buscado Then
        ArrCeldas(x) = celda.Address
        x = x + 1
    End If
Next celda

'devolvemos el valor a la función
PosCelda = ArrCeldas(pos)
End Function

Vemos que es muy similar al primero, pero ahora controlamos cuál es el valor buscado... para así recuperar la dirección de ese valor que se encuentre en la posición requerida...

Seguir leyendo...