Ampliando y mejorando el objeto PivotTable en VBA/Excel

Estándar

Hace poco estuve trabajando a fondo en un proyecto que calcularía a futuro (3 meses) los días de inventario para varias divisiones, categorías, mg y sku por cliente. Para empezar las bases de datos con los que tendría que hacer los cálculos (inventarios, sell in, sell out) eran bases de datos que superaban los 500 mil registros, tuve que verme en la necesidad de previamente compactar la información de manera manual usando tablas dinámicas, luego supuse que tendría que crear varios resúmenes de datos, ya sea por cliente, categoría, etc… así que pensé pudiera hacerlo en Access (y quizás hubiese sido buena alternativa), desgraciadamente el prototipo tenía que estar listo en el menor tiempo posible, así que decidí hacerlo en VBA/Excel sobre todo por tenía que manejar acumulados entre meses y no se hacer ese tipo de consultas en Access (ya aprenderé).

Así que… resúmenes de datos, eh? Nada mejor para resumir datos que una tabla dinámica! Pero no estaba muy familiarizado con los métodos y propiedades así que decidí crear un objeto para ayudarme en el proyecto, este objeto tendría que ayudarme a diseccionar los datos, es decir de un resumen obtenido, quedarme con cierta parte del mismo.

Vaya solo cree algunos pocos métodos y propiedades y eso basto para facilitarme el trabajo, pero aún me falta agregar más funcionalidad a mi clase, por el momento les comparto la misma.

Les comparto el archivo de ejemplo.

Saludos!

Anuncios

Accediendo a .Net Framework desde VBA

Estándar

Siempre estoy buscando nuevas maneras de ampliar las posibilidades con VBA, ya sea combinándolo con otros lenguajes o herramientas, y en mi búsqueda he dado con una maravilla que será de gran ayuda en lo que deseo y estoy seguro que para muchos otro también. Había visto desde crear dll’s en visual studio, complementos como ExcelDNA, entre otras, pero esta última se me hace más práctica y con mayores posibilidades:

¡Hola, chicos del scripting!

Ahí se muestra como poder acceder a las estructuras de datos que brinda el .Net Framework lo cual supone nuevas posibilidades en el desarrollo desde VBA, el único inconveniente a primera vista es que hay que contar con el .Net Framework instalado en el equipo. El mío ya lo tenía y funciono a la primera.

Tal como se menciona en el artículo, donde se ve un ejemplo del uso del Objeto ArrayList, el cual pudiéramos comparar a usar una matrix en VBA, pero con esteroides, más poderosa y fácil de usar:

¿Es útil ArrayList para los generadores de scripts? Digámoslo así: después de ver la utilidad de arraylist en .NET Framework, no estamos seguros de por qué quería usar una matriz VBScript integrada, al menos no para listas simples de elementos como las empleadas aquí.

Con las estructuras de datos de .Net Framework VBA vuelve a tomar nuevas y mas posibilidades en mi trabajo diario.

Creación y uso de Listas en VBA/Excel

Estándar

Para todos aquellos que han programado en Python, sabrán la gran ventaja que se obtiene al utilizar Listas o quizás para aquellos que han probado Ruby y han utilizado Arrays, ambos… Listas y Arrays son lo mismo. Son colecciones ordenadas de elementos a las cuales se accede a través de un índice y con dichos objetos la programación se simplifica enormemente, ya que poseen métodos y propiedades que facilitan la labor del programador. Desgraciadamente para todos aquellos que programamos en VBA/Excel tendremos que conformarnos al usar simples matrices las cuales nos brindan pocas posibilidades al respecto, ya que las mismas no son objetos.

Mi fascinación respecto a estos objetos, me llevo a la tarea de tratar de emularlos en VBA/Excel con la creación de una clase llamada clsList la cual incorpora algunos elementos Python y otros de Ruby.

Espero que esta clase sea de gran utilidad para todos aquellos programadores de VBA/Excel que están en busca de herramientas que faciliten su labor diario y al mismo tiempo recibir comentarios y sugerencias para ampliar y mejorar la clase.

Comparto con ustedes el siguiente enlace, donde podrán descargar el módulo de clase y un libro con ejemplos sobre el funcionamiento de la misma.

Clase Lista

clsLog – Registrar el funcionamiento y operaciones que realiza una macro

Estándar

Hace unas semanas me encontraba elaborando la macro para recolectar los datos de varios libros en unos solo. Había casos en los que debido a la mala disposición de los datos, mi macro no sería capaz de tomar los datos. Entonces, que hacer para informar de ello al usuario?

Podría imprimir un mensaje en pantalla indicando el archivo y el tipo de error y detener la ejecución de la macro para que el usuario hiciera las modificaciones de forma manual y ejecutara nuevamente la macro. Este proceso no se me hizo tan fluido, así que me vino a la mente que en un anterior empleo había un sistema que llevaba un Log (bitácora) de todos los cambios y errores en el mismo, así que puse manos a la obra y programe un modulo de clase llamado clsLog con lo cual podría emular la funcionalidad de dicho sistema.

Una vez contando con dicho modulo de clase el usuario ejecutaba la macro y si la macro encontraba algún error en cualquier archivo y por lo mismo no incorporaba los datos, se escribían las observaciones al archivo Log, con esto la ejecución era más fluida. Al terminar la macro, el usuario revisaba el archivo Log para ver si había archivos que tenían problemas y si era así corregirlos todos para ya solamente ejecutar la macro una vez mas.

La clase clsLog cuenta:

  • AddData              – Se encarga de agregar una cadena de texto al Log
  • NewLine             – Agrega un salto de línea al Log
  • Save                      – Escribe el contenido del Log en un archivo
  • Path                      – Establece la ruta de donde se escribirá el Log, en caso de no proporcionar una ruta y nombre, el Log se escribirá en la ruta del actual y con un nombre genérico como “Log-(ddmmyyyy)”

 

Dejo un archivo de ejemplo que incluye la clase clsLog, recuerden que solo deben importarla a sus proyectos con lo cual tendrán todas sus ventajas a su alcance.