VBA: Los decimales y los filtros avanzados con macros en Excel.

Hoy hablaré del tratamiento que debemos dar a los decimales si queremos trabajar con ellos en nuestras macros. Y a modo de ejemplo, propondré un ejercicio de filtro avanzado con macros, ya que es una cuestión bastante reiterativa planteada por los lectores del blog.

Lo que vamos a ver es cómo debemos tratar los valores decimales si queremos que nuestro código VBA lo entienda, y es que sabemos (seguro lo hemos sufrido alguna vez) en nuestras líneas de programación los valores decimales no emplean nuestro mismo sistema de puntuación (miles y decimales con puntos y comas respectivamente), ya que normalmente en nuestras hojas de cálculo ponemos los puntos para miles y las comas para decimales, justo al contrario que en las sentencias de código VBA.
Nuestro trabajo consistirá entonces en convertir y hacer comprensible nuestro decimal a nuestra macro. El ejercicio consite entonces en aplicar un Filtro avanzado sobre una base de datos, para obtener los registros superiores a un valor decimal....
Ojo, siempre estamos hablando al trabajar con macros!!! (no existe ningún problema o incompatibilidad al trabajar normalmente sobre la hoja de cálculo).
Veamos nuestra base de datos en el rango A1:A16, y el Rango de criterios en G1:G2, a ambos rangos les hemos asignado un Nombre definido:
Criterios =Hoja1!$G$1:$G$2
datos =Hoja1!$A$1:$E$16
que usaremos en nuestras macros.

El objetivo de nuestra macro será señalar una celda del campo 'Precio total' para que a continuación muestre todos aquellos registros de la base de dato que cumplan la condición de ser mayores estrictamente al valor indicado. Como vemos, esos importes de 'Precio Total' tienen decimales, lo que normalmente provocaría un error en el filtrado, no reconociendo el importe y sus decimales!!!.

Accederemos al Editor de VBA (alt+F11) e insertaremos un Módulo, y dentro de él el siguiente código, como primera versión:

Sub Filtro1v1()
Dim criteria As Range
Dim precio As String

'preguntamos celda donde esté el valor-Preci- sobre el que filtrar
Set valor = Application.InputBox("Valor precio", Default:="$G$2", Type:=8)
'componemos con el valor y el operador de Mayor el importe del precio
'en este caso lo importante es el Reemplazamiento de la coma por el punto
'para convertir en decimal en algo entendible por nuestro código
precio = ">" & (Replace(valor, ",", "."))
'llevamos la composición final (el decimal con punto y no con coma) a la celda G2
Sheets("Hoja1").Range("$G$2").Value = precio

'aplicamos el filtro avanzado
Set criteria = Sheets("Hoja1").Range("G1:G2")
Range("datos").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range(criteria.Address) _
        , CopyToRange:=Sheets("Hoja1").Range("$G$5"), Unique:=False

Set criteria = Nothing
Set valor = Nothing
End Sub

En este primer código conseguimos reemplazar los signos de puntuación de miles y decimales, con la función de VBA Replace; sencillamente tratamos el valor obtenido como un texto y reemplzamos en el el signo decimal de puntuación por el que emplea el código VBA, es decir, la coma por el punto. Luego, simplemente, llevamos el texto alfanumérico a la celda G2, y aplicamos el filtro avanzado desde la macro, obteniendo el resultado esperado.
Podemos verlo en el video siguiente:

El siguiente código actúa de igual forma, excepto por el método empleado para convertir el decimal, de una manera más técnica, usando la propiedad .Formula, ya que esta propiedad tiene la virtud de representar el valor o fórmula de la celda en el lenguaje de la macro.

Accederemos al Editor de VBA (alt+F11) e insertaremos en el mismo Módulo de antes (u otro diferente a elegir) el siguiente código, como segunda versión de nuestra macro:

Sub Filtro1v2()
Dim criteria As Range
Dim precio As String

