jueves, 23 de mayo de 2013

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.

VBA: Los decimales y los filtros avanzados con 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:

VBA: Los decimales y los filtros avanzados con macros.



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...

4 comentarios:

  1. Buenas tardes,

    Tengo creado un archivo de Excel 2010 con una hoja de Datos Base (listado de tareas). En esta hoja dependiendo el valor de una celda A, la columna "Status" define si el estado es Complete, In Progress, NO RUN, etc.

    Luego tengo una hoja con la tabla pivote donde para cada tarea por locación, cuenta la cantidad de tareas Complete, In Progress, No Run, etc.

    El problema es que no siempre tengo cada tipo de estado (es decir, a veces en un listado, no hay tareas "In Progress"). En este caso, como la columna de status de la hoja de Datos Base no tiene ningún In Progress, al actualizar la tabla pivote la columna donde debería estar In Progress (mostrando 0 si no hay) simplemente desaparece.

    Hay alguna forma de setear los encabezados de forma estática (ejemplo: columna A para Complete, columna B para In Progress y columna C para NO RUN) de manera que al actualizar la tabla pivote si no encuentra tareas en alguno de los estados me agregue un cero -o la deje vacia- pero no me elimine la columna?

    No he podido adjuntar el ejemplo porque no sé por donde hacerlo. Si lo necesitas, tengo un ejemplo armado como para subir.

    Desde ya, muchas gracias por la ayuda!
    Saludos,
    Lucia

    ResponderEliminar
    Respuestas
    1. Hola Lucía,
      habría una manera (un poco laboriosa si tuvieras muchos 'Status'), consiste ne crear tantos elementos calculados en el campo 'Status' como elementos (Complete, In Progress, NO RUN, etc) tuvieras, luego aplicando el filtro del campo deja sólo visibles tus elementos calculados nuevos, ocultando los originales.
      Así siempre te aparecerán todos los Estados, con ceros para donde no haya valores...

      Es lo primero que se me ocurre...
      Slds cordiales

      Eliminar
  2. Hola. Tengo una duda con Decimales en macros pero no se si este es el foro. En fin, que tengo que cambiar el tamaño de una imagen tras insertarla. Tengo diferentes imágenes y para mantener el ratio lo que hago es:
    coef1=ancho de la celda/ancho de la imagen
    coef2=alto de la celda/alto de la imagen
    Coef=min(coef1,coef2)

    Imagen.widht=ancho*coef
    Imagen.height=alto*coef

    El problema es que al calcular el coef me sale como , en los decimales.
    Si uso replace(anchofinal,",",".") es string y no vale (donde anchofinal=ancho*coef).

    De hecho lo estoy haciendo en ppt. Hay alguna manera de hacerlo?

    Mil gracias. Consulto bastante el foro porque tenéis muchas soluciones.

    ResponderEliminar
    Respuestas
    1. Hola Antonio,
      el asunto es que estas propiedades .Width y .Height trabajan en puntos (1 punto = 1/72 pulgadas, y 1 in = 2,54 cm aprox).

      El caso es que para trabajar correctamente debes convertir tus medidas a puntos.
      Por otro lado debe saber que los decimales en VBA funciona siempre con el punto, y sus cálculos los hace sobre este separador...
      Otra cosa es que el dato te lo muestre en la hoja o la ventana inmediato o en otro sitio con el separador coma

      Puedes ver un ejemplo similar al tuyo en
      http://excelforo.blogspot.com.es/2012/05/vba-centrar-una-imagen-en-el-interior.html

      Slds

      Eliminar