'preguntamos celda donde esté el valor-Preci- sobre el que filtrar
Set valor = Application.InputBox("Valor precio", Default:="$G$2", Type:=8)
'componemos con el valor y el operador de Mayor el importe del precio
'en este caso lo importante es que aplicamos sobre el valor la propiedad .Formula
'que devuelve o establece un valor de tipo Variant que representa
'la fórmula del objeto en notación de estilo A1 y en el lenguaje de la macro;
'es decir, para convertir en decimal en algo entendible por nuestro código.
precio = Range(valor.Address).Formula
precio = ">" & precio
'llevamos la composición final (el decimal con punto y no con coma) a la celda G2
Sheets("Hoja1").Range("$G$2").Value = precio

'aplicamos el filtro avanzado
Set criteria = Sheets("Hoja1").Range("G1:G2")
Range("datos").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range(criteria.Address) _
        , CopyToRange:=Sheets("Hoja1").Range("$G$5"), Unique:=False

Set criteria = Nothing
Set valor = Nothing
End Sub

Tras realizar la prueba se comprueba que con ambas versiones, el resultado es el mismo, el que esperábamos, la macro ha 'comprendido' y ejecutado adecuadamente el proceso con el decimal.
Sin este tratamiento nuestas macros fallarían de igual forma que si en nuestras hojas de cálculo emplearamos la puntuación cambiada, es decir, puntos para decimales y comas para miles...

Algo más sobre Celdas ocultas y vacías en un gráfico de Excel.

Dias atrás un lector planteó una interesante cuestión, inicialmente la cuestión se solucionaba configurando adecuadamente cómo mostrar las Celdas vacías, pero en un segundo comentario proponía un nuevo planteamiento:

...viendo el caso en que no deje en blanco dicho campo [en un rango de datos para construir un gráfico], es decir simplemente con que le ponga un guion lo asume como si fuese un 0 y obviamente la linea se va hasta abajo, como se podria solucionar eso y ya se que probablemnte sea esa una pregunta tonta pues al tener un dato obviamente lo asumira como eso, pero como podria hacer para si quisiese que el campo no quede vacio por una cuestion de presentacion igual el grafico no lo asuma como un dato...

La idea es por tanto que por un lado queremos que en el origen de datos de nuestro gráfico uno o varios valores sean tomados como vacíos (a la hora de configurar las Celdas vacías u ocultas), pero por otro lado, por un tema de presentación, esas celdas NO pueden estarlo, por lo que completaremos dichas celdas con un guión (por ejemplo).
El problema, como explicaba el lector es que si el gráfico se contruye directamente sobre ese origen de datos, y en él, una de las celdas tiene un '-' (guión u otro caracter), el grafico NO lo toma como vacío y no aplica la utilidad, llevando el gráfico al cero.
Importante, en nuestro gráfico, tenemos activa la opción Conectar puntos de datos con línea en la configuración de Celdas ocultas y vacías, a activar desde la venta de Selección de datos de nuestro gráfico:

Veamos en la imagen nuestro gráfico y cómo actúa con un valor vacío o relleno con otro caracter:

Como hemos observado al incluir cualquier valor (numérico o no) en la celda B3 el gráfico toma un valor cero para ese punto. Nuestro objetivo consiste que al incluir un caracter (por ejemplo un guión medio '-') el gráfico lo tome como vacío e interpole o conecte los datos con una línea.

El trabajo para conseguir nuestro objetivo consistirá en una combinación de técnicas. La primera asignar a un Nombre definido una fórmula matricial.

Fórmula autocompletar en Excel.

Hoy toca una entrada sencilla, pero muy práctica.
Probablemente emplees a diario esta funcionalidad sin darle mayor importancia, pero seguro que si la eliminasen de pronto, la echarías en falta rápidamente.
Estoy hablando de la opción de Fórmula autocompletar, es decir, de la aparición de los listados de funciones y las etiquetas descriptivas de las funciones de Excel que aparecen según las escribimos en nuestras celdas.
Veamos un pequeño video donde aparece ese listado automático (autocompletado) según introducimos el nombre de la función, hasta que aparece la etiqueta descriptiva de la función en concreto:

Controlar esta opción es sencillo, basta acceder a la ficha Archivo > botón de Opciones, y dentro del menú de Opciones de Excel entraremos en Fórmulas, buscaremos la sección de Trabajando con fórmulas y desmarcaremos/marcaremos la opción de Fórmula autocompletar:

Esto bastará para trabajar con esta útil funcionalidad